Search Results

Search found 1214 results on 49 pages for 'tomaz tsql'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 13/49 | < Previous Page | 9 10 11 12 13 14 15 16 17 18 19 20  | Next Page >

  • SQL Server 2005/2008: Identify current user

    - by Torben H.
    Hello I have a web application, which is using a SQL Server 2005 database. My problem is, that the application has no role management. So the application always accesses the database with one default user. But now I have to save and access a value only for the current user. Is there any way to do this? Maybe something like a session on the web server? The best way would be, if there is any possibility to access the current session id of the web server from T-SQL. Do anyone understand my problem? :) Torben

    Read the article

  • Concatenation of fields in different rows

    - by spender
    I'm stuck on an aggregation problem that I can't get to the bottom of. I have some data which is best summarized as follows id |phraseId|seqNum|word ========================= 1 |1 |1 |hello 2 |1 |2 |world 3 |2 |1 |black 4 |2 |2 |and 5 |2 |3 |white I'd like a query that gives back the following data: phraseId|completePhrase ======================== 1 |hello world 2 |black and white Anyone?

    Read the article

  • INSTEAD OF triggers do not support direct recursion

    - by senzacionale
    ALTER TRIGGER [dbo].[TRG_DeleteUser] ON [dbo].[Users] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON DECLARE @AspNetUserGuid UniqueIdentifier DECLARE @UserId NVARCHAR(36) BEGIN SET @AspNetUserGuid = (SELECT AspNetUserGuid FROM deleted) SET @UserId = (SELECT UserId FROM dbo.Users WHERE AspNetUserGuid = @AspNetUserGuid) IF @AspNetUserGuid IS NOT NULL AND @UserId IS NOT NULL BEGIN EXECUTE [dbo].UsersDelete @AspNetUserGuid, @UserId END END SET NOCOUNT OFF END problem is here: EXECUTE [dbo].UsersDelete @AspNetUserGuid, @UserId i need to call triger before row is actually deleted-

    Read the article

  • Address Match Key Algorithm

    - by sestocker
    I have a list of addresses in two separate tables that are slightly off that I need to be able to match. For example, the same address can be entered in multiple ways: 110 Test St 110 Test St. 110 Test Street Although simple, you can imagine the situation in more complex scenerios. I am trying to develop a simple algorithm that will be able to match the above addresses as a key. For example. the key might be "11TEST" - first two of 110, first two of Test and first two of street variant. A full match key would also include first 5 of the zipcode as well so in the above example, the full key might look like "11TEST44680". I am looking for ideas for an effective algorithm or resources I can look at for considerations when developing this. Any ideas can be pseudo code or in your language of choice. We are only concerned with US addresses. In fact, we are only looking at addresses from 250 zip codes from Ohio and Michigan. We also do not have access to any postal software although would be open to ideas for cost effective solutions (it would essentially be a one time use). Please be mindful that this is an initial dump of data from a government source so suggestions of how users can clean it are helpful as I build out the application but I would love to have the best initial I possibly can by being able to match addresses as best as possible.

    Read the article

  • SQL Server stored procedures - update column based on variable name..?

    - by ClarkeyBoy
    Hi, I have a data driven site with many stored procedures. What I want to eventually be able to do is to say something like: For Each @variable in sproc inputs UPDATE @TableName SET @variable.toString = @variable Next I would like it to be able to accept any number of arguments. It will basically loop through all of the inputs and update the column with the name of the variable with the value of the variable - for example column "Name" would be updated with the value of @Name. I would like to basically have one stored procedure for updating and one for creating. However to do this I will need to be able to convert the actual name of a variable, not the value, to a string. Question 1: Is it possible to do this in T-SQL, and if so how? Question 2: Are there any major drawbacks to using something like this (like performance or CPU usage)? I know if a value is not valid then it will only prevent the update involving that variable and any subsequent ones, but all the data is validated in the vb.net code anyway so will always be valid on submitting to the database, and I will ensure that only variables where the column exists are able to be submitted. Many thanks in advance, Regards, Richard Clarke Edit: I know about using SQL strings and the risk of SQL injection attacks - I studied this a bit in my dissertation a few weeks ago. Basically the website uses an object oriented architecture. There are many classes - for example Product - which have many "Attributes" (I created my own class called Attribute, which has properties such as DataField, Name and Value where DataField is used to get or update data, Name is displayed on the administration frontend when creating or updating a Product and the Value, which may be displayed on the customer frontend, is set by the administrator. DataField is the field I will be using in the "UPDATE Blah SET @Field = @Value". I know this is probably confusing but its really complicated to explain - I have a really good understanding of the entire system in my head but I cant put it into words easily. Basically the structure is set up such that no user will be able to change the value of DataField or Name, but they can change Value. I think if I were to use dynamic parameterised SQL strings there will therefore be no risk of SQL injection attacks. I mean basically loop through all the attributes so that it ends up like: UPDATE Products SET [Name] = '@Name', Description = '@Description', Display = @Display Then loop through all the attributes again and add the parameter values - this will have the same effect as using stored procedures, right?? I dont mind adding to the page load time since this is mainly going to affect the administration frontend, and will marginly affect the customer frontend.

    Read the article

  • Problem counting item frequency on T-SQL

    - by Raúl Roa
    I'm trying to count the frequency of numbers from 1 to 100 on different fields of a table. Let's say I have the table "Results" with the following data: LottoId Winner Second Third --------- --------- --------- --------- 1 1 2 3 2 1 2 3 I'd like to be able to get the frequency per numbers. For that I'm using the following code: --Creating numbers temp table CREATE TABLE #Numbers( Number int) --Inserting the numbers into the temp table declare @counter int set @counter = 0 while @counter < 100 begin set @counter = @counter + 1 INSERT INTO #Numbers(Number) VALUES(@counter) end -- SELECT #Numbers.Number, Count(Results.Winner) as Winner,Count(Results.Second) as Second, Count(Results.Third) as Third FROM #Numbers LEFT JOIN Results ON #Numbers.Number = Results.Winner OR #Numbers.Number = Results.Second OR #Numbers.Number = Results.Third GROUP BY #Numbers.Number The problem is that the counts are repeating the same values for each number. In this particular case I'm getting the following result: Number Winner Second Third --------- --------- --------- --------- 1 2 2 2 2 2 2 2 3 2 2 2 ... When I should get this: Number Winner Second Third --------- --------- --------- --------- 1 2 0 0 2 0 2 0 3 0 0 2 ... What am I missing?

    Read the article

  • basic sql group by with percentage

    - by David in Dakota
    I have an issue and NO it is not homework, it's just a programmer who has been away from SQL for a long time having to solve a problem. I have the following table: create table students( studentid int identity(1,1), [name] varchar(200), [group] varchar(10), grade numeric(9,2) ) go The group is something arbitrary, assume it's the following "Group A", "Group B"... and so on. The grade is on a scale of 0 - 100. If there are 5 students in each group with grades randomly assigned, what is the best approach to getting the top 3 students (the top 80%) based on their grade? To be more concrete if I had the following: Ronald, Group A, 84.5 George H, Group A, 82.3 Bill, Group A, 92.0 George W, Group A, 45.5 Barack, Group A, 85.0 I'd get back Ronald, Bill, and Barack. I'd also need to do this over other groups.

    Read the article

  • MS SQL Index Scripting

    - by Fouad Masoud
    I'm using MS SQL 2008. Is it possible to create a script to loop over all tables in a database generating a set of index drop scripts and create scripts separately? What I have to do is drop all indexes on a set of databases to run a heavy data load process but then I want to re-enable all the indexes. I don't want to have to go through each table and script an index drop and then index create. Thanks!

    Read the article

  • NullPointerException with CallableStatement.getResultSet()

    - by Raj
    Hello, I have a stored proc in SQL Server 2005, which looks like the following (simplified) CREATE PROCEDURE FOO @PARAMS AS BEGIN -- STEP 1: POPULATE tmp_table DECLARE @tmp_table TABLE (...) INSERT INTO @tmp_table SELECT * FROM BAR -- STEP 2: USE @tmp_table FOR FINAL SELECT SELECT abc, pqr FROM BAZ JOIN @tmp_table ON some_criteria END When I run this proc from SQL Server Management Studio, things work fine. However, when I call the same proc from a Java program, using something like: cs = connection.prepareCall("exec proc ?,"); cs.setParam(...); rs = cs.getResultSet(); // BOOM - Null! while(rs.next()) {...} // NPE! I fail to understand why the first result set returned is NULL. Can someone explain this to me? As a workaround, if I check cs.getMoreResults() and if true, try another getResultSet() - THIS time it returns the proper result set. Any pointers please? (I'm using JTDS drivers, if it matters) Thanks, Raj

    Read the article

  • Can T-SQL function return user-defined table type?

    - by abatishchev
    I have my own type: CREATE TYPE MyType AS TABLE ( foo INT ) and a function receiving it as a parameter: CREATE FUNCTION Test ( @in MyType READONLY ) RETURNS @return MyType AS ... can it return MyType or only TABLE repeating MyType's structure: CREATE FUNCTION Test ( @in MyType READONLY ) RETURNS @return TABLE (foo INT) AS ... ?

    Read the article

  • Is it safe to use Select Top and Delete Top in sequence?

    - by Rob Nicholson
    I often write T-SQL loops that look like this While Exists (Select * From #MyTable) Begin Declare @ID int, @Word nvarchar(max) Select Top 1 @ID=ID, @Word=[Word] From #MyTable -- Do something -- Delete #MyTable Where ID=@ID End Works a treat but I noticed the new Delete Top function which would be useful when #MyTable is just a list of strings. In this case, would this work: While Exists (Select * From #MyTable) Begin Declare @Word nvarchar(max) Select Top 1 @Word=[Word] From #MyTable -- Do something -- Delete Top(1) #MyTable End Well yes, it works in my test script but is this safe? Will Select Top 1 and Delete Top(1) always refer to the same record or is Top a little more vague. Thanks, Rob.

    Read the article

  • Boolean 'NOT' in T-SQL not working on 'bit' datatype?

    - by Joannes Vermorel
    Trying to perform a single boolean NOT operation, it appears that under MS SQL Server 2005, the following block does not work DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = NOT @MyBoolean; SELECT @MyBoolean; Instead, I am getting more successful with DECLARE @MyBoolean bit; SET @MyBoolean = 0; SET @MyBoolean = 1 - @MyBoolean; SELECT @MyBoolean; Yet, this looks a bit a twisted way to express something as simple as a negation. Am I missing something?

    Read the article

  • SqlServer2008 - Can I Alter a Scalar Function while it is referenced in many places

    - by Casey C.
    We have a scalar function that returns a DateTime. It performs a couple of quick table selects to get its return value. This function is already in use throughout the database - in default constraints, stored procs, etc. I would like to change the implementation of the function (to remove the table hits and make it more efficient) but apparently I can't do that while it is referenced by other objects in the database. Will I actually need to update every object in the database that references it to remove the reference, update the function and then update all those objects to restore the reference to the function? Thanks for any insight you can give.

    Read the article

  • Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

    - by TheImirOfGroofunkistan
    I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

    Read the article

  • Is it possible to use ContainsTable to get results for more than one column?

    - by LockeCJ
    Consider the following table: People FirstName nvarchar(50) LastName nvarchar(50) Let's assume for the moment that this table has a full-text index on it for both columns. Let's suppose that I wanted to find all of the people named "John Smith" in this table. The following query seems like a perfectly rational way to accomplish this: SELECT * from People p INNER JOIN CONTAINSTABLE(People,*,'"John*" AND "Smith*"') Unfortunately, this will return no results, assuming that there is no record in the People table that contains both "John" and "Smith" in either the FirstName or LastName columns. It will not match a record with "John" in the FirstName column, and "Smith" in the LastName column, or vice-versa. My question is this: How does one accomplish what I'm trying to do above? Please consider that the example above is simplified. The real table I'm working with has ten columns and the input I'm receiving is a single string which is split up based on standard word breakers (space, dash, etc.)

    Read the article

  • Correct escaping of delimited identifers in SQL Server without using QUOTENAME

    - by Ross Bradbury
    Is there anything else that the code must do to sanitize identifiers (table, view, column) other than to wrap them in double quotation marks and "double up" and double quotation marks present in the identifier name? References would be appreciated. I have inherited a code base that has a custom object-relational mapping (ORM) system. SQL cannot be written in the application but the ORM must still eventually generate the SQL to send to the SQL Server. All identifiers are quoted with double quotation marks. string QuoteName(string identifier) { return "\" + identifier.Replace("\"", "\"\"") + "\""; } If I were building this dynamic SQL in SQL, I would use the built-in SQL Server QUOTENAME function: declare @identifier nvarchar(128); set @identifier = N'Client"; DROP TABLE [dbo].Client; --'; declare @delimitedIdentifier nvarchar(258); set @delimitedIdentifier = QUOTENAME(@identifier, '"'); print @delimitedIdentifier; -- "Client""; DROP TABLE [dbo].Client; --" I have not found any definitive documentation about how to escape quoted identifiers in SQL Server. I have found Delimited Identifiers (Database Engine) and I also saw this stackoverflow question about sanitizing. If it were to have to call the QUOTENAME function just to quote the identifiers that is a lot of traffic to SQL Server that should not be needed. The ORM seems to be pretty well thought out with regards to SQL Injection. It is in C# and predates the nHibernate port and Entity Framework etc. All user input is sent using ADO.NET SqlParameter objects, it is just the identifier names that I am concerned about in this question. This needs to work on SQL Server 2005 and 2008.

    Read the article

  • Sorting tree with other column in SQL Server 2008

    - by bodziec
    Hi, I have a table which implements a tree using hierarchyid column Sample data: People \ Girls \1\ Zoey \1\1\ Kate \1\2\ Monica \1\3\ Boys \2\ Mark \2\1\ David \2\2\ This is the order using hierarchyid column as sort column I would like to sort data using hierarchyid but also using name so it would look like this: People \ Boys \2\ David \2\2\ Mark \2\1\ Girls \1\ Kate \1\2\ Monica \1\3\ Zoey \1\1\ Is there a simple solution to do this? Czy da sie to zrobic w jednym zapytaniu sql ?

    Read the article

  • Inserting "null" (literally) in to a stored procedure parameter.

    - by Nazadus
    I'm trying to insert the word "Null" (literally) in to a parameter for a stored procedure. For some reason SqlServer seems to think I mean NULL and not "Null". If I do a check for IF @LastName IS NULL // Test: Do stuff Then it bypasses that because the parameter isn't null. But when I do: INSERT INTO Person (<params>) VALUES (<stuff here>, @LastName, <more stuff here>); // LastName is 'Null' It bombs out saying that LastName doesn't accept nulls. I would seriously hate to have this last name, but someone does... and it's bombing the application. We're using SubSonic 2.0 (yeah, it's fairly old but upgrading is painful) as our DAL and stepping through it, I see it does create the parameters properly (for what I can tell). I've tried creating a temp table to see if I could replicate it manually but it seems to work just fine. Here is the example I create: DECLARE @myval VARCHAR(50) SET @myval = 'Null' CREATE TABLE #mytable( name VARCHAR(50)) INSERT INTO #mytable VALUES (@myval) SELECT * FROM #mytable DROP table #mytable Any thoughts on how I can fix this?

    Read the article

  • How do I create a foreign key in SQL Server?

    - by mmattax
    I have never "hand coded" creation code for SQL Server and foreign key deceleration is seemingly different from SQL Server and Postgres...here is my sql so far: drop table exams; drop table question_bank; drop table anwser_bank; create table exams ( exam_id uniqueidentifier primary key, exam_name varchar(50), ); create table question_bank ( question_id uniqueidentifier primary key, question_exam_id uniqueidentifier not null, question_text varchar(1024) not null, question_point_value decimal, constraint question_exam_id foreign key references exams(exam_id) ); create table anwser_bank ( anwser_id uniqueidentifier primary key, anwser_question_id uniqueidentifier, anwser_text varchar(1024), anwser_is_correct bit ); when I run the query I get this error: Msg 8139, Level 16, State 0, Line 9 Number of referencing columns in foreign key differs from number of referenced columns, table 'question_bank'. Can you spot the error? thanks.

    Read the article

  • T-Sql Modify Insert SProc To Update If Exists.

    - by Goober
    Scenario I have a stored procedure written in T-Sql that I use to insert data into a table as XML. Since the data gets updated regularly, I want the rows to be updated if they already exist (Aside from when the application is first run, they will always exist). Question Below is the code of my Insert Sproc, however I cannot seem to workout the Update side of the stored procedure & would appreciate some help. CODE set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[INS_Curve] ( @Curve varchar(MAX) ) AS DECLARE @handle int exec sp_xml_preparedocument @handle OUTPUT, @Curve INSERT INTO CurveDB..tblCurve(LoadID,BusinessDate, Factor) SELECT LoadID,BusinessDate, Factor FROM OPENXML(@handle, 'NewDataSet/Table1',2) WITH( LoadID int, BusinessDate DateTime, Factor float ) exec sp_xml_removedocument @handle

    Read the article

  • Batch vs SQL statement

    - by AspOnMyNet
    a) A SQL statement is a single SQL command (for example, SELECT * FROM table1 or SET NOCOUNT ON). A batch on the other hand, is a number of SQL statements sent to the server for execution as a whole unit. The statements in the batch are compiled into a single execution plan. Batches are separated by the GO command So the only difference between SQL statement and a Batch is that each SQL statement is sent to server as a separate unit and thus is compiled separately from other SQL statements, while SQL statements in a Batch are compiled together? b) I assume one of major differences between a stored procedure and a Batch is that stored procedures are precompiled while Batches aren’t? thanx

    Read the article

  • Trigger Code on a table in my ERP Database

    - by David Stein
    My ERP Vendor has the following trigger on a table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[SOItem_DeleteCheck] ON [dbo].[soitem] FOR DELETE AS BEGIN DECLARE @RecCnt int, @LogInfo varchar(256) SET @RecCnt = (SELECT COUNT(*) FROM deleted) IF @RecCnt > 150 BEGIN RAISERROR (54010, 18, 1, 'SOItem') WITH LOG ROLLBACK TRANSACTION END SET @LogInfo = 'Deleting ' + LTRIM(STR(@RecCnt)) + ' Rows From SOItem' EXEC LogDeletes @LogInfo END GO This seems very inefficient to me. Doesn't select count(*) take longer than Count(specific field)?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 9 10 11 12 13 14 15 16 17 18 19 20  | Next Page >