Chris, I have used it in VBScript but I'm new to PS and was trying to figure out how to read and use in PS.
Main Topics
Browse All TopicsI have a script that searches the file system for a string then if found copies the file to a destination directory. these parameters are in the ps1 file. I want to be able to put these parameters in a table and also log the results to a seperate table. How can i do this?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
There are some good examples out there:
http://sqlblog.com/blogs/a
I used the same to build mine. If you can tell me the queries you'd like to run I'm sure I could help put that in context.
Chris
Well basically I want read one line at a time from the BD then search for file based on that info then copy the file then log the file, path and destination to a SQL table. Here is what I have so far. I know it very rough so please excuse my mess. lol. I am able to query the table and get back info but it doesnt read into the gci command and it doesnt write to sql.
There are 2 tables one for params and one for logging. All fields are setup for varchar.
Any help is appreciated. Thank you in advance.
Joe
Haven't used Powershell to this extent by reading and writing SQL Server Tables.
Anyhow read it long time back on this one.
Kindly check this one out..
http://msdn.microsoft.com/
http://msdn.microsoft.com/
Sorry, forgot to reply.
Lets take a look...
First you have to deal with scoping. That is, the variables you create and use within the function Get-SqlParms don't exist outside of that.
I believe this is why you're running into problems writing back, a lot of null values in there.
We either need to make Get-SqlParms return information, or we need to remove the functions entirely. I've gone with the latter here, it's simpler for now.
And finally, I'm making assumptions about what you want from the logging, so yell if it's not correct.
Chris
Chris, thanks for that.
Here is the error i get when i run what you created. It does copy the files but does not write log to sql.
Exception calling "ExecuteNonQuery" with "0" argument(s): "There is already an
open DataReader associated with this Command which must be closed first."
At C:\Documents and Settings\user\My Documents\
tions3.ps1:74 char:38
+ $Results = $SqlCmd2.ExecuteNonQuery( <<<< )
rrjegan17, thanks for the info, i will look into that as well.
Getting there :) (you're awesome)
Now we get this error
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '('."
At \path to script\ServerMigration4.ps
+ $Results = $SqlCmd.ExecuteNonQuery( <<<< )
refering to
$Results = $SqlCmd.ExecuteNonQuery()
But now $SqlData contains information when checked before the for each statment.
still get this
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax nea
r '('."
At c:\pathtoscript\ServerMigr
but the query echos back with only partial info like this
INSERT INTO (server, path, filename, copydate, copiedto) VALUES ('server', '\\server\c$\compaq\hpdiag
Well this is what i get.
INSERT INTO (server, path, filename, copydate, copiedto) VALUES ('servername', '\\servername\c$\compaq\ne
The second item should only be '\\servername\c$\compaq\ne
statement looks correct now: what did you change?
INSERT INTO (server, path, filename, copydate, copiedto) VALUES ('server', '\\server\c$\compaq\networ
But I still get that error
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '('."
Here is the final product. I created two connection and enabled MARS. The only gotcha is that it will copy the file even if it exists on the destination and write to the table that it was moves. It would be nice if it would not copy the file and insert a record if the file was already there, but it's not a big deal. let me know wheat you think.
Joe
Chris,
Yes that does work, however i may go a different route, a little. I may want duplicate name but would need to increment them by say 1. so the file name that is copied would be text.txt1 and would be the same in the database for logging. That way if it come across multiple configuration files with the same name we will have them all. Take IIS for instance. You may have multiple sites with the same file structure and same config file names. well i want them all.
So....
i want to say if the file already exists in destination then add a unique number to the end of the filename and copy the file and log with the same name.
sound good? any thought or ideas?
Joe
Thanks again for all your help!
That works well.
One problem now. Whenever i run any version of the above script it writes the first two files to the table then gets "InputStream" and stops writing to the table.
I inserted a "Write-Host $_.filename" before the SQL Query to see what files were found.
I have seen some similar post that had the same issue and the fixed by using Get-Item instead of Get-Contents but that doesnt work for searching all subfolders.
Joe
Found this when i get help on select-string. Not quite sure what this means look at >>>>>>
If not from a file what would it be and why would it stop writing to SQL?
RETURN TYPE
If the Quiet parameter is specified, a boolean value indicating whether the pattern was found.
Otherwise, the output will be a set of MatchInfo objects, each of which have the following properties:
IgnoreCase: Boolean
LineNumber: integer (0 if input is not from a file)
Line: string
>>>> Filename: string ("InputStream" if input is not from a file)
>>>>Path: string ("InputStream" if input is not from a file)
Pattern: the string that was actually matched
OriginalObject: object that was processed
Chris,
I changed the field in the table to be data type text instead of varchar(50). The path string was too long and wasn't populating the field and now I'm not seeing the InputString message. Weird stuff.
I think we can close this guy out. I will finalize it with some tweaks and re-post the final code so anyone can enjoy. Thanks again for all your help. I really appreciate it.
Joe
Chris, the InputStream is caused by invalid data, in this case a "Directory" name. Directories do no have a property of .filename or .path. I can write and If statement to exclude the directory type but when you use select-string it does not pass the .fullname and .name property that I would need to put in the database. How can I take the directory name and put the .fullname in one column and "directory" in the filename column. any ideas?
Business Accounts
Answer for Membership
by: Chris-DentPosted on 2009-08-31 at 01:03:08ID: 25221136
Used System.Data.SQLClient before? That's what you'd be using to talk to an SQL database for both retrieving data and updating tables.
Chris