How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you ar
e
using Management Studio on the Restore Database window select the Options ta
b
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and th
e
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:

> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
Ekrem nsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you are
using Management Studio on the Restore Database window select the Options tab
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and the
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
--
Ekrem Önsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?
By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you are
using Management Studio on the Restore Database window select the Options tab
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and the
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:

> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>
|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
Ekrem nsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the connection string in a web.config file

I've recently uploaded my website, http://www.bigredsongbus.com, to my host. Unfortunately, they don't support the use of SQL Server Express. So, I've purchased an addon through my host - discountasp.net. I've attached my database file to their server, now all that I need to do is to change the connection string in my .config file so that it points to the SQL Server. I don't know how to do this. Any help, please?

Jeffrey Way

<connectionStrings>
<add name="<CONNECTIONSTRINGNAME>" connectionString="Data Source=<SERVERAME or IPADRESSPROVIDED BY HOST>;Initial Catalog=<DATABASENAME>;User ID=<USERNAME>;Password=<PASSWORD>" providerName="System.Data.SqlClient"/>
</connectionStrings>|||

<

connectionStrings>

<

clear/>

<

addname="LocalSQLServer"connectionString="Data Source=serverName;Initial Catalog=DatanaseName;Persist Security Info=True;User ID=sa;Password=sa"/></connectionStrings>

Hope this is what ur looking for

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:
> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
> > Database space can be allocated using EM. But sometimes people allocated a
> > huge size for data or log file, for instance, 10GB.
> >
> > When the database goes into running for production, maybe only needs maximun
> > 1GB
> > . So I want to reduce this size.
> >
> > Shrink only truncate log.
> >
> > How to do this with recreating a db. How to know the space used in db? For
> > instance, maybe only 300MB used although 10GB allocated.
> >
> > Does the huge space affect the db performance?
> >|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
> > Hi
> >
> > Check out DBCC SHRINKFILE see
> > http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> >
> > If you allocate the size too small the database will only spend time and
> > resources growing the database, therefore it should be a reasonable initial
> > size that avoids this. If the files are always growing and being shrunk you
> > may get fragmentation of the physical files may affect performance.
> >
> > If you are in full recovery mode and have regular log backups, the size of
> > the log file should be reasonably constant. You should make sure that the
> > initial size is also large enough to cover your normal usage. Read
> > http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> > information.
> >
> > Make sure that your filegrowth is not set to a percentage otherwise it would
> > grow exponentially.
> >
> > John
> >
> > "KentZhou" wrote:
> >
> > > Database space can be allocated using EM. But sometimes people allocated a
> > > huge size for data or log file, for instance, 10GB.
> > >
> > > When the database goes into running for production, maybe only needs maximun
> > > 1GB
> > > . So I want to reduce this size.
> > >
> > > Shrink only truncate log.
> > >
> > > How to do this with recreating a db. How to know the space used in db? For
> > > instance, maybe only 300MB used although 10GB allocated.
> > >
> > > Does the huge space affect the db performance?
> > >sql

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maxim
un
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initia
l
> size that avoids this. If the files are always growing and being shrunk yo
u
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it wou
ld
> grow exponentially.
> John
> "KentZhou" wrote:
>|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DB
CC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
>

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
.. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?
Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>
|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:

How to change name of Excel attachment in data-driven subscription

Hello,
I have a data-driven subscription that sends out the report as an Excel
file. The file of the Excel spreadsheet is the name of the report. However,
a customer wants the name of the Excel file to be personalized. And I don't
see how to accomplish that and still use the subscription mechanism of
Reporting Services. If possible, I want to avoid writing my own subscription
routines.
Appreciates any hints on how to do this.
Best regards,
Vemund HagaI also would like to know how because I have to send some different copies of
the same template with different datas to the same customer. He will receive
some different copies of the Excel report with the same name.
Thank you,
Sophy G.
"Vemund Haga" wrote:
> Hello,
> I have a data-driven subscription that sends out the report as an Excel
> file. The file of the Excel spreadsheet is the name of the report. However,
> a customer wants the name of the Excel file to be personalized. And I don't
> see how to accomplish that and still use the subscription mechanism of
> Reporting Services. If possible, I want to avoid writing my own subscription
> routines.
> Appreciates any hints on how to do this.
> Best regards,
> Vemund Haga
>
>

how to change model DB

Hi,
How to change model DB, so that new created DB can use file size & location
or other options copied from model DB?
Thanks,
Jack
just clarification: change the default values of the database options for
Model DB, so any newly created databases can use new value such as DB file
location & size.
Many thanks,
"Jack" <chongh.hc@.gmail.com> wrote in message
news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How to change model DB, so that new created DB can use file size &
> location or other options copied from model DB?
> Thanks,
> Jack
>
|||Jack
You simply need to Right Click on the Model Database in SQL Server
Enterprise Manager and update the desired properties such as the Data File
and Log File sizes. To set the Default database location you need to Right
Click on the SQL Server instance in SQL Server Enterprise Manager and Select
the Database Settings Tab and set the Default Data and Log directories.
- Peter Ward
WARDY IT Solutions
"Jack" wrote:

> just clarification: change the default values of the database options for
> Model DB, so any newly created databases can use new value such as DB file
> location & size.
> Many thanks,
> "Jack" <chongh.hc@.gmail.com> wrote in message
> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>
|||Excellent!
thanks, Peter!
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5039A552-9DB0-4C62-ACA1-24C85B045C2C@.microsoft.com...[vbcol=seagreen]
> Jack
> You simply need to Right Click on the Model Database in SQL Server
> Enterprise Manager and update the desired properties such as the Data File
> and Log File sizes. To set the Default database location you need to
> Right
> Click on the SQL Server instance in SQL Server Enterprise Manager and
> Select
> the Database Settings Tab and set the Default Data and Log directories.
>
> - Peter Ward
> WARDY IT Solutions
> "Jack" wrote:
sql

how to change model DB

Hi,
How to change model DB, so that new created DB can use file size & location
or other options copied from model DB?
Thanks,
Jackjust clarification: change the default values of the database options for
Model DB, so any newly created databases can use new value such as DB file
location & size.
Many thanks,
"Jack" <chongh.hc@.gmail.com> wrote in message
news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How to change model DB, so that new created DB can use file size &
> location or other options copied from model DB?
> Thanks,
> Jack
>|||Jack
You simply need to Right Click on the Model Database in SQL Server
Enterprise Manager and update the desired properties such as the Data File
and Log File sizes. To set the Default database location you need to Right
Click on the SQL Server instance in SQL Server Enterprise Manager and Select
the Database Settings Tab and set the Default Data and Log directories.
- Peter Ward
WARDY IT Solutions
"Jack" wrote:
> just clarification: change the default values of the database options for
> Model DB, so any newly created databases can use new value such as DB file
> location & size.
> Many thanks,
> "Jack" <chongh.hc@.gmail.com> wrote in message
> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > How to change model DB, so that new created DB can use file size &
> > location or other options copied from model DB?
> >
> > Thanks,
> >
> > Jack
> >
>
>|||Excellent!
thanks, Peter!
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5039A552-9DB0-4C62-ACA1-24C85B045C2C@.microsoft.com...
> Jack
> You simply need to Right Click on the Model Database in SQL Server
> Enterprise Manager and update the desired properties such as the Data File
> and Log File sizes. To set the Default database location you need to
> Right
> Click on the SQL Server instance in SQL Server Enterprise Manager and
> Select
> the Database Settings Tab and set the Default Data and Log directories.
>
> - Peter Ward
> WARDY IT Solutions
> "Jack" wrote:
>> just clarification: change the default values of the database options for
>> Model DB, so any newly created databases can use new value such as DB
>> file
>> location & size.
>> Many thanks,
>> "Jack" <chongh.hc@.gmail.com> wrote in message
>> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > How to change model DB, so that new created DB can use file size &
>> > location or other options copied from model DB?
>> >
>> > Thanks,
>> >
>> > Jack
>> >
>>

how to change model DB

Hi,
How to change model DB, so that new created DB can use file size & location
or other options copied from model DB?
Thanks,
Jackjust clarification: change the default values of the database options for
Model DB, so any newly created databases can use new value such as DB file
location & size.
Many thanks,
"Jack" <chongh.hc@.gmail.com> wrote in message
news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How to change model DB, so that new created DB can use file size &
> location or other options copied from model DB?
> Thanks,
> Jack
>|||Jack
You simply need to Right Click on the Model Database in SQL Server
Enterprise Manager and update the desired properties such as the Data File
and Log File sizes. To set the Default database location you need to Right
Click on the SQL Server instance in SQL Server Enterprise Manager and Select
the Database Settings Tab and set the Default Data and Log directories.
- Peter Ward
WARDY IT Solutions
"Jack" wrote:

> just clarification: change the default values of the database options for
> Model DB, so any newly created databases can use new value such as DB file
> location & size.
> Many thanks,
> "Jack" <chongh.hc@.gmail.com> wrote in message
> news:eKs4i%23P2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>|||Excellent!
thanks, Peter!
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5039A552-9DB0-4C62-ACA1-24C85B045C2C@.microsoft.com...[vbcol=seagreen]
> Jack
> You simply need to Right Click on the Model Database in SQL Server
> Enterprise Manager and update the desired properties such as the Data File
> and Log File sizes. To set the Default database location you need to
> Right
> Click on the SQL Server instance in SQL Server Enterprise Manager and
> Select
> the Database Settings Tab and set the Default Data and Log directories.
>
> - Peter Ward
> WARDY IT Solutions
> "Jack" wrote:
>

How to Change Graph Type in a .rpt File

Hi all,

I'm working with Crystal report and a java Swing application. The interaction between the two is made up by a simple wrapper that starts the .rpt file when a button is pressed, passing just a few parameters.

i'd like to know if it's possible (and how :D ) to pass the type of chart to be displayed to the .rpt file as a parameter, letting the java application user to choose the chart type inside the Swing environment.

thanks for the help

EnricoI think you need to create as many reports as the number of charts and show corresponding reports as what the user chooses

how to change date format in a select statement

when i use this command in a aspx file

"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.......................

'Format$' is not a recognized function name.

so how do i change date from mm/dd/yyyy to mm/yyyy

Check out the CAST and CONVERT functions in SQL BOL. They have a listing of all the possible combinations of formatting you can do for datetime values.|||

Hi~

Try this:

SELECTRIGHT(CONVERT(VARCHAR(10), Column_Name, 103), 7)AS [MM/YYYY]from Table_Name

Hope it helps.

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

How to change crystal report document title dynamically?

I have ReportClass object of .Net CrystalReport
that uses .rpt file
ResCalc.SalesReport rpt = new ResCalc.SalesReport();
rpt.SetDataSource(dsSaleRep);
How to change crystal report document title dynamically?
Thanks,
Alex.Hi Alex,
i am new member of this forum...

you can try the following:

ReportDocument oRpt = new ReportDocument ();
oRpt.SummaryInfo.ReportTitle = yourinputcontrol.Text;

as in my case it was a checklist box so i did as follows

Reports.TIPSheetsSummary frmTIPSummaryReport = new TIPSheetsSummary();
frmTIPSummaryReport.oRpt.SummaryInfo.ReportTitle = "TIP Sheets Summary for Airplane Tab No. " + this.checkedListBox1.SelectedItem.ToString();

Regards,
Ashwini

How to change a data file name

Hi All,
Is anyone can help me to change a existing data file into a new new name?
Thanks.The logical name of the file, or the actual path and filename of the .NDF file?|||Logical name.|||Alter database DB modify file (name = 'old_name_of_file', newname = 'new_name_of_file')|||It worked good.
Thank you MCrowley, it's very helpful.

How to capute "Execution Reasults" to a text file

How can I save all the text in the SSIS "Execution results" pane to a file? (I can copy 1 line at a time, by right-clicking the message of interest, but there must be a better way.)

TIA,

Barker

Look up how to create log providers in BOL. It will let you dump whatever information you want out to a variety of sources.

How to Capture data from server side trace to file?

Hi,
I want to automate tracing for SQL server Profiler. I have written a set of
stored procedures using the SQL Server in-built sp's for the very purpose.
A trace is also being successfully created with trace id and a
file(MyTrace.trc) is also created however the trace is not writing anything
into the file MyTrace.trc
I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
and when accessing info of the trace using
SELECT *
FROM ::fn_trace_getinfo(@.TraceId)
it shows the correct file path and status as 1
Could some please help me out in this.
What else am i required to do for enabling server side trace and so that the
capture sql traces are written into the file.
Regards
Shajee
The easiest way to make sure you're on the right track is to use the
Profiler tool, get a trace running that is capturing what you want, stop it
and the script it (File>Script Trace). Then run the generated script and
stop it and check you have results. If this is all working okay then check
your code and make sure you don't have any filters present that might be
interfering with the capture. In SQL2000 you need to both stop the
serverside trace and delete its definition from the server in order to open
it correctly using Profiler or fn_trace_gettable. In SQL2005 this is no
lonfer the case and you can access a running server side trace. Are you
actually getting an error or is it simply that the trace file is empty. If
it is truly empty then it's likely an issue with your code - double check it
against the script generated by Profiler
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"msnews.microsoft.com" <ahmadshajee@.hotmail.com> wrote in message
news:OT5zGaS6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to automate tracing for SQL server Profiler. I have written a set
> of
> stored procedures using the SQL Server in-built sp's for the very purpose.
> A trace is also being successfully created with trace id and a
> file(MyTrace.trc) is also created however the trace is not writing
> anything
> into the file MyTrace.trc
> I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
> and when accessing info of the trace using
> SELECT *
> FROM ::fn_trace_getinfo(@.TraceId)
> it shows the correct file path and status as 1
> Could some please help me out in this.
> What else am i required to do for enabling server side trace and so that
> the
> capture sql traces are written into the file.
> Regards
> Shajee
>
|||Writes to the trace file happen in 128K chunks. Are you sure you've
generated that many events?
Try stopping and closing the trace (this takes to calls to
sp_trace_setstatus, first stop and then close the trace.
Or try generating lots of events to get 128K worth of data generated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <ahmadshajee@.hotmail.com> wrote in message
news:OT5zGaS6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to automate tracing for SQL server Profiler. I have written a set
> of
> stored procedures using the SQL Server in-built sp's for the very purpose.
> A trace is also being successfully created with trace id and a
> file(MyTrace.trc) is also created however the trace is not writing
> anything
> into the file MyTrace.trc
> I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
> and when accessing info of the trace using
> SELECT *
> FROM ::fn_trace_getinfo(@.TraceId)
> it shows the correct file path and status as 1
> Could some please help me out in this.
> What else am i required to do for enabling server side trace and so that
> the
> capture sql traces are written into the file.
> Regards
> Shajee
>
>
|||Thanks for the help. I really appreciate that.
The problem is resolved. Actually i was stopping the trace but not
closing it. So the captured events were not being written into
file(.trc). I'm using SQL 2000
Now after deleting the definition of trace by using
sp_trace_setstatus @.TraceId,2 it works and the specified file gets
update which's then accessed by function
::fn_trace_gettable(@.filepath,default)
Thanks for the help once again
Regards
Shajee
*** Sent via Developersdex http://www.codecomments.com ***

How to callapse rows together

I'm need to collaspe a few rows together and output one row per group. The rows come from a flat file and are already sorted by orderid. Basically these are transactions with just the changed fields in them all unchanged fields are null (blank). They need to collapsed down to show the final state of all changed fields per order. Obviously the aggregate transform won't work in this situation since the goal is to collaspe instead of aggregate. In a language, I would normally buffer the previous row and apply the logic as needed until the orderid changed and then write out the new row. Then input file has millions of rows so I also didn't want to cursor through it updating individual rows in a table.

Example:

Orderid StatusCd OrderDt OrdrAmt

1 A 01/01/06 10

1 B

2 X 02/02/06 20

3 03/03/06 30

3 03/04/06

Collapse as

1 B 01/01/06 10

2 X 02/02/06 20

3 03/04/06

Danny,

The logic that you say you would apply in a programming language can be applied in exactly the same in SSIS by using a script component. I recommend you take a look at building an asynchronous script component.

-Jamie