How to Setup VS Studio 2005 for Import and Export to SQL Server 2005 Express as performed in SQL

Greetings all,

I have just been getting used to the new VS.NET 2005 IDE environment, IT ROCKS!. And this integrated help is pretty useful when we can all collaborate on the many technical topics we all share.

After reading many post blasting Microsoft for NOT including the DTS Import/Export functionality to SQL Server 2005 Express, and many people just threatening to go back to only use SQL Server 2000. I decided to look into this.

And found an actually simple way to do this.

The new Microsoft IDE in VS.NET 2005 includes the ability to define custom tools to be accessible in the Main Tools Menu.

Follow these steps:

1. Click Tools Menu (in VS.NET 2005 IDE)
2. Click External Tools
3. Define name for menu item in the Title text box
4. Define full path and name of executable in the Command text box
5. Define parameters to pass in the Arguments text box

You can launch any external application here, and a Menu Item is created. You can also pass parameters to this, and this is how we can do some DTS stuff if you do have SQL Server 2000 installed on either your local system, or a system you can access from your computer.

There is a program that ships with SQL Server 2000 call DTSWIZ.EXE. This
is used from the SQL Server Enterprise Manager. (Note: Make sure you have sufficient access to this location)

To use this, first check out the parameters is expects.

1. Shell out to DOS
Click Start, Run, then type in cmd and hit [ENTER]

2. Navigate to location of SQL Server
\Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe

This program supports several parameters, you can launch this wizard in several ways.

example:

To start up DTS Import
dtswiz.exe /n /i

To start up DTS Export
dtswiz.exe /n /x

Once you have this in place, then you want to Import Data. Simply choose
Import from the Tools menu (the External Tool item you created above)

1. In the Data Source drop down, select SQL Native Client.
2. Click Properties Button (lower left corner)
3. In the Data Source of this screen, put full path to SQL 2005 Express Database
4. Choose authentication
5. Choose Exit

Follow the remaining steps of the wizard for selecting which tables you wanted to import.

This has been thoroughly tested on the following:

Pentium 3 Two-Gigerhz Dual Processer
1 Gig Memory
Running Visual Studio 2005, SQL Server 2005 Express
and SQL Server 2000 located on a different Server computer.

I've simply created a Mapped Network drive to the
\Program Files\Microsoft SQL Server\80\Tools\Binn
to have access to the tools.

Hopefully others can use this activating IMPORT and EXPORT functionality
so you can port some of your smaller SQL Server 2000 databases to a different data tier model.

I am starting to contribute here, hopefully others will get themself a Windows Password account if you don't have one already, and start sharing more material between ourselves.

?/"/"[ Dreams pull reality closer to use all ]"\"\"
Dim Mind as New Mind
If Mind.Thinking then
Mind.GetThought("VS.NET")
If Mind.Feelings = Feel.FreakingROCKS then
Call KeepThingsRockin
End If
End If
CodeDoctor

I can't get this to work on Vista. I get an error that DTS couldn't create an instance.
|||

Am new to this. I have access to the DTS WIZ but cannot edit the saved package in SSE.

Going Mad!!!

Sudip

sql

How to Setup VS Studio 2005 for Import and Export to SQL Server 2005 Express as performed in SQL

Greetings all,

I have just been getting used to the new VS.NET 2005 IDE environment, IT ROCKS!. And this integrated help is pretty useful when we can all collaborate on the many technical topics we all share.

After reading many post blasting Microsoft for NOT including the DTS Import/Export functionality to SQL Server 2005 Express, and many people just threatening to go back to only use SQL Server 2000. I decided to look into this.

And found an actually simple way to do this.

The new Microsoft IDE in VS.NET 2005 includes the ability to define custom tools to be accessible in the Main Tools Menu.

Follow these steps:

1. Click Tools Menu (in VS.NET 2005 IDE)
2. Click External Tools
3. Define name for menu item in the Title text box
4. Define full path and name of executable in the Command text box
5. Define parameters to pass in the Arguments text box

You can launch any external application here, and a Menu Item is created. You can also pass parameters to this, and this is how we can do some DTS stuff if you do have SQL Server 2000 installed on either your local system, or a system you can access from your computer.

There is a program that ships with SQL Server 2000 call DTSWIZ.EXE. This
is used from the SQL Server Enterprise Manager. (Note: Make sure you have sufficient access to this location)

To use this, first check out the parameters is expects.

1. Shell out to DOS
Click Start, Run, then type in cmd and hit [ENTER]

2. Navigate to location of SQL Server
\Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe

This program supports several parameters, you can launch this wizard in several ways.

example:

To start up DTS Import
dtswiz.exe /n /i

To start up DTS Export
dtswiz.exe /n /x

Once you have this in place, then you want to Import Data. Simply choose
Import from the Tools menu (the External Tool item you created above)

1. In the Data Source drop down, select SQL Native Client.
2. Click Properties Button (lower left corner)
3. In the Data Source of this screen, put full path to SQL 2005 Express Database
4. Choose authentication
5. Choose Exit

Follow the remaining steps of the wizard for selecting which tables you wanted to import.

This has been thoroughly tested on the following:

Pentium 3 Two-Gigerhz Dual Processer
1 Gig Memory
Running Visual Studio 2005, SQL Server 2005 Express
and SQL Server 2000 located on a different Server computer.

I've simply created a Mapped Network drive to the
\Program Files\Microsoft SQL Server\80\Tools\Binn
to have access to the tools.

Hopefully others can use this activating IMPORT and EXPORT functionality
so you can port some of your smaller SQL Server 2000 databases to a different data tier model.

I am starting to contribute here, hopefully others will get themself a Windows Password account if you don't have one already, and start sharing more material between ourselves.

?/"/"[ Dreams pull reality closer to use all ]"\"\"
Dim Mind as New Mind
If Mind.Thinking then
Mind.GetThought("VS.NET")
If Mind.Feelings = Feel.FreakingROCKS then
Call KeepThingsRockin
End If
End If
CodeDoctor

I can't get this to work on Vista. I get an error that DTS couldn't create an instance.
|||

Am new to this. I have access to the DTS WIZ but cannot edit the saved package in SSE.

Going Mad!!!

Sudip

How to Setup VS Studio 2005 for Import and Export to SQL Server 2005 Express as performed in

Greetings all,

I have just been getting used to the new VS.NET 2005 IDE environment, IT ROCKS!. And this integrated help is pretty useful when we can all collaborate on the many technical topics we all share.

After reading many post blasting Microsoft for NOT including the DTS Import/Export functionality to SQL Server 2005 Express, and many people just threatening to go back to only use SQL Server 2000. I decided to look into this.

And found an actually simple way to do this.

The new Microsoft IDE in VS.NET 2005 includes the ability to define custom tools to be accessible in the Main Tools Menu.

Follow these steps:

1. Click Tools Menu (in VS.NET 2005 IDE)
2. Click External Tools
3. Define name for menu item in the Title text box
4. Define full path and name of executable in the Command text box
5. Define parameters to pass in the Arguments text box

You can launch any external application here, and a Menu Item is created. You can also pass parameters to this, and this is how we can do some DTS stuff if you do have SQL Server 2000 installed on either your local system, or a system you can access from your computer.

There is a program that ships with SQL Server 2000 call DTSWIZ.EXE. This
is used from the SQL Server Enterprise Manager. (Note: Make sure you have sufficient access to this location)

To use this, first check out the parameters is expects.

1. Shell out to DOS
Click Start, Run, then type in cmd and hit [ENTER]

2. Navigate to location of SQL Server
\Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe

This program supports several parameters, you can launch this wizard in several ways.

example:

To start up DTS Import
dtswiz.exe /n /i

To start up DTS Export
dtswiz.exe /n /x

Once you have this in place, then you want to Import Data. Simply choose
Import from the Tools menu (the External Tool item you created above)

1. In the Data Source drop down, select SQL Native Client.
2. Click Properties Button (lower left corner)
3. In the Data Source of this screen, put full path to SQL 2005 Express Database
4. Choose authentication
5. Choose Exit

Follow the remaining steps of the wizard for selecting which tables you wanted to import.

This has been thoroughly tested on the following:

Pentium 3 Two-Gigerhz Dual Processer
1 Gig Memory
Running Visual Studio 2005, SQL Server 2005 Express
and SQL Server 2000 located on a different Server computer.

I've simply created a Mapped Network drive to the
\Program Files\Microsoft SQL Server\80\Tools\Binn
to have access to the tools.

Hopefully others can use this activating IMPORT and EXPORT functionality
so you can port some of your smaller SQL Server 2000 databases to a different data tier model.

I am starting to contribute here, hopefully others will get themself a Windows Password account if you don't have one already, and start sharing more material between ourselves.

?/"/"[ Dreams pull reality closer to use all ]"\"\"
Dim Mind as New Mind
If Mind.Thinking then
Mind.GetThought("VS.NET")
If Mind.Feelings = Feel.FreakingROCKS then
Call KeepThingsRockin
End If
End If
CodeDoctor

I can't get this to work on Vista. I get an error that DTS couldn't create an instance.|||

Am new to this. I have access to the DTS WIZ but cannot edit the saved package in SSE.

Going Mad!!!

Sudip

How to set Report manager PDF export timeout?

In the report manager you have the option to export to PDF which pops up a
windows that has a timeout encoded into the URL that generates the PDF. The
URL generated looks like:
http://localhost/Reports/Reserved.ReportViewerWebControl.axd?Server=http%3a%2f%2flocalhost%2fReportServer&Timeout=100000&ReportSession=ayxrhe45b5gzjh55mcra5o45&ControlID=18a6c1bb-379f-4332-9b44-16aa3eb2468b&OpType=Export&Format=PDF
I'm pretty sure it's this hardcoded value in the URL because when I increase
it; it works. Is there any way to configure this? I searched the web but I
haven't seen this addressed anywhere.
Windows 2003 server -
SQL Server April 2005 CTPAny ideas on this...
Is there no way to increase this timeout? Can Microsoft maybe add a
configuration setting into the 2005 version so this can be set in the config
file? It seems like an easy request since someone hardcoded the default to
100000.
Thanks,
Jeff
"Jeff Glenn" wrote:
> In the report manager you have the option to export to PDF which pops up a
> windows that has a timeout encoded into the URL that generates the PDF. The
> URL generated looks like:
> http://localhost/Reports/Reserved.ReportViewerWebControl.axd?Server=http%3a%2f%2flocalhost%2fReportServer&Timeout=100000&ReportSession=ayxrhe45b5gzjh55mcra5o45&ControlID=18a6c1bb-379f-4332-9b44-16aa3eb2468b&OpType=Export&Format=PDF
> I'm pretty sure it's this hardcoded value in the URL because when I increase
> it; it works. Is there any way to configure this? I searched the web but I
> haven't seen this addressed anywhere.
> Windows 2003 server -
> SQL Server April 2005 CTP

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set a column to be an identity column in TSQL.

Can someone provide the syntax for setting a column to be an identity column
in a stored proceedure. When I export my database, my identity columns are
no longer identity columns.I don't beleive you can add an Identity property to an existing column.
You may need to create a temp table with the identity column in it, copy the
data to the temp table, drop the existing table and then rename your temp
one.
"Jim Mitchell" <jim_mitchell@.mindspring.com> wrote in message
news:uSn1WWFfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> Can someone provide the syntax for setting a column to be an identity
column
> in a stored proceedure. When I export my database, my identity columns
are
> no longer identity columns.
>

how to separate date & time during export

I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?

Sample:

select getdate() ==> 2007-08-30 14:42:11.870

V

select convert(char, getdate(), 111) as date, convert(char, getdate(), 108) as [time]

==> Date = 2007/08/30

==> Time = 14:44:50

Others datetime format

century century
(yy) (yyyy) standard output format
- - --
- 0 or 100 (*) default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mms
- 9 or 109 (*) default mon dd yyyy
milliseconds hh:mis:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default dd mon yyyy
milliseconds hh:mms:mmm(24h)
14 114 - hh:mis:mmm(24h)

(*) the default values (style 0 or 100, 9 or 109, and
13 or 113) always return the century (yyyy)

|||

You have to use Derived Column Transformation of Data Flow.

So:

1. Add a Data Flow task to Control Flow window

2.Add OleDB Source then Derived Column and Finaly Flat File Destination linked with arrow

3. Double Click Derived Column and

write Derived Column Name desired then <add as new column> to Derived Column then in expresion : (DT_DBTIME)YourDataColumnName and Data Type database time[DT_DBTIME]

in second line write Derived Column Name desired then Replace 'YourDataColumnName' and in expression (DT_DBDATE)YourDataColumnName and Data Type databasedate[DT_DBDATE]

|||

I would use this expressions in a derived column:

For Date:

RIGHT("00" + (DT_WSTR,2)MONTH(MyDate),2) + "-" + RIGHT("00" + (DT_WSTR,2)DAY(MyDate),2) + "-" + (DT_WSTR,4)YEAR(MyDate)

For time:

(DT_DBTIME)MyDate