Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Need some eyes on a SP I'm working on Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 10:39 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:15 AM
Points: 494, Visits: 2,052
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.
Post #1467284
Posted Tuesday, June 25, 2013 11:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,141, Visits: 12,768
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
Post #1467293
Posted Tuesday, June 25, 2013 11:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:15 AM
Points: 494, Visits: 2,052
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
Post #1467310
Posted Tuesday, June 25, 2013 12:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,141, Visits: 12,768
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
Post #1467314
Posted Tuesday, June 25, 2013 12:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,817, Visits: 32,491
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467320
Posted Tuesday, June 25, 2013 12:56 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:15 AM
Points: 494, Visits: 2,052
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.
Post #1467327
Posted Tuesday, June 25, 2013 1:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,817, Visits: 32,491
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467333
Posted Tuesday, June 25, 2013 1:39 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:15 AM
Points: 494, Visits: 2,052
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.

Post #1467357
Posted Tuesday, June 25, 2013 3:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 35,817, Visits: 32,491
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467392
Posted Tuesday, June 25, 2013 3:08 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:15 AM
Points: 494, Visits: 2,052
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.
Post #1467393
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse