Thursday, March 21, 2013

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

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

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.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog