Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containing
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:
> I'm running SQL Server 2000. I have a DB with a logical file name containing
> a hyphen. When trying to change the size of the db and log files by running
> the following SQL statement it throws an error stating the file name is not
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned them
> automatically and put the hyphens in. What is causing the problem with the
> SQL command and how can I get around this problem.
> Thanks for any help!|||SP_HELPFILE results:
DB_NAME-DS_Data
1 <drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY 32896 KB Unlimited 10% data only
DB_NAME-DS_Log
2 <drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL 102400 KB Unlimited 25600 KB log only
"Absar Ahmad" wrote:
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:
> > I'm running SQL Server 2000. I have a DB with a logical file name containing
> > a hyphen. When trying to change the size of the db and log files by running
> > the following SQL statement it throws an error stating the file name is not
> > listed in sysfiles:
> >
> > -- Modify db file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_DATA],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > -- Modify log file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_Log],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > GO
> >
> > I've verified the logical file names using SP_HELPDB. When the DB was
> > created I did not specify any logical file names. SQL Server assigned them
> > automatically and put the hyphens in. What is causing the problem with the
> > SQL command and how can I get around this problem.
> >
> > Thanks for any help!sql

Change location of RS log files?

Hi,
How can I change the location where the RS log files are
stored/created? The .config file seems to have options to change the
filename, but will this allow me to specify a different folder as
well?
We would like to put these files on a seperate hard drive from our
main application drive, in case the log files ever happen to grow
exponentially and fill the entire DB again.
Matt BrownEach of the following files for MS Reporting Services has a section called
RStrace that contains the settings for the log files.
...\ReportServer\bin\ReportingServicesService.exe.config
â?¦\ReportServer\web.config
â?¦\ReportManager\web.config
A default installation writes the log files to the directory where Reporting
Services is installed under the LogFiles folder. In order to change the
default location of the log files you need to add the following line under
the RStrace section for each of the above files. Once you have added the
directory line you will need to restart the ReportServer service.
<add name=â'Directoryâ' value=â'your dirâ' />
Example:
<RStrace>
<add name="Directory" value="d:\RSLogs" />
<add name="FileName" value="ReportServerService_" />
<add name="FileSizeLimitMb" value="32" />
<add name="KeepFilesForDays" value="14" />
<add name="Prefix" value="tid, time" />
<add name="TraceListeners" value="debugwindow, file"
/>
<add name="TraceFileMode" value="unique" />
<add name="Components" value="all" />
</RStrace>

Change Filename creating database

I wish to change filename parameter depending on the root path of SQL Server :

C:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL where X>=1

I can find this root path by the following querie :

select SUBSTRING (physical_name,1,len(physical_name)-11) from sys.database_files where type_desc like 'LOG')

But i can't include the result in the create database command to be independant of the new root path in a the case of a deployment :

CREATE DATABASE [ACS] ON PRIMARY
(
NAME = N'ACS',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ACS_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf',
SIZE = 3840KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)

Somebody can help me please ?

You will have to compose your SQlString first, then executing it with the help of sp_executesql or EXEC.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Following your clear but short instruction, i write the next queries that don't work, have a correction ?

DECLARE @.prmname nvarchar(500)
DECLARE @.prmname2 nvarchar(500)
DECLARE @.prmpath nvarchar(500)
DECLARE @.prmpath2 nvarchar(500)
DECLARE @.SQLString nvarchar(500)
DECLARE @.ParmDefinition nvarchar(500);

/* Build the SQL string */
SET @.SQLString =
N'CREATE DATABASE [ACS] ON PRIMARY
(
NAME = @.name,
FILENAME = @.path,
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = @.name2,
FILENAME = @.path2,
SIZE = 3840KB,
MAXSIZE = 2048GB,
FILEGROWTH = 10%
)
COLLATE Latin1_General_BIN2';
SET @.ParmDefinition = N'@.path varchar(500),@.path2 varchar(500), @.name varchar(500), , @.name2 varchar(500)';

/* Execute the string with the parameter value. */
SET @.prmname = N'ACS';
SET @.prmpath = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf';
SET @.prmname2 = N'ACS_log';
SET @.prmpath2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf';

EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.name= @.prmname,@.path=@.prmpath, @.name2= @.prmname2,@.path2=@.prmpath2;

|||

DECLARE @.prmname nvarchar(500)

DECLARE @.prmname2 nvarchar(500)

DECLARE @.prmpath nvarchar(500)

DECLARE @.prmpath2 nvarchar(500)

DECLARE @.SQLString nvarchar(500)

DECLARE @.ParmDefinition nvarchar(500);

/* Execute the string with the parameter value. */

SET @.prmname = N'ACS';

SET @.prmpath = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS.mdf';

SET @.prmname2 = N'ACS_log';

SET @.prmpath2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACS_log.ldf';

/* Build the SQL string */

SET @.SQLString =

N'CREATE DATABASE [ACS] ON PRIMARY

(

NAME = ' + @.prmname + ',

FILENAME = ' + @.prmpath + ',

SIZE = 5120KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 1024KB

)

LOG ON

(

NAME = ' + @.prmname2 + ',

FILENAME = ' + @.prmpath2 + ',

SIZE = 3840KB,

MAXSIZE = 2048GB,

FILEGROWTH = 10%

)

COLLATE Latin1_General_BIN2'

EXECUTE sp_executesql @.SQLString

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

It's not working.

Incorrect Syntax for 'C' for DECLARE @.prmpath2 nvarchar(500)

Change Drive Location for Log Files.

Hello,
I am attempting to change the location of the log files for one of the
default SQL instance, every time I click on the ... button under Properties
of the instance/Database settings/default log directory, I only receive the
single drive that the SQL Server instance was installed on, and not any of
the other active drives. Anyone have thoughts or instructions on this for me?
Thanks,
Jeremey
I take you have an additional drive which has been added to the MSCS
configuration as Physical Disk
If so then you must also take the SQLServer resource off-line and add a
dependency to the logs physical disk resource and bring sql server back
on-line. Then you'll be able to see the new disk on the Database Setting
property page.
Nik Marshall-Blank MCSD/MCDBA
"Jeremey" <Jeremey@.discussions.microsoft.com> wrote in message
news:B9C72029-3D58-4486-B485-AE649DCC6E81@.microsoft.com...
> Hello,
> I am attempting to change the location of the log files for one of the
> default SQL instance, every time I click on the ... button under
> Properties
> of the instance/Database settings/default log directory, I only receive
> the
> single drive that the SQL Server instance was installed on, and not any of
> the other active drives. Anyone have thoughts or instructions on this for
> me?
> Thanks,
> Jeremey
|||That was the problem.
Thanks,
Jeremey
"Nik Marshall-Blank" wrote:

> I take you have an additional drive which has been added to the MSCS
> configuration as Physical Disk
> If so then you must also take the SQLServer resource off-line and add a
> dependency to the logs physical disk resource and bring sql server back
> on-line. Then you'll be able to see the new disk on the Database Setting
> property page.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Jeremey" <Jeremey@.discussions.microsoft.com> wrote in message
> news:B9C72029-3D58-4486-B485-AE649DCC6E81@.microsoft.com...
>
>

Change data source dynamically in report server.

Hi,

I need to change the data source for report server objects (rdl files) dynamically. In other words, I am having databases with identical structures but different data for different clients. Then I need to use same set of reports for different customers. They will access reports through web and they should provide user id/pwd and database name in web login interface, and then it should direct to the the particular database and should be able to access the same set of reports.

Any thoughts !

Roshan.

You can accomplish this using Report Parameters. The connection string would look something like the following.

="Data Source=ServerName;Initial Catalog=" & Parameters!Database.Value

Ian|||

Thanks, it is working.

Is there any way to change datasource in report model dynamically ?. i.e. sharing reports created from Report Builder between multiple databases.

I would like to create one standard report model with standard set of reports and letting usrs (different customers) add their own reports.

-Roshan

change data files name and transaction log name after restore

Hi All,
I have SQL Server database. Server name is DBServer. I have database A
in this server. If i click properties, the data files name show
"A_Data" and the transaction log name show "A_Log". I backup database A
to C:\ABackup.
After that, i create database B. I want to restore database A to
database B. I choose "from device" and select the location to
C:\ABackup. But when i tried to restore, in the Original File Name
(tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
data files name and transaction log name will change into A_Data and
A_Log.
What i want is that in my database B the name of data files and
transaction log not change after i do restore from database A.
Does anybody know how to do that?
Thanks before.On the Restore Database window select your file using 'From device'.
Go to Options. On 'Restore As' update the directory path and file name. You
can not change the logical name here, only the physical name.
Finish the Restore
If you are using SQL Server 2005
Select Database Properties, select Files tab
You can change the Logical Name here.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"afang" wrote:

> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Hi
You can restore a database with a different name ,so there is no need to
create a new database prior to restore operation
"afang" <khokimfang@.gmail.com> wrote in message
news:1147053596.925983.99420@.j33g2000cwa.googlegroups.com...
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Thanks Ben, for your information

change data files name and transaction log name after restore

Hi All,
I have SQL Server database. Server name is DBServer. I have database A
in this server. If i click properties, the data files name show
"A_Data" and the transaction log name show "A_Log". I backup database A
to C:\ABackup.
After that, i create database B. I want to restore database A to
database B. I choose "from device" and select the location to
C:\ABackup. But when i tried to restore, in the Original File Name
(tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
data files name and transaction log name will change into A_Data and
A_Log.
What i want is that in my database B the name of data files and
transaction log not change after i do restore from database A.
Does anybody know how to do that?
Thanks before.On the Restore Database window select your file using 'From device'.
Go to Options. On 'Restore As' update the directory path and file name. You
can not change the logical name here, only the physical name.
Finish the Restore
If you are using SQL Server 2005
Select Database Properties, select Files tab
You can change the Logical Name here.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"afang" wrote:
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Hi
You can restore a database with a different name ,so there is no need to
create a new database prior to restore operation
"afang" <khokimfang@.gmail.com> wrote in message
news:1147053596.925983.99420@.j33g2000cwa.googlegroups.com...
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Thanks Ben, for your information

change acceptable filesize of files to be placed in the portal

Nice thing of the reporting services portal is the option to upload
different type of files like xls and pdf.
I was trying to upload a pdf of 5 megs but the portal didn't accept it.
Is it possible to change the maximum acceptable filesize?Do you realize that RS anything deployed into a database. Nothing is stored
as a file. RS is not really designed to be used as a document storage
system. Although as you discovered you can do this (I have done it for some
word documents).
I am not aware of any parameter to change in this regard (changing the max
file size).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"nickismyname" <j.koopmans@.novio.nl> wrote in message
news:1166110729.330622.237020@.16g2000cwy.googlegroups.com...
> Nice thing of the reporting services portal is the option to upload
> different type of files like xls and pdf.
> I was trying to upload a pdf of 5 megs but the portal didn't accept it.
> Is it possible to change the maximum acceptable filesize?
>

Certain rows to excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

You can easily achieve this in DTS Import/Export Wizard. Suppose you're using SQL2000, open Enterprise Manager->go to the table from which you want to export data->right click choose All Tasks-> Export Data-> in the Specify Table Copy or Query step, choose Use a Query...->enter the SELECT command (e.g. SELECT * FROM Orders WHERE EmployeeID=6), or use Query Builder->complete the wizard.

|||

Thanks for the reply, How should I run the same queries for all the IDs separately in DTS and save the results to excel files?

certain rows to Excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

Hi,

While Using DTS Import/Export Wizard,
After Choosing Data Source and DataDestination,
Wizard asks to Specify Table Copy or Query,
At the Time Select the Radio Button [Use a Query to Specify the Data To Transfer].

It will Take you To Type Sql Statement where Query Statement Panel is there ,you can Type Query with your Condition.

|||

Thanks for the reply.

This should be done once every day , so I am trying to automate it. How should I develop my query and dts based on this criteria? Any example will be greatly appreciated.

|||

Select Jobs From SQL Server Agent Under Management of the Selected Server(In EnterpriseManager)

Select New Job and Name it
Select Steps Tab in New Job Properties
Click New step and Name Step and Write Query in Command Panel
Click New Schedule and Name Schedule and Select Schedule Type

Now you Achieve the Required Thing

|||

I still do not see how the query that I am writing will export data to a different excel file based on the ID. All the rows that have the same ID should go to a single excel file, so I am expecting more than one excel file based on the distinct values of ID to be created. How can I do this in DTS?

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the sam
e
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will b
e
greatly appreciated.
"Paul Ibison" wrote:

> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use th
e
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:

> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the packag
e
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they ar
e,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I a
m
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic...926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic...926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the same
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will be
greatly appreciated.
"Paul Ibison" wrote:
> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use the
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:
> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the package
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they are,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I am
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > This table has IDs and Desc already in it. IDs might be duplicated and I am
> > trying to export the rows with the same IDs to a single excel file, so
> > expecting many excel files based on the distinct values of IDs. DTS seems
> > quite complicated to me since I have not done anything with that yet.
> >
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

CD Install

I would like to extract the self extracting exe files to go to a directory so I can burn a CD. I have tried burning the CD's from the temp directory but it wouldn't install properly from the CD. Is there a command line parameter to get around this? TIA

Add "/x" (no quotes) to any of the express packages (e.g. SQLEXPR.EXE /x) and you will be prompted for a directory to extract the files to.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

Casting...

I've got an ftp task that will be downloading a couple of files each night. today they're called

blah20060830blah

the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.

Thank you

It'd help if you posted your expression and the error that you're getting.

-Jamie

|||Sorry

the expression
(DT_STR) GETDATE()

generates this:
Attempt to parse the expression "(DT_STR) GETDATE()" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

I'm no genius when it comes to making these expressions, and was unable to find a source on the msn site

Thanks again|||

All the source you need is in BOL. And in this case the syntax for the cast is given in the expression editor.

You need:

(DT_STR, 100, 1252)GETDATE()

-Jamie

|||What is BOL?

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?|||

killerless wrote:

What is BOL?

Sorry. Books Online. i.e. The help pages that you get with SQL Server.

killerless wrote:

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?

Hmmm...are you trying to add this as anew column or replace an existing one? If the latter, change it so that you are adding it as a new column.

-Jamie

|||Maybe I'm completely missing the boat here.

in an ftp task, there is a property called "Remote Path" As I understand, this designates what the path and filename of the remote file to be downloaded.

Here's my leap of understanding. I want that value to change daily, I'm assuming that I can define this using an expression. I will concat the parts of the string in the expression builder that don't change daily, to the part that i'm trying to generate right now (which is based on the date)

I am double clicking on the ftp task, setting appropriate properties, then going to the expression section and trying to set the remote path w/ an expression using the expression builder. I'm literally cutting and pasting what you typed in to the expression text area and trying to see if it will evaluate the expression as a string.

As far as I know there are no columns involved. (I will check out the BOL)

Thanks|||

Oh OK. Sorry, I thought you were in a Derived Column Component (which you are not).

Your approach is the corect one. its jsut going to take some debugging that's all.

The Expression Editor itself has some really useful visual aids as to the syntax of all the functions etc... Loom in the box in the top-right of the expressoin editor.

-Jamie

|||I think i'm getting there i will post when i figure this out...|||There's got to be a better way!!!!

DATEPART("weekday", GETDATE()) == 2 ?
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
)
:
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
)
+ "_today.gz.file.asc"

will successfully determine the last day of trading, and afix it to the middle of the string. Can you make temporary variables in these expressions that would probably cut this in half
|||How can I set this value to a variable, so that I can use it in other expressions?|||

killerless wrote:

How can I set this value to a variable, so that I can use it in other expressions?

Good idea!

There is a property of the variable called EvaluateAsExpression. Set this proeprty to false and then paste your expression into the Expression property.

I talk about this a bit here:

Using variables to store expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx)

Evaluating variables as expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx)

-Jamie

|||AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Is it possible to call a stored procedure? From a expression?

Thanks :)|||so...if the remote variable is now being set w/ an expression, why can't I leave the field blank? Aside from making lost of extra variables is there any advantage to putting these strings in variable derived from my date variable?

Thanks|||

killerless wrote:

AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Install Sp1 and you'll be able to

killerless wrote:

Is it possible to call a stored procedure? From a expression?

No! Sorry.

-Jamie

Casting...

I've got an ftp task that will be downloading a couple of files each night. today they're called

blah20060830blah

the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.

Thank you

It'd help if you posted your expression and the error that you're getting.

-Jamie

|||Sorry

the expression
(DT_STR) GETDATE()

generates this:
Attempt to parse the expression "(DT_STR) GETDATE()" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

I'm no genius when it comes to making these expressions, and was unable to find a source on the msn site

Thanks again|||

All the source you need is in BOL. And in this case the syntax for the cast is given in the expression editor.

You need:

(DT_STR, 100, 1252)GETDATE()

-Jamie

|||What is BOL?

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?|||

killerless wrote:

What is BOL?

Sorry. Books Online. i.e. The help pages that you get with SQL Server.

killerless wrote:

I put your suggestion into the expression builder, but got the following error

Expression cannot be evaluated.

The expression "(DT_STR, 100, 1252)GETDATE()" has a result type of "DT_STR", which cannot be converted to a supported type.

Do I need to put more in the expression builder?

Hmmm...are you trying to add this as anew column or replace an existing one? If the latter, change it so that you are adding it as a new column.

-Jamie

|||Maybe I'm completely missing the boat here.

in an ftp task, there is a property called "Remote Path" As I understand, this designates what the path and filename of the remote file to be downloaded.

Here's my leap of understanding. I want that value to change daily, I'm assuming that I can define this using an expression. I will concat the parts of the string in the expression builder that don't change daily, to the part that i'm trying to generate right now (which is based on the date)

I am double clicking on the ftp task, setting appropriate properties, then going to the expression section and trying to set the remote path w/ an expression using the expression builder. I'm literally cutting and pasting what you typed in to the expression text area and trying to see if it will evaluate the expression as a string.

As far as I know there are no columns involved. (I will check out the BOL)

Thanks|||

Oh OK. Sorry, I thought you were in a Derived Column Component (which you are not).

Your approach is the corect one. its jsut going to take some debugging that's all.

The Expression Editor itself has some really useful visual aids as to the syntax of all the functions etc... Loom in the box in the top-right of the expressoin editor.

-Jamie

|||I think i'm getting there i will post when i figure this out...|||There's got to be a better way!!!!

DATEPART("weekday", GETDATE()) == 2 ?
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) )
)
:
"positions_01_CLARENROAD_" + (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) +
(
DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )
) +
(
DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ?
"0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
:
(DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) )
)
+ "_today.gz.file.asc"

will successfully determine the last day of trading, and afix it to the middle of the string. Can you make temporary variables in these expressions that would probably cut this in half
|||How can I set this value to a variable, so that I can use it in other expressions?|||

killerless wrote:

How can I set this value to a variable, so that I can use it in other expressions?

Good idea!

There is a property of the variable called EvaluateAsExpression. Set this proeprty to false and then paste your expression into the Expression property.

I talk about this a bit here:

Using variables to store expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx)

Evaluating variables as expressions
(http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx)

-Jamie

|||AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Is it possible to call a stored procedure? From a expression?

Thanks :)|||so...if the remote variable is now being set w/ an expression, why can't I leave the field blank? Aside from making lost of extra variables is there any advantage to putting these strings in variable derived from my date variable?

Thanks|||

killerless wrote:

AWESOME, thanks a lot, i'm looking forward to the day when the variable expression can be created w/ the expression builder. (I'd settle for a text area a this point)

Install Sp1 and you'll be able to

killerless wrote:

Is it possible to call a stored procedure? From a expression?

No! Sorry.

-Jamie