[T-SQL] OPENROWSET - SYNTEX ERROR NEAR @CMD

---

• OPENROWSET - SYNTEX ERROR NEAR @CMD
• Conversion from varchar to strong data types
• status on a given date or date range
• i want update the 2nd column based on first column:
• Performance Problem
• SQL Server Logon Trigger Problems
• index re-build on trucated table
• System M Derived in SQL Server ?
• How to call a batch file to execute from an SP
• Optimization of dynamic SQL
• Bushy plan vs left-deep
• How to report on historical movements within a changing hierarchy
• sp_addlinkedserver not able to execute with other command line
• Debugging stacked CTEs efficently
• Running Query on Multiple Database/Servers
• Add variable number of rows into a table based on the values in another table (without cursors/while loops)

OPENROWSET - SYNTEX ERROR NEAR @CMD Posted: 21 Mar 2013 01:57 AM PDT -- this works fineSELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=sql01;Trusted_Connection=yes;', 'SELECT * from syte_APP.DBO.employee') AS a -- this code gives me error DECLARE @Cmd VarChar(4000) SET @CMD = 'SELECT * from syte_APP.DBO.employee'SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=sql01;Trusted_Connection=yes;', @cmd ) AS a -- Msg 102, Level 15, State 1, Line 4-- Incorrect syntax near '@cmd'

Conversion from varchar to strong data types Posted: 20 Mar 2013 11:09 PM PDT Hi all,There are a few databases I work with that have been designed where varchar columns are used to store what actually displays on the front end as Ints, Decimals, Varchars, Datetimes, checkboxes.I often have to write integrations with these databases bringing data in and prefer to validate the data whilst loading from the staging tables.I have seen allsorts of values being passed into the staging tables that will load into the target database because the columns are all varchars but the values don't display on the front end because the app actively filters bad values out.Poor design, I know, the designers have their reasons and I don't want to really get into all that on this thread.What I would like to do is for my validation scripts to warn up front of potentially invalid datatypes. My problem is that forexample the ISNUMERIC() function return 1 for the value ',1234' but a CONVERT(NUMERIC, ',1234') or CAST(',1234' AS NUMERIC) will fail with a "Error converting data type varchar to numeric).I've been trying to locate a set of reliable datatype testing functions that will reliably determine if a varchar can be converted to a given data type or not.Does anyone know of any?

status on a given date or date range Posted: 20 Mar 2013 07:18 AM PDT I have a situation where we need to be able to query the status a claim was at any given time.For example, I have a table in (2008R2):CREATE TABLE ClaimStatus( ClaimID int NOT NULL, StatusDate datetime NOT NULL, StatusCode int NOT NULL)INSERT INTO ClaimStatus VALUES (7150,'2013-03-11 10:41:29.823',100) INSERT INTO ClaimStatus VALUES (7150,'2013-03-12 07:20:41.720',300) INSERT INTO ClaimStatus VALUES (7150,'2013-03-15 13:35:50.000',310)INSERT INTO ClaimStatus VALUES (7148,'2013-03-01 10:41:29.780',100)INSERT INTO ClaimStatus VALUES (7148,'2013-03-10 07:21:26.557',300)INSERT INTO ClaimStatus VALUES (7148,'2013-03-20 13:35:50.000',310)INSERT INTO ClaimStatus VALUES (7149,'2013-02-01 01:19:20.110',100)INSERT INTO ClaimStatus VALUES (7149,'2013-02-14 07:21:26.557',300)INSERT INTO ClaimStatus VALUES (7149,'2013-03-14 00:35:50.000',310)INSERT INTO ClaimStatus VALUES (7147,'2013-02-01 01:19:20.110',100)INSERT INTO ClaimStatus VALUES (7147,'2013-02-14 07:21:26.557',300)INSERT INTO ClaimStatus VALUES (7147,'2013-03-10 00:35:50.000',310)Let's say I need a query that would tell me which claims were at a status of 300 on 3/14/2013. Some of these claims were, in fact, at a status of 300 on that day but how do I query that since there is no entry for the specific date of 3/14/2013? ( ie: claim 7148 was changed to a status of 300 on 3/10 and was not changed to another status until 3/20, so I would need that claim pulled back in my query as it would have still been status 300 on 3/14.)

i want update the 2nd column based on first column: Posted: 20 Mar 2013 08:49 PM PDT hear i have id column based on that getting one more column as mid and data as belo:[u]id[/u]100200300400500Expected output:[u]id[/u] [u]Mid[/u]100 NULL200 100 300 200400 300500 400

Performance Problem Posted: 21 Mar 2013 12:25 AM PDT Hi,I have been struggling with a procedure for a few days. even though the tables sizes are not very big, but a procedure is taking more than 2 minutes to return the results. Most probably this is happening due to nested views used inside it which have a lot of left joins. I am not sure if a view is actually ran to some extent behind the scenes because I just not se this doing good here. For example, one of the small tables is scanned in the execution plan and shows correct actual number of rows but then there is a Lazy Pool operator whose output becomes billion number of actual rows, I have not see this honestly.Please give me some inputs by looking at the execution plan. I can share the tables schema but not sure if that is required.RegardsChandan Jha

SQL Server Logon Trigger Problems Posted: 20 Mar 2013 05:34 AM PDT Hello,I have two business needs to deny logon to any connection that is accessing SQL Server with a session that is not encrypted (we use SSL and the force encryption option cannot be set due to various third party app problems) and I need to capture certain audit data for each successful logon to SQL Server.I created two logon triggers to do this.The problem is that the two triggers work fine and as expected when testing through SQL Server Management Studio.The check for encryption trigger fails with the infamous 'login fails due to trigger execution' error when using any .Net app (SSIS, SSRS, VB program, etc.).The audit trigger works fine from all our applications except one written in PHP.I created a table that stores the dbid, dbname, an encrypted required indicator and an audit required indicator. I use this to control if the connections are denied or if auditing of the database connection is required. We use SQL Server 2008 R2 SP2 (build 10.50.4000).Example of the deny logon trigger:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [DenyUnencryptedConnection]ON ALL SERVER WITH EXECUTE AS 'DatabaseLogonAuditUser'FOR LOGONASBEGIN DECLARE @LoginName VARCHAR(128), @DBName VARCHAR(128)SELECT @LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'), @DBName = ORIGINAL_DB_NAME ()IF (SELECT COUNT(ad.DatabaseName) FROM DatabaseLogonAudit.dbo.AuditDatabase ad INNER JOIN master.sys.sysprocesses sp on sp.dbid = ad.DatabaseId INNER JOIN master.sys.dm_exec_connections ec ON ec.session_id= sp.spid WHERE sp.loginame = @LoginName AND ad.DatabaseName = @DBName AND ad.DBLogonAuditRequired = 1 AND ec.Encrypt_option <> 'TRUE') > 0 ROLLBACK;END;GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOExample of the audit trigger:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [CaptureLogonInfo]ON ALL SERVER WITH EXECUTE AS 'DatabaseLogonAuditUser'AFTER LOGONASBEGIN DECLARE @SessionID INT, @LoginTime DATETIME, @LoginName VARCHAR(128) SELECT @SessionID = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'int'), @LoginTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar (128)'), @LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') IF (SELECT COUNT(AuditDatabaseIdn) FROM databaseLogonAudit.dbo.AuditDatabase WHERE DatabaseName = ORIGINAL_DB_NAME () AND DBLogonAuditRequired = 1) > 0 INSERT INTO DatabaseLogonAudit.dbo.AuditLogin (ServerName, LoginTime, LoginName, DatabaseName, Session_id, HostName ) VALUES ( @@Servername, @LoginTime, @LoginName, ORIGINAL_DB_NAME (), @SessionID, HOST_NAME() ); END;GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOHas anyone had unpredictable results using logon triggers and are there any steps/processes that I may be missing in implementing these? Thanks…

