Auto Data Extraction through ODBC

Hi,

Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).

Thanks

Amir

hi Amir,

please excuse my poor english, but can you please rephrase your question?

thank you and regards

|||

For SQL Express, you would most likely need to do the following:

1. Create a script file using bcp to unload the data.

2. Use SQLCmd.exe to run the script file

3. Use Windows Scheduler to execute SQLCmd.exe on a time schedule.

Look up using [ bcp ] in books Online

Look up using SQLCmd.exe in Books Online.

Look up using Windows Scheduler in Windows Help.

|||

Hi,

Thanks for your reply, I have gone through BCP link http://msdn2.microsoft.com/en-us/library/ms162802.aspx, I am wondering if you can provide a link of Sample "Sample Script of BCP to import" it would be great help for me.

Thanks

Amir

|||

Perhaps this will help you.

http://msdn2.microsoft.com/en-us/library/aa173839(SQL.80).aspx

|||

Thanks for info.

It might be a silly question for you but I am using first time and I have no idea could you please define below also

a) Should I creat Batch file OR Text File for BCP

b) How should I run and test

|||

I normally consider a 'batch file' as an input source, and a 'text file' as an output source.

Look up bcp in Books Online, Topic: bcp Utility.

There are a couple of examples that will execute in your query window.

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.
We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>
|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.
We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>
|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.
We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>
|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>

attempting to create a linked server to MySQL

We attempted to create a link server between an MSDE database and mysql 4.1
database using ODBC. However, whenever we attempt to look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interf
ace
error: Cocreate of DSO for MSDASQL returned 0x800700005].
When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).
Any direction or source of specific aids would be appreciated.We have had some success, but different problem which may be essentially the
same.
On a different machine which has MSDE , loaded a mysql 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string mysql 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}
After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the mysql database was visible.
I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the mysql databas3e when I executed the
statemnet:
Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')
End of success.
i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. ...AAAH ...the next problem
Thanks to those who sent on comments. Comments and
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
[Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>|||Not an expert on this but I believe that linking servers requires OLEDB.
Can't be done with an ODBC driver. You should be able to fine an OLEDB
driver out there to make this work.
Andrew Robinson
www.binaryocean.com
www.bellinghamdotnet.org
"Sean Moore" <smoore@.123progeny.net> wrote in message
news:OOMAQUq7FHA.2152@.TK2MSFTNGP10.phx.gbl...
> We attempted to create a link server between an MSDE database and MySQL
> 4.1
> database using ODBC. However, whenever we attempt to look at the tables
> associated with the link we are greeted with the message: Could not
> create
> an instance of OLE DB provider 'MSDASQL'. OLD DB error trace
> [Non-interface
> error: Cocreate of DSO for MSDASQL returned 0x800700005].
> When I searched the web for this message, I learned that it is not unusual
> to encounter it, but have not been able to find a way to get past it. The
> version of MDAC appears to be consistent with the Microsoft table relating
> version and Windows version (2000).
> Any direction or source of specific aids would be appreciated.
>