Monday, October 7, 2013

[T-SQL] Need help with Dynamic Query Results

[T-SQL] Need help with Dynamic Query Results

Need help with Dynamic Query Results

Posted: 07 Oct 2013 12:55 AM PDT

So my problem:I have to query Linked servers for Version number and evaluate it against another. This is going to be used in a SP that will do other stuff, not related to this issue. Also, the environment has MSSQL 2000 up to MSSQL 2008R2. I specifically want to identify the MSSQL 2000 servers, they are my issue in the next phase of my project.My solution (but not it seems):I have created the following Dynamic Query to hit the Linked servers:DECLARE @ServerName AS VARCHAR(50) SET @ServerName = <YOURSERVERNAME> DECLARE @Sql AS VARCHAR(100), @Result AS VARCHAR(10) SET @Sql = 'SELECT * FROM OPENQUERY([' + @ServerName + '], ' + '''' + 'SELECT SERVERPROPERTY(' + '''' + '''' + 'productversion' + '''' + '''' + ')' + '''' + ')'EXECUTE (@Sql) OUT, @RESULT OUTPUTIF @Result <= '9.00.1399.06'BEGINPRINT 'Yes'ENDELSEPRINT 'No'The first declare parameter @ServerName and PRINT statements are just to test my conditional results. @ServerName will be generated from a CURSOR later. I'm sure my issue has to do with the OUT and OUTPUT parameters, but I just can't seem to get my head unlocked from this one. Any help is greatly appreciated!

Sending messages to specified connection/spid through SP

Posted: 06 Oct 2013 11:29 PM PDT

Hi,I have done a bridge between an ERP system and a TA system. The user calls a SP on the sql server. The SP retrives a lot of data and makes several texfiles using XP_cmdshell. Finally it uses XP_cmdshell to send one file through FTP (curl.exe). It also updates a loggfile and should send error messages to the client if needed. My huge problem is that when the SP uses XP_cmdshell the spid changes and looses connection to the client and after that I cant get the SP to send error messages to the client anymore. Everything works fine when I execute the SP in a Query on the server. PLEASE, PLEASE, PLEASE could someone help me to solve this? Is it possible to send errormessges to a specified spid? What is the common solution to this problem?By the way, is it stupid to have everything in the same SP? Is it better to split the SP into seperate ones? Regards Malin

Need some help with finding broken views

Posted: 06 Oct 2013 11:22 PM PDT

Good Morning Everyone. Happy Monday! Lol.I have a script that runs daily to find broken views. This morning I realized that my code would hang on a specific view. After some research, I noticed the view had a couple of columns that was not in the table and sql server would just sit there.Need some help on how to overcome this. I was thinking if I can set a timeout property for the query, lets say 10 seconds, and just throw a generic error.[code="sql"]    declare @viewname as varchar(max);                 begin try                                --insert nic_schedulerlog select 'NIC_GetBrokenViews Started', '', getdate()                                                               if object_id('tempdb..#views') is not null drop table #views;                                                               truncate table NIC_BrokenViews                                 --get all views                                select name                                into #views                                from sys.objects                                where type='V' and name not like '%prep' and name not like '%zz%'                                order by name                                                               while 1=1                                                begin                                                                --get next view to work with                                                                select top 1 @viewname=name from #views                                                                                                                               --if 0, then we're done                                                                if @@rowcount=0 break;                                                                                                                               --try to get a result                                                                begin try                                                                                --exec('select top 1 * into #tmp from ' + @viewname + ' where 1=2')                                                                                print @viewname                                                                                exec('set fmtonly on; select top 1 * into #tmp from ' + @viewname + ' set fmtonly off;')                                                                end try                                                                begin catch                                                                                --failed, store viewname and errormsg                                                                                --insert NIC_BrokenViews                                                                                select @viewname, error_message()                                                                end catch                                                                                                                               --were done with it, delete it                                                                delete from #views where name=@viewname                                                end                                 drop table #views                                                               --insert nic_schedulerLog select 'NIC_GetBrokenViews Completed', '', getdate()                end try                begin catch                                PRINT ' Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));                                PRINT ' Error Message : ' + ERROR_MESSAGE();                                PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));                                PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10));                                PRINT ' Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10));                                PRINT ' Error Proc : ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');                                --insert nic_schedulerLog                                --select 'NIC_GetBrokenViews Failed', ERROR_MESSAGE(), getdate()                                                               --if OBJECT_ID('tempdb..##SchedulerErrors') is not null                                --             begin                                --                             insert ##SchedulerErrors select ERROR_PROCEDURE(), ERROR_MESSAGE()                                --             end                end catch [/code]

How do you map to rows inside different branches of same XML document using OPENXML rowset function?

Posted: 06 Oct 2013 09:02 AM PDT

I'm able to map to one hierarchy of an XML document at a time, but I need to retrieve data sitting in other hierarchies of the same document. In the XML below 1. Root is Joblog2. Header node contains server, name, start_time, type, log_name3. Media_drive_and_media_info node contains it's own set of children of which I am interested in (for example) media_mount_date.But, with the below query, only header node info is retrieved[code="plain"]USE SandboxGOdeclare @dochandle as int;declare @xmldocument as nvarchar(max);set @xmldocument = '<joblog> <job_log_version version="2.0" /> <header> <filler /> <server>Job server: BUMMERMMS0 </server> <name>Job name: BUMMERBUILDS\DAILYBUILDS D:-Normal Weekly Backup Policy-Weekly - FULL To LTO3 </name> <start_time>Job started: Wednesday, September 04, 2013 at 9:17:28 AM </start_time> <type>Job type: Backup </type> <log_name>Job Log: BEX_BUMMERMMS0_00004.xml </log_name> <filler /> </header><media_mount_date>Drive and media mount requested: 9/4/2013 9:17:28 AM</media_mount_date><media_drive_and_media_info> <media_mount_date>Drive and media information from media mount: 9/4/2013 9:17:38 AM</media_mount_date> <drive_name>Drive Name: BUMMERMMS0-B2D</drive_name> <media_label>Media Label: B2D006436</media_label> <media_guid>Media GUID: {0c9fa835-f3dc-42fe-a7e6-9894842919c9}</media_guid> <media_overwrite_date>Overwrite Protected Until: 12/30/9999 4:00:00 PM</media_overwrite_date> <media_append_date>Appendable Until: 12/30/9999 4:00:00 PM</media_append_date> <media_set_target>Targeted Media Set Name: WEEKLY - FULL</media_set_target></media_drive_and_media_info></joblog>';EXEC sp_xml_preparedocument @dochandle OUTPUT, @xmldocument;SELECT server, name, start_time, type, log_name, media_mount_dateFROM OPENXML(@dochandle, 'joblog/header', 1) WITH (server [varchar](20) 'server',name [varchar](300) 'name',start_time [varchar](100) 'start_time',type varchar(20) 'type',log_name varchar(100) 'log_name',media_mount_date varchar(100) 'media_mount_date')EXEC sp_xml_removedocument @dochandleGO[/code]To get both Header and Media_drive_and_media_info info I have tried CROSS APPLY without success: SELECT server, name, start_time, type, log_name, media_mount_dateFROM OPENXML(@dochandle, 'joblog/header', 1) CROSS APPLY OPENXML (@dochandle, 'joblog', 1) WITH (server [varchar](20) 'server',name [varchar](300) 'name',start_time [varchar](100) 'start_time',type varchar(20) 'type',log_name varchar(100) 'log_name',media_mount_date varchar(100) 'media_mount_date')The CROSS APPLY attempt retrieved only the media_mount_date with NULLs for all preceding columns.Can you help me rewrite the query so that I can I traverse up and down the nodes hierarchies to get ALL data extracted?Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog