Save query results directly using variables and date/time

Question

Post reply

Save query results directly using variables and date/time

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 1

Hi Wayne

Thank you for your valueable addition .

I've looked into the stairway, and there's much to learn 🙂

It will take some time but that's OK

The Redgate eBook I've taken a glimse at. Useful, but indeed: I also see at first glance no mentioning of the Powershell

You've given me homework, thanks for that

Hein

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Grant Fritchey (9/2/2015)
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.

I would first focus on why someone is calling a stored procedure in a RBAR fashion.

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.

[/quote]

I've got to take exception to most of that, ol' friend. Just because a million people think so, doesn't make it right. So, let me get this straight... it's not OK to run a protected stored procedure that uses xp_CmdShell to get to the OS to make a call to BCP but it's alright to give someone privs to stumble about in the OS to use PowerShell in an unchecked manner to do the same thing? :-P;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Luis Cazares (9/2/2015)
You also need to be sure about the server name you're using.

Nicely done, Luis.

Just a tip... if it all occurs on the server itself, you don't need the name of the server unless there is more than one instance on the same server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Jeff Moden (9/4/2015)
Luis Cazares (9/2/2015)
You also need to be sure about the server name you're using.
Nicely done, Luis.
Just a tip... if it all occurs on the server itself, you don't need the name of the server unless there is more than one instance on the same server.

I had the problem with a single instance on my laptop which is a named instance. So it seems that it won't care about the number, only the name.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Jeff Moden (9/4/2015)
Grant Fritchey (9/2/2015)
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.
I would first focus on why someone is calling a stored procedure in a RBAR fashion.
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.
I've got to take exception to most of that, ol' friend. Just because a million people think so, doesn't make it right. So, let me get this straight... it's not OK to run a protected stored procedure that uses xp_CmdShell to get to the OS to make a call to BCP but it's alright to give someone privs to stumble about in the OS to use PowerShell in an unchecked manner to do the same thing? :-P;-)

You're mean! 😛

As with all things, you gotta do it right. You're correct of course, just exposing the system through PowerShell is pretty nuts. However, you really don't have to do that. A remote system running PowerShell can connect directly to SQL Server. It'll never have direct access to the underlying server. Or, you can have the powershell run, say within Agent, again, the DBA access doesn't change. You're just using the right tool for the job.

And boy, I wish it was a million DBAs using Powershell. That would be awesome! 😀

"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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Grant Fritchey (9/8/2015)
Jeff Moden (9/4/2015)
Grant Fritchey (9/2/2015)
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.
I would first focus on why someone is calling a stored procedure in a RBAR fashion.
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.
I've got to take exception to most of that, ol' friend. Just because a million people think so, doesn't make it right. So, let me get this straight... it's not OK to run a protected stored procedure that uses xp_CmdShell to get to the OS to make a call to BCP but it's alright to give someone privs to stumble about in the OS to use PowerShell in an unchecked manner to do the same thing? :-P;-)
You're mean! 😛
As with all things, you gotta do it right. You're correct of course, just exposing the system through PowerShell is pretty nuts. However, you really don't have to do that. A remote system running PowerShell can connect directly to SQL Server. It'll never have direct access to the underlying server. Or, you can have the powershell run, say within Agent, again, the DBA access doesn't change. You're just using the right tool for the job.
And boy, I wish it was a million DBAs using Powershell. That would be awesome! 😀

Heh... would it help if I told you that I used xp_CmdShell to call PowerShell to call WMI across multiple servers to get my morning disk report data and then used XML to create HTML and send it all in an email using CDOSYS? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)