index re-build on trucated table Posted: 20 Mar 2013 09:26 PM PDT hi,i've seen a couple of cases where huge staging tables containing c/nc indexes are truncated and the index drop and rebuild happens , so its like 1. drop index 2. truncate table 3.re-create indexmy question is1. would a NC/C index slow down the truncation(not delete) of data from a huge table.2. And most importantly, is there any advantage of re-building index on a truncated table.i think while truncating a table , the indexes need not be touched at all.Please enlighten me.

System M Derived in SQL Server ? Posted: 20 Mar 2013 06:21 PM PDT All,I have recently read the below articles.[url]https://www.simple-talk.com/sql/performance/join-reordering-and-bushy-plans/http://www.benjaminnevarez.com/2010/06/optimizing-join-orders/http://en.wikipedia.org/wiki/Query_optimizer[/url][quote] Most query optimizers determine join order via a dynamic programming algorithm pioneered by IBM's System R database project[citation needed]. [/quote]how about sqlserver , sybase & oracle?[quote]Historically, System-R derived query optimizers would often only consider left-deep query plans, which first join two base tables together, then join the intermediate result with another base table, and so on. This heuristic reduces the number of plans that need to be considered (n! instead of 4^n)[/quote][quote]sql server = System-R derived query optimizers ?[/quote]If System-R is derived on SqlServer, How "Bushy Plan" is implemented in SQL Server? it seems like by-passing the System-R properties? [url]http://en.wikipedia.org/wiki/IBM_System_R[/url]I am not that much clear abou System_R. what exactly the role of System_R in DB?

How to call a batch file to execute from an SP Posted: 20 Mar 2013 04:15 AM PDT Hi All,Need your assistance please, I am not very good with scripting.I have created a draft of SP, and I need syntax to make a call to a batch file(.bat) from within the SP. Once I have that I can incorporate it in the code and begin testing.Can someone please provide sample script.Thanks,SueTons.

Optimization of dynamic SQL Posted: 20 Mar 2013 08:19 PM PDT Hi All,I have a stored procedure with many optional parameters.Following is one of the code blocks from that SP which is taking long time to execute. If there is any other optimized way to perform the same task please suggest me:[code="sql"]declare @City varchar(50) = 'aadorp', @Region varchar(50)= 'europe', @strProvince varchar(200) = '''Overijssel'', ''Utrecht''', @strCountry1 varchar(50) = '''netherlands''', @strCountry2 varchar(50) = '''''', @strCountry3 varchar(50) = '''''', @strCountry4 varchar(50) = '''''',@PtSql varchar(8000), @PtJoinString varchar(4000), @PtWhereString varchar(4000),@VacSearchLocLatitude numeric(14,10), @VacSearchLocLongitude numeric(14,10)Create table #LatLong (Latitude numeric(14,10), Longitude numeric(14,10), CityID int)IF LTRIM(rtrim(@City)) <> '' BEGIN set @PtSql = 'insert into #LatLong Select CityLatitude, CityLongitude, ci.CityInternalID from GeoData.TB_City ci' set @PtJoinString = ' left join (select CityInternalID,CityName,CityTranslationStatusID from GeoData.TB_CityTranslation UNION select CityInternalID, CitySynonymName, CitySynonymStatusID from GeoData.TB_CitySynonym ) cits on ci.CityInternalId = cits.CityInternalID' set @PtWhereString =' where ((CityDefaultName = '''+ @City +''' and ci.CityStatusID = 1) or (CityName = ''' + @City + ''' and cits.CityTranslationStatusID = 1)) ' IF LTRIM(RTRIM(@Region)) <> '' BEGIN set @PtWhereString = @PtWhereString + ' and re.RegionDefaultName = ''' + @Region + ''' and re.RegionStatusID = 1' set @PtJoinString = @PtJoinString + ' inner join ( select RegionInternalID,RegionDefaultName,RegionStatusID from GeoData.TB_Region UNION select RegionInternalID,RegionName,RegionTranslationStatusID from GeoData.TB_RegionTranslation UNION select RegionInternalID, RegionSynonymName, RegionSynonymStatusID from GeoData.TB_RegionSynonym) re on ci.CityRegionID = re.RegionInternalID' END --If Province is true and country is false IF (LTRIM(RTRIM(@strCountry1)) <> '''''' OR LTRIM(RTRIM(@strCountry2)) <> '''''' OR LTRIM(RTRIM(@strCountry3)) <> '''''' OR LTRIM(RTRIM(@strCountry4)) <> '''''') and NOT (LTRIM(RTRIM(@strProvince)) <> '') BEGIN set @PtWhereString = @PtWhereString + ' and co.CountryDefaultName in (' + @strCountry1 +','+ @strCountry2 +','+ @strCountry3 +','+ @strCountry4 + ') and co.CountryStatusID = 1' set @PtJoinString = @PtJoinString + ' inner join ( select CountryInternalID,CountryDefaultName,CountryStatusID from GeoData.TB_Country UNION select CountryInternalID,CountryName,CountryTranslationStatusID from GeoData.TB_CountryTranslation UNION select CountryInternalID,CountrySynonymName,CountrySynonymStatusID from GeoData.TB_CountrySynonym) co on ci.CityCountryId = co.CountryInternalID' END --If Province is false and country is true IF (LTRIM(RTRIM(@strProvince)) <> '') and NOT (LTRIM(RTRIM(@strCountry1)) <> '''''' OR LTRIM(RTRIM(@strCountry2)) <> '''''' OR LTRIM(RTRIM(@strCountry3)) <> '''''' OR LTRIM(RTRIM(@strCountry4)) <> '''''') BEGIN set @PtWhereString = @PtWhereString + ' and pr.ProvinceDefaultName in (' + @strProvince + ') and pr.ProvinceStatusID = 1' set @PtJoinString = @PtJoinString + ' inner join ( select ProvinceInternalID,ProvinceDefaultName,ProvinceStatusID from GeoData.TB_province UNION select ProvinceInternalID,ProvinceName,ProvinceTranslationStatusID from GeoData.TB_provinceTranslation UNION select ProvinceInternalID,ProvinceSynonymName,ProvinceSynonymStatusID from GeoData.TB_ProvinceSynonym) pr on ci.CityProvinceID = pr.ProvinceInternalID' END --If Province is true and country is true IF (LTRIM(RTRIM(@strProvince)) <> '') and (LTRIM(RTRIM(@strCountry1)) <> '''''' OR LTRIM(RTRIM(@strCountry2)) <> '''''' OR LTRIM(RTRIM(@strCountry3)) <> '''''' OR LTRIM(RTRIM(@strCountry4)) <> '''''') BEGIN set @PtWhereString = @PtWhereString + ' and ((pr.ProvinceDefaultName in (' + @strProvince + ') and co.CountryDefaultName in (' + @strCountry1 + ') and pr.ProvinceLevel = 1) OR (pr.ProvinceDefaultName in (' + @strProvince + ') and co.CountryDefaultName in (' + @strCountry2 + ') and pr.ProvinceLevel = 2) OR (pr.ProvinceDefaultName in (' + @strProvince + ') and co.CountryDefaultName in (' + @strCountry3 + ') and pr.ProvinceLevel = 3) OR (pr.ProvinceDefaultName in (' + @strProvince + ') and co.CountryDefaultName in (' + @strCountry4 + ') and pr.ProvinceLevel = 4)) and pr.ProvinceStatusID = 1' set @PtJoinString = @PtJoinString + ' inner join ( select ProvinceInternalID,ProvinceDefaultName,ProvinceStatusID, ProvinceLevel from GeoData.TB_province UNION select ProvinceInternalID,ProvinceName,ProvinceTranslationStatusID, ProvinceLevel from GeoData.TB_provinceTranslation UNION select ProvinceInternalID,ProvinceSynonymName,ProvinceSynonymStatusID, ProvinceLevel from GeoData.TB_ProvinceSynonym) pr on pr.ProvinceInternalID in (ci.CityProvinceID_1, ci.CityProvinceID_2,ci.CityProvinceID_3,ci.CityProvinceID_4) inner join ( select CountryInternalID,CountryDefaultName,CountryStatusID from GeoData.TB_Country UNION select CountryInternalID,CountryName,CountryTranslationStatusID from GeoData.TB_CountryTranslation UNION select CountryInternalID,CountrySynonymName,CountrySynonymStatusID from GeoData.TB_CountrySynonym) co on ci.CityCountryId = co.CountryInternalID' END set @PtSql = @PtSql + @PtJoinString + @PtWhereString Print @PtSql --exec sp_executesql @PtSql, N'@City nvarchar(1000) OUTPUT, @Region nvarchar(1000) OUTPUT, @strCountry nvarchar(1000) OUTPUT, @strProvince nvarchar(1000) OUTPUT' , @City , @Region, @strCountry , @strProvince EXEC(@PtSql) select @VacSearchLocLatitude = Latitude, @VacSearchLocLongitude = Longitude from #LatLong ENDdrop table #LatLong [/code]And following is the final SQL query formed(Printed from Above code): [code="sql"]Select CityLatitude, CityLongitude, ci.CityInternalID from GeoData.TB_City ci left join (select CityInternalID,CityName,CityTranslationStatusID from GeoData.TB_CityTranslation UNION select CityInternalID, CitySynonymName, CitySynonymStatusID from GeoData.TB_CitySynonym ) cits on ci.CityInternalId = cits.CityInternalID inner join ( select RegionInternalID,RegionDefaultName,RegionStatusID from GeoData.TB_Region UNION select RegionInternalID,RegionName,RegionTranslationStatusID from GeoData.TB_RegionTranslation UNION select RegionInternalID, RegionSynonymName, RegionSynonymStatusID from GeoData.TB_RegionSynonym) re on ci.CityRegionID = re.RegionInternalID inner join ( select ProvinceInternalID,ProvinceDefaultName,ProvinceStatusID, ProvinceLevel from GeoData.TB_province UNION select ProvinceInternalID,ProvinceName,ProvinceTranslationStatusID, ProvinceLevel from GeoData.TB_provinceTranslation UNION select ProvinceInternalID,ProvinceSynonymName,ProvinceSynonymStatusID, ProvinceLevel from GeoData.TB_ProvinceSynonym) pr on pr.ProvinceInternalID in (ci.CityProvinceID_1, ci.CityProvinceID_2,ci.CityProvinceID_3,ci.CityProvinceID_4) inner join ( select CountryInternalID,CountryDefaultName,CountryStatusID from GeoData.TB_Country UNION select CountryInternalID,CountryName,CountryTranslationStatusID from GeoData.TB_CountryTranslation UNION select CountryInternalID,CountrySynonymName,CountrySynonymStatusID from GeoData.TB_CountrySynonym) co on ci.CityCountryId = co.CountryInternalIDwhere ((CityDefaultName = 'aadorp' and ci.CityStatusID = 1) or (CityName = 'aadorp' and cits.CityTranslationStatusID = 1)) and re.RegionDefaultName = 'europe' and re.RegionStatusID = 1 and ((pr.ProvinceDefaultName in ('Overijssel', 'Utrecht') and co.CountryDefaultName in ('netherlands') and pr.ProvinceLevel = 1) OR (pr.ProvinceDefaultName in ('Overijssel', 'Utrecht') and co.CountryDefaultName in ('') and pr.ProvinceLevel = 2) OR (pr.ProvinceDefaultName in ('Overijssel', 'Utrecht') and co.CountryDefaultName in ('') and pr.ProvinceLevel = 3) OR (pr.ProvinceDefaultName in ('Overijssel', 'Utrecht') and co.CountryDefaultName in ('') and pr.ProvinceLevel = 4)) and pr.ProvinceStatusID = 1[/code]All parameters are optional except @CityIf there is more efficient way (I'm sure there is) please suggest me.Thank you.

Bushy plan vs left-deep Posted: 20 Mar 2013 07:09 PM PDT I have read about Bushy plan and left deep in some articles. Bushy plan will consume more memory as it runs parallely. is it advisable to write query which uses bushy plan to resolve? Apart from CTE, the below code will also use bushy plan or not. right?select * from (select * from client a,security_account b where a.cli_id = b.cli_id)a, (select * from client a,security_account b where a.cli_id = b.cli_id)bwhere a.cli_id = b.cli_idUnder which circumstance, we have to write a query which uses bushy plan?

How to report on historical movements within a changing hierarchy Posted: 20 Mar 2013 10:33 AM PDT I'm in an IT environment where we're wanting to:1. run queries which will show rates of consumption of entities which are occasionally moving between nodes of a hierarchy 2. AND to correlate the rates of consumption of those entities to the nodes at the same time. At this time, a senior developer has developed a lookup table of hierarchy paths to correspond to entities, but the moment the entities position in the hierarchy changes, a record of it's old hierarchy path is lost. What approach would you use to preserve the relationship of an entity with a node path it used to belong to?

sp_addlinkedserver not able to execute with other command line Posted: 20 Mar 2013 11:15 AM PDT Hi there,I am barely new with T-SQL and trying to link to the other server database with sp_addlinkedserver.I am able to make connection with only executing the sp_addlinkedserver and then pull the data from the remote server.But, when I put everything together, the sp_addlinkedserver stops working and keep on prompt me that the target server is not on the sys.servers.sp_addlinkedserver also will not work if it were executed from the stored procedure?Working:[code="sql"]EXECUTE sp_addlinkedserver@server = 'pHpnng', @srvproduct = '',@provider = 'SQLNCLI',@datasrc = 'pHpnng',@provstr = 'DRIVER=SQL Server;SERVER=pHpnng;UID=sa;PWD=Spm2009!;'ENDSELECT * FROM sys.servers WHERE data_source = 'pHpnng'[/code]Not Working:(With the additional line will cause everything to stop working and the 'pHpnng' will not appear on the sys.servers at the end of the execution)[code="sql"]EXECUTE sp_addlinkedserver@server = 'pHpnng', @srvproduct = '',@provider = 'SQLNCLI',@datasrc = 'pHpnng',@provstr = 'DRIVER=SQL Server;SERVER=pHpnng;UID=sa;PWD=Spm2009!;'ENDSELECT * FROM [pHpnng].[OTestSystemDB].[dbo].[ListName][/code]This only works if I ran the 1st code, then run the "SELECT * FROM [pHpnng].[OTestSystemDB].[dbo].[ListName]" seperately.

Debugging stacked CTEs efficently Posted: 20 Mar 2013 06:16 AM PDT I'm curious if anyone else has figured out a good system for performing analysis on a query that may have multiple CTEs stacked. While some problems can be resolved by considering the T-SQL and making logical evaluations, sometime it's nice to be able to see the intermediate data so you can see what is really going on under the hood.When I have such query, I have the following steps:1) add a /* and */ delimiter on the bottom part of the query to comment out all subsequent steps2) comment out the line after the closing parenthesis of last CTE to make the next CTE a outer statement3) run the query, analyze the data4) uncomment the comment from #2, move the /* to the next CTE and comment out the next line after the new closing parenthesis. Repeat until we reach the final statement.This works but I wondered if there was a more effective and a bit less time-consuming & error-prone approach that would permit for data analysis of individual steps. Thanks in advance!

Running Query on Multiple Database/Servers Posted: 20 Mar 2013 09:20 AM PDT HiI have a database table that contains all the server details. I have a query and wanted to run against all servers available in the table.[u][b]Sample Query:[/b][/u][code="sql"]SELECT DISTINCT Col1, Col2 FROM dbo.Test[/code][b][u]Requirement[/u][/b]1. Connect to one server 2. Run the Query against all the user DB and save the results into a table.Please suggest a best way to from SQL server.ThanksShuaib

Add variable number of rows into a table based on the values in another table (without cursors/while loops) Posted: 20 Mar 2013 05:46 AM PDT Below is a simplified version of my tables:set nocount ondrop table #x create table #x (docid int, pages int)insert into #x values (1, 1)insert into #x values (2, 5)insert into #x values (3, 2)insert into #x values (4, 3)select * from #x;drop table #y create table #y (docid int, pagenumber int)insert into #y values (1, 1)insert into #y values (2, 1)insert into #y values (2, 2)insert into #y values (2, 3)insert into #y values (2, 4)insert into #y values (2, 5)insert into #y values (3, 1)insert into #y values (3, 2)insert into #y values (4, 1)insert into #y values (4, 2)insert into #y values (4, 3)select * from #y;set nocount offSo basically I have an input table #x with a docid and total number of pages within that docid.How can I construct the output table #y that has n rows per #x.docid where n is #x.pages?I can do it with cursors or while loops etc in a few different ways (either per docid or one insert/select per distinct #x.pages value)I am wondering if there is a set based T-SQL solution to this? Can CTEs be somehow used for this? I am creating code ultimately for SQL Server 2008 R2 enterprise edition.Any help is greatly appreciated!Thanks.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] SQL Refusing to use an index

---

• SQL Refusing to use an index
• T-SQL substring replace function
• Table Join using unique values or where Null = all values
• Help with query using exclusion table
• help with query - am I going down the right path on this
• Incrementing code to add code
• How to read XML file with multiple children and load it in SQL Server
• asp with sql server 2000?
• SP Help
• How to get month number like Jan -01, Feb-02 ... Instead of Jan-1, Feb-2
• Design of database table and PK update
• event is occurring between today at 1:00am and tomorrow at 12:59am
• Substring with IF or CASE statement
• How is SQL query processed in this example?

SQL Refusing to use an index Posted: 19 Mar 2013 10:14 PM PDT I've just had a designer come to me with a really odd problem. A query, joining two tables on a primary key, example below:SELECT <fields> FROM <table1> INNER JOIN <table2> ON <table1.primarykey> = <table2.indexed field>The query runs optimally, within a couple of seconds .. until .. an additional field is added to the SELECT fields from <table2>.I can see what is happening. Until the additional field is added, all of the fields in the SELECT are available in an index; it is an old index migrated from SQL2000 with the fields as part of the index list and not simply INCLUDEd. Stats show the index is being used and to good use.However the moment the new field from table 2 is added, which is not being covered by the index, rather than the optimizer looking for another qualifying index, it is changing from the non-clustered index to a clustered index scan of table 2 which is processing nearly 2 million rows instead of the small subset it was doing before, and the execution time increases twenty fold.As this is running under a development environment we have tried everything from 1: Creating a similar index with all the fields (a very wide index and not my first choice), but this is ignored.2: Adding the additional fields to the existing index in the INCLUDE - also ignored.3: Dropped the previously working index - This did cause it to revert back to a better existing index - but obviously any other queries benefiting from it would now degrade, and the moment we recreated the index it was back to it's primary key scan.I've cleared the cache plans, updated statistics, rebuilt the indexes, everything I can think of.It will work if we put an index hint in place and tell it the index we think it should be using, and the performance is back to a couple of seconds. I know hints are the last resort, but I can't think of anything else I can investigate before we choose this as the final solution.Any advice would be greatly appreciated. :-D

T-SQL substring replace function Posted: 19 Mar 2013 11:59 AM PDT Hello, I have some data and need to extract the first 9 numbers from this pattern across multiple rows of data. I would think that using something like this:substring(value_expression, ,1,9)might work but am unaware of how to find the first 9 digits in this sequence:01J416781|1|2013|2|I am looking to find '01J416781', each row of data starts with this pattern. I would greatly appreciate any help.

Table Join using unique values or where Null = all values Posted: 20 Mar 2013 12:35 AM PDT Hi folks,I have a complex query that involves many tables but I am struggling with one particular join (TableA and TableB). There is only one field in each table where a join can be made. TableA.Area contains values that can be matched with TableB. However TableA may also contain a Null value in the joining column. If there is a Null value, then TableA.Area needs to join with [b]all[/b] values in TableB.ColId. [code="sql"]USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TableA]( [Id] [tinyint] IDENTITY(1,1) NOT NULL, [Area] [nvarchar](10) NULL, [Item] [nvarchar](10) NOT NULL, [Description] [nvarchar](50) NOT NULL, CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]) ON [PRIMARY];INSERT INTO dbo.TableA(Area, Item, [Description])VALUES('A1', 'B1232', 'Thingymebobs');INSERT INTO dbo.TableA(Area, Item, [Description])VALUES('A4', 'B4352', 'Whatits');INSERT INTO dbo.TableA(Area, Item, [Description])VALUES(Null, 'B8769', 'Dunno');USE [DB1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TableB]( [ColId] [nvarchar](10) NOT NULL, [DeliveryName] [nvarchar](60) NOT NULL, CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED ( [ColId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]) ON [PRIMARY];INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A1', 'Acme1');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A4', 'Acme4');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A8', 'Acme8');INSERT INTO dbo.TableB(ColId, DeliveryName)VALUES('A9', 'Acme9');[/code]The columns to be joined are TableA.Area AND TableB.ColId[code="sql"]SELECT b.DeliveryName, a.Item, a.[Description]FROM TableA a JOIN Tableb b ON --????;[/code]Any ideas please?Thanks in advance,

Help with query using exclusion table Posted: 20 Mar 2013 12:38 AM PDT I have a metrics database and a few reports running from it.One of them is to report on databases that haven't had a full backup run in the last week...It checks other tables for Production and Online dbs etc etc...My query looks something like:[code="sql"]SELECT bh.Server AS Server ,bh.database_name ,MAX(bh.backup_finish_date) AS last_db_backup_date ,datediff(dd,MAX(bh.backup_finish_date),Getdate()) AS Age ,bh.backup_type FROM Backup_History bh INNER JOIN ServerList_SSIS sl ON bh.Server = sl.ServerDisplayName AND sl.Environment = 'PROD' AND sl.BackupReports = 'Y' INNER JOIN Database_Info dbi ON bh.Server = dbi.Server AND bh.database_name = dbi.DatabaseName AND dbi.Status <> 'OFFLINE' WHERE bh.backup_type = 'Database' GROUP BY bh.Server ,bh.database_name ,bh.backup_type HAVING (MAX(bh.backup_finish_date) < DATEADD(dd, - 8, GETDATE())) ORDER BY Age DESC ,bh.Server ,bh.database_name[/code]The output right now is:Server database_name last_db_backup_date Age backup_typeSERVER1 DB1 2013-03-09 18:32:50.000 11 DatabaseSERVER1 DB2 2013-03-09 13:03:16.000 11 DatabaseSERVER1 DB1 2013-03-09 14:31:14.000 11 DatabaseSERVER2 DB2 2013-03-09 12:56:19.000 11 DatabaseSERVER2 DB3 2013-03-09 13:30:17.000 11 Database(Sorry about the formatting)Now I need to exclude the DB1 database so I created an exclusion table with Server and DB columns and added that server and database.I need help with the query to exclude that one from the report:I've tried:[code="sql"]AND (bh.Server NOT IN(SELECT SERVER from ReportExclusionList)AND bh.database_name NOT IN(SELECT DatabaseName FROM ReportExclusionList))[/code]After the WHERE clause but it excludes all databases from the specified server...I Just can't wrap my head around the sql... (Maybe change the NOT IN portion to include the database and server...)Any help would be appreciated.Thanks

help with query - am I going down the right path on this Posted: 19 Mar 2013 11:20 PM PDT I'm working on a query that will be comparing columns in tables in 2 seperate databases. If the where clause is met, I need to create a log file and then email it at the end of the procedure.here is my query:SELECT db1.*, db2.*FROM db1..ORGANIZATION db1inner join db2..ORGANIZATION db2ON db1.ORG_ID = db2.ORG_IDwhere db1.TITLE <> db2.TITLEordb1.CODE <> db2.CODEordb1.ACTIVE <> db2.ACTIVEif any of the criteria in the where clause is met, I need to create a log file and email it. I'd like to this is using SSIS, however, how can I have a return value of [true] returned in the where statement if something meant, plus the values?so for exampleif db1.organization <> db2.orgranization, I want the values from both databases, return true so I can create a log file, like I mentioned I'd like to do this using SSIS, but if I can't I'll be doing this in a stored procedure that will be kicked off nightlyAm I even going down the right direction with comparing values in the tables?

Incrementing code to add code Posted: 19 Mar 2013 05:25 PM PDT Hi,I am trying to join the table to itself many times on a condition. Is there a way to automate number of joins based on count of something from another table?Any help appreciable!Thanks,Akber.

How to read XML file with multiple children and load it in SQL Server Posted: 19 Mar 2013 08:31 PM PDT I have an XML file in the below given format. How can I import it into SQL Server using OPENXML or some other method ?----------------------------------------------------------------------------------------------------------------------<?xml version="1.0" encoding="utf-8"?><SRCL> <list> <AOSRC xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SRC> <SRCNm> <string>Orders.xml_</string> </SRCNm> <SRCHsh> <string>KA-ORDRS-21-Jan-2013</string> </SRCHsh> <SRCRslts> <XSDOS /> </SRCRslts> </SRC> <SRC> <SRCNm> <string>OrderDetails.bin_</string> </SRCNm> <SRCHsh> <string>KJ-18-Mar-2013-WEGraded</string> </SRCHsh> <SRCRslts> <XSDOS> <item> <FieldNm> <string>WarehouseEntryTS</string> </FieldNm> <value> <SRlt> <name> <string>WearhouseEntryTS</string> </name> <value> <string>25-Jul-12 8:24:35 PM</string> </value> <weight> <int>2</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>Warehouse Type</string> </FieldNm> <value> <SRlt> <name> <string>Closed and Roofed</string> </name> <value> <string>Type A</string> </value> <weight> <int>143</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>DLCR</string> </FieldNm> <value> <SRlt> <name> <string>DLCR</string> </name> <value> <string>00008540</string> </value> <weight> <int>1</int> </weight> </SRlt> </value> </item> <item> <FieldNm> <string>CandF_Section</string> </FieldNm> <value> <SRlt> <name> <string>Forwarding Sections</string> </name> <value> <string>PEandPaperCorrugated</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>PackingAndMovement</string> </FieldNm> <value> <SRlt> <name> <string>PackingAndMovement</string> </name> <value> <string>5 X 6 Feet , With godowns</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> </XSDOS> </SRCRslts> </SRC> <SRC> <SRCNm> <string>PendingOrders.bin_</string> </SRCNm> <SRCHsh> <string>KA-UE-04-Feb-2013</string> </SRCHsh> <SRCRslts> <XSDOS> <item> <FieldNm> <string>WearhouseEntryTS</string> </FieldNm> <value> <SRlt> <name> <string>WearhouseEntryTS</string> </name> <value> <string>25-Jan-13 8:10:15 PM</string> </value> <weight> <int>2</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Warehouse Type</string> </FieldNm> <value> <SRlt> <name> <string>Warehouse Type</string> </name> <value> <string>v8.0</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>DLCR</string> </FieldNm> <value> <SRlt> <name> <string>DLCR</string> </name> <value> <string>00008540</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Forwarding Sections</string> </FieldNm> <value> <SRlt> <name> <string>Forwarding Sections</string> </name> <value> <string>ElectricalCarriage</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>PackingAndMovement</string> </FieldNm> <value> <SRlt> <name> <string>PackingAndMovement</string> </name> <value> <string>5 X 6 Feet , Without godowns</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>QuantityTransfer</string> </FieldNm> <value> <SRlt> <name> <string>QuantityTransfer</string> </name> <value> <string>yes</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>StoreID</string> </FieldNm> <value> <SRlt> <name> <string>StoreID</string> </name> <value> <string>Footware</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>Sodexo</string> </FieldNm> <value> <SRlt> <name> <string>Sodexo</string> </name> <value> <string>Sodexo</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> <item> <FieldNm> <string>AC Head</string> </FieldNm> <value> <SRlt> <name> <string>AC Head</string> </name> <value> <string>1</string> </value> <weight> <int>1</int> </weight> <ConvertibleYN> <int>0</int> </ConvertibleYN> <tag> <anyType xsi:nil="true" /> </tag> </SRlt> </value> </item> </XSDOS> </SRCRslts> </SRC> </AOSRC> </list></SRCL>

asp with sql server 2000? Posted: 19 Mar 2013 04:42 PM PDT hai friends, I m currently using sql server 2000 DB for my asp applicationmy table iscreate table ttr(startdate datetime,from_loctn varchar(100),to_location varchar(100),Modeoftravel nvarchar(100),seattype nvarchar(100))Modeoftravel and seattype is dropdownbox. if im choose it ll store value only not name(ex:insert into ttr('20-03-2013','abc','dbn','train','IIAC')but showing my table as modeoftravel value is 1 seattype value 1 how to modify

SP Help Posted: 19 Mar 2013 11:41 AM PDT Hello

How to get month number like Jan -01, Feb-02 ... Instead of Jan-1, Feb-2 Posted: 19 Mar 2013 09:34 AM PDT Hi, Need help on get month number from Date field, I am getting month number as 1 for Jan, 2 for FebBut I need 01 for Jan, 02 for Feb..like thisCan you please some one help me outThanksGrace

Design of database table and PK update Posted: 19 Mar 2013 11:05 AM PDT We are creating an address database to maintain student address info.I have two tables like this :The address table is a master table that holds all addresses. The studHomeAddress table will hold all students and their address- the addressID comes from the master address table. The primary key of the Address table is Address ID, the PK for studHomeAddress table is studentID and AddressID, we are going to have a screen for user to add or edit for each of the two tables.They can add and edit records, for both of the tables.For the first table Address, I think it is no problem. For the second table, if we want to change the address of the student to another one, then the AddressID will be changed for this record, but it is the PK, the PK for the row will be changed. Is that an OK design?Thanks,

event is occurring between today at 1:00am and tomorrow at 12:59am Posted: 19 Mar 2013 08:57 AM PDT Hello,I need a query that finds event is occurring between today at 1:00 am and tomorrow at 12:59 am. I have 2 columns.event_date datatype =date and event_time datatype= timeThis query gives me,SELECT CONVERT(datetime, CONVERT(varchar, DATEADD(day, 0, GETDATE()), 102))--2013-03-19 00:00:00.000 but instead of 0.00 I need my time whihc is 1:00 AM and 12:59 AM of next day.

Substring with IF or CASE statement Posted: 19 Mar 2013 02:15 AM PDT Hi,I am trying to import into SQL Server 2008 R2 a list of strings as follows:=KET+N.207-13-F4001=KET+DG014-13-F4011=KET+RE002-36-MV009I need to split the strings so they are inserted into separate fields. One of the substring statements is:'SUBSTRING(xlCode; 15; 2) [if the second position is a number, and]'SUBSTRING(xlCode; 15; 1) [if the second position is anything else]AS GroupNoMy experience with T-SQL is just not enough to figure this one out. Can you tell me how the statement should be set up?Thanks in advance for any help.Maarten

How is SQL query processed in this example? Posted: 19 Mar 2013 04:04 AM PDT Hi,I have already posted the problem on Stackoverflow. Can you please help?[url=http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example]http://stackoverflow.com/questions/15505165/how-is-sql-query-processed-in-this-example[/url]Thanks much in advance!Regards,Nayan

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Help with the query!

---

Help with the query! Posted: 18 Mar 2013 10:20 PM PDT I came across a situation as follow[code="sql"]CREATE TABLE DAYOFWEEK(WeekID INT IDENTITY,DAY NVARCHAR(50))CREATE TABLE SubjectToStudy(WeekID INT,SUBJECT NVARCHAR(50))INSERT INTO DAYOFWEEK VALUES('MONDAY')INSERT INTO DAYOFWEEK VALUES('TUESDAY')INSERT INTO DAYOFWEEK VALUES('WEDNESDAY')INSERT INTO SubjectToStudy VALUES(1,'PHYSICS')INSERT INTO SubjectToStudy VALUES(1,'CHEMISTRY')INSERT INTO SubjectToStudy VALUES(1,'MATHEMATICS')INSERT INTO SubjectToStudy VALUES(2,'COMPUTERSCIENCE')INSERT INTO SubjectToStudy VALUES(2,'BIOLOGY')INSERT INTO SubjectToStudy VALUES(3,'BOTANY')INSERT INTO SubjectToStudy VALUES(3,'PHYSICS')[/code]I want the output as MONDAY PHYSICS CHEMISTRY MATHEMATICSTUESDAY COMPUTERSCIENCE BIOLOGY WEDNESDAY BOTANY PHYSICSI tried using Pivot table, but was not able come up with the above output. Could you guys please help!!!

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. Posted: 29 Apr 2011 02:15 AM PDT I have the following XML statement, where I want the union of several tables to be presented as XML.I get this error: The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.My SQL:select ISNULL((SELECT top 10 au.sex,au.firstname,au.middlename,au.lastname,am.email,c.id AS objectid,c.title AS objecttitle,c.friendlyurl as objecturl,3 as objecttype,am.CreateDate FROM aspnet_users au INNER JOIN aspnet_membership am ON am.userid=au.userid INNER JOIN cameras c ON c.userid=au.userid WHERE c.indexboost=0 UNION ALL SELECT top 10 au.sex,au.firstname,au.middlename,au.lastname,am.email,c.id AS objectid,c.title AS objecttitle,c.friendlyurl as objecturl,1 as objecttype,am.CreateDate FROM aspnet_users au INNER JOIN aspnet_membership am ON am.userid=au.userid INNER JOIN locations c ON c.userid=au.userid WHERE c.indexboost=0 AND c.id NOT IN (SELECT objectid FROM emailssent WHERE category=c.objecttype AND emailid=2) order by am.CreateDate asc FOR XML RAW, ELEMENTS ,ROOT ('user')),0) as recordsHow can I fix it to get it to work?Thanks!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Ranking functions, views, and predicates

---

• Ranking functions, views, and predicates
• How can I create intervals fro start-dates only
• Sql Query
• Stop large queries from running
• BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'.
• outputting the Stored Proc output to Text File Pipe (|) Seperated with Headers
• For Learning

Ranking functions, views, and predicates Posted: 18 Mar 2013 05:52 AM PDT So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue in SQL Server 2005 but supposedly addressed in SQL Server 2008. I'm running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4260.0 (X64). Here is my scenario:View is defined as follows:[code="sql"]CREATE VIEW dbo.vw_BatchItemsSELECT BatchID, FieldA, FieldB, CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrderFROM <myDatabase>.<mySchema>.<myTable>;[/code]Now when I code a procedure to query the view and add a WHERE clause it looks like this:[code="sql"]CREATE VIEW dbo.usp_sel_BatchItems (@BatchID INT)ASSELECT *FROM <myDatabase>.<mySchema>.vw_BatchItemsWHERE BatchID = @BatchID;[/code]We found that this procedure now is in our top 10 (well, the top 1 of the top 10) I/O consuming procedures (the underlying tables are large). While the query still runs fast we found that the ROW_NUMBER is causing an index scan on the index over BatchID. When we remove the ranking function from the view the SELECT statement now does an index seek on the index over BatchID. And after reviewing both execution plans it clearly shows that the predicate is not pushed to the view when the ROW_NUMBER() function is in the view. Without it the predicate is pushed to the view.Any thoughts on this one? Is there a way to force SQL server to perform the WHERE condition first instead of scanning the entire index to resolve the ROW_NUMBER() function? In our case, we only need the function to return results based on the filtered criteria anyway.

How can I create intervals fro start-dates only Posted: 18 Mar 2013 07:23 AM PDT Let's assume we have1) A key value, like a product number2) and a ValidFrom date, like thisMyProductKey, 20100111MyProductKey, 20110205MyProductKey, 20120101What we want is the followingMyProductKey, 20100111, 20110204MyProductKey, 20110205, 20111231MyProductKey, 20120101, NULLWhat would be the simplest way to do this?TIAPeter

Sql Query Posted: 17 Mar 2013 07:02 PM PDT I have to return one result set by writing two different logic. Both of the logic are very complex.. one logic returns about 5 columns and other logic too returns 5 columns. I am not sure how to combine all columns and display the result in one place as there is not only one key.. there is no fixed key to link both results from temp table... Any idea? If my question is not clear then please let me know.. I will give an example..

Stop large queries from running Posted: 17 Mar 2013 05:41 PM PDT Hi,I have a query window with a lot of code but I do not want to run it entirely in error, is there something I can place in the beginning of the code that will throw me a message?Thanks,Akber.

BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'. Posted: 18 Mar 2013 05:47 AM PDT DECLARE @Cmd VarChar(4000) DECLARE @SProcName VarChar(40) DECLARE @SP_Parm1Value VarChar(40) DECLARE @SP_Parm2Value VarChar(4000) set @SProcName = 'Rpt_JobTransactionsSp'set @SP_Parm2Value = '|SRMQDIC|,|ROD|,|B|,|HNS|,|0|,|B|,null,null,null,null,|000|,|9999|,|1/1/2013|,|3/17/2013|,null,null,null,null,null,null,null,null,|T|,null,null,|1|,|0|,|1033|'set @SP_Parm2Value = REPLACE (@SP_Parm2Value,'|', '''')-- Build BCP Command set @CMD = '' Set @Cmd = @Cmd + 'bcp "exec ' + db_name() Set @Cmd = @Cmd + '..' + @SProcName + ' 'Set @Cmd = @Cmd + @SP_Parm2Value + '"'Set @Cmd = @Cmd + ' Queryout ' + ' "c:\temp\query.txt" ' + ' -T -c -t^| -S '+ @@servernameSELECT @Cmd--output looks like thisbcp "exec RSDV_App..Rpt_JobTransactionsSp 'SRMQDIC','ROD','B','HNS','0','B',null,null,null,null,'000','9999','1/1/2013','3/17/2013',null,null,null,null,null,null,null,null,'T',null,null,'1','0','1033'" Queryout "c:\temp\query.txt" -T -c -t^| The following statement works great and gives me desired data in the grid.exec RSDV_App..Rpt_JobTransactionsSp 'SRMQDIC','ROD','B','HNS','0','B',null,null,null,null,'000','9999','1/1/2013','3/17/2013',null,null,null,null,null,null,null,null,'T',null,null,'1','0','1033'But when i execute the follwing commandEXEC master..xp_cmdshell @cmdErrors outoutput---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQLState = 37000, NativeError = 2812Error = [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'.NULLThere is a prcedure 'dbo.EXTGEN_InitSessionContextSp' running inside 'Rpt_JobTransactionsSp'.Whats the workaround for this ?

outputting the Stored Proc output to Text File Pipe (|) Seperated with Headers Posted: 18 Mar 2013 04:56 AM PDT Command to runExec Rpt_OrderReport '1/1/2013' , '1/31/2013'Need a code which will save the output to Text file with headers and column data pipe (|) seperated.

For Learning Posted: 18 Mar 2013 12:00 AM PDT I want to improve my DB Knowledge , i have experience in MS-SQL and ORACLE.My Problem is when i face any interview i couldn't write query very fast .i have taken very long time to finished that query .please help me

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Pivot Query with Count

---

Pivot Query with Count Posted: 17 Mar 2013 12:41 AM PDT Hi thereI am fairly new to SQL Server TSQL and I have a strong MS Access background.I can't get my head around pivot queries is TSQL.I have 3 Colums with data in it:1) City2) Delivery_type3) Date_DeliveredI want to create a pivot query that has the format with 14 columns1) City (Grouped)2) Deliver (Grouped)3) Jan (Count the amount of dates for month of Jan)4) Feb (Count the amount of dates for month of Feb)...14) Dec (Count the amount of dates for month of Feb)If you can guide me in the right direction I will appreciate it, Thx

problem in select command Posted: 16 Mar 2013 10:53 AM PDT hi please look at this select command :[Code][b]select top 2 * from dbo.NFFeeds order by id desc [/b][/code]for my database the result is 2 rows .first by id=33 and second by id=32by this command i get two last rows from my table . how can i get this two rows like this :first row by id=32 and second row by id=33

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Backup large database to multiple files

---

• Backup large database to multiple files
• Custom sp_who
• Column update based on expiration criteria
• SQL Query Help using XML

ABC123DEF456'2, '2ColABC2ColDef2Colxyz'Tried few combinations using FOR XML, but could not get the desired result at each row level.

Backup large database to multiple files Posted: 15 Mar 2013 06:13 AM PDT Hi everyone,I manage 100+ SQL servers and some of them contain over 120 – 800 GB + databases. I googled for backup script that backs up database depending on the size and strips the .bak file to multiple files. So far no luck. The closest one I got to is this script. Is there a way I can backup smaller databases in one .bak file and 60 + GB databases to multiple .bak (stripped) files dynamically in the same backup script? This way I can standardize it across all the servers. Thanks in advance or your help.Hi everyone,I manage 100+ SQL servers and some of them contain over 120 – 500 GB + databases. I googled for backup script that backs up database depending on the size and strips the .bak file to multiple files. So far no luck. The closest one I got to is this script. Is there a way I can backup smaller databases in one .bak file and 60 + GB databases to multiple .bak (stripped) files? Thanks in advance or your help.DECLARE @Baksql VARCHAR(8000) DECLARE @BackupFolder VARCHAR(100) DECLARE @BackupFile VARCHAR(100) DECLARE @BAK_PATH VARCHAR(4000) DEclare @BackupDate varchar(100) DEclare @Size varchar(100)-- Setting value of backup date and folder of the backup SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551 SET @BackupFolder = 'C:\temp\' SET @Baksql = '' -- Declaring cursor DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR SELECT NAME FROM SYS.DATABASES WHERE state_desc = 'ONLINE' -- Consider databases which are online AND database_id > 4 -- Exluding system databases -- Opening and fetching next values from sursor OPEN c_bakup FETCH NEXT FROM c_bakup INTO @BackupFile WHILE @@FETCH_STATUS = 0 BEGIN SET @BAK_PATH = @BackupFolder + @BackupFile -- Creating dynamic script for every databases backup SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;' -- Executing dynamic query PRINT (@Baksql) EXEC(@Baksql) -- Opening and fetching next values from sursor FETCH NEXT FROM c_bakup INTO @BackupFile END -- Closing and Deallocating cursor CLOSE c_bakup DEALLOCATE c_bakup

Custom sp_who Posted: 15 Mar 2013 08:09 AM PDT Hello All,Couldn't find anything that addresses this specifically, so here goes.I have a group that I want to allow to view process on their dev server, but I don't want to grant view server state permissions and all that it exposes, so I'm trying to write a proc that will execute sp_who as me (the DBA) for users, but it still only returns one row instaed of all of them; what am I missing? MS's docs say this should work.USE [master]GO/****** Object: StoredProcedure [dbo].[usp_who] Script Date: 03/14/2013 14:57:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================-- Author: -- Created: 03/14/13-- Desc: sp_who for users-- =============================ALTER PROCEDURE [usp_who]with execute as selfASBEGIN SET NOCOUNT ON; exec sp_who;END

Column update based on expiration criteria Posted: 15 Mar 2013 06:52 AM PDT I am trying to get a homegrown database to automatically update a 'IsActive' column. I have a merge statement for the update, but I have no idea how to get this statement to run when the record is suppose to go from active to inactive and vice versa.My merge statement uses a view like this.[code="sql"]SELECT T1.SerialNumber, CASE WHEN (DATEADD(MONTH, T2.Schedule, T2.Date1) >= CAST(CURRENT_TIMESTAMP AS DATE)) THEN 1 WHEN (T2.Extension = 1 AND T2.DateExtended >= CAST(CURRENT_TIMESTAMP AS DATE)) THEN 1 WHEN (DATEADD(MONTH, T2.Schedule, T2.Date1) < CAST(CURRENT_TIMESTAMP AS DATE) AND (T2.DateExtended IS NULL OR T2.DateExtended < CAST(CURRENT_TIMESTAMP AS DATE))) THEN 0 ELSE CAST(NULL AS TINYINT) END AS DetermineIsActive, CAST (CURRENT_TIMESTAMP AS DATE) AS CurrentTimestampFROM dbo.Table1 AS T1 INNER JOIN dbo.Table2 AS T2 ON T1.SerialNumber = T2.SerialNumber[/code]My merge statement is[code="sql"] MERGE dbo.Table1 T1 USING (SELECT SerialNumber, DetermineIsActive, CurrentTimestamp FROM vIsActiveUpdate) AS vT2 ON T1.SerialNumber = vT2.SerialNumber WHEN MATCHED THEN UPDATE SET T1.IsActive = vT2.DetermineIsActive;[/code]This does the update I want, but I have no idea how to get it to execute when, for example T2.DateExtended < CURRENT_TIMESTAMP. I looked at triggers, but I don't think I want to do that. Everyone says not to use triggers, plus I can't figure out how the trigger would know when to execute. The criteria isn't really updating, it is expiring.Hopefully I was able to explain this clearly enough.Thanks

SQL Query Help using XML Posted: 15 Mar 2013 03:18 AM PDT Looking for SQL Query help on following.Have a key value pair, as below.declare @t table (id int, [key] varchar(10), value varchar(10))insert @t values (1, 'ColA', 'ABC123')insert @t values (1, 'colB', 'DEF456')insert @t values (2, 'colA', '2ColABC')insert @t values (2, 'colC', '2ColDef')insert @t values (2, 'colE', '2Colxyz')Need to bundle the Key-value combination into XML for each ID row. The expected results are1, '

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610