Import Prs

Hey guys,

I was curious how other people create several 100 SQL objects on a
new database. Example: I have each procedure in its own text file (IE
100 .sql files), so I just check them out of source control, then run
a perl script that just dumps them into one txt file. Then I paste
that mess into Query Analyzer.

So to reiterate my question, how do other people get sql objects
into a database. Obviously I'd do an object copy if they resided in
some other database.

While my solution works, there is always a better way. Thanks for
your suggestions.

- CptI don't know Perl but I assume you could execute OSQL from your script.
Another method is to invoke a Windows FOR command in a command prompt
window to execute OSQL. For example:

CD C:\SQLScripts
FOR %v in (*.sql) DO OSQL -i "%v" -o "%v.out" -E

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"CptVorpal" <cptvorpal@.hotmail.com> wrote in message
news:76cffbe0.0310081530.6c7a034b@.posting.google.c om...
> Hey guys,
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.
> - Cpt|||cptvorpal@.hotmail.com (CptVorpal) wrote in message news:<76cffbe0.0310081530.6c7a034b@.posting.google.com>...
> Hey guys,
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.
> - Cpt

You could start by looking at using OSQL.EXE to run your .sql scripts
from the command line - it should be straightforward to wrap that in a
script of some sort which gets the list of scripts, then executes them
one by one. You can trap the output from OSQL and use it for error
handling as well.

Simon|||[posted and mailed]

CptVorpal (cptvorpal@.hotmail.com) writes:
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.

The suggestion from Dan and Simon to use OSQL is a good one. I'll
add that you can invoke OSQL for each file. This could be help to
track any errors.

For a faster execution you could look into to connect to the database
from Perl using some interface. (There is a short overview on my
web site at: http://www.algonet.se/~sommar/mssql...ternatives.html.

And if you want a ton of bells and whistles, you can look at
http://www.abaris.se/abaperls/. This is the load tool that we use
in our shop, and as the name indicates it's all Perl. You could
say that I started where you are now, and this is what I have seven
years later. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian
> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian
|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:

>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>
|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:
>>1. add login
>>2. set psw to generic value
>>3. set default database
>>
>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:
> >>1. add login
> >>2. set psw to generic value
> >>3. set default database
> >>
> >>
> >
> >4. add user to database
> >5. add them to user defined role
> >
> >The required script template:
> >
> >EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
> >USE MyDatabase
> >EXEC sp_adduser 'SomeLogin'
> >EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
> >
> >Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
> >and Role. One method is to generate the needed script using SQL and
> >OPENROWSET. The query below will generate a script for each row in the
> >spreadsheet. You can then copy/paste the results into a Query Analyzer
> >window and execute. The spreadsheet file needs be accessible by the SQL
> >Server service.
> >
> >SELECT
> >'EXEC sp_addlogin ''' +
> > Login +
> > ''', ''SomePassword'', ''' +
> > DefaultDatabase + '''
> >USE ' + DefaultDatabase + '
> >EXEC sp_adduser ''' +
> > Login + '''
> >EXEC sp_addrolemember ''' +
> > Role +
> > ''', ''' +
> > Login +
> > ''''
> >FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> > 'Select * from [Sheet1$]')
> >
> >
> >

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx
.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:

>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:
>

Import from excel - Could not find installable ISAM

I'm trying to quickly create a way of importing data from an excel sheet on my C drive to a table on a sql 2005 db hosted by a provider.

I set this up according to the following:

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

The article is in C# but I write in VB. I think I got it except I get this error:

Could not find installable ISAM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: Could not find installable ISAM.

Source Error:

Line 23: Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)Line 24:Line 25: connection.Open()Line 26: Line 27: ' Create DbDataReader to Data Worksheet

Microsoft is not much help. A couple of folks say to contact my host. Before I do I just want to be sure there code I have is correct.

Could someone take a quick look at this.

Thanks,

Here is the code behind the button.

ProtectedSub BTNImport_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles BTNImport.Click

' Connection String to Excel Workbook

Dim excelConnectionStringAsString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"""

' Connection to Excel Workbook

Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)

connection.Open()

' Create DbDataReader to Data Worksheet

Using drAs Data.Common.DbDataReader = command.ExecuteReader()

' SQL Server Connection String

Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString

'Origina from David Hyden's site - Dim sqlConnectionString As String = "Data Source=.;Initial Catalog=Test;Integrated Security=True"

' Bulk Copy to SQL Server

Using bulkCopyAs Data.SqlClient.SqlBulkCopy =New Data.SqlClient.SqlBulkCopy(connectionString)

bulkCopy.DestinationTableName ="ExcelData"

bulkCopy.WriteToServer(dr)

EndUsing

EndUsing

EndUsing

EndSub

Are you running this code on your local machine?

|||

Yes

|||

Actually, I'm using Visual Studio 20005 and am using Sql server express edition to test it.

|||

Maybe this will help

http://support.microsoft.com/kb/209805

|||

I read this before. This relates to Access.

I did push the page up to production in the even that it was my local machine. But, when I hit it online I still get the error.

|||

Got it. I think it was the comination of two problems.

First - I may have needed the following namespace:

Imports System.Data.OleDb

Previously I only had System.Data

Second- I revised the connection string as follows:

Dim excelConnectionStringAsString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0"

Works like a charm now.

Import from an ODBC data source into SQL Server

Hi,

I am trying to import tables from an ODBC data source into an SQL Server 2005 database. I presume that one way to achieve this is to create an Integration Services package, via Business Intelligence Studio (I already used DTS in SQL Server 2000, but not Integration Services) ?

Or is there a simpler way ? For instance, is there an import wizard that woult include an ODBC Data source ?

Thanks i advance.

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_frm/thread/2d0b1220a73e2894/f9adfb6af01a8306?hl=de#f9adfb6af01a8306

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

sql

Import Excel with varying worksheet name using DTS

Hi Everyone,

I'm trying to create a DTS package that will let me import an Excel file. The user will be able to name the file the same name every time. But can the DTS package read a different worksheet name each time? Right now, if I use the Excel connection object in DTS designer, it wants to hard code the worksheet name.

Thanks,

Eric

You might want to post in the DTS forum:

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.dts

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Hi, I'm a Student, and since a few months ago I'm learning JAVA. I'm creating an application to call and compare times. For this I create in Excel a time table which is quite big and it would be a lot of typing work to input one by one the data in each cell in SQL Server, considering that I have to create 8 more tables. I was able to retreive the data from excel usin the JXL API of JAVA but it doesn't give all the funtions to perform math operations as JDBC. That's why I need to move the tables from Excel to SQL.

I found this sitehttp://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx which gives a code to do so, but I guess that some heathers are missing or maybe I don't know which compiler to use to run that code, I would like you help to identify which compiler use to run that code or if there is some vital piece of code missing.

// Connection String to Excel Workbookstring excelConnectionString=@."Provider=Microsoft
 .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
 Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection=
new OleDbConnection(excelConnectionString)){ OleDbCommand command=new OleDbCommand
 ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr= command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString="Data Source=.;
 Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy=
new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName="ExcelData"; bulkCopy.WriteToServer(dr); } }}

On the other hand in this forum I that someelse use that link but implements a totally different code which I'm not able to compile alsohttp://forums.asp.net/p/1110412/2057095.aspx#2057095. It seems this code works as I was able to read, but I do not know which language is used.

Dim excelConnectionStringAsString ="Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

' Using

Dim connectionAs OleDbConnection =New OleDbConnection(excelConnectionString)

Try

Dim commandAs OleDbCommand =New OleDbCommand("Select ID,Data FROM [Data$]", connection)

connection.Open()

' Using

Dim drAs DbDataReader = command.ExecuteReader

Try

Dim sqlConnectionStringAsString = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString

' Using

Dim bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString)

Try

bulkCopy.DestinationTableName =

"ExcelData"

bulkCopy.WriteToServer(dr)

Finally

CType(bulkCopy, IDisposable).Dispose()

EndTry

Finally

CType(dr, IDisposable).Dispose()

EndTry

Finally

CType(connection, IDisposable).Dispose()

EndTry

Catch exAs Exception

EndTry

The Compilers I have are: Eclipse, Netbeans, MS Visual C++ Express Edition and MS Visual C# Express Edition. In MS Visual C++

Thanks for your help.

Regads,

Robert.

Hi,

From your description, it seems that you want to connect to your EXCEL table by OLDEB from your application, right?

If so, you can build your connection string in the following way, and create the oledbcommand object to execute your query statement.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelFilePath;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
string SQLQuery = "SELECT * FROM [sheet1$]".
// excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

For details, see:

http://support.microsoft.com/kb/326548 (Excel Part)

Besides, If your application runs in 64-bit mode, all of the components it uses must also be 64-bit. There is no 64-bit Jet OLE DB Provider, so you get the message described. You would receive a similar error when trying to connect to a database using OLE DB or ODBC if there is no 64-bit version of the specified OLE DB provider or ODBC driver.

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=123311

Thanks.

|||

if you really would like be sure that your import is successfully, do not use OLE DB or any other tolls to export your data from excel. Just use excel to export data to any common format like DBase, ACCESS, flat file and next import it to place you want. It will save you a lot of problems because the best tool to read Excel data is Excel itself and do not use other tools to do it. do not use SQL server SSIS also, it use OLE DB and does not work correctly with some excel shits.

Import Dump File

Hello,
I need to import a dump file (.dmp) and create a database from that. I
cannot find the syntax in the SQL Server books online.
If anyone konws, could you please tell me the sql syntax for importing a
dump file and creating a database from it.
Thanks in advance for your help,
Steve K.
Hi
Asszuming it is a SQL Server 7.0 or 2000 dump (backup), that you want to
restore to SQL Server 2000
RESTORE DATABASE dbname
FROM DISK = 'path and file goes here'
WITH RECOVERY
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Kitley" wrote:

> Hello,
> I need to import a dump file (.dmp) and create a database from that. I
> cannot find the syntax in the SQL Server books online.
> If anyone konws, could you please tell me the sql syntax for importing a
> dump file and creating a database from it.
> Thanks in advance for your help,
> Steve K.
>
>
|||Thanks Mike,
I used the 'with move' command with what you said and imported the data just
fine.
Best regards,
Steve K.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BA6E1CFC-CD29-4B27-8E09-A030D072E0B7@.microsoft.com...[vbcol=seagreen]
> Hi
> Asszuming it is a SQL Server 7.0 or 2000 dump (backup), that you want to
> restore to SQL Server 2000
> RESTORE DATABASE dbname
> FROM DISK = 'path and file goes here'
> WITH RECOVERY
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Steve Kitley" wrote:

Import Dump File

Hello,
I need to import a dump file (.dmp) and create a database from that. I
cannot find the syntax in the SQL Server books online.
If anyone konws, could you please tell me the sql syntax for importing a
dump file and creating a database from it.
Thanks in advance for your help,
Steve K.Hi
Asszuming it is a SQL Server 7.0 or 2000 dump (backup), that you want to
restore to SQL Server 2000
RESTORE DATABASE dbname
FROM DISK = 'path and file goes here'
WITH RECOVERY
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Kitley" wrote:

> Hello,
> I need to import a dump file (.dmp) and create a database from that. I
> cannot find the syntax in the SQL Server books online.
> If anyone konws, could you please tell me the sql syntax for importing a
> dump file and creating a database from it.
> Thanks in advance for your help,
> Steve K.
>
>|||Thanks Mike,
I used the 'with move' command with what you said and imported the data just
fine.
Best regards,
Steve K.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BA6E1CFC-CD29-4B27-8E09-A030D072E0B7@.microsoft.com...[vbcol=seagreen]
> Hi
> Asszuming it is a SQL Server 7.0 or 2000 dump (backup), that you want to
> restore to SQL Server 2000
> RESTORE DATABASE dbname
> FROM DISK = 'path and file goes here'
> WITH RECOVERY
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Steve Kitley" wrote:
>sql

Import Dump File

Hello,
I need to import a dump file (.dmp) and create a database from that. I
cannot find the syntax in the SQL Server books online.
If anyone konws, could you please tell me the sql syntax for importing a
dump file and creating a database from it.
Thanks in advance for your help,
Steve K.Hi
Asszuming it is a SQL Server 7.0 or 2000 dump (backup), that you want to
restore to SQL Server 2000
RESTORE DATABASE dbname
FROM DISK = 'path and file goes here'
WITH RECOVERY
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Kitley" wrote:
> Hello,
> I need to import a dump file (.dmp) and create a database from that. I
> cannot find the syntax in the SQL Server books online.
> If anyone konws, could you please tell me the sql syntax for importing a
> dump file and creating a database from it.
> Thanks in advance for your help,
> Steve K.
>
>|||Thanks Mike,
I used the 'with move' command with what you said and imported the data just
fine.
Best regards,
Steve K.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:BA6E1CFC-CD29-4B27-8E09-A030D072E0B7@.microsoft.com...
> Hi
> Asszuming it is a SQL Server 7.0 or 2000 dump (backup), that you want to
> restore to SQL Server 2000
> RESTORE DATABASE dbname
> FROM DISK = 'path and file goes here'
> WITH RECOVERY
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Steve Kitley" wrote:
>> Hello,
>> I need to import a dump file (.dmp) and create a database from that. I
>> cannot find the syntax in the SQL Server books online.
>> If anyone konws, could you please tell me the sql syntax for importing a
>> dump file and creating a database from it.
>> Thanks in advance for your help,
>> Steve K.
>>

import database

I need to import a database from MS SQL 2000. I created the database. Then I create the database scripts and run them in sql express but I get this error.

"The specified schema name "username" either does not exist or you do not have permission to use it."

I have also tried to use DTS on MS SQL 2000 to export the data into text file. I do not have the ability to copy the mdf files and attach them in SQL express or make a backup.SQL Server introduced the schema whcih did not exist in SQL Server 200. YOu would either have to rename the object owner in the script to a existing schema (like dbo) or create the appropiate schemas in SQL Server 2005 which were fromer the owners of an object.

HTH, Jens K. Suessmeyer.

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

Hi,

Its self Explainatery message, it means that the Schema or User (Owner) does not exists on the system you are trying to run the script, create a similar user /change the schema in script . Do you have proper privilege !!!? Refer some link below, it will clear your doubts

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=176311&SiteID=1

http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/

http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=30620

Hemantgiri S. Goswami

|||I made the schema change and it worked. Thanks.|||Thanks for the links it make a little more sense now.|||

Hi,

Thanks for the information, please click on Help full button if you feel answer is usefull.

Hemantgiri S. Goswami

Import Data to SQL server from Excel spreadsheet

Hi all,

Firstly, i'm new to integration services and have only done a little with DTS jobs.

I'm trying to create an integration services project which will import data from an two worksheets in an Excel spreadsheet to two different tables in a database. I'm looking at only one table at present to make things a little more understandable.

One stipulation i have is that i need to be able to specify a variable value and insert that as an additional column in the database. I have and Excel source and a SQL destination both of which have been set up with there specific connection managers. I also have a variable which i add in using the derived column task.

When i try to debug this i am getting a few problems. I think these may be to do with the fact that although the worksheet in Excel has 20 rows (1st column shows these numbers) i only want those rows with data in them. If i preview the excel table it shows all the rows including those with null columns. Is there some sort of way that i can only get the rows that have data in the columns after the row number. I.e. can i select rows that do not have a second column value = to NULL.

I hope this makes sense and that someone can help me out with this problem.

All help is greatly appreciated.

Cheers,

Grant

P.S.

Apologies. I have this resolved now. I didn't see the option to use a SQL command as apposed to a table or view when setting up the Excel source.

I am still however getting the following errors which i'd appreciate some help on:

Error: 0xC0202009 at Data Flow Task, Excel Source [1]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0208265 at Data Flow Task, Excel Source [1]: Failed to retrieve long data for column "Rework Entry Information (BE SPECIFIC)".
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "Rework Entry Information" (170) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Data Flow Task, Excel Source [1]: The "output column "Rework Entry Information" (170)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Rework Entry Information" (170)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help on this would be greatly appreciated.

GrantI'd also like to know how to go about specifying a variable as the datasource for my Excel connection. This is so that at runtime i can specify a number of different files to process.

Thank you,

Grant|||

You can use a ForEach loop container in your control flow to go through all files in a specific file system folder; then inside of that ForEach container add a dataflow task that does what you want. You may need to use an expression to change the connection string of your Excel Connection manager for every iteration (using the variable that has the collection value). I have never tried that before; this is just an idea.

good luck!

Rafael Salas

|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio. If you're using SQL 2000, try using the appropriate version of Enterprise Manager.|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio for SQL Server 2005. If you're using SQL Server 2000, try using the appropriate version of Enterprise Manager.

import data into one text file

I have sp that I have written that gets info from a table. but in the text
file
I need header and results set. For ex. this sp looks like this
create proc info
as
declare @.count int
set @.count = (select count(*) from info)
select getdate()
select * from info
select 'total count '+convert(varchar(20),@.count)
So, how is it possible to get this results set into one text file?
Thanks in advance.
sonalHi,
Execute the procedure using the command line utility OSQL.
OSQL -Usa -Ppassword -Q"dbname..sp_name" -Oc:\result.txt -n
Thanks
Hari
MCDBA
"sonal" wrote:
> I have sp that I have written that gets info from a table. but in the text
> file
> I need header and results set. For ex. this sp looks like this
> create proc info
> as
> declare @.count int
> set @.count = (select count(*) from info)
> select getdate()
> select * from info
> select 'total count '+convert(varchar(20),@.count)
> So, how is it possible to get this results set into one text file?
> Thanks in advance.
> sonal
>|||Several ways.
I prefer to use bcp for exporting to text files.
You can format the data into a single resultset.
select s = convert(varchar(1000),getdate())
union all
select convert(varchar(20), col1)
+ ',' + convert(varchar(20), col2)
+ ....
from info
union all
select 'total count '+convert(varchar(20),@.count)
In that way you can test the structure without going to the text file (and
also save it to a table if need be).

Import + ActiveX (cross)

What I will do is create a temporary table in the same
database with the data from the file.
Next, I will write a script to (import) insert from the
temp table to where you want the data to go.
Mary

>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>Ah, allright - does somebody have code examples I can use?
Best regards
Jakob
"Mary Lou Friend" <anonymous@.discussions.microsoft.com> skrev i en
meddelelse news:4d9301c402bf$698bdf80$a601280a@.phx.gbl...
> What I will do is create a temporary table in the same
> database with the data from the file.
> Next, I will write a script to (import) insert from the
> temp table to where you want the data to go.
> Mary
>
> commaseparated files
> import of ALL info in
> command...
> the file has an ID
> UPDATE all NAME-cols in
> the file might have
> are in the file, they
> but it would be
> perhaps...? or is the
> within an ActiveX task?

Imporint CSV file using store procedure

I would like to create DTS for Importing from CSV using store procedure, in
which I would like to update fields conditionally.
Anyone can help in this matter or let me know url/tutorial on this.
Thanks in advance
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.699 / Virus Database: 456 - Release Date: 06/04/2004Ashish,
there are various ways of doing this. My preference is to import the data to a staging
area in SQL Server then use Execute SQL tasks (TSQL) to clean/transform it, before impo
rting it to the production system. Alternatively you can do row by row iteration and fo
r each row decide what to do using VBScript. It sounds like this is the route you're in
terested in, and in that case the Transform data task with Lookup queries would be usef
ul. For more info, have a look at http://www.sqldts.com/default.aspx?277.
HTH,
Paul Ibison|||I made the logic in , but how do this logic in DTS. Here is my logic
*---
Dim reccount As Double
'On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Set objConnection = CreateObject("ADODB.Connection")
Set objConn = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
strPathtoTextFile = "C:\"
objConn.Open ("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;In
itial Catalog=stocks;Data Source=webserver1")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""
objRecordset.Open "SELECT * FROM accounts.csv", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
strCSV = "update accounts set closed = 0 where accountid=" & objRecordset.Fi
elds.Item("AccountID")
objConn.Execute strCSV
objRecordset.MoveNext
Loop
objRecordset.Close
objRecordset.Open "select count(*) from accounts where closed=0", objConn
MsgBox objRecordset(0)
*--
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:uvSKyXuSEHA.398
8@.tk2msftngp13.phx.gbl...
I would like to create DTS for Importing from CSV using store procedure, in
which I would like to update fields conditionally.
Anyone can help in this matter or let me know url/tutorial on this.
Thanks in advance
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.699 / Virus Database: 456 - Release Date: 06/04/2004
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004|||Ashish,
have a look at the Transform Data Task with lookups integrated (lookups can
also do updates, despite their name)
http://www.sqldts.com/default.aspx?277,1
HTH,
Paul Ibison

Imporint CSV file using store procedure

I would like to create DTS for Importing from CSV using store procedure, in which I would like to update fields conditionally.
Anyone can help in this matter or let me know url/tutorial on this.
Thanks in advance
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.699 / Virus Database: 456 - Release Date: 06/04/2004
Ashish,
there are various ways of doing this. My preference is to import the data to a staging area in SQL Server then use Execute SQL tasks (TSQL) to clean/transform it, before importing it to the production system. Alternatively you can do row by row iteration and for each row decide what to do using VBScript. It sounds like this is the route you're interested in, and in that case the Transform data task with Lookup queries would be useful. For more info, have a look at http://www.sqldts.com/default.aspx?277.
HTH,
Paul Ibison
|||I made the logic in , but how do this logic in DTS. Here is my logic
*---
Dim reccount As Double
'On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Set objConnection = CreateObject("ADODB.Connection")
Set objConn = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
strPathtoTextFile = "C:\"
objConn.Open ("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=stocks;Data Source=webserver1")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""
objRecordset.Open "SELECT * FROM accounts.csv", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
strCSV = "update accounts set closed = 0 where accountid=" & objRecordset.Fields.Item("AccountID")
objConn.Execute strCSV
objRecordset.MoveNext
Loop
objRecordset.Close
objRecordset.Open "select count(*) from accounts where closed=0", objConn
MsgBox objRecordset(0)
*--
"Ashish Kanoongo" <ashishk@.armour.com> wrote in message news:uvSKyXuSEHA.3988@.tk2msftngp13.phx.gbl...
I would like to create DTS for Importing from CSV using store procedure, in which I would like to update fields conditionally.
Anyone can help in this matter or let me know url/tutorial on this.
Thanks in advance
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.699 / Virus Database: 456 - Release Date: 06/04/2004
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004
|||Ashish,
have a look at the Transform Data Task with lookups integrated (lookups can
also do updates, despite their name)
http://www.sqldts.com/default.aspx?277,1
HTH,
Paul Ibison