September 1, 2015 at 12:29 pm
Hi all,
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
)
AS
BEGIN
SELECT @LastName, @FirstName, prsBirthDate, prsCountryID
FROM tbPersons
WHERE prsLastName = @LastName AND prsFirstName = @FirstName
END
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:\SQLServerResults
Is this possible?
Thanks in advance
Hein
September 1, 2015 at 12:46 pm
You can do it with the bcp utility by using xp_cmdshell or SSIS or Powershell.
September 1, 2015 at 1:22 pm
Hi Luis
Thanks for your reply.
I have not yet heard of the bcp - utility but am searching for information.
Your suggestion looks very promising, but has as far as I can see, a set back in the way I would like to use it.
I need to run a query mulitple times, with varying variables.
With a bcp utility it seems, as far as I can see, that I need to manually adjust the line / code each time I run it.
As said, I need to run the query a lot of times, the less repeating work is involved, the better / faster it is.
Is there no way I can add the output - code directly in the query, using the variables.
My example is just simple. I am trying to understand what to do, with the goal to adjust it to the real situation
The reason I would like the date and time add is to easily identify the last version.
Hein
September 1, 2015 at 2:00 pm
How is this procedure going to be called, will it be run manually from SSMS bcp and powershell would both be command line tools would you run it manually there? Do you plan to automate it? Will it be called by an external service of some kind?
September 1, 2015 at 2:23 pm
Here's an example on how to do it dynamic. It's not that complicated and it could become a stored procedure on itself.
DECLARE @LastName nvarchar(100) = 'Tamburello',
@FirstName nvarchar(100) = 'Roberto'
DECLARE @Command nvarchar(4000)
SET @Command = 'bcp "EXEC TEST.dbo.spTryOut @LastName='
+ QUOTENAME(@LastName,'''')
+ ', @FirstName='
+ QUOTENAME(@FirstName,'''')
+ '" queryout "D:\SQLServerResults\'
+ @LastName + @FirstName + REPLACE( CONVERT(varchar(19), GETDATE(), 120), ':', '')
+ '.csv" '
+ '-c '
+ '-CRAW '
+ '-S YourServer\AndInstance '
+ '-T '
+ '-t,'
EXEC xp_cmdshell @Command;
September 2, 2015 at 7:05 am
I would focus on using PowerShell. You can make the call to the procedure, capture the output to file. It's the most direct mechanism available. You'll have maximum control and flexibility.
It's generally considered a poor practice to directly access the OS from within a stored procedure. That's why it's better to access the stored procedure from the OS. Use PowerShell.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 2, 2015 at 11:59 am
Luis,
Sorry, I am afraid this is ground too unfamiliair for me. Alas.
I have spent the better part of my afternoon trying to get things to work, but have failed so far, no matter how many variations I try.
This is what I've done.
There was a message that the xp_cmdshell option wasn't enabled. So I tried (found the code on the site from msdn.microsoft):
"
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
"
It then asked for another Reconfigure. so I did that again (just that word and Go)
Than I changed my stored procedure to
ALTER PROCEDURE [dbo].[spTryOut]
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
)
AS
BEGIN
SELECT @LastName, @FirstName, prsBirthDate, prsCountryID
FROM tbPersons
WHERE prsLastName = @LastName AND prsFirstName = @FirstName
DECLARE @Command nvarchar(4000)
SET @Command = 'bcp "EXEC TEST.dbo.spTryOut @LastName='
+ QUOTENAME(@LastName,'''')
+ ', @FirstName='
+ QUOTENAME(@FirstName,'''')
+ '" queryout "D:\SQLServerResults\'
+ @LastName + @FirstName + REPLACE( CONVERT(varchar(19), GETDATE(), 120), ':', '')
+ '.csv" '
+ '-c '
+ '-CRAW '
+ '-S MSSQLSERVER\HEIN-PC '
+ '-T '
+ '-t,'
EXEC xp_cmdshell @Command;
END
There are some things I am not sure if they are correct (changing them didn't help)
1. Why is the name TEST.dbo.spTryOut instead of spTryOut?
2. the '-s MSSQLSERVER\HEIN-PC' part I am not sure this is correct I have also tried HEIN-PC\MSSQLSERVER and MSSQLSERVER\HEIN
These are the names in configuraion manager and in SQL Server in the object explorer.
The 1st part of the stored procedure works (I get the result I expected, albeit very slow (trying to find a connection?)
The saving of the file didn't work
In the bottom screen I get:
"
SQLState = 08001, NativeError = 87
Error = [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Connection string is not valid [87].
SQLState = 08001, NativeError = 87
Error = [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Login timeout expired
NULL
"
I see that the connection is not working properly (or maybe there are more problems)
Maybe the solution is simple, I don't see it.
Hein
PS
Working with Powershell? I looked at some films on YouTube and that seems something for later
September 2, 2015 at 12:23 pm
This isn't a natural thing to do, but you should be able to get it done.
I might not have been very clear, but you need 2 stored procedures. One is for the query (this is optional but preferred) and the other one is to create the file. You also need to be sure about the server name you're using. You can also let the query handle it, but that's up to you.
Here's a full example:
CREATE PROCEDURE [dbo].[spTryOut]
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
)
AS
SELECT *--@LastName, @FirstName, prsBirthDate, prsCountryID
FROM AdventureWorks2012.Person.Person
WHERE LastName = @LastName AND FirstName = @FirstName
GO --This is what defines the end of a Stored Procedure or more exactly the end of the batch. BEGIN and END don't define limits of the procedure.
CREATE PROCEDURE [dbo].[spTryOutCreateFile]
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
)
AS
DECLARE @Command nvarchar(4000)
SET @Command = 'bcp "EXEC TEST.dbo.spTryOut @LastName='
+ QUOTENAME(@LastName,'''')
+ ', @FirstName='
+ QUOTENAME(@FirstName,'''')
+ '" queryout "D:\SQLServerResults\'
+ @LastName + @FirstName + REPLACE( CONVERT(varchar(19), GETDATE(), 120), ':', '')
+ '.csv" '
+ ' -c ' --Character data type
+ ' -CRAW ' --No code page transformation is done
+ ' -S ' + CAST( SERVERPROPERTY ( 'SERVERNAME' ) AS varchar(128)) --Current server
+ ' -T ' --Use a trusted connection
+ ' -t,' --Field terminator is a comma
EXEC xp_cmdshell @Command;
GO
EXEC spTryOutCreateFile 'Miller', 'Dylan';
GO
DROP PROC spTryOut
DROP PROC spTryOutCreateFile
September 2, 2015 at 12:56 pm
Hi Luis
Good that you're online and thanks for your response.
I think there's some movement in our quest.
I have ran your code (after adapting it to my situation, like servername, and used it.
This worked for 3 times, now no more files are added in the directory SQL ServerResults, no matter how often I run the procedure.
Maybe a question
I now have 2 stored procedures
Should I not call the procedure [spTryOutCreateFile] from [spTryOut] so that it can save the results from spTryOut
In Excel's VBA I can call a function to work with the results from a sub
2nd: I don't understand the phrase "TEST.dbo.spTryOut" Where does this TEST. come from?
Thanks Hein
September 2, 2015 at 1:03 pm
Should I not call the procedure [spTryOutCreateFile] from [spTryOut] so that it can save the results from spTryOut
No, you should call [spTryOut] from [spTryOutCreateFile]. That's how it's coded.
[spTryOut] will return the results so they can be used by any application. [spTryOutCreateFile] calls bcp which uses [spTryOut] to get the results and generate the file.
2nd: I don't understand the phrase "TEST.dbo.spTryOut" Where does this TEST. come from?
TEST is the name of the database I use to test solutions that I post in here. Change it to the appropriate database name for your environment.
September 2, 2015 at 1:36 pm
Luis
I can't say how happy I am, it works!! 😀
Muchas Gracias, soy?/estoy? muy contento
In first instance I had "overcorrected" the code, changing 'servername' with 'MSSQLSERVER' which didn't work.
After I changed it back everything works like clockwork.
Sorry for my lack of understanding the code in the beginning, which is what you get if you want to do things you're basically not ready for.
Anyway, I have a working code and i am very, very happy.
Thanks again
Hein
PS
how can I add to your reputation?
September 2, 2015 at 1:45 pm
You're welcome. I hope that you had finally understood the code and what does it do on each step.
It would be "estoy". Use "estar" for something that denotes a temporary state.
About my reputation, it's just based on personal opinion and my posts. The system on this forum doesn't care about that and leaves all to the users.;-)
September 2, 2015 at 1:48 pm
I would still encourage you to learn how to do this in Powershell or SSIS (DBAs prefer PoS, Developers usually go for SSIS). This will give you additional flexibility and more security features.
September 2, 2015 at 2:02 pm
I will dive into Powershell
In fact I have looked on Youtube, but these films take longer than I had time today.
The learning never ends 😀
Hein
September 3, 2015 at 9:19 am
SpeedSkaterFan (9/2/2015)
I will dive into PowershellIn fact I have looked on Youtube, but these films take longer than I had time today.
The learning never ends 😀
Hein
A couple more sources. Take a look at the Stairways series here on this site, http://www.sqlservercentral.com/stairway/, there's a Powershell series that's 9 chapters long, last updated 5 months ago. And there's Redgate's free book series at https://www.red-gate.com/community/books/. I don't see any books there right now for Powershell, but there might be others there that might strike your fancy.
And you're absolutely right, the learning never ends. 🙂
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply