Import external data

Hi friends,
I want to import data from remote database(oracle) to my sql
server
local database.
As one of the group mate suggested I used Import(Tasks -> Import)
Its ok. But I want this to be done for a frequent of times and put in
a schedule. How can I do this.
And every time I import, the duplicates will not be imported I mean
the old data will not be imported again. ONly the current data to be
imported.
How can I make schedule and not alowing to import old records again?
Thanks in advanceMake a linked server and a stored procedure that pulls the data using a NOT
EXISTS clause. Then create an agent job on sql server to run it on whatever
schedule you desire. The same can probably be accomplished using SSIS as
well.
<mrajanikrishna@.gmail.com> wrote in message
news:1190732099.352879.45570@.y42g2000hsy.googlegroups.com...
> Hi friends,
> I want to import data from remote database(oracle) to my sql
> server
> local database.
>
> As one of the group mate suggested I used Import(Tasks -> Import)
> Its ok. But I want this to be done for a frequent of times and put in
> a schedule. How can I do this.
>
> And every time I import, the duplicates will not be imported I mean
> the old data will not be imported again. ONly the current data to be
> imported.
>
> How can I make schedule and not alowing to import old records again?
>
> Thanks in advance
>

Import data option not visible

Hello All,

I have created a database on my local machine using SQL Server Express 2005. I am trying to export data from an excel file into a table in this database but when i see tasks available for the database there is no option of import data like I assume there should.

Only the following options are visible.

Detach

Shrink

Back Up

Restore

Generate Scripts

Any solutions on how I can get my excel file into the table.

Kiran

Import / Export is not included in the Express edition.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Ohh.

Then would my only option be to download a different version like SQL Server compact.

Thanks,

Kiran

|||

Kiranvukkadala wrote:

Then would my only option be to download a different version like SQL Server compact.

No, only SQL Server Standard, Developer, or Enterprise editions. Read the link John provided.

|||

Would any of these editions be free to download from Microsoft. I need only a test database on my machine which uploads tables through excel files. If there is a workaround with express edition it would be great as I already downloaded that version.

Thanks,

Kiran

|||Nope. None are free. Sorry.

The developer edition is only $50 (US) though.

Import data from MS SQL Server - error

I have a remote database which I need to make a local copy of. I have sucessfully scripted the db and created it locally. however I cannot import the data to the local server. I get this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table

i think i am doing everything ok but it still wont work.dbo.xxx beacause its being referenced by a foreign key constraint.

Any ideaas welcome.

Gary

Apparently, the process is attempting to TRUNCATE the table -and since there is an established PK-FK relationship on the table, TRUNCATE will not work.

You can either
(a) DROP the FK CONSTRAINT, inport the data, recreate the FK CONSTRAINT, or
(b) USING "DELETE FROM MyTable", remove all of the rows of data, or
(c) Load the Parent table first before loading the child table.

|||

my question is why don't u use backup/restore method to get the database created localy. the only reason for not doing so is .. you don't want all the object created in the local copy... if that is the case then ok... otherwise go for backup/restore method

Madhu

Import data from about 50 different servers to a local database ta

Hello all,
First of all thank you very much for all your help.
Can you please tell me what is the best way to import data (select
@.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
servers to a local database table. I would be great if job itself would enter
location name into
the table so it will look like data came from different location and not
from one.
I would think first I will need to create Remote servers under local SQL
server
database so my job will recognize different locations. And then ...
Thanks,
-DHi
If you have all the servers as linked servers you would need to use dynamic
SQL to change the name of the linked server in your code (without repeating
the code 50 times!). If your code inserted the output into a staging table,
then you could use the variable holding the server name whilst inserting the
data into the destination table.
e.g.
-- Destination table
CREATE TABLE ALLServerDrives ( servername sysname, drive char(1), size
bigint )
DECLARE @.linkedServer sysname
SET @.linkedServer = 'RemoteServer'
CREATE TABLE #drives ( drive char(1), size bigint )
EXEC ('INSERT INTO #drives ( drive, size )
EXEC ' + @.linkedServer + '.master.dbo.xp_fixeddrives' )
INSERT INTO ALLServerDrives ( servername, drive, size )
select @.linkedServer, Drive, size FROM #drives
drop table #drives
SELECT * FROM ALLServerDrives
John
"D''Animal" wrote:
> Hello all,
> First of all thank you very much for all your help.
> Can you please tell me what is the best way to import data (select
> @.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
> servers to a local database table. I would be great if job itself would enter
> location name into
> the table so it will look like data came from different location and not
> from one.
> I would think first I will need to create Remote servers under local SQL
> server
> database so my job will recognize different locations. And then ...
> Thanks,
> -D|||John,
How do I generate unique value (such as date and/or automatically generated
numbers 1,2,3,4...) as new column so it will not overwrite old entries
when it executes?
Thanks
-D
"John Bell" wrote:
> Hi
> If you have all the servers as linked servers you would need to use dynamic
> SQL to change the name of the linked server in your code (without repeating
> the code 50 times!). If your code inserted the output into a staging table,
> then you could use the variable holding the server name whilst inserting the
> data into the destination table.
> e.g.
> -- Destination table
> CREATE TABLE ALLServerDrives ( servername sysname, drive char(1), size
> bigint )
> DECLARE @.linkedServer sysname
> SET @.linkedServer = 'RemoteServer'
> CREATE TABLE #drives ( drive char(1), size bigint )
> EXEC ('INSERT INTO #drives ( drive, size )
> EXEC ' + @.linkedServer + '.master.dbo.xp_fixeddrives' )
> INSERT INTO ALLServerDrives ( servername, drive, size )
> select @.linkedServer, Drive, size FROM #drives
> drop table #drives
> SELECT * FROM ALLServerDrives
>
> John
> "D''Animal" wrote:
> > Hello all,
> >
> > First of all thank you very much for all your help.
> > Can you please tell me what is the best way to import data (select
> > @.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
> > servers to a local database table. I would be great if job itself would enter
> > location name into
> > the table so it will look like data came from different location and not
> > from one.
> > I would think first I will need to create Remote servers under local SQL
> > server
> > database so my job will recognize different locations. And then ...
> >
> > Thanks,
> >
> > -D|||Hi
As the example as an insert then it will only add to the table providing
there was no unique key violations. You may want to either add a column with
a rowversion data type, or a datetime column with a default of getdate() or
and identity column.
CREATE TABLE ALLServerDrives ( id int not null identity(1,1),
servername sysname, drive char(1), size bigint )
The identity column will increment for each row inserted and you do not need
to reference it in the insert statement. You may need to order the
information when you insert it into the desination table to make sure the
order is more meaningful! More information on all of these datatypes is in
books online.
John
"D''Animal" wrote:
> John,
> How do I generate unique value (such as date and/or automatically generated
> numbers 1,2,3,4...) as new column so it will not overwrite old entries
> when it executes?
> Thanks
> -D
> "John Bell" wrote:
> > Hi
> >
> > If you have all the servers as linked servers you would need to use dynamic
> > SQL to change the name of the linked server in your code (without repeating
> > the code 50 times!). If your code inserted the output into a staging table,
> > then you could use the variable holding the server name whilst inserting the
> > data into the destination table.
> >
> > e.g.
> >
> > -- Destination table
> > CREATE TABLE ALLServerDrives ( servername sysname, drive char(1), size
> > bigint )
> >
> > DECLARE @.linkedServer sysname
> >
> > SET @.linkedServer = 'RemoteServer'
> > CREATE TABLE #drives ( drive char(1), size bigint )
> >
> > EXEC ('INSERT INTO #drives ( drive, size )
> > EXEC ' + @.linkedServer + '.master.dbo.xp_fixeddrives' )
> >
> > INSERT INTO ALLServerDrives ( servername, drive, size )
> > select @.linkedServer, Drive, size FROM #drives
> >
> > drop table #drives
> >
> > SELECT * FROM ALLServerDrives
> >
> >
> > John
> >
> > "D''Animal" wrote:
> >
> > > Hello all,
> > >
> > > First of all thank you very much for all your help.
> > > Can you please tell me what is the best way to import data (select
> > > @.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
> > > servers to a local database table. I would be great if job itself would enter
> > > location name into
> > > the table so it will look like data came from different location and not
> > > from one.
> > > I would think first I will need to create Remote servers under local SQL
> > > server
> > > database so my job will recognize different locations. And then ...
> > >
> > > Thanks,
> > >
> > > -D

Import data from about 50 different servers to a local database ta

Hello all,
First of all thank you very much for all your help.
Can you please tell me what is the best way to import data (select
@.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
servers to a local database table. I would be great if job itself would ente
r
location name into
the table so it will look like data came from different location and not
from one.
I would think first I will need to create Remote servers under local SQL
server
database so my job will recognize different locations. And then ...
Thanks,
-DHi
If you have all the servers as linked servers you would need to use dynamic
SQL to change the name of the linked server in your code (without repeating
the code 50 times!). If your code inserted the output into a staging table,
then you could use the variable holding the server name whilst inserting the
data into the destination table.
e.g.
-- Destination table
CREATE TABLE ALLServerDrives ( servername sysname, drive char(1), size
bigint )
DECLARE @.linkedServer sysname
SET @.linkedServer = 'RemoteServer'
CREATE TABLE #drives ( drive char(1), size bigint )
EXEC ('INSERT INTO #drives ( drive, size )
EXEC ' + @.linkedServer + '.master.dbo.xp_fixeddrives' )
INSERT INTO ALLServerDrives ( servername, drive, size )
select @.linkedServer, Drive, size FROM #drives
drop table #drives
SELECT * FROM ALLServerDrives
John
"D''Animal" wrote:

> Hello all,
> First of all thank you very much for all your help.
> Can you please tell me what is the best way to import data (select
> @.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 differe
nt
> servers to a local database table. I would be great if job itself would en
ter
> location name into
> the table so it will look like data came from different location and not
> from one.
> I would think first I will need to create Remote servers under local SQL
> server
> database so my job will recognize different locations. And then ...
> Thanks,
> -D

Import data from about 50 different servers to a local database ta

Hello all,
First of all thank you very much for all your help.
Can you please tell me what is the best way to import data (select
@.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
servers to a local database table. I would be great if job itself would enter
location name into
the table so it will look like data came from different location and not
from one.
I would think first I will need to create Remote servers under local SQL
server
database so my job will recognize different locations. And then ...
Thanks,
-D
Hi
If you have all the servers as linked servers you would need to use dynamic
SQL to change the name of the linked server in your code (without repeating
the code 50 times!). If your code inserted the output into a staging table,
then you could use the variable holding the server name whilst inserting the
data into the destination table.
e.g.
-- Destination table
CREATE TABLE ALLServerDrives ( servername sysname, drive char(1), size
bigint )
DECLARE @.linkedServer sysname
SET @.linkedServer = 'RemoteServer'
CREATE TABLE #drives ( drive char(1), size bigint )
EXEC ('INSERT INTO #drives ( drive, size )
EXEC ' + @.linkedServer + '.master.dbo.xp_fixeddrives' )
INSERT INTO ALLServerDrives ( servername, drive, size )
select @.linkedServer, Drive, size FROM #drives
drop table #drives
SELECT * FROM ALLServerDrives
John
"D''Animal" wrote:

> Hello all,
> First of all thank you very much for all your help.
> Can you please tell me what is the best way to import data (select
> @.@.version, exec xp_fixeddrives, sp_helpdb and other) from about 50 different
> servers to a local database table. I would be great if job itself would enter
> location name into
> the table so it will look like data came from different location and not
> from one.
> I would think first I will need to create Remote servers under local SQL
> server
> database so my job will recognize different locations. And then ...
> Thanks,
> -D

Import and Export Wizard throws errors when theres alot of tables

I’m trying to copy data from production to my local machine using the SQL Server 2005 import and export wizard. It works fine if I select a small number of tables but throws errors

When there’s a lot of tables. Have you ever experienced problems using it? Is there a better way to transfer the data?

the data source is SQL Server 2000 and the target is 2005. I have the optimize for many tables and transaction options selected

Here’s the errors I get

Execute the transfer with the TransferProvider. (Error)

Messages

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (52)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

· ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (49)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Umm, do you have existing tables that have a column named, ErrorCode?|||very possible..if so what am i supposed to do to fix the problem ?|||Rename the columns in the tables... Really, not many options to do here.

I have created a Connect bug in relation to this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=252471

You should go there and vote, with comments, to help give more weight to the bug. Also "validate" it.

Thanks,
Phil|||

Thx, Didn;t see the siginficance of the fact that the column was called ErrorCode Right awy. I've been trying to use Import and Export to load data from production 2000 to local 2005 for several days now and I'm pretty burnt out. The vote does not seem enabled at the moment I'll look at it again tomorrow

|||

Mike C wrote:

Thx, Didn;t see the siginficance of the fact that the column was called ErrorCode Right awy. I've been trying to use Import and Export to load data from production 2000 to local 2005 for several days now and I'm pretty burnt out. The vote does not seem enabled at the moment I'll look at it again tomorrow

You have to sign into Connect before you can vote.