SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some eyes on a SP I'm working on


Need some eyes on a SP I'm working on

Author
Message
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2244 Visits: 2536
For the simple file move, I agree with you. But I'm doing a bit more than that with the file name as a variable. I'm not sure I could easily do that with PS.

So like, where I insert the file name into the Schema.ini file, or email the file name... Yeah. Not sure about those things with your method.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40636 Visits: 14413
erikd (6/25/2013)
For the simple file move, I agree with you. But I'm doing a bit more than that with the file name as a variable. I'm not sure I could easily do that with PS.

So like, where I insert the file name into the Schema.ini file, or email the file name... Yeah. Not sure about those things with your method.

Use Out-File to write a new file with PowerShell.

One of PowerShell's strengths is its help system. If you learn to use the help, you can learn how to script in PowerShell in a hurry. At a PowerShell prompt type

help Out-File

or

help Out-File -Examples


to see how to use Out-File.

To send an email with PowerShell, if you like the audit trail Database Mail offers you can connect to your database and execute sp_send_dbmail using Invoke-SqlCmd. Or you can skip the database dependency completely and add a function to your script to send an email directly out of PowerShell, like this: http://blogs.msdn.com/b/rkramesh/archive/2012/03/16/sending-email-using-powershell-script.aspx

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2244 Visits: 2536
I'll see if I can figure it out when I have some extra time. I'm most likely going to leave this SP as-is (at least for now), since it's working, and try to integrate PS into something a bit simpler when starting fresh.

The way things usually go with me at work is: I use T-SQL because it's all I have any practical experience using to do things programmatically (and everything I need to do involves a file needing to end up in a table, or something in a table that needs to be manipulated).

I know other things like SSIS and PS exist as solutions, but I'd have no idea where to start with them. And since it's a constant issue of providing the fastest solution, I don't necessarily have the bandwidth to figure something totally foreign out. Or to even know when something else would be a better solution than SQL. Does that make sense?

Thanks
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40636 Visits: 14413
erikd (6/25/2013)
I'll see if I can figure it out when I have some extra time. I'm most likely going to leave this SP as-is (at least for now), since it's working, and try to integrate PS into something a bit simpler when starting fresh.

The way things usually go with me at work is: I use T-SQL because it's all I have any practical experience using to do things programmatically (and everything I need to do involves a file needing to end up in a table, or something in a table that needs to be manipulated).

I know other things like SSIS and PS exist as solutions, but I'd have no idea where to start with them. And since it's a constant issue of providing the fastest solution, I don't necessarily have the bandwidth to figure something totally foreign out. Or to even know when something else would be a better solution than SQL. Does that make sense?

Thanks

It definitely makes sense. I deal with that dilemma as well. Regarding where to start or knowing which tool might be better suited to do something than another, that's what is great about these forums, and the community in general, is we can share ideas when a problem is presented for comment. You knowing what is possible using PowerShell and knowing that it is better suited to handle the problem you're trying to solve than is T-SQL is a large part of the barrier to you trying to use it. I am not saying to tell your folks that the project is going to take longer because you have to learn PowerShell first. I am saying to give it a look, and try to work it into your day. I am confident you will find that not all problems are best solved using T-SQL, in fact most problems that involve interacting with the file system are not best solved using T-SQL. I won't get into the reasons, but suffice it to say that if you find yourself using xp_cmdshell then you are probably in need of a design rethink, and in need of looking to something other than T-SQL to solve the problem at hand.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218989 Visits: 42001
I'm using a Schema.ini file because when I load these files without one, the data becomes garbled.


"Load these files" how and where? I'm asking because one of the biggest problems you're having seems to be with the creation of the Schema.Ini file for each file. If you're using something like BULK INSERT, the Schema.Ini file isn't necessary. You can just change to a "raw" type file setting.

Also, you didn't answer my question. Why can't we move "all" the files at once and send a single email with the list of files moved?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2244 Visits: 2536
Jeff Moden (6/25/2013)
I'm using a Schema.ini file because when I load these files without one, the data becomes garbled.


"Load these files" how and where? I'm asking because one of the biggest problems you're having seems to be with the creation of the Schema.Ini file for each file. If you're using something like BULK INSERT, the Schema.Ini file isn't necessary. You can just change to a "raw" type file setting.

Also, you didn't answer my question. Why can't we move "all" the files at once and send a single email with the list of files moved?


Sorry, when I referred to "these" files, I meant the test files they've been sending. There will only be one file to move at a time when their process is finalized. So one file, every Monday (morning hopefully).

I tried with bulk insert, and the data did not pipe out to columns correctly. I'm using a Schema.ini because it was more straight forward to create for me than a .fmt file.

The files they're sending are being created overseas, so I haven't gotten a straight answer about how they're being created. They're supposed to arrive to me Unicode, tab-delimited, but there's something weird about them, and the extension is just being changed to .txt from whatever it is originally. It took three weeks for them to stop sending me UTF-8 files (I know, code page 65001).

So, I ended up using openrowset with a Schema.ini that runs essentially like this, but within a SP:


select * into sample.dbo.[eriktest]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=\\server\Sample\;
FORMATFILE=\\server\Sample\Schema.ini',
'SELECT * FROM [file.txt]')



Eventually it will be going to a real table when they've finalized the layout.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218989 Visits: 42001
Ah. Understood.

If the data isn't sensitive, could you attach one of the test files so we can see if there's a better way? If it is sensitive, could you doctor up a version that I could try for you? It would also be handy if you posted the CREATE TABLE script for the final destination table.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2244 Visits: 2536
I don't even have what the final version of these files will be yet. So, no table creation data either. I'd hesitate to waste any of your time on "evolving" test data.

This is a result of some SAP compliance, so about all that's clear is that they're totally annihilating the old layout and format. There are probably a few more hour long phone calls trying to get them to explain any mapping of new columns to old columns in my near future, as things change.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218989 Visits: 42001
Understood but you have at least one file that made you think you need a Schema.ini file. If we solve that, then it'll be one less thing to worry about when the real data shows up.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2244 Visits: 2536
I would be okay with messaging you a DB link to some dummy-ish data, but I don't think I'd want any of it out in the wild. Let me know if that works for you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search