SQLServerCentral / SQL Server 7,2000 / T-SQL

T-SQL and Stored Procedure Reference Manual

tammyf — Thu, 24 May 2012 11:56:29 GMT

I am curious of others' thoughts on what book would meet my needs. I bought the Microsoft SQL-Server 2008 T-SQL Fundamentals book but I find it very sparse in what I need.I am a data analyst, not a DBA. I am very good at writing Oracle queries and I am very experienced with Microsoft Access. SQL Server is a little new to me. I am one month into a new job where SQL Server is the primary database that we use. What I mostly need is a reference book. Usually, I know what I want to do. I know how to do it in other languages. I just need to find the appropriate command for certain issues and the syntax for them. So, I need a reference book where I can find them easily.For example, when I need to convert an date stored as an integer (ex. '20120521') and I want to convert it to a date, I would like to be able to look up the convert/cast functions and see the options and examples or find a better way to do it. It would be nice if there was a chapter/section on date functions with all the date functions, the syntax for them, and examples.Or, when I want to write a stored procedure and I am looking to branch my code with a Case statement I want a book that clearly tells me that you can't do that because the Case statement can only be used in Select statements. It took me awhile to realize that you have to use nested If statements instead of Case statements.Also, I want a book that tells me different string functions (maybe all in the same chapter) where I can go to when I need to work with strings and need syntax. Like the date function suggestion I had before.I am not looking for the book to be limited to SQL functions. I would like the book to have information on writing stored procedures, user defined functions, and triggers, also.What book would you suggest?Thank you.Tammy

T-SQL book recommendation

chip1295 — Tue, 28 Oct 2003 10:05:00 GMT

Hi, would someone out there provide me the name of a T-SQL book (SQL Server 2000) that they've found to be relevant to typical everyday queries. I'm a junior-intermediate DBA who's trying to improve upon his SQL skills. Ideally I'd like a book that has numerous exercises to work through.Appreciate any help.thanks .. brad

BCP Out error SQLState = 08001, NativeError = 22

Rodrigo Moraes — Tue, 22 May 2012 10:16:12 GMT

Hi all,I'm looking for the exact "SQLState = 08001, NativeError = 22" error through the web since last 14, May.It looks like nobody got this error but I'm facing it now.I have an client tool(C# WinForm) that connects to a sql server and list all the tables in a datagrid. This grid have some checkboxes that I use to check what tables going to be exported through an BCPOut command, that will run on the client's windows shell through a call on cmd.exe process, not on ".NET System.Data.SqlClient".My current scenario is:[quote]Client machine: Windows 7 64bit with SQL Server Management Studio (SQL 2008)Server machine: Windows 2000 with SQL Server 2000Suspect: Client machine with Win 7 and SSMS 2008 does not have a SQL 2000 client installed[/quote]An example of command:[quote]BCP "select a.* from Database.dbo.Table a (nolock)" QUERYOUT C:\tabela.txt -SMyServer -UUser -PPass -c -t\t -E -b10000[/quote]And the error message:[quote]SQLState = 08001, NativeError = 22Error = [Microsoft][SQL Server Native Client 11.0]SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.[/quote]I really would like to know how could I install the SQL 2000 client on win7 64bit.I appreciate any help.Thanks!

Query runs quick until placed in SSRS

themangoagent — Wed, 16 May 2012 12:18:23 GMT

Hi everyone.My query (below) returns results within 5 seconds or less in SQL Server Management Studio. I even declared and set parameters to mimic the performance it would have in SQL Server Reporting Services. When I place it into a report, it takes over 20 minutes to run! I'm at a loss, and would appreciate any help or advice!Some notes: [i]The unions are because the tables I'm pulling from are not normalized (ew!).I'm using Visual Studio 2008I had this problem initially, and it would take 30 mins in SSMS (as well as SSRS!), and I've cut it down to 5 seconds or less.[/i][code="sql"]Declare @subid varchar(14)set @subid = '200502005149'SELECT c.SUB_ID as ' Subscriber ID', c.Diagnosis, d.FHDXDESC as Description, c.Source, c.PROV_NAME as 'Provider Name', c.DOS as 'Date of Service', 1 as 'Reported' FROM ( SELECT m.SUB_ID, m.Diagnosis, 'Medical' as Source, m.PROV_NAME,CONVERT(datetime, md.BEG_DATE_OF_SERV) AS DOS FROM ( /*Begin medical encounters Union */ select Sub_ID, PRIMARY_DIAG_ICD as Diagnosis, 'Medical' as Source, PROV_NAME, Claim_ref_num from SQL4.Encounter.dbo.medhdr_new_format Union select Sub_ID, Second_DIAG_ICD as Diagnosis, 'Medical' as Source, PROV_NAME, Claim_ref_num from SQL4.Encounter.dbo.medhdr_new_format Union select Sub_ID, Tertiary_DIAG_ICD as Diagnosis, 'Medical' as Source, PROV_NAME, Claim_ref_num from SQL4.Encounter.dbo.medhdr_new_format Union select Sub_ID, DIAG_4TH as Diagnosis, 'Medical' as Source, PROV_NAME, Claim_ref_num from SQL4.Encounter.dbo.medhdr_new_format Union select Sub_ID, DIAG_5TH as Diagnosis, 'Medical' as Source, PROV_NAME, Claim_ref_num from SQL4.Encounter.dbo.medhdr_new_format Union select Sub_ID, DIAG_6TH as Diagnosis, 'Medical' as Source, PROV_NAME, Claim_ref_num from SQL4.Encounter.dbo.medhdr_new_format ) as m INNER JOIN SQL4.Encounter.dbo.meddet_new_format AS md ON md.CLAIM_REF_NUM = m.CLAIM_REF_NUM Union /*Begin hospital encounters Union */ select sub_ID, diagnosis, source, prov_name, convert(datetime, beg_date_serv) as DOS from ( select Sub_ID, Primary_Diag_Icd as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num From SQL4.Encounter.dbo.hosphdr_new_format Union select Sub_ID, Second_Diag_Icd as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num From SQL4.Encounter.dbo.hosphdr_new_format Union select Sub_ID, tertiary_Diag_Icd as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num From SQL4.Encounter.dbo.hosphdr_new_format Union select Sub_ID, Diag_4th as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num From SQL4.Encounter.dbo.hosphdr_new_format Union select Sub_ID, Diag_5th as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num From SQL4.Encounter.dbo.hosphdr_new_format Union select Sub_ID, Diag_6th as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num From SQL4.Encounter.dbo.hosphdr_new_format )h inner join SQL4.Encounter.dbo.hospdet_new_format hd on h.Claim_Ref_Num = hd.claim_ref_num )cinner join SQL2.DIam_725.diamond.JDIAGNM0_Dat d on d.FHDXCODE = c.diagnosis where (c.Diagnosis IS NOT NULL) AND (c.Diagnosis <> '999.9') AND (c.Diagnosis <> 'DXINV') AND (c.Diagnosis <> '') and (sub_id = @SUbid +'00' or sub_id = @SUbid + '01')and dos >= '01-01-2010'[/code]

Show database access

Luis Cazares — Wed, 16 May 2012 09:55:03 GMT

I'm trying to run a query to find SP code in any database located in the server. However, I don't have access to every database. Is there a way to filter the databases to the ones I have access?Here is what I'm doing:[code="sql"]DECLARE @db varchar(50), @cSQL varchar(5000)DECLARE bases CURSOR FOR SELECT name FROM master.dbo.sysdatabasesOPEN basesFETCH NEXT FROM bases INTO @dbWHILE @@FETCH_STATUS = 0BEGIN SET @cSQL = 'SELECT ''' + @db+ ''' AS db,* FROM ' + @db+ '.dbo.sysobjects o JOIN ' + @db+ '.dbo.syscomments c ON o.id = c.id WHERE o.type = ''p'' AND text LIKE ''%Something%''' EXEC( @cSQL) FETCH NEXT FROM bases INTO @dbENDCLOSE basesDEALLOCATE bases[/code]I get this error every time it gets to the database with no permissions and stops the query from completing.[code="other"]Msg 916, Level 14, State 1, Line 1Server user 'Company\P63927' is not a valid user in database 'AC700'.[/code]

OR operator precedence

natha — Wed, 16 May 2012 07:34:46 GMT

I was wondering how will be the precedence of operation in following query:[code="other"]UPDATE table1 t1SET t1.col1 = (SELECT TOP 1 t0.col0 FROM table0 t0 WHERE t0.date_of_call < t1.date_of_call AND (t0.dur_connection = t1.dur_network OR t0.dur_connection = t1.dur_network - 1 OR t0.dur_connection = t1.dur_network + 1 OR t0.dur_connection = t1.dur_network - 2 OR t0.dur_connection = t1.dur_network + 2 OR t0.dur_connection = t1.dur_network - 3 OR t0.dur_connection = t1.dur_network + 3) AND t0.caller_id = t1.caller_id ORDER BY t0.date_of_call desc)[/code]What I want to achieve here is to update t1.col1 with t0.col0 where the gap between t0.dur_connection (current record) and t1.dur_network (some previous record) is the smallest.So in case of multiple match, will the db engine first treat the smallest gap, where difference is 0, then where difference is +1/-1, then where difference is +2/-2 and so on? Or will it treat it randomly?Thanks for your reply,Natha

Msg 1828, Level 16, State 5, Line 1 The logical file name "address" is already in use. Choose a different name.

vsreddy_6984 — Sun, 13 May 2012 06:03:36 GMT

create database dairyon primary(name='address',filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\address.mdf',size=5mb)log on(name='address',filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\address.ldf',size=10mb)

How to display integer value from numeric data

DURGESH-769360 — Sun, 06 Sep 2009 22:39:32 GMT

Hi all,I need the output as followscol1 col2---- ---- val1 1val2 2val3 4val4 1.3val5 5val6 1.5wherein my col2 is of type numericcan anybody help me to solve this issueRegards Durgesh J

select from multiple comma separated string

fanindrabhortakke — Sun, 13 May 2012 03:29:54 GMT

declare @str1 as VARCHAR(100)='2,5,4,3,7,7'declare @str2 as VARCHAR(100)='5,6,7,8,9,0'declare @str3 as VARCHAR(100)='2,5,4,3,7,7'I want output from select like 2 5 25 6 54 7 43 8 37 9 77 0 7How can I do this ?

Msg 153, Level 15, State 1, Line 14 Invalid usage of the option size in the CREATE/ALTER DATABASE statement.

vsreddy_6984 — Sun, 13 May 2012 05:44:46 GMT

create database contacts on primary(name='contacts',filename='D:\contacts\contacts.mdf',size=5mb,filegrowth=3%) log on(name='contacts',filename='D:\contacts\contacts.ldf'size=5mb,filegrowth=3%)

How to retrieve different records from same table present in different databases

sagar-506255 — Wed, 14 Nov 2007 21:37:15 GMT

Hi guys,I hv same table say "Patient" in 2 different databases that are present on different or same server, Now i want to get those different records from the "Patient" table that are present in these databases.Does anyone have any stored procedure or a SQL query that will solve my above problem.I need it urgently.......thanks,Sagar

Reverse DNS lookup using T-SQL

dcnorman — Mon, 21 Feb 2005 09:53:00 GMT

Hello, I have a stored procedure that is passed an IP address that I would like to do a reverse DNS lookup on to get the machine name. Has anyone ever done anything like this, and if so, how?Thanks

stored procedure running slower than sql query

sahana — Thu, 06 Oct 2005 12:29:00 GMT

I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.

What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.

Thanks.

Uppercase vs. lowercase for keywords

Vic Kirkpatrick — Mon, 30 Aug 2004 10:40:00 GMT

This might seem crazy, but I just don't get it. Why does everyone go through the trouble of hitting the shift key for every keystroke on a keyword in T-SQL just so it can be capitalized? Why not just leave keywords lowercase like other languages. Am I missing a benefit of this or something?

Thanks,

Vic

Reverse order of numbers in a given field

philgee — Tue, 08 May 2012 12:45:30 GMT

I have a table (sql 2000) that contains records for a manufacturing process. Each item produced is identified by a serial number and has 72 records that are physically realated to "positions". Numbered 1 - 72. For each serial number. I just learned today that through a mis-communication we have been entering the data backwards the first entry should have been for position 72 and the last should have been position 1. What is an efficient method of reversing the data in the table so that the information matches up with the correct position? Position 1 = 72, 2 = 71 .... .....72 = 1

Banker''s rounding in T-SQL (like Math.Round in .NET)

Andrew Vasylevskyy — Sun, 25 Dec 2005 08:53:00 GMT

Hi!I'm in extreme need of T-SQL function wich will round to the nearest even number, called banker's rounding or rounding to nearest (like Math.Round in .NET)There are lots of discussions about rounding and realizations of banker's rounding in C#,VB,C++ but I haven't found any realization in SQL. Using exdended procedure is a solution but it's not to my liking. I really can't think out it by myself.Thanks in advance

Pull back up to the first Carridge Return

Ryan Keast — Thu, 26 Apr 2012 09:03:53 GMT

Hi there we have a database table and a field which is an address field.The address field is a nvarchar at 255 in length.This address field includes all of the address but seperated over lines by a carridge return.What I want to pull back is just the first line of the address up to the first carridge return.Is there a way to do this at all?Thank you

Server IP Address

sebmr2 — Fri, 27 Apr 2012 14:58:40 GMT

I was asked to create a centralized location that contains various bits of information about all of my servers, and as it turns out my company still has around 20 SQL Server 2000 instances. One of the pieces of information that I need to pull weekly is the IP address of the server. Now for the SQL Server 2005 and 2008 machines I used: SELECT local_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPIDDoes anyone know of a simple query that would pull the server IP and would work on SQL 2000 machines?Thanks.

Converting from Gregorian to Julian Dates For JDE

paeast — Thu, 03 Apr 2003 08:43:00 GMT

Because JD Edward uses Julian Dates from 01/01/1900, you can use this code for convertingCONVERT(VARCHAR, datepart(yy,getdate())-1900) + CASE WHEN datepart(dy,getdate()) < 10 THEN CONVERT(VARCHAR , 0) + CONVERT(VARCHAR , 0) + CONVERT(VARCHAR , datepart(dy,getdate())) WHEN datepart(dy,getdate()) < 100 and datepart(dy,getdate()) > 9 THEN CONVERT(VARCHAR , 0) + CONVERT(VARCHAR , datepart(dy,getdate())) ELSE CONVERT(VARCHAR , datepart(dy,getdate())) END,

Installing the SQL SERVER 2008...

jose_luiz — Thu, 26 Apr 2012 16:25:07 GMT

Dear Sirs,Soon I will update the SQL Server 2005 servers to version 2008 R2. All my servers run Windows Server 2008 R2 Enterprise SP 1.But before I do these updates (which will be "side-by-side" and not "in-place"), I would like to address some initial questions:1) Some prerequisites for installing SQL SERVER 2008 R2 are the "Windows Installer 4.5," ". NET Framework 3.5 SP1 or 4.0" and "Windows PowerShell 1.0." I need to install them before or even the Setup verifies the absence of these and install them?2) In the link "[url=http://www.sqlteam.com/article/sql-server-versions]http://www.sqlteam.com/article/sql-server-versions[/url]" I checked that, for SQL SERVER 2008 R2, we have the Service Pack 1 (SP 1) and Cumulative Update 6 (CU6).I downloaded the SP1 from the link "[url=http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727]http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727[/url]", but I noticed that, in addition to the SP1 files for SQL SERVER 2008 R2, this page there are also some files to the "Management Studio". I downloaded both, but after installing SQL server, I should only run SP1 for SQL SERVER or I need to also run on the server the "Management Studio" SP1 file ?3) If I install any feature of BI (for example, Analysis Services, Reporting Services and Integration Services), the setup of the installation also requires the prior existence of the "Visual Studio 2008 SP 1". I need to install it before SQL SERVER Setup, or if it does not exist, Setup takes care of installing it for me ? Moreover, as the 2010 version is available for Visual Studio, it would be best to install this version now or keep the 2008 SP1 version ?I'm waiting for the answers and tips that will certainly be very useful for me.Thanks in advanceJosé Luiz

Strange Date overflow - what am I missing

kangarolf — Fri, 20 Apr 2012 05:17:17 GMT

Hi all,I am using a tally table (Numbers) to select a sequence of dates based on a repeating event (daily, weekly, monthly etc).It was all working fine when I did it for weeks and days but months is getting an over flow error.I tested it and boiled it down to this test statement (you'll need a tally table somewhere to test)[code]DECLARE @FREQ INTSET @FREQ = 2SELECT DATEADD(MM, @FREQ* NUMBER, '01/05/2012') AS MYDATE, NUMBERFROM UTILITY..NUMBERSWHERE DATEADD(MM, @FREQ*NUMBER, '01/05/2012') < '01/05/2020'[/code]If you set any frequency aside from 1 (ie every month) it fails on the last date to be calculated before the end date in the where clause, no matter what the end date or start dates are.Anyone have any ideas...Im lost!Thanks

Using T-SQL to run Crystal Report

eletuw — Sat, 16 Aug 2003 10:06:00 GMT

Is it possible using automation to run a crystal report from t-sql and prepare a report which can then be emailed. The t-sql will be contained in a scheduled job. If anyone has done this please let me know how to go about it.Thanks.

Sp_executesql with Dynamic SQL string exceeding 4000

satishchandra — Wed, 03 Jan 2007 12:46:00 GMT

Hi,This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.I have my SQL string exeeding more than 4000 characters. The sp_executesql expects its parameters to be declared as nvarchar/ntext.ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.The following was the original approach.===============DECLARE @sql nvarchar(4000), -----select @sql=@sql+'select.................'----INSERT INTO #Temp EXEC sp_executesql @SQL,@paramlist===============================The above sql was split into two sql variables and tried in the following way.==============================DECLARE @sql nvarchar(4000), @sql1 nvarchar(4000),@paramlist nvarchar(4000)-----select @sql=@sql+'select.................'select @sql1=@sql1+'from table1, table2.........'----INSERT INTO #Temp exec('EXEC sp_executesql ''' + @sql + @sql1 + ''',@paramlist')====================However, it compiles correctly but during the execution, the error indicates as syntax error. I ran the resulting SQL string (@sql + @sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through sp_executesql, it gives me errorAppreciate if anyone can suggest a workaround for this situation.

using bcp,how to load the data into a table in the same order as the records in the file

srihari nandamuri — Thu, 19 Apr 2012 06:28:49 GMT

Hi,While loading(using bcp) data from file into a table,I want to load the file data into a table in the same order as in the file. Example:-File contains data as belowabcdand in table it should get inserted in the same order likeabcdPlease suggest.

Function error

nadersam — Wed, 18 Apr 2012 01:54:37 GMT

Dear All,I have a function that accepts a string as it's parameter as following script.Create FUNCTION [dbo].[GetQty] (@ItemCodes as varchar(15))RETURNS MoneyASBegin Declare @Qty MoneySELECT @Qty = SUM(item_qty ) FROM TRANS where item_code in ( @ItemCodes ) return @QtyEndwhen i try to run itselect dbo.GetQty('141,142') i get errorMsg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '141,142' to data type int.Thanks in advanceNader

Rollback Help

thota.v — Tue, 17 Apr 2012 07:35:29 GMT

Hi I had done a mistake .'' i had updated on column but it refelected to all columns. i m not used BEGIN TRAN .'' Please tell me how can i get back that data..please help me..---- Veer

option (LOOP JOIN) slow

Edward-445599 — Mon, 28 Jul 2008 06:27:21 GMT

I have a query not overly complex a few joins addresses addss with(nolock), roles roles with(nolock), patients patnt with(nolock)AND addss.addss_refno = roles.addss_refnoAND roles.patnt_refno = patnt.patnt_refnoHas a distinct in the select and a few and or conditions PLUS ‘option (LOOP JOIN)’It takes 3 minutes to run BUT if I remove the ‘option (LOOP JOIN)’ is completes in 20 seconds, what is happening what is ‘option (LOOP JOIN)’? Is obsolete if I specify inner joins? Ie addresses addss inner join roles roles on addss.addss_refno = roles.addss_refnoinner join patients patnt on roles.patnt_refno = patnt.patnt_refnoMany thanks

update an subquery, how to optimize it?

fcastilloa — Mon, 16 Apr 2012 14:27:57 GMT

Hi!I need help with a optimization of my SP.The issue is this.Users insert records in Table "Operation", those records describes an accion made to the customers.this table contain: CustomerId, Date (datetime of action), ActionId, and other fields more.I need to find the best ActionId, and this is the clasification.Table GroupActionIdGroupID, Detail, GroupPriority1, First, 12, Second, 23, Thirth, 3Table RankActionIdGroupId, ActionId, Ranking1, 10, 11, 15, 21, 20, 32, 25, 12, 30, 22, 35, 33, 40, 13, 45, 2 .....First: I need find the best GroupActionId (Min Field GroupPriority), Then, in table RankActionId, i have to search for the minimun ranking of ActionId, associated to MIN of GroupPriority field. With this, I obtain the best ActionId of each customer. Each customer may have the same ActionId in a different space of time. in this case i need obtain the MAX date of the best ActionId.The final results is an ActionId for each customer, and this ActionId must be the best.But my updates are not optimal, because I make the update with a Subquery.Can help me to optimize this code?GreetingsFernando[code="sql"]/* Find and Update the Best ActionId Group */Update BestActionSet BestGroupPriority = (Select Min(en.GroupPriority) From BestAction mg Join Operation op On op.CustomerId = mg.CustomerId Join dbo.RankActionId de On de.ActionId = op.ActionId Join dbo.GroupActionId en On en.GroupID = de.GroupID Where mg.CustomerId = mm.CustomerId)From BestAction mm /* Find the best Rank, asocciate with best group */Update BestActionSet BestRank = (Select min(de.Ranking) From BestAction mg Join dbo.Operation op On op.CustomerId = mg.CustomerId Join dbo.RankActionId de On de.ActionId = op.ActionId Join dbo.GroupActionId en On en.GroupID = de.GroupID And en.GroupPriority = mg.BestGroupPriority Where mg.CustomerId = mm.CustomerId)From BestAction mmWhere Isnull(mm.BestGroupPriority, -1) <> -1 /* Update the best ActionId */ Update BestActionSet BestActionId = de.ActionIdFrom BestAction mg Join dbo.GroupActionId en On en.GroupPriority = mg.BestGroupPriority Join dbo.RankActionId de On en.GroupID = de.GroupID And de.Ranking = mg.BestRankWhere Isnull(mg.BestGroupPriority, -1) <> -1/* Update the Date of the Best ActionID */ Update BestActionSet BestDate = (Select Max(op.Date) From BestAction mg Join dbo.Operation op On op.CustomerId = mg.CustomerId And mg.BestActionId = op.ActionId Where mg.CustomerId = mm.CustomerId)From BestAction mmWhere Isnull(mm.BestGroupPriority, -1) <> -1 [/code]

Finding median with a group by (SQL 2000)

SQLMAIN — Fri, 13 Apr 2012 08:35:41 GMT

I have a table carrying two columns - lets say id and age.I need to find the median age for each id.create table getmedian ( id int, age int)insert into getmedian values (1, 1)insert into getmedian values (1, 2)insert into getmedian values (1, 3)insert into getmedian values (1, 4)insert into getmedian values (2, 3)insert into getmedian values (2, 5)insert into getmedian values (2, 7)insert into getmedian values (2, 9)insert into getmedian values (2, 11)insert into getmedian values (3, 2)insert into getmedian values (3, 4)insert into getmedian values (3, 8)insert into getmedian values (3, 8)insert into getmedian values (3, 9)A quick respocse will be appreciated.Thanks

Query to list the service accounts ?

Leeland — Tue, 27 Mar 2007 08:24:00 GMT

I have to go through a lot of SQL severs to get the service accounts running each service, it would be a heck of a lot easier if I had a select statement or system stored proc to list the accounts running each service...Is there such a way to do this via T-SQL? Thanks,Leeland

Multiple results

David Lester — Wed, 11 Apr 2012 10:15:26 GMT

Is it possible to return more than one data set from a stored proc?I have a situation where I need to generate on-the-fly roughly 20 reports which the finance group wants created from the same data extract. (As in one pull of data)Additionally, this will need to be able to be run by multiple people at the same time. I would be interested in any suggestions on methods to go about this.

Dates of MMR vaccine

SQL_FS — Fri, 06 Apr 2012 11:07:44 GMT

A member of the reporting team came to me with this business problem because it was out of their league, unfortunately I think it's out of my league as well after beating my head against it for about a week without coming up with anything that I would feel OK about running in production (hundreds of thousands of patients). I've simplified the problem down to its most basic form which I have outlined below in the code block. If anyone has any ideas, I'd be most grateful.Thanks,Alan[code]/*Goal: A query that will identify the dates that an MMR vaccine, or in which the full combination of individual vaccines that makes up a completed MMR vaccine, was received by each patient.I have full access to the database, and can create supporting tables as needed to store rules/metadata/etc for the query*/--Patient tableDECLARE @Patient TABLE (PatientID int NOT NULL, PatName varchar(25) NOT NULL)--Immunization ListDECLARE @Immunization TABLE (ImmunizationID int NOT NULL, ImmunizationName varchar(30) NOT NULL)--Patient immunization ListDECLARE @PatientImmunization TABLE (PatientImmunizationID int NOT NULL IDENTITY(1,1), PatientID int NOT NULL, ImmunizationID int NOT NULL, ImmunizationDate date NOT NULL)INSERT INTO @Patient SELECT 1, 'Patient 1' UNION ALL SELECT 2, 'Patient 2' UNION ALL SELECT 3, 'Patient 3' UNION ALL SELECT 4, 'Patient 4'INSERT INTO @Immunization SELECT 1, 'MMRV' UNION ALL SELECT 2, 'MMR' UNION ALL SELECT 3, 'MEASLES' UNION ALL SELECT 4, 'MEASLES/RUBELLA' UNION ALL SELECT 5, 'MUMPS' UNION ALL SELECT 6, 'RUBELLA'--Patient 1 Sample DataINSERT INTO @PatientImmunization SELECT 1, 1, '1/1/2005' UNION ALL --MMRV (stands on its own) SELECT 1, 2, '6/1/2005' --MMR (stands on its own)--Patient 2 sample DataINSERT INTO @PatientImmunization SELECT 2, 3, '1/1/2006' UNION ALL --Measles SELECT 2, 5, '1/15/2006' UNION ALL --Mumps SELECT 2, 2, '2/1/2006' UNION ALL --MMR (stands on its own) SELECT 2, 6, '6/1/2006' --Rubella, completes the Measles/Mumps set from earlier--Patient 3 sample dataINSERT INTO @PatientImmunization SELECT 3, 3, '1/1/2007' UNION ALL --Measles SELECT 3, 4, '2/1/2007' UNION ALL --Measles/Rubella SELECT 3, 6, '2/15/2007' UNION ALL --Rubella SELECT 3, 5, '3/1/2007' UNION ALL --Mumps (finishes ONE of the earler measles/rubella sets) SELECT 3, 1, '4/1/2007' UNION ALL --MMRV (stands on its own) SELECT 3, 5, '5/1/2007' --Mumps (finishes the other remaining measles/rubella set) --Patient 4 sample dataINSERT INTO @PatientImmunization SELECT 4, 3, '1/1/2008' UNION ALL --Measles SELECT 4, 5, '1/15/2008' UNION ALL --Mumps SELECT 4, 4, '2/1/2008' UNION ALL --Measles/Rubella (Rubella portion finishes off the earlier set, measles unattached at this time) SELECT 4, 5, '3/1/2008' UNION ALL --Mumps SELECT 4, 6, '9/1/2008' --Rubella (finishes the 2nd full MMR set) /*The result set should look something like the following=========================================================PatientID MMRDate1 1/1/20051 6/1/20052 2/1/20062 6/1/20063 3/1/20073 4/1/20073 5/1/20074 2/1/20084 9/1/2008*/[/code]

Select And Count Consecutive Numbers

David-250683 — Sat, 19 May 2007 02:11:00 GMT

1) How can I select only the first record in a series of certain consecutive numbers?2) How can I obtain the count for the number of times a set of those certain consecutive numbers appear?Example: (this assumes an un-altered Northwind database)USE NorthwindGOWITH Peter AS(SELECT EmployeeID, Freight,ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Freight) AS [RecordNumber]FROM Orders)SELECT * FROM PeterPartial Result SetEmployeeID Freight RecordNumber-------------- -------- ------------------1 0.21 11 0.45 21 0.93 31 1.27 41 1.35 51 1.36 61 1.51 71 1.66 81 2.50 91 3.94 101 4.03 111 4.27 121 4.41 131 4.98 141 4.99 151 4.99 161 7.46 17From the list, records 5 and 6 show consecutive Freight figures of 1.35 and 1.36 for EmployeeID 1.From the list, records 14 and 15 show consecutive Freight figures of 4.98 and 4.99 for EmployeeID 1. In this example how could I get a result set that only showed records, 1.35 and 4.98 - that is the first record in a series of consecutive Freight figures?How could I get a count of the number of consecutive records for each set? A partial result set should look like this.EmployeeID 1stFreightRecord Count1 1.35 2 1 4.98 2

How to Open Two Excel Files in Multiple Monitors in Windows 7 pro excel 2010?

Ritesh_Dev — Mon, 09 Apr 2012 12:51:11 GMT

I want to open two (or more) excel windows on different screen (extended displays), But was not able to. I tried few solutions found on Google.One worked, that was making changes in registry and after that checking the option '[b]Ignore other application that use DDE[/b]'. Now I am able to get separate excel windows but same time I get this error '[b]There was a problem sending the command to the program[/b]'How to avoid this error. Can someone help me in this?Thanks Ritesh

Parse String based on

asha.bagesh — Thu, 05 Apr 2012 04:00:02 GMT

Str 1 :^0609|1|2834.56150|07710.32664|17.170|330.60|!Str 2 : FC03 00140700 00007D04 0002E702 0001624F 00C02A9D Str 3 :^id=PTTRK30ES861001000245502&POS=<3.1><1259.429640><07735.382010><084038><260312>@They are all having the same type of data(U1 L1 L2 T1 T2 S1)U1|L1|L2|T1|T2|S1|! in Str 1 ( delimiter | )S1 U1 L1 L2 T2 T1 ins Str 2 (delimiter space)^id=U1&POS=<T1><L1><L2><S1><T2> ( delimiter >< )I can have more than these string formats . these are stored in a db table called rawdata. I need to parse them and store them in db table which will have the fields (U1 L1 L2 T1 T2 S1) and table name is processeddata. How can I map these strings to the processeddata table. They have same type of info, but are in different positions in the string.Will creating another table create table fmt ( StrFormat int,Delimiter varchar(10)null,[U1] int,[L1] int,[L2] int,[S1] int,[T1] int,[T2] int,)and populating with the positions help. how can i write a general query which will handle the format . The string can be recognized by the beginning part (^,^id=)insert into fmt values( 1,'|',19,3,4,7,5,6)insert into fmt values(2,' ',1,4,5,6,7,8)insert into fmt values (3,'><',1,3,4,5,6,7)Any other way to create a common parser ? ( Also the no of records in the rawdata is huge) So the query must be quick. Please helpThanks

Weird Logical Reads

tiago.palhota — Wed, 04 Apr 2012 08:37:28 GMT

Hi there,I'm trying to check out which queries are running on our server and i'm using this query:SELECT db.name as DB, sid.login_name, sqltext.TEXT,req.session_id SID,req.start_time ,req.status,req.command,req.cpu_time,req.logical_reads,req.total_elapsed_time / 1000 as total_elapsed_time_seconds,req.last_wait_type,req.wait_time,plan_used.query_planFROM sys.dm_exec_requests reqCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltextCROSS APPLY sys.dm_exec_query_plan(plan_handle) as plan_usedJOIN sys.databases db ON req.database_id = db.database_idJOIN sys.dm_exec_sessions sid ON sid.session_id = req.session_idORDER BY req.total_elapsed_time DESCOne of the lines returned says that one SID is using 367257823 Logical Reads.WHAT ???? I'm very new to SQL Server DBA Stuff, but the right way to calculate the number of buffer used, that this guy is something like this ?number of logical reads * 8(kb per page) ??This returns a huge number...What i'm doing wrong here ?I know that logical reads are records comming from the buffer, but i'm trying to understand the volume of data used by the query.Thanks for your help.

Activex script as a SQL Job

lucky-80472 — Wed, 31 Mar 2004 01:28:00 GMT

Hello Gurus,

I want to know the steps to execute an activex script through a job which will be called from a trigger.

I am having 2 tables ie.,

tblMsg------Msgid int,Studid int,Msgtype int,Msgsub varchar(100)

tblMsgDtl---------MsgDtlid int,Msgid int,Sendto varchar(100),Content text,Status int

It is a sort of SMS messaging where the "Msgtype" = 2. The relation between these two tables is "Msgid" (one to many relation).

Now my requirement is to know how the avtivex script when run as a job which will be called in a trigger will execute. I have written a trigger "after insert" on the second table ie., tblMsgDtl as follows:

======================================================================

Create trigger SMS on dbo.tblMsgDtlafter insertas exec msdb..sp_start_job 'SMSActivex'

======================================================================

and the activex script "SMSActivex" is as follows:

======================================================================

Dim smsgateway,Cn,rsUsers,strSQL, strMsgIds

smsgateway = "" -- gateway address

Set Cn = CreateObject("ADODB.Connection")

Cn.Open "Provider=sqloledb;Data Source=<Servername>;Initial Catalog=<Database name>;User Id=<Username>;Password=<Password>;"

Set rsUsers= CreateObject("ADODB.RecordSet")

strSQL = " " -- selecting the records where the "Msgtype" = 2 and "Status" = 0

rsUsers.Open strSQL, Cn, 3, 1

If rsUsers.RecordCount > 0 Then

Dim arMsgIds()

ReDim arMsgIds(rsUsers.RecordCount-1)

Dim strUsername, strPwd, strPriority, strData, strCellPhone

strUsername = " " -- username to connect to the gateway

strPwd = " " -- password

strPriority = "normal"

strData=rsUsers("Body")

Dim xmlDOMDocument

Set xmlDOMDocument=CreateObject("MSXML2.DOMDocument")

Set rootElement=xmlDOMDocument.createElement("itouchCMA")

xmlDOMDocument.appendChild rootElement

Set headerNode=xmlDOMDocument.createElement("auth")

rootElement.appendChild headerNode

Set user=xmlDOMDocument.createElement("name")

user.Text=strUsername

headerNode.appendChild user

Set password=xmlDOMDocument.createElement("password")

password.Text=strPwd

headerNode.appendChild password

Set headerNode1=xmlDOMDocument.createElement("message-submit")

rootElement.appendChild headerNode1

Set priority=xmlDOMDocument.createElement("priority")

priority.Text="normal"

headerNode1.appendChild priority

Set validity=xmlDOMDocument.createElement("validity")

validity.Text="7"

headerNode1.appendChild validity

Set contenttype=xmlDOMDocument.createElement("content-type")

contenttype.Text = "text"

headerNode1.appendChild contenttype

Set message=xmlDOMDocument.createElement("message")

headerNode1.appendChild message

Set Data=xmlDOMDocument.createElement("data")

Data.Text=strData

message.appendChild Data

Do Until rsUsers.EOF

Set cellphone=xmlDOMDocument.createElement("cellphone")

cellphone.Text=rsUsers("RECIPIENT")

message.appendChild cellphone

arMsgIds(rsUsers.AbsolutePosition-1)=rsUsers("MSGDTLID")

rsUsers.MoveNext

Loop

strMsgIds=Join(arMSgIds, ",")

Dim xmlRequest

xmlRequest="<?xml version=""1.0"" encoding=""UTF-8""?>" & xmlDOMDocument.xml

Set oHTTP=CreateObject("Microsoft.XMLHTTP")

oHTTP.open "POST", smsgateway , False

oHTTP.Send xmlRequest

Dim xmlDOMDocumentr

Set xmlDOMDocumentr = CreateObject("MSXML2.DOMDocument")

xmlDOMDocumentr.Load (oHTTP.responseText)

If Instr(1, oHTTP.responseText, "<user-message id=""snd-003"">") > 0 Then

Cn.Execute " " -- updating the status to 1 when it is successful.

Else

Cn.Execute " " -- updating the status to 2 when it is unsuccessful.

End If

rsUsers.close

Set rsUsers=Nothing

Cn.close

Set Cn=Nothing

Set xmlDOMDocument=Nothing

Set oHTTP=Nothing====================================================================

If the activex script is executed individually, then the process is fine. If it is executed from a trigger by calling the job to start, then the following error message is being fired as follows:

SQLServerAgent Error: Request to run job SMSActivex (from User sa) refused because the job is already running from a request by User sa.

When i check it up in the enterprise manager->Server->Management->SQL Server Agent->Jobs->SMSActivex

the status is being shown as executing step 1

====================================================================

My System Configuration:

Operating System : Microsoft Windows 2000 Advanced Server (SP 4)SQL Server: SQL Server 2000 Enterprise Edition (SP 3)

MS BI Warehouse project

Ritesh_Dev — Tue, 03 Apr 2012 07:47:51 GMT

Is there any link or zip file where I could get whole MS BI warehouse project (sample) from starting to end? (2008)Incremental load and even possible creating cubes too. What kind of problems one faced in real time projects, such things.I could find things on you tube in parts but couldn't link it. Please help.Rohan

Design a query to update values of a column basing on other value

Annee — Tue, 27 Mar 2012 09:51:28 GMT

Using SQL Server 2005I have table which looks something like below:(A given DocId can have one or more versions)[code]DocId Version StatusA1 0 HTA1 1 DWA1 2 DWA2 0 DWA2 1 DWA3 0 HTA4 0 DWA4 1 HTA4 2 HTA4 3 DW[/code]Now I added a new column to the table called OrigStatus[code]DocId Version Status OrigStatusA1 0 HT NULLA1 1 DW NULLA1 2 DW NULLA2 0 DW NULLA2 1 DW NULLA3 0 HT NULLA4 0 DW NULLA4 1 HT NULLA4 2 HT NULLA4 3 DW NULL[/code]And I want to run a query which would update the value of OrigStatus for all the trans.OrigStaus (of a given docId) = status (of the given docId where version = 0)After running the query table looks like below: [code]DocId Version Status OrigStatusA1 0 HT HTA1 1 DW HTA1 2 DW HTA2 0 DW DWA2 1 DW DWA3 0 HT HTA4 0 DW DWA4 1 HT DWA4 2 HT DWA4 3 DW DW[/code]Greatly apprecaite any help designing the query to update the value as mentioned above

Help me with SQL_Variant

kodalisridevi — Tue, 27 Mar 2012 08:30:18 GMT

HI All , Below is my script:USE [DevSalesSecondDrawer]GO/****** Object: StoredProcedure [Neg].[UsersCreateStaticsGet] Script Date: 03/27/2012 09:47:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [Neg].[UsersCreateStaticsGet](@UserID typeSalesID =null,@StartDate date ,@EndDate date)As Begin if LEN(@UserID) = 0 set @UserID = null Select OwnerID,COUNT(*) as JobsCreated into #jobsCount from Neg.jobs where (OwnerID = @UserID or @UserID is null ) and (CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime) between CAST (CONVERT (CHAR(10),@StartDate,120)as Date) and CAST (CONVERT(CHAR(10),@EndDate,120)as Date)) group by OwnerID select AlternateOwnerID as OwnerID, COUNT(*) as AlternatesCreated into #AlternatesCount From Neg.Alternates where (AlternateOwnerID = @UserID or @UserID is null ) and (CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime) between CAST (CONVERT(CHAR(10), @StartDate,120)as Date) and CAST( CONVERT (CHAR(10), @EndDate,120)as Date)) group by AlternateOwnerID select CreatedByUser as OwnerID , COUNT(*) as ItemsCreated into #ItemsCount From neg.Items where (CreatedByUser = @UserID or @UserID is null ) and (CAST (CONVERT(CHAR(10),CreatedDate,120) as DateTime ) between CAST(CONVERT (CHAR(10),@Startdate,120) AS Date) and CAST(CONVERT (CHAR(10),@EndDate,120)as Date)) group by CreatedByUser --Select * from #JobsCount --Select * from #ItemsCount --Select * from #alternatesCount Select ISNUll (ISNULL(j.OwnerID, a.OwnerID), i.OwnerID )as OwnerID ,j.JobsCreated,a.AlternatesCreated,i.ItemsCreated from #JobsCount J full outer join #alternatesCount a on j.OwnerID=a.OwnerID full outer join #ItemsCount i on a.OwnerID=i.OwnerID --drop table #JobsCount --drop table #Itemscount --drop table #alternatesCount Endthe above stored procedure is running fine SSMS.But , When I am running from the reports. I am getting the error message. Implicit conversion from data type sql_variant to date is not allowed. Use the CONVERT function to run this query. (.Net SqlClient Data Provider)Please help with this.