I worked on a restore script that had to consume values from the network share and produce a restore statement. This database happened to be striped to eight files. I needed a way to construct the restore statement into a single row and I was able to achieve the desired results using the following syntax.
DECLARE @temp TABLE ( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,colors VARCHAR(30) ) INSERT INTO @temp (colors) VALUES ('Red') ,('Blue') ,('Green') ,('Yellow') ,('Brown'); SELECT colors FROM @temp SELECT 'Colors' = 'RESTORE ' + (SELECT colors + ';' FROM @temp FOR XML PATH (''), type).value('.', 'varchar(max)')
Here’s the end results.
I’ve dealt with a lot of scenarios where I needed to troubleshoot and test a reporting services subscription simply because it failed. Most of the issues stem from the owner permissions, typos when it comes to the email addresses or because the person left the company and didn’t remove their subscriptions. Knowing where to look and how to associate the rather ugly naming convention of the sql jobs in reference to the subscriptions can eat up some valuable time. So let me save you some time by sharing a script I have used on numerous occasions.
The script joins a few ReportServer specific tables to sys.objects. I cannot recall if I had tested this against SQL 2005 but I do know it works well with SQL 2008 and it would be safe to say that it works with 2008 R2 as well. I am pretty certain it should execute without a hiccup on a 2005 instance. I haven’t tested against 2012 as of yet, but when I do I’ll definitely update this post.
To bread and butter. Here’s the script. The following will return all SQL Jobs associated to SQL Server Reporting Services subscriptions.
SELECT cmd='EXEC msdb.dbo.sp_start_job @job_name= ''' + sj.[name] + ''';' ,ReportName=c.[name] ,ReportPath=c.[Path] ,LoginName=u.[UserName] ,SQLJobCreateDate=sj.[date_created] FROM msdb.dbo.sysjobs sj JOIN [ReportServer].[dbo].[ReportSchedule] AS rs (NOLOCK) ON sj.[name] = CAST(rs.[ScheduleID] AS NVARCHAR(128)) JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON rs.[ReportID] = c.[ItemID] JOIN [ReportServer].[dbo].[Users] u (NOLOCK) ON c.[CreatedByID] = u.[UserID] ORDER BY sj.[date_created] DESC
I like to know what was created today so I run the following:
SELECT cmd='EXEC msdb.dbo.sp_start_job @job_name= ''' + sj.[name] + ''';' ,ReportName=c.[name] ,ReportPath=c.[Path] ,LoginName=u.[UserName] ,SQLJobCreateDate=sj.[date_created] FROM msdb.dbo.sysjobs sj JOIN [ReportServer].[dbo].[ReportSchedule] AS rs (NOLOCK) ON sj.[name] = CAST(rs.[ScheduleID] AS NVARCHAR(128)) JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON rs.[ReportID] = c.[ItemID] JOIN [ReportServer].[dbo].[Users] u (NOLOCK) ON c.[CreatedByID] = u.[UserID] WHERE (CONVERT(VARCHAR(10),sj.[date_created],101) = CONVERT(VARCHAR(10),GETDATE(),101)) ORDER BY sj.[date_created] DESC
The cmd column provides me the command needed to kick off the subscription. Typically most of the subscriptions are email based so if it hits my inbox I know I am good!
First off this post should have been pressed long ago (5/31/2012), but for whatever reason I merely saved it to draft rather than publish it. So without further ado…
Not long ago I started to encounter very intermittent lockups with SSMS 2012. I searched the web hi and low to see if anyone else had encountered this very problem, but I ended up with nothing remotely close. So I decided to turn to twitter and posted my issue to the sql community using the #sqlhelp hashtag.
— Samson J. Loo (@ayesamson) May 24, 2012
@ayesamson: Yes. Are you running into this issue constantly, or just once? Problem might come from shared DLL’s with VS2010…
— J. Verheul (@DevJef) May 24, 2012
And that’s how our glorious conversation began… Then @DevJef mentioned he reinstalled SP1 for VS2010 and that cleared up his problem.
— J. Verheul (@DevJef) May 24, 2012
So I followed suit and applied SP1 for VS2010 and I am happy to report that I am no longer experiencing random lockups with SSMS 2012! Thank you @DevJef and thank you sql community for always being there!
Please visit Jef’s post (#SQLHelp – SQL 2012 Management Studio Freezes). His insight helped me tremendously and save me a lot of frustration and headaches!
I won’t get into all of the “ins and outs” about BCP, but feel free to read up on the subject here: Import and Export Bulk Data by Using the bcp Utility (SQL Server)
I have images stored in a SQL Server database and I need to access them without writing, purchasing or using a custom app. What can I do?
Step 1: Identify the table in question
In this case we are working with the Images table inside of the [devdb] database
Step 2: Generate a BCP format file
Create the format file by executing the following statement in a command prompt window.
bcp devdb.dbo.Images format nul -c -fimages.fmt -T -S (local)\DEV12 -v
This will produce a file named images.fmt in the C:\Temp\ directory
Step 3: Modify the format file
Open the newly pressed images.fmt with notepad. We will (very carefully) edit the contents. So here’s a look at the original file. Note: I modified the following image to fit the width of this layout. Meaning I cropped some white-space. Also note I have SQL 2005, 2008 R2 and SQL 2012 installed on my box. The 10.0 on the first line is present because the BCP version is from SQL 2008. It would show 11.0 if I were using BCP for SQL 2012.
Here is what it needs to look like after we make some adjustments. We modified the file to single out the image column. Be sure to SAVE your changes, especially changing SQLCHAR to SQLBINARY; otherwise, empty files will be created with a zero file size. Notice the white-space in the following image. I really don’t know if it makes a difference but the slight modifications I do make always work.
Step 4: Enable xp_cmdshell
If you don’t have xp_cmdshell enabled here’s the means to do so. Keep in mind if you don’t use xp_cmdshell often then be sure to disable it when you’re finished. You can search the subject in respects to security and xp_cmdshell.
EXEC sp_configure 'Show Advanced Options',1; RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE; GO
Step 5: Write up query
In the event you don’t have images this is what I used to load up a few sample files. I literally used the sample pictures that came preloaded with windows 7. Which are located in the C:\Users\Public\Pictures\Sample Pictures directory.
/* LOAD TABLE */ INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Chrysanthemum.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Chrysanthemum.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Desert.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Desert.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Hydrangeas.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Hydrangeas.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Jellyfish.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Jellyfish.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Koala.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Koala.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Lighthouse.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Lighthouse.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Penguins.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Penguins.jpg',SINGLE_BLOB) AS x; INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Tulips.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Tulips.jpg',SINGLE_BLOB) AS x;
Let’s take a look at the contents of the table.
/* VERIFY TABLE CONTENTS */ SELECT id, Image, FileName FROM devdb.dbo.Images;
Step 6: Export images
Now that we know the table definition, adjusted the format file and enabled xp_cmdshell let’s extract some photos! We could run through this one-by-one but where’s the fun in that. I used a WHILE loop as opposed to a cursor to extract each photo by id.
/* EXPORT PHOTOS FROM DATABASE */ DECLARE @cnt INT, @rcnt INT, @cmd NVARCHAR(150), @fname NVARCHAR(50), @filepath NVARCHAR(50), @inst NVARCHAR(50) /* SET VARIABLES */ SELECT @rcnt = COUNT (*) FROM [devdb].[dbo].[Images] SET @cnt = 1 SET @filepath = 'c:\temp\' SET @inst = '(local)\DEV12' /* LOOP THROUGH RECORDS */ WHILE @cnt < @rcnt BEGIN SELECT @fname = FileName FROM devdb.dbo.Images WHERE (id = @cnt) SELECT @cmd = 'BCP "SELECT Image FROM [devdb].[dbo].[Images] WHERE (id = ' + CAST(@cnt AS VARCHAR) + ')" queryout "' + @filepath + @fname + '" -T -S ' + @inst + ' -f c:\temp\Images.fmt' --SELECT @cmd = 'BCP "SELECT Image FROM [devdb].[dbo].[Images] WHERE (id = ' + CAST(@cnt AS VARCHAR) + ')" queryout "c:\temp\' + @fname + '" -T -S (local)\DEV12 -f c:\temp\Images.fmt' PRINT @cmd /* PRINT STATEMENT TO SCREEN */ EXEC xp_cmdshell @cmd SET @cnt = @cnt + 1 END
That’s pretty much it. Happy BCP’ing and exporting!
If you are like me you save most (if not all) of the scripts or queries that you create. It is just a huge time saver and with our aggressive work load we need all the time we can spare. In my case I deal with a significant volume of production deployments/promotions so I burn through scripts frequently.
On a few occasions (when I need it most of course) the file names often drift from memory so I sometimes look to my recent file list in SSMS to help jog my memory. Yes we have deployment request which I can easily reference but this post is about SQL Server Management Studio, so there. :-)
Back to the subject… by default SSMS 2012 will only display the last six but what if you want to extend that number to 10 or more?
I’d like to point out that Jugal Shah (blog | @imjugal) published an article that covers SSMS 2000, 2005, 2008 and 2008 R2. Here is the link to his article if you would like to read his post Change Setting for Recently Used Files in SQL Server Management Studio SSMS.
The process is pretty much identical with SSMS 2012 and you go about it like so…
Step 1: Tools >> Options
Step 2: Increase the value for: “items shown in recently used lists”
Step 3: Let’s Verify
The change is immediate, so there’s no need to close and reopen SSMS.
Well today was my first attempt outside of a classroom setting to dig my heels into SSAS DMX. I have an idea that I want to put into action (automating a process) and I believe it will benefit my organization. So fire up SSMS and connect to instance that has Analysis Services.
1) Start a New DMX Query
2) Execute Script
SELECT * FROM $system.DBSCHEMA_CATALOGS
3) Returning Specific Columns
Similar to Transact-SQL you just specify the columns you want; however, you need to wrap up the column names with brackets [column].
SELECT [CATALOG_NAME] FROM $system.DBSCHEMA_CATALOGS
Pretty simple. Now let’s try with XMLA, which is XML for Analysis Services.
Just like before connect to an instance which has Analysis Services and published cubes.
1. Start a New XMLA Query
2. Execute the Script
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DBSCHEMA_CATALOGS</RequestType> <Restrictions /> <Properties /> </Discover>
There are many ways to go about getting the last backup date for any and all database but I tend to lean towards set based methods over using the GUI for many reasons. One in particular is for the fact that I can turn set based into an actionable report since monitoring backups is important after all.
The method I use the most is the following because it only returns information about databases that currently exists. I really do not need to see information about what used to exist, but I will also show that example as well. Before I go on you might want to understand the anatomy of both the master.sys.databases catalog view and msdb.dbo.backupset table.
/* GET LAST BACKUP DATE FOR ALL EXISTING DATABASES */ SELECT d.name, MAX(b.backup_finish_date) 'Last Backup Date' FROM master.sys.databases d JOIN msdb.dbo.backupset b ON d.name = b.database_name WHERE (b.type = 'D' OR b.type = 'I') GROUP BY d.name ORDER BY d.name
The results indicate (104 row(s) affected). So I have 104 databases on this particular instance.
This is the example of returning the history for the last backup date which may contain information about past databases which depends on your maintenance for backup history. So this may vary.
/* GET LAST BACKUP DATE FROM BACKUPSET */ SELECT database_name, MAX(backup_finish_Date) 'Last Backup Date' FROM msdb.dbo.backupset WHERE (type = 'D' OR type = 'I') GROUP BY database_name ORDER BY database_name
The results indicate (108 row(s) affected) so between the two statements you can see there is a difference of four rows which is why I avoid using the msdb.dbo.backupset as the only source of record.
The easiest way to determine which databases no longer exists is to execute the following.
/* RETURN NON EXISTING DATABASE NAMES */ SELECT DISTINCT database_name FROM msdb.dbo.backupset WHERE ( database_name NOT IN ( SELECT name FROM master.sys.databases ) )
The results indicate that (4 row(s) affected) and the names of the database that are not current.