Wednesday, October 2, 2013

[T-SQL] wish to add rows having NULL values as placeholders for "missing" dates

[T-SQL] wish to add rows having NULL values as placeholders for "missing" dates

wish to add rows having NULL values as placeholders for "missing" dates

Posted: 02 Oct 2013 12:06 AM PDT

I have a table that contains stuff happening. There's a datetime stamp column to show on what date the stuff happened. There are days where nothing happens. I want to select from this table and add rows to the result set on the fly, for those days on which nothing happened. Reason: I've got to pull it into excel and need a placeholder for those days.For the purpose of illustration, I've created a table containing the start datetimes of all backups that have been kicked off for a server called Peaches. This table also contains the name of the server, the size of backup, and the associated jobid. On some days there are no backups eg. 8/05/2013 through 8/13/2013, BUT I still want to generate a row for those days as a placeholder.Can you provide guidance on how to do?Here's DDL.[code="plain"]CREATE TABLE [dbo].[CannedBackupJobs]( [jobid] [int] NULL, [SizeTB] [float] NULL, [StartTime] [datetime] NULL, [ServerName] [varchar](20) NULL) insert into [dbo].[CannedBackupJobs]values(83, 365.226943141887,'2013-08-04 03:20:30.777', 'Peaches'),(83, 408.830221699759, '2013-08-14 18:26:53.220', 'Peaches'),(83, 391.654500133873, '2013-08-15 15:44:34.977', 'Peaches'),(83, 397.063717616127, '2013-08-20 02:10:57.747', 'Peaches'),(83, 353.803773579467, '2013-08-24 05:56:26.090', 'Peaches');[/code]

Linking a SQL Server 2005 (Windows Authentication) to a SQL Server 2008 (Mixed Mode)

Posted: 01 Oct 2013 08:20 PM PDT

I am trying to link a production database (SQL Server 2000) that is on Windows authentication to a test database (SQL Server 2008) that is on mixed mode authentication.I have already tried the code below from [url=http://www.sqlservercentral.com/Forums/Topic699311-149-1.aspx#bm699348]this link[/url]:[code="sql"]DECLARE @LinkName SYSNAME SET @LinkName = 'PRODUCTIONSERVER'DECLARE @SrvName SYSNAME SET @SrvName = 'PRODUCTIONSERVER'DECLARE @LocalLogin SYSNAME SET @LocalLogin = 'sa' --login on test dbDECLARE @RmtLogin SYSNAME SET @RmtLogin = 'DOMAIN\UserName' --win auth login on prod dbDECLARE @RmtPwd SYSNAME SET @RmtPwd = 'password' --win auth password for the login on prod dbIF NOT EXISTS (SELECT * FROM Master..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName)BEGIN EXECUTE sp_addlinkedserver @server = @LinkName, @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @SrvName EXECUTE sp_addlinkedsrvlogin @rmtsrvname = @LinkName, @useself = 'false', @locallogin = @LocalLogin, @rmtuser = @RmtLogin, @rmtpassword = @RmtPwdEND[/code]...a linked server is successfully added but when I try a query like:[code="sql"]SELECT COUNT(*) FROM PRODUCTIONSERVER.ProdDatabase.dbo.ProdTable[/code]I get an error:[code="plain"]Msg 18456, Level 14, State 1, Line 0Login failed for user 'DOMAIN\UserName'.[/code]I have also tried logging into the prod database using the mentioned windows authentication credentials (my personal login) and I can get through.The reason for the linking is because I want to test the merging of live data (from sql 2000) into the test database (sql 2008).Any thoughts on how I can link the servers?Thank you very much. :)

Need help on SP

Posted: 01 Oct 2013 06:03 AM PDT

I'm trying to create an SP to execute the followingmsdb.dbo.sp_add_jobstepmsdb.dbo.sp_update_jobstepA 3rd party software installer created about 300-400 jobs. And naturally, the jobs aren't emailing out when it fails. So I'm trying to add a step to the job and alter the 1st steps of the job. I can run this for each job replacing the job_name with the job name that got created; however, it'll take forever.[quote]USE MSDBGOEXEC sp_add_jobstep @job_name = N'InstallerJob1', @step_name = N'EmailOut', @subsystem = N'TSQL', @command = N'EXEC sp_FailedJob ''InstallerJob1''', @on_success_action = 2GOEXEC sp_update_jobstep @job_name = N'InstallerJob1', @step_id=1, @on_fail_action = 4, @on_fail_step_id = 2GO[/quote]However, I thought it'll be quicker to execute an SP.This is the SP I have so far.[quote]SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONGOCREATE PROCEDURE sp_StepsWITH ENCRYPTIONAS --Purpose: To create job step email notification for -- jobs created automatically from installer.DECLARE@jobneed VARCHAR(150)BEGIN -- begin spDECLARE job_cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobsWHERE name NOT LIKE 'BACKUP%'AND name NOT LIKE 'sys%'ORDER BY name;OPEN job_cursorFETCH NEXT FROM job_cursorINTO @jobneedWHILE @@FETCH_STATUS = 0BEGIN -- begin fetchEXEC msdb.dbo.sp_add_jobstep @job_name = N'@jobneed', @step_name = N'EmailOut', @subsystem = N'TSQL', @command = N'EXEC sp_FailedJob ''@jobneed''', @on_success_action = 2EXEC msdb.dbo.sp_update_jobstep @job_name = N'@jobneed', @step_id=1, @on_fail_action = 4, @on_fail_step_id = 2END -- end fetch close job_cursor deallocate job_cursorEND -- end procGO[/quote]Getting the following errors:Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67The specified @job_name ('@jobneed') does not exist.I think I'm just missing passing the jobneed variable into the param. Am I on the right path?

Dropping/Recreating Clustered Index

Posted: 01 Oct 2013 05:23 AM PDT

Hi SSC,I've got a table with a clustered index which needs to be dropped and recreated to add an additional column to it. The table also has three non-clustered indices on it. I seem to recall reading somewhere that to do that, I should either drop or disable the non-clustered indices beforehand, but I can't remember exactly why or which one. Can anyone shed some light on the best practice here?

BCP text out that includes quotation marks

Posted: 01 Oct 2013 04:45 AM PDT

I'm trying to export a text string that includes " to a text file using BCP.This code works as expected by exporting the word blah into a text file on my C drive:[code="sql"]-- Turn on cmdshell EXEC sp_configure 'xp_cmdshell', 1reconfiguregoDECLARE @cmd varchar(1000)SET @cmd = 'bcp "SELECT ''blah''" queryout "C:\bcpout.txt" -w -T'EXEC master..xp_cmdshell @cmd[/code]However if I change my text string from 'blah' to include quotation marks so it reads 'blah"blah', it fails to do anything.[code="sql"]DECLARE @cmd varchar(1000)SET @cmd = 'bcp "SELECT ''blah"blah''" queryout "C:\bcpout.txt" -w -T'EXEC master..xp_cmdshell @cmd[/code]Is there a way I can get the quotation marks exported to my text file using BCP?

Slow Script

Posted: 01 Oct 2013 01:14 AM PDT

Hello. I ran the following script that too 3 hours 25 minutes to insert about 40,000 rows. Is there any logic that I can put into this to speed it up? Thank you!insert into T3select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3from T1join T2 on T1.COL4 = T2.COL3where T2.COL4 != 'data1'and T1.COL1 is not NULLand T1.COL5 is not NULLand T1.COL6 = 'data2'and CAST (T2.COL1 as VARCHAR)+'-'+CAST(T2.COL2 as VARCHAR)+'-'+CAST(T1.COL1 as VARCHAR)+'-'+CAST(T1.COL2 as VARCHAR) not in(select CAST(COL1 as VARCHAR)+'-'+CAST(COL2 as VARCHAR)+'-'+CAST(COL3 as VARCHAR)+'-'+CAST(COL4 as VARCHAR) from T3)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog