How Can I Restore a Database to Different Files and ...............

Hi,

I have a database that over time has become spread over different files, file groups all of various sizes.

I want to restore this database to a different set of files/filegroups and evenly spread.

It appears that I can only resotore a database to number/of and size of files from which it was backed up..

I want to redistribute a 40GB file, using EMPTY is taking for ever and then eventually fails.

What can I do?

Thanks for your help

Try adding several new data files, then doing a shrink-empty on the big file to get it to be spread out to the new files. If the big data file is your primary data file, that will not work.

Suggestion two is to Rebuild the clustered index for several of your larger tables into the new files. This will move the data.

How can I read/create a file from t-sql?

I need to create/read files from t-sql? Does anyone know how can I do it?
Thanksbcp?

bulk insert?

DTS?

You probably mean as device the you read sequentially through...

Nope, and you wouldn't want to.|||Thanks Brett. I know the tools you suggested me, but I think they cant help me so much.

What I need to do is process the information. The scenario is: I have two dbs one exporting data, A, and the other, B, importing them, communication is not assured, and moreover, depending on the incoming data and data on B db, I should update records on B or insert them, so, I think, I cant use bcp or bulk insert.

I have been working with oracle and pl-sql has system defined functions and procedures to access files from stored functions or procedures. I have look for the equivalent functions/procedures on T-sql and I havent found any information, dont they exist?

If they dont exist I want to know if I can program DTS task to read data from files and process them.

Thanks in advance|||You basically want to do delta processing right?

If it's in A and not in B, add to b
If it's in A and in B, update B
If it's not in A and in B delete B..

something like that?

I would just compare the tables...

have a look

Delta Process (http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx)

how can i process multiple binary files into my sql database?

i have a table with rows of file names and paths. what i'm trying to do is process each file and store it in my sql database. i want to store the files as binary files (they are word and excel and pdf files) anyone know a way to do this? it would especially be useful if i could do this with a console application so i can schedule it

There are lots of examples which shows how to upload/retrieve BLOB data in SQL. For example:

HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NEThttp://support.microsoft.com/default.aspx?scid=kb;en-us;326502

You can also use OPENROWSET command with SINGLE_BLOB option in SQL2005. Please refer to:

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

And you can go through the tale to retrieve the file names/paths to locate the source files, using SqlDataReader:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(d=ide).aspx

|||

thank you!

that was exactly what i've been looking for. i grabbed the code and it works beautifully.

you saved my butt

threeo

How can I pass parameters into an RSS file for RS.EXE

I have a stored proc running:
Declare @.cmd nvarchar(255)
Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
"C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
exec master..xp_cmdshell @.cmd
But, the report the RSS file will render has 3 parameters the user specifies.
I know I can easily pass parameters to the command line from my stored proc,
but can I pass parameters from the command line into the RSS file like I can
do with a batch file?the syntax is
-v parameter1Name="parameterValue"
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>I have a stored proc running:
> Declare @.cmd nvarchar(255)
> Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
> "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
> exec master..xp_cmdshell @.cmd
> But, the report the RSS file will render has 3 parameters the user
> specifies.
> I know I can easily pass parameters to the command line from my stored
> proc,
> but can I pass parameters from the command line into the RSS file like I
> can
> do with a batch file?|||You should not declare PID, RID and RptName in the script.
What happens if you run same command from command window?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
> So then how do I use these parameters in the RSS file?
> I already have an array of parameters like this:
> ' Report Parameters
> Dim parameters(2) As ParameterValue
> parameters(0) = New ParameterValue()
> parameters(0).Name = "PID"
> parameters(0).Value = PID
> parameters(1) = New ParameterValue()
> parameters(1).Name = "RID"
> parameters(1).Value = RID
> parameters(2) = New ParameterValue()
> parameters(2).Name = "Name"
> parameters(2).Value = RptName
>
> PID, RID, and RptName are being passed to the RSS file as Global Variables
> from the command line using -v but when I run this, I get error BC30451
> (variable not declared).
> If I add:
> Dim PID as integer
> Dim RID as integer
> Dim RptName as string
> I get "Unhandled Exception. An error has occured during report
> processing".
>
> "Lev Semenets [MSFT]" wrote:
>> the syntax is
>> -v parameter1Name="parameterValue"
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>> >I have a stored proc running:
>> >
>> > Declare @.cmd nvarchar(255)
>> > Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
>> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
>> >
>> > exec master..xp_cmdshell @.cmd
>> >
>> > But, the report the RSS file will render has 3 parameters the user
>> > specifies.
>> >
>> > I know I can easily pass parameters to the command line from my stored
>> > proc,
>> > but can I pass parameters from the command line into the RSS file like
>> > I
>> > can
>> > do with a batch file?
>>|||Here is what I run from the command line:
rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s http://ibm1pbs001/reportserver
-v PID=3 RID=1 RptName="NJIT Flex Plan Report"
Here is the content of FlexPlan.rss:
Public Sub Main()
Dim format as string = "PDF"
Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan Report"
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
' Report Parameters
Dim parameters(2) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "PID"
parameters(0).Value = PID
parameters(1) = New ParameterValue()
parameters(1).Name = "RID"
parameters(1).Value = RID
parameters(2) = New ParameterValue()
parameters(2).Name = "Name"
parameters(2).Value = RptName
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
And the error I get when I execute the above command line code is:
The specified script failed to compile with the following errors:
C:\Documents and Settings\adam.baruh>
"c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe /main:MainModule
/utf8output /R:"System.dll" /R:"System.Xml.dll" /R:"System.Web.Services.dll"
/R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
/out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
for Microsoft (R) .NET Framework version 1.1.4322.573
Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error BC30451:
Name 'RID' is not declared.
parameters(1).Value = RID
~~~
C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error BC30451:
Name 'RptName' is not declared.
parameters(2).Value = RptName
"Lev Semenets [MSFT]" wrote:
> You should not declare PID, RID and RptName in the script.
> What happens if you run same command from command window?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
> > So then how do I use these parameters in the RSS file?
> >
> > I already have an array of parameters like this:
> >
> > ' Report Parameters
> > Dim parameters(2) As ParameterValue
> > parameters(0) = New ParameterValue()
> > parameters(0).Name = "PID"
> > parameters(0).Value = PID
> >
> > parameters(1) = New ParameterValue()
> > parameters(1).Name = "RID"
> > parameters(1).Value = RID
> >
> > parameters(2) = New ParameterValue()
> > parameters(2).Name = "Name"
> > parameters(2).Value = RptName
> >
> >
> > PID, RID, and RptName are being passed to the RSS file as Global Variables
> > from the command line using -v but when I run this, I get error BC30451
> > (variable not declared).
> >
> > If I add:
> > Dim PID as integer
> > Dim RID as integer
> > Dim RptName as string
> >
> > I get "Unhandled Exception. An error has occured during report
> > processing".
> >
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> the syntax is
> >> -v parameter1Name="parameterValue"
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
> >> >I have a stored proc running:
> >> >
> >> > Declare @.cmd nvarchar(255)
> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
> >> >
> >> > exec master..xp_cmdshell @.cmd
> >> >
> >> > But, the report the RSS file will render has 3 parameters the user
> >> > specifies.
> >> >
> >> > I know I can easily pass parameters to the command line from my stored
> >> > proc,
> >> > but can I pass parameters from the command line into the RSS file like
> >> > I
> >> > can
> >> > do with a batch file?
> >>
> >>
> >>
>
>|||Please use separate -v option for every parameter:
-v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:199317DB-4D61-4247-B719-3D982DA2169A@.microsoft.com...
> Here is what I run from the command line:
> rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s
> http://ibm1pbs001/reportserver
> -v PID=3 RID=1 RptName="NJIT Flex Plan Report"
>
> Here is the content of FlexPlan.rss:
> Public Sub Main()
> Dim format as string = "PDF"
> Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
> Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan Report"
> ' Prepare Render arguments
> Dim historyID as string = Nothing
> Dim deviceInfo as string = Nothing
> Dim showHide as string = Nothing
> Dim results() as Byte
> Dim encoding as string
> Dim mimeType as string
> Dim warnings() AS Warning = Nothing
> Dim reportHistoryParameters() As ParameterValue = Nothing
> Dim streamIDs() as string = Nothing
> ' Report Parameters
> Dim parameters(2) As ParameterValue
> parameters(0) = New ParameterValue()
> parameters(0).Name = "PID"
> parameters(0).Value = PID
> parameters(1) = New ParameterValue()
> parameters(1).Name = "RID"
> parameters(1).Value = RID
> parameters(2) = New ParameterValue()
> parameters(2).Name = "Name"
> parameters(2).Value = RptName
> results = rs.Render(reportPath, format, _
> Nothing, Nothing, parameters, _
> Nothing, Nothing, encoding, mimeType, _
> reportHistoryParameters, warnings, streamIDs)
> ' Open a file stream and write out the report
> Dim stream As FileStream = File.OpenWrite(fileName)
> stream.Write(results, 0, results.Length)
> stream.Close()
> End Sub
> And the error I get when I execute the above command line code is:
> The specified script failed to compile with the following errors:
> C:\Documents and Settings\adam.baruh>
> "c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe
> /main:MainModule
> /utf8output /R:"System.dll" /R:"System.Xml.dll"
> /R:"System.Web.Services.dll"
> /R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
> /out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
> "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
> "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
> Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
> for Microsoft (R) .NET Framework version 1.1.4322.573
> Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
> C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error BC30451:
> Name 'RID' is not declared.
> parameters(1).Value = RID
> ~~~
> C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error BC30451:
> Name 'RptName' is not declared.
> parameters(2).Value = RptName
> "Lev Semenets [MSFT]" wrote:
>> You should not declare PID, RID and RptName in the script.
>> What happens if you run same command from command window?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
>> > So then how do I use these parameters in the RSS file?
>> >
>> > I already have an array of parameters like this:
>> >
>> > ' Report Parameters
>> > Dim parameters(2) As ParameterValue
>> > parameters(0) = New ParameterValue()
>> > parameters(0).Name = "PID"
>> > parameters(0).Value = PID
>> >
>> > parameters(1) = New ParameterValue()
>> > parameters(1).Name = "RID"
>> > parameters(1).Value = RID
>> >
>> > parameters(2) = New ParameterValue()
>> > parameters(2).Name = "Name"
>> > parameters(2).Value = RptName
>> >
>> >
>> > PID, RID, and RptName are being passed to the RSS file as Global
>> > Variables
>> > from the command line using -v but when I run this, I get error BC30451
>> > (variable not declared).
>> >
>> > If I add:
>> > Dim PID as integer
>> > Dim RID as integer
>> > Dim RptName as string
>> >
>> > I get "Unhandled Exception. An error has occured during report
>> > processing".
>> >
>> >
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> the syntax is
>> >> -v parameter1Name="parameterValue"
>> >>
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>> >> >I have a stored proc running:
>> >> >
>> >> > Declare @.cmd nvarchar(255)
>> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL
>> >> > Server\80\Tools\Binn>rs -i
>> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
>> >> >
>> >> > exec master..xp_cmdshell @.cmd
>> >> >
>> >> > But, the report the RSS file will render has 3 parameters the user
>> >> > specifies.
>> >> >
>> >> > I know I can easily pass parameters to the command line from my
>> >> > stored
>> >> > proc,
>> >> > but can I pass parameters from the command line into the RSS file
>> >> > like
>> >> > I
>> >> > can
>> >> > do with a batch file?
>> >>
>> >>
>> >>
>>|||Gotta love those easy answers. I wish the documentation was a bit more clear
on this utility though. Would love even more if there were samples without
having to install the samples on my Report Server. Thanks for the help
though!
"Lev Semenets [MSFT]" wrote:
> Please use separate -v option for every parameter:
> -v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:199317DB-4D61-4247-B719-3D982DA2169A@.microsoft.com...
> > Here is what I run from the command line:
> >
> > rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s
> > http://ibm1pbs001/reportserver
> > -v PID=3 RID=1 RptName="NJIT Flex Plan Report"
> >
> >
> > Here is the content of FlexPlan.rss:
> >
> > Public Sub Main()
> >
> > Dim format as string = "PDF"
> > Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
> > Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan Report"
> >
> > ' Prepare Render arguments
> > Dim historyID as string = Nothing
> > Dim deviceInfo as string = Nothing
> > Dim showHide as string = Nothing
> > Dim results() as Byte
> > Dim encoding as string
> > Dim mimeType as string
> > Dim warnings() AS Warning = Nothing
> > Dim reportHistoryParameters() As ParameterValue = Nothing
> > Dim streamIDs() as string = Nothing
> >
> > ' Report Parameters
> > Dim parameters(2) As ParameterValue
> > parameters(0) = New ParameterValue()
> > parameters(0).Name = "PID"
> > parameters(0).Value = PID
> >
> > parameters(1) = New ParameterValue()
> > parameters(1).Name = "RID"
> > parameters(1).Value = RID
> >
> > parameters(2) = New ParameterValue()
> > parameters(2).Name = "Name"
> > parameters(2).Value = RptName
> >
> > results = rs.Render(reportPath, format, _
> > Nothing, Nothing, parameters, _
> > Nothing, Nothing, encoding, mimeType, _
> > reportHistoryParameters, warnings, streamIDs)
> >
> > ' Open a file stream and write out the report
> > Dim stream As FileStream = File.OpenWrite(fileName)
> > stream.Write(results, 0, results.Length)
> > stream.Close()
> > End Sub
> >
> > And the error I get when I execute the above command line code is:
> >
> > The specified script failed to compile with the following errors:
> > C:\Documents and Settings\adam.baruh>
> > "c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe
> > /main:MainModule
> > /utf8output /R:"System.dll" /R:"System.Xml.dll"
> > /R:"System.Web.Services.dll"
> > /R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
> > /out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
> >
> > Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
> > for Microsoft (R) .NET Framework version 1.1.4322.573
> > Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
> >
> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error BC30451:
> > Name 'RID' is not declared.
> >
> > parameters(1).Value = RID
> > ~~~
> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error BC30451:
> > Name 'RptName' is not declared.
> >
> > parameters(2).Value = RptName
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> You should not declare PID, RID and RptName in the script.
> >> What happens if you run same command from command window?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> >> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
> >> > So then how do I use these parameters in the RSS file?
> >> >
> >> > I already have an array of parameters like this:
> >> >
> >> > ' Report Parameters
> >> > Dim parameters(2) As ParameterValue
> >> > parameters(0) = New ParameterValue()
> >> > parameters(0).Name = "PID"
> >> > parameters(0).Value = PID
> >> >
> >> > parameters(1) = New ParameterValue()
> >> > parameters(1).Name = "RID"
> >> > parameters(1).Value = RID
> >> >
> >> > parameters(2) = New ParameterValue()
> >> > parameters(2).Name = "Name"
> >> > parameters(2).Value = RptName
> >> >
> >> >
> >> > PID, RID, and RptName are being passed to the RSS file as Global
> >> > Variables
> >> > from the command line using -v but when I run this, I get error BC30451
> >> > (variable not declared).
> >> >
> >> > If I add:
> >> > Dim PID as integer
> >> > Dim RID as integer
> >> > Dim RptName as string
> >> >
> >> > I get "Unhandled Exception. An error has occured during report
> >> > processing".
> >> >
> >> >
> >> >
> >> > "Lev Semenets [MSFT]" wrote:
> >> >
> >> >> the syntax is
> >> >> -v parameter1Name="parameterValue"
> >> >>
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> >> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
> >> >> >I have a stored proc running:
> >> >> >
> >> >> > Declare @.cmd nvarchar(255)
> >> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL
> >> >> > Server\80\Tools\Binn>rs -i
> >> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
> >> >> >
> >> >> > exec master..xp_cmdshell @.cmd
> >> >> >
> >> >> > But, the report the RSS file will render has 3 parameters the user
> >> >> > specifies.
> >> >> >
> >> >> > I know I can easily pass parameters to the command line from my
> >> >> > stored
> >> >> > proc,
> >> >> > but can I pass parameters from the command line into the RSS file
> >> >> > like
> >> >> > I
> >> >> > can
> >> >> > do with a batch file?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||That is documentation bug.
You may find some scripts here: http://blogs.msdn.com/bryanke/
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:936D34E0-3E68-4C38-871E-BA738D2147D9@.microsoft.com...
> Gotta love those easy answers. I wish the documentation was a bit more
> clear
> on this utility though. Would love even more if there were samples
> without
> having to install the samples on my Report Server. Thanks for the help
> though!
> "Lev Semenets [MSFT]" wrote:
>> Please use separate -v option for every parameter:
>> -v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> news:199317DB-4D61-4247-B719-3D982DA2169A@.microsoft.com...
>> > Here is what I run from the command line:
>> >
>> > rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s
>> > http://ibm1pbs001/reportserver
>> > -v PID=3 RID=1 RptName="NJIT Flex Plan Report"
>> >
>> >
>> > Here is the content of FlexPlan.rss:
>> >
>> > Public Sub Main()
>> >
>> > Dim format as string = "PDF"
>> > Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
>> > Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan
>> > Report"
>> >
>> > ' Prepare Render arguments
>> > Dim historyID as string = Nothing
>> > Dim deviceInfo as string = Nothing
>> > Dim showHide as string = Nothing
>> > Dim results() as Byte
>> > Dim encoding as string
>> > Dim mimeType as string
>> > Dim warnings() AS Warning = Nothing
>> > Dim reportHistoryParameters() As ParameterValue = Nothing
>> > Dim streamIDs() as string = Nothing
>> >
>> > ' Report Parameters
>> > Dim parameters(2) As ParameterValue
>> > parameters(0) = New ParameterValue()
>> > parameters(0).Name = "PID"
>> > parameters(0).Value = PID
>> >
>> > parameters(1) = New ParameterValue()
>> > parameters(1).Name = "RID"
>> > parameters(1).Value = RID
>> >
>> > parameters(2) = New ParameterValue()
>> > parameters(2).Name = "Name"
>> > parameters(2).Value = RptName
>> >
>> > results = rs.Render(reportPath, format, _
>> > Nothing, Nothing, parameters, _
>> > Nothing, Nothing, encoding, mimeType, _
>> > reportHistoryParameters, warnings, streamIDs)
>> >
>> > ' Open a file stream and write out the report
>> > Dim stream As FileStream = File.OpenWrite(fileName)
>> > stream.Write(results, 0, results.Length)
>> > stream.Close()
>> > End Sub
>> >
>> > And the error I get when I execute the above command line code is:
>> >
>> > The specified script failed to compile with the following errors:
>> > C:\Documents and Settings\adam.baruh>
>> > "c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe
>> > /main:MainModule
>> > /utf8output /R:"System.dll" /R:"System.Xml.dll"
>> > /R:"System.Web.Services.dll"
>> > /R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
>> > /out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
>> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
>> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
>> >
>> > Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
>> > for Microsoft (R) .NET Framework version 1.1.4322.573
>> > Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
>> >
>> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error
>> > BC30451:
>> > Name 'RID' is not declared.
>> >
>> > parameters(1).Value = RID
>> > ~~~
>> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error
>> > BC30451:
>> > Name 'RptName' is not declared.
>> >
>> > parameters(2).Value = RptName
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> You should not declare PID, RID and RptName in the script.
>> >> What happens if you run same command from command window?
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> >> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
>> >> > So then how do I use these parameters in the RSS file?
>> >> >
>> >> > I already have an array of parameters like this:
>> >> >
>> >> > ' Report Parameters
>> >> > Dim parameters(2) As ParameterValue
>> >> > parameters(0) = New ParameterValue()
>> >> > parameters(0).Name = "PID"
>> >> > parameters(0).Value = PID
>> >> >
>> >> > parameters(1) = New ParameterValue()
>> >> > parameters(1).Name = "RID"
>> >> > parameters(1).Value = RID
>> >> >
>> >> > parameters(2) = New ParameterValue()
>> >> > parameters(2).Name = "Name"
>> >> > parameters(2).Value = RptName
>> >> >
>> >> >
>> >> > PID, RID, and RptName are being passed to the RSS file as Global
>> >> > Variables
>> >> > from the command line using -v but when I run this, I get error
>> >> > BC30451
>> >> > (variable not declared).
>> >> >
>> >> > If I add:
>> >> > Dim PID as integer
>> >> > Dim RID as integer
>> >> > Dim RptName as string
>> >> >
>> >> > I get "Unhandled Exception. An error has occured during report
>> >> > processing".
>> >> >
>> >> >
>> >> >
>> >> > "Lev Semenets [MSFT]" wrote:
>> >> >
>> >> >> the syntax is
>> >> >> -v parameter1Name="parameterValue"
>> >> >>
>> >> >>
>> >> >> --
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> >> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>> >> >> >I have a stored proc running:
>> >> >> >
>> >> >> > Declare @.cmd nvarchar(255)
>> >> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL
>> >> >> > Server\80\Tools\Binn>rs -i
>> >> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
>> >> >> >
>> >> >> > exec master..xp_cmdshell @.cmd
>> >> >> >
>> >> >> > But, the report the RSS file will render has 3 parameters the
>> >> >> > user
>> >> >> > specifies.
>> >> >> >
>> >> >> > I know I can easily pass parameters to the command line from my
>> >> >> > stored
>> >> >> > proc,
>> >> >> > but can I pass parameters from the command line into the RSS file
>> >> >> > like
>> >> >> > I
>> >> >> > can
>> >> >> > do with a batch file?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||In the documentation it says:
For example, -v a="b" c="d" results in a variable named a with a value of
"b" and a variable c with a value of "d".
Not -v a="b" -v c="d" >> which by the way does not work!
Somebody please change the documentation!!!
"Lev Semenets [MSFT]" wrote:
> Please use separate -v option for every parameter:
> -v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
> --

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
Johnny
Check out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny
|||How to Move TEMPDB from one location to another
--X--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny
|||Perfect. Thanks guys!
"Hari Prasad" wrote:

> How to Move TEMPDB from one location to another
> --X--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
> 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
> 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
>
>

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
JohnnyCheck out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
--
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||How to Move TEMPDB from one location to another
--­--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME ='E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME ='E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||Perfect. Thanks guys!
"Hari Prasad" wrote:
> How to Move TEMPDB from one location to another
> --­--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME => 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME => 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> > Hello,
> >
> > Does anyone know how I can successfully move the tempdb database to a
> > different drive besides C? I tried detaching and re-attaching it but you
> > can't do that with system databases.
> >
> > FYI, I am using SQL Server 2005.
> >
> > Thanks!
> > Johnny
>
>sql

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
JohnnyCheck out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||How to Move TEMPDB from one location to another
--_--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||Perfect. Thanks guys!
"Hari Prasad" wrote:

> How to Move TEMPDB from one location to another
> --_--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
> 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
> 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files
,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
>
>

how can i move a local directory with all files within a procedure

Hello i'd like to know how i can move all my files from C:\SQL to C:\DB
using a Table with a reference to the batch file.
it looks like that:
1.) i have batch.bat file with " call move c:\sql\* to c:\db "
2.) a table MyTable contains a column with value 'c:\db\batch.bat'
3.) i'd like to know if there are any procedure possible, that can execute
this batch.bat from MyTable.
maybe using ASP...?
can anybody tell if thats possible'Felix
Try xp_cmdshell system stored procedure in the BOL
"Felix" <andreas21@.gmx.net> wrote in message
news:uj0LKps1EHA.2292@.TK2MSFTNGP15.phx.gbl...
> Hello i'd like to know how i can move all my files from C:\SQL to C:\DB
> using a Table with a reference to the batch file.
> it looks like that:
> 1.) i have batch.bat file with " call move c:\sql\* to c:\db "
> 2.) a table MyTable contains a column with value 'c:\db\batch.bat'
> 3.) i'd like to know if there are any procedure possible, that can execute
> this batch.bat from MyTable.
> maybe using ASP...?
> can anybody tell if thats possible'
>|||thanks its working now! great!!!
master.dbo.xp_cmdshell procedure...
same question, is that possible to use such a systm procedure on mysql
server? any idea?
ive tried but there are no such cmdshell procedure like that. maybe there is
another way?
"Uri Dimant" schrieb im Newsbeitrag
news:eNzgelu1EHA.2612@.TK2MSFTNGP14.phx.gbl...
> Felix
> Try xp_cmdshell system stored procedure in the BOL
>
>
> "Felix" wrote in message
> news:uj0LKps1EHA.2292@.TK2MSFTNGP15.phx.gbl...
>

How can I make tempdb to its original size?

Hi, everybody!
I installed MS SQL Server 2000, and add two files to tempdb.
And now I want to make tempdb to its original size by removing two files
which had beed added.
How can I do it?http://www.support.microsoft.com/?id=307487
Andrew J. Kelly
SQL Server MVP
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:uF0WdC1cDHA.2572@.TK2MSFTNGP11.phx.gbl...
>
> Hi, everybody!
> I installed MS SQL Server 2000, and add two files to tempdb.
> And now I want to make tempdb to its original size by removing two files
> which had beed added.
> How can I do it?
>
>

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
Thanks
Benny wrote:

> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Regards
Amish Shah
|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How can I import txt files using fdf field info ??

I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
Server database. They were apparently exported from SAP, and each came with
a corresponding fdf file that lists the field layout information ...
PCFDF
PCFT 1
PCFO 1,1,1,1,1
PCFL YR 1 2
PCFL DIV 1 18
...
PCFL E12 2 13/3
Is there any way that I can get Enterprise Manager to use this information,
rather than forcing me to enter it manually ? Alternatively, is there a
third party app that'll do that ? The layouts are not necessarily
identical for all the files, so I really need to use the information from
each file's corresponding fdf.
Thanks
--
Jim Cook
jimcook@.panix.com.no.spamJim
you can easily import text files. withEM
EM> right click on Data Transformation services > import data and give path
of .txt
This is abt txt. I dont know about sap
Regards
R.D
"Jim Cook" wrote:
> I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
> Server database. They were apparently exported from SAP, and each came with
> a corresponding fdf file that lists the field layout information ...
> PCFDF
> PCFT 1
> PCFO 1,1,1,1,1
> PCFL YR 1 2
> PCFL DIV 1 18
> ...
> PCFL E12 2 13/3
> Is there any way that I can get Enterprise Manager to use this information,
> rather than forcing me to enter it manually ? Alternatively, is there a
> third party app that'll do that ? The layouts are not necessarily
> identical for all the files, so I really need to use the information from
> each file's corresponding fdf.
> Thanks
> --
> Jim Cook
> jimcook@.panix.com.no.spam
>|||Yes, I've done that - but the wizard gets to a window where it wants me to
click where each field ends and the next begins. I have that information in
the fdf files, but I do not want to go through the process of defining ~100
field spans for ~20 files. I strongly suspect that the fdf files were
created by SAP during the export operation, but they're apparently not
recognized by EM as proper import templates. So what I'm looking for is an
app that converts the fdf files to EM's import template format, or natively
uses fdf files in doing SQL Server imports. I've done a fair amount of
googling, and have found leads re using fdfs in MySQL imports, but nada re
SQL Server imports.
--
Jim Cook
jimcook@.panix.com.no.spam
"R.D" wrote:
> Jim
> you can easily import text files. withEM
> EM> right click on Data Transformation services > import data and give path
> of .txt
> This is abt txt. I dont know about sap
> Regards
> R.D
>
> "Jim Cook" wrote:
> > I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
> > Server database. They were apparently exported from SAP, and each came with
> > a corresponding fdf file that lists the field layout information ...
> >
> > PCFDF
> > PCFT 1
> > PCFO 1,1,1,1,1
> > PCFL YR 1 2
> > PCFL DIV 1 18
> > ...
> > PCFL E12 2 13/3
> >
> > Is there any way that I can get Enterprise Manager to use this information,
> > rather than forcing me to enter it manually ? Alternatively, is there a
> > third party app that'll do that ? The layouts are not necessarily
> > identical for all the files, so I really need to use the information from
> > each file's corresponding fdf.
> >
> > Thanks
> > --
> > Jim Cook
> > jimcook@.panix.com.no.spam
> >

How can I import txt files using fdf field info ??

I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
Server database. They were apparently exported from SAP, and each came with
a corresponding fdf file that lists the field layout information ...
PCFDF
PCFT 1
PCFO 1,1,1,1,1
PCFL YR 1 2
PCFL DIV 1 18
...
PCFL E12 2 13/3
Is there any way that I can get Enterprise Manager to use this information,
rather than forcing me to enter it manually ? Alternatively, is there a
third party app that'll do that ? The layouts are not necessarily
identical for all the files, so I really need to use the information from
each file's corresponding fdf.
Thanks
Jim Cook
jimcook@.panix.com.no.spam
Jim
you can easily import text files. withEM
EM> right click on Data Transformation services > import data and give path
of .txt
This is abt txt. I dont know about sap
Regards
R.D
"Jim Cook" wrote:

> I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
> Server database. They were apparently exported from SAP, and each came with
> a corresponding fdf file that lists the field layout information ...
> PCFDF
> PCFT 1
> PCFO 1,1,1,1,1
> PCFL YR 1 2
> PCFL DIV 1 18
> ...
> PCFL E12 2 13/3
> Is there any way that I can get Enterprise Manager to use this information,
> rather than forcing me to enter it manually ? Alternatively, is there a
> third party app that'll do that ? The layouts are not necessarily
> identical for all the files, so I really need to use the information from
> each file's corresponding fdf.
> Thanks
> --
> Jim Cook
> jimcook@.panix.com.no.spam
>
|||Yes, I've done that - but the wizard gets to a window where it wants me to
click where each field ends and the next begins. I have that information in
the fdf files, but I do not want to go through the process of defining ~100
field spans for ~20 files. I strongly suspect that the fdf files were
created by SAP during the export operation, but they're apparently not
recognized by EM as proper import templates. So what I'm looking for is an
app that converts the fdf files to EM's import template format, or natively
uses fdf files in doing SQL Server imports. I've done a fair amount of
googling, and have found leads re using fdfs in MySQL imports, but nada re
SQL Server imports.
Jim Cook
jimcook@.panix.com.no.spam
"R.D" wrote:
[vbcol=seagreen]
> Jim
> you can easily import text files. withEM
> EM> right click on Data Transformation services > import data and give path
> of .txt
> This is abt txt. I dont know about sap
> Regards
> R.D
>
> "Jim Cook" wrote:

How can I import txt files using fdf field info ??

I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
Server database. They were apparently exported from SAP, and each came with
a corresponding fdf file that lists the field layout information ...
PCFDF
PCFT 1
PCFO 1,1,1,1,1
PCFL YR 1 2
PCFL DIV 1 18
...
PCFL E12 2 13/3
Is there any way that I can get Enterprise Manager to use this information,
rather than forcing me to enter it manually ? Alternatively, is there a
third party app that'll do that ? The layouts are not necessarily
identical for all the files, so I really need to use the information from
each file's corresponding fdf.
Thanks
--
Jim Cook
jimcook@.panix.com.no.spamJim
you can easily import text files. withEM
EM> right click on Data Transformation services > import data and give path
of .txt
This is abt txt. I dont know about sap
Regards
R.D
"Jim Cook" wrote:

> I have many ca. 1-5 Gb fixed field width txt files to import into a SQL
> Server database. They were apparently exported from SAP, and each came wi
th
> a corresponding fdf file that lists the field layout information ...
> PCFDF
> PCFT 1
> PCFO 1,1,1,1,1
> PCFL YR 1 2
> PCFL DIV 1 18
> ...
> PCFL E12 2 13/3
> Is there any way that I can get Enterprise Manager to use this information
,
> rather than forcing me to enter it manually ? Alternatively, is there a
> third party app that'll do that ? The layouts are not necessarily
> identical for all the files, so I really need to use the information from
> each file's corresponding fdf.
> Thanks
> --
> Jim Cook
> jimcook@.panix.com.no.spam
>|||Yes, I've done that - but the wizard gets to a window where it wants me to
click where each field ends and the next begins. I have that information in
the fdf files, but I do not want to go through the process of defining ~100
field spans for ~20 files. I strongly suspect that the fdf files were
created by SAP during the export operation, but they're apparently not
recognized by EM as proper import templates. So what I'm looking for is an
app that converts the fdf files to EM's import template format, or natively
uses fdf files in doing SQL Server imports. I've done a fair amount of
googling, and have found leads re using fdfs in mysql imports, but nada re
SQL Server imports.
Jim Cook
jimcook@.panix.com.no.spam
"R.D" wrote:
[vbcol=seagreen]
> Jim
> you can easily import text files. withEM
> EM> right click on Data Transformation services > import data and give pat
h
> of .txt
> This is abt txt. I dont know about sap
> Regards
> R.D
>
> "Jim Cook" wrote:
>

How can I get the files under a special directory on a remote sql server?

we can get directory information by EnumDirectories method of sql server
object from a remote sql server, but how can I get the files under a special
directory on a remote sql server?
I tried finding result in MSDN, but I can't get it. Any one can help me?
Thanks in advance.
Bill WhiteBill White wrote:
> we can get directory information by EnumDirectories method of sql
> server object from a remote sql server, but how can I get the files
> under a special directory on a remote sql server?
> I tried finding result in MSDN, but I can't get it. Any one can help
> me?
> Thanks in advance.
> Bill White
From SQL Server?
Exec master..xp_cmdshell N'DIR C:\MyFolder\*.*'
You can create a temp table and insert the results of xp_cmdshell into
the temp table for processing if you need to. If this is SQL 2005, then
xp_cmdshell could be disabled on the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||It's a good idea, but I have another question about it.
Does SQL Server do it in this manner? or does it by another unopened method?
Thanks a lot.
Bill White
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u4i%23wJL9FHA.2816@.tk2msftngp13.phx.gbl...
Bill White wrote:
> we can get directory information by EnumDirectories method of sql
> server object from a remote sql server, but how can I get the files
> under a special directory on a remote sql server?
> I tried finding result in MSDN, but I can't get it. Any one can help
> me?
> Thanks in advance.
> Bill White
From SQL Server?
Exec master..xp_cmdshell N'DIR C:\MyFolder\*.*'
You can create a temp table and insert the results of xp_cmdshell into
the temp table for processing if you need to. If this is SQL 2005, then
xp_cmdshell could be disabled on the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com

How can I generate xsd files from existing tables?

I would like to generate xml schema files for my data model for later use with testing tools such as ndbunit. I realize that Visual Studio datasets can be used to do this, however, Visual Studio does not automatically determine table realtionships. I do not want to manually specify these relationships. I also do not have the luxury of using TeamServer for DB professionals. The Database Diagrams feature of SQL Server Management Studio seems capable of determining the table relationships on its own. However, I don't see any way to simply export a databse diagram to an xml schema definition. I realize I could write code to do this myself using the nice APIs under the Microsoft.Sqlserver.management and System.xml namespace, but I'm trying to avoid doing this. Is there a way to do this with SQL Server Management Studio?

Would something like this work for you? It's not technically a "right-click" operation, but it does generate the schema:

SELECT *
FROM Customers
FOR XML RAW('Customer'), XMLSCHEMA('urn:example.com')
-

<xsd:schema targetNamespace="urn:example.com"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes"
elementFormDefault="qualified">
<xsd:import namespace=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes" />
<xsd:element name="Customer">
...
</xsd:element>
</xsd:schema>
<Customer xmlns="urn:example.com" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" />
...

|||Thanks for the suggestion. This will work fine for my needs!|||

I should be a little clearer. If you want just the schema for the table, select something that returns all nulls or an empty set, but make sure you use the XMLSCHEMA option. You can find more about that in Books Online - past this in the "URL" bar of BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/04b35145-1cca-45f4-9eb7-990abf2e647d.htm

Buck

|||When using the XMLSCHEMA option, I cannot seem to get the generated schema to contain foreign key constraints. I looked over the MSDN documentation, but I have not found any specific options related to this. Is this a limitation or am I simply missing some parameters that will cause the contraints to be included?|||You're not missing anything - it definitely won't do that. I would suggest you live.com search for a "database documenter" script (there are tons out there) and add the XMLSCHEMA to the end of one of those. That should give you a database design in an XML document.

How can I FTP 3 files

Hello,

I am new to SSIS.

I am trying to write a simple package that saves data into some tables from elsewhere in the database, extracts it into flat files and then FTPs them to another file system.

I have a control flow with three tasks. [1] Execute SQL (Executes a stored proc to fill the tables with data) [2] A data flow to extract the data into 3 flat files [3] An FTP task to move the data to the other system.

In my data flow I have three parallel streams each with an OLE-DB source and a Flat file destination so that three flat s files get produced in parallel.

The problem I have is that I cant get the FTP task to handle the files. I can get the flat files produced fine and I write them to the c: drive. But how do I program the FTP transform to pick up the three files and dispatch them? I am guessing that I need to edit the FTP task, choose the file transfer tab and edit the local parameters section to tell it to pick up the three files........but how?

As mentioned I am new to SSIS so simple+clear answers appreciated.

Thanks in advance

MGale1

This is a completely reasonable request. What I would recommend is to put each file you create in a ForEach Loop Container. Each time you loop to create a new file, inside the loop you can use the FTP Task to send the one file. You can dynamically set the file you wish to send by setting an expression on the Connection Manager that holds the file's name. I know I didn't specify exactly how to each step but you can see a more in depth about how to use SSIS expressions to set the package to be dynamic here: http://www.jumpstarttv.com/Media.aspx?vid=34.

I know you mentioned that you wanted to create the file inside the same data flow. Unfortunately, the files will have to be FTP individually to the destination. If you were to receive files from a FTP site, the task accepts wild cards (give me all the *.txt files from the ftp server). When you send files though, it's a different story. SSIS is going to force you to use a File Connection Manager to specify which file you wish to send. You cannot send multiple files at one time other than doing it in a loop as I mentioned above. At least, I'm not sure of a way to do it :).

For a slew of SSIS how-to starter videos, you can see this link: http://www.jumpstarttv.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd

|||From what you've written, it sounds like you could use three FTP tasks, one for each file.