I am trying to find the identity value of an inserted record inserted by exec(@Sql), but it seems that exec() excutes in a different scope. /* create table [dbo].[__Test]( [id] [int] IDENTITY(1,1) NOT NULL, [description] [varchar](100) NULL ) ON [PRIMARY] GO */ declare @Sql varchar(512) set @Sql = 'insert into [dbo].[__Test] ([...
Say I have Table1 which has duplicate rows (forget the fact that it has no primary key...) Is it possible to rewrite the following without using a JOIN, subquery or CTE and also without having to spell out the columns in something like a GROUP BY? SELECT COUNT(*) FROM ( SELECT DISTINCT * FROM Table1 ) T1 ...
Group, I'm looking for a query that can do a location search in radians with in a binding box. With a table structure like ProductID,latitude,longitude,timestampGMT. Any helpful suggestions. Chad ...
Hi, I have the following SQL query: SELECT CountryID, [10201] AS CountryGDPPerCapita, [10677] AS LifeExpTotal FROM ( SELECT CountryID,FieldID,numeric FROM globaledge.dbo.DIBS_Data WHERE CountryID IN (3,5) AND FieldID IN (10201,10677) AND year = 2002 ) SourceTable PIVOT ( MAX(numeric) FOR FieldID IN ([10201...
I have borrowed a query and adapted it for my own purposes, but I don't like doing this when I am not entirely sure what it is doing. SQL docs are less than descriptive on this clause. Here is what I borrowed and modified, can you tell me what it is doing basically? (SELECT Id FROM ( SELECT Id ,RAN...
Is there a way we can specify values in for a case statement? The below statement doesn't execute because it thinks 53,57,82,83 etc are columns.. Is there a work around.. i googled up but found nothing that say you can't use IN case-when expression.. select x = case when xvalue in ([52],[57],[82],[83]) then "xvalue" w...
I have a table whose schema is declare @tbl table(field varchar(20)) insert into @tbl select 'Pāo' union all select 'Paorum' union all select 'Some Pao' union all select 'Pao' If I want to search the record only on the macron, I am unable to do so select * from @tbl where field = 'Pāo' Output: field Pao Pao If I use like ope...
Hi all Here is the data Flag Zone Info Date R North AAA 2010-2-14 R North AAA 2010-2-24 T North AAA 2010-2-4 R South AAA 2010-2-23 T South AAA 2010-2-14 R EAST AAA 2010-2-...
For the first time in years I've been doing some T-SQL programming in SQL Server 2008 and had forgotten just how bad the language really is: Flow control (all the begin/end stuff) feels clunky Exception handling is poor. Exceptions dont bubble in the same way they do in every other language. There's no re-throwing unless you code it yo...
Consider two tables: Transactions, with amounts in a foreign currency: Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ExchangeRates, with the value of the primary currency (let's say dollars) in the foreign currency: Date Rate ========= ======= 2/1/2009 ...
Is it possible to use the name of a table as a parameter in t-sql? I want to insert data into a table, but I want one method in C# which has a parameter for the table. Is this a good approach? I think if I have one form and I am choosing the table and fields to insert data into, I am essentially looking to write my own dynamic sql quer...
Hi folks, I'm wanting to shorten some url's and sql guids into some 'short' url format. Does anyone have any code or links to some code, for url shortening in T-Sql? ...
Hi I want to check if SQL logins have passwords same as thier login name. E.g. login name 'abc123' has password= 'abc123'. I need to do this for a security audit across many 2000 and 2005 servers. Is it possible to check using TSQL? Thanks in advance ...
I'm trying to find a way to 'throttle' CDC on SQL2008. The reason being that under normal circumstances, CDC performs brilliantly, but as soon as it needs to deal with a 'large' number of rows, it starts tanking. Typical throughput is between 1000 and 3000 rows a second. It starts to die at about 5000 rows per second. Usually, this is...
I am writing a program that uses ADO.NET's SqlCommand to execute a number of user provided batches of T-SQL statements. My application opens a transaction in which all of the statements are executed to ensure that if there is an error executing any, the whole lot are rolled back. The problem I have come across is that a badly placed CO...
I never remember if a system T-SQL function, with no parameters, requires parenthesis, so I wonder if there is a reason. For example: CURRENT_TIMESTAMP is a function that doesn't require parenthesis, while SCOPE_IDENTITY() requires them. ...
I am getting errors executing the following statement: /* AccountTypes Constraints */ IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountTypes]') AND type in (N'U')) BEGIN PRINT 'Table [AccountTypes] exist.' IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Accoun...
if i have a string with values like A,B,C,D,E,F, and i wanted to remove the last comma? is there a function or script i can use to do that? Thanks ...
I'm trying set up an execution account following the steps here: http://msdn.microsoft.com/en-us/library/ms156302.aspx I follow these steps: Start the Reporting Services Configuration tool and connect to the report server instance you want to configure. On the Execution Account page, select Specify an execution account. Type the ac...
I have this string that i am getting from .net application A,B,C,D,E,F, I wanted to write a sql select statement like set @string = 'A,B,C,D,E,F' select * from tbl_test where tbl_test.code in (@string) This wont work in t-SQL because it is using the @string as one string it is not separating the values. Is there any ways i can do ...