Moving/Copying Data
Oct 4, 2007
Hello everyone!
I have two servers, one is 2005 standard, the other is 2005 express.
I'm using express to record test results and it will be keep recording non-stop.
So, what I want to do is I want to transfer data from express to standard using SSIS package, and as soon as data gets transferred the data to no longer exists in express.
I have a job running the "copy" package every minute, but I don't know how to delete the data in express.
Does anyone know how to help me out pleaseeeee..?
Thanks in advance.
MIKE!
View 6 Replies
ADVERTISEMENT
Oct 22, 2007
Hi
I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution.
Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.
Of course, any suggestions for speeding this up would be helpful!
What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -
-- it is actually pretty easy to follow if you just read the code......
-- first, we find all the views which are present, so they can be ignored when copying the raw data
CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )
INSERT #VIEWS
SELECT TABLE_NAME FROM
OPENDATASOURCE(
'SQLOLEDB',
Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS
-- now, we get the actual tables with data
CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )
INSERT #TEMP
SELECT TABLE_SCHEMA , TABLE_NAME FROM
OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)
-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row.
-- the first cursor loop gets all table names, the second will find row counts of source tables.
-- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not
-- processing anything, and is used only to find tables which have a rowcount > 0
-- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding
-- tables with row counts > 0
SET @QUOT = CHAR(39)
SET @LBRAKT = '['
SET @RBRAKT = ']'
SET @IUT = 'IsUserTable'
SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.dbo.'
PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE GETEM CURSOR FOR
SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP
OPEN GETEM
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE #TEMP SET RECCNT = ' +
'(SELECT COUNT(*) FROM OPENDATASOURCE(' +
@QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' +
' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT
EXEC (@SQL)
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
END
CLOSE GETEM
DEALLOCATE GETEM
PRINT 'FINIS TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE @PLIN VARCHAR (80),@LFT_PLIN VARCHAR (20),
@RT_PLIN VARCHAR (20), @TLIN INT
PRINT 'BEGIN TABLE CHECK AND LOAD CURSOR.'
DECLARE FETCHIT CURSOR FOR
SELECT TBL_NAME, RECCNT FROM #TEMP WHERE RECCNT > 0
OPEN FETCHIT
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'CHECKING ' + @LBRAKT+ @TBL_NAME + @RBRAKT
IF NOT EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
BEGIN
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'NOT FOUND. BUILD ' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
-- #TEMP (RECCNT) CONTAINS ROW COUNT OF SOURCE SYSTEM.
-- COMPARE TO ROW COUNT OF LOCAL TABLE
BEGIN
SELECT @DEST_RECCNT = RECCNT FROM #TEMP
WHERE TBL_NAME = @TBL_NAME
IF @DEST_RECCNT = @RECCNT
BEGIN
PRINT 'TABLE row COUNT MATCHES. BYPASSING REBUILD.'
END
IF @DEST_RECCNT <> @RECCNT
BEGIN
SET @SQL = 'DROP TABLE ' + @TBL_NAME
EXEC (@SQL)
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'REBUILD.' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
END
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
END
CLOSE FETCHIT
DEALLOCATE FETCHIT
PRINT 'FINIS TABLE CHECK AND LOAD CURSOR.'
XIT:
RAISERROR (50002,010,1) WITH LOG -- FLAG COMPLETION TIME IN SQL LOG
SET QUOTED_IDENTIFIER OFF
View 1 Replies
View Related
Jul 23, 1999
Does anyone know how to move or copy DTS Package?
That I need to do:
I would have to move database from one server to the other
and last thing I want to do is recreate DTS packages from
scrach.
I could not find any way of transfering DTS packages.
Any help greatly appreciated
View 3 Replies
View Related
Sep 27, 2006
Ok. So we just moved over one DB from our original server to another server. The DB copied over fine.
Our next step is trying to see if there is a way to move accounts. Is that possible?
We have about 40 accounts (using SQL authentication) on the original server. Is there a way to move or copy those accounts to the new SQL server that is holding that same DB?
Haven't found anything in the Enterprise manager, so im thinking it will be something to do in the SQL analyzer.
My lack of knowledge in SQL is not helping me much here.
I appreciate the help.
View 17 Replies
View Related
May 30, 2006
hi,
When you copy a sequence container between dtsx you obtain a different size at destination. Any way to avoid this? I'd like to see that task with the same size.
Maybe some posh from my side...
View 1 Replies
View Related
Nov 14, 2007
I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,
Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.
View 4 Replies
View Related
Jan 2, 2008
i've created a package that will copy data from an oracle table to a sqlserv table (that table elemenst are identical), when i click on the connecting line between the two connections it executes without any errors, but nothing is copied, when i try to execute the package i'm getting an error... where can i go to find out what's causing the error. There is no error message or number returned from dts, all i get is a red 'x' . There are 1236 records that need to be inserted and when i get the red 'x' it tells me 1236 records have been processed. When i click on tranformations and select test, it works, but since it's a test nothing is actually copied. I've got other packages that i've created that do the same thing with other tables and they work. I tried just copying one record and that worked, so i assumed it must be data dependent, i've checked all the fields and made sure they weren't null, i've checked to make sure there aren't duplicate primary key records. Without knowing what the actual error is I'm stumped ???
View 4 Replies
View Related
Mar 30, 2007
Is it possible to easily copy data from one table to another if the data types don't match. I know you can do a INSERT INTO table1(col1,col2) SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't. I'm not trying to copy date times into bit fields or anything. I just have an old table that I built when I really didn't know what I was doing now I at leastthink I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one. Most of the fields in the olddatabase are text datatypes and the new database is nvarchar(50) data types. Thanks for any suggestions.
View 4 Replies
View Related
Apr 28, 2006
I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also. Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.What is the easiest way to do so?
View 6 Replies
View Related
Oct 10, 2006
Rene writes "Good evening. This is my first time using sqlteam for answers and I'm hoping I can get some much needed direction.
Basically what I am trying to accomplish is taking records from a temporary source table to a permanent table. Here is what the tables look like (oversimplistic version)
tblTemp
ID
Value1
Imported
FailedReason
tblPerm
ID
Value1
Basically I would like to take the values from tblTemp and INSERT them into tblPerm. The catch is that values in tbltemp might violate primary key constraint because of duplicate values in the ID field. The value1 field is required in tblPerm and it might contain a null value on tbltemp causing the insert statement to fail.
What I would like the end result to be is that any records which are INSERTED from tbltemp to tblperm are flagged with a value of imported=1 on the tbltemp table. Any records which fail should then be flagged as imported=0 and failedreason=reason for failing.
I am trying the following to start with but am not sure if I am steering in the right direction or not. The process should be as automated as possible, perhaps part of a scheduled dts package or likewise since new data will be inserted in the tbltemp table on a weekly basis.
set rowcount 1
update tblsource set imported = 0 where imported is null
insert into tbldest (col1, col2)
select top 1 col1, col2 from tblsource where imported = 0 and notimportreason is null
IF @@Error <> 0
GOTO ErrorHandler
UPDATE tblsource SET imported = 1 where imported = 0 and notimportreason is null
ErrorHandler:
update tblsource SET notimportreason = @@error where imported = 0 and notimportreason is null
RETURN"
View 2 Replies
View Related
Mar 10, 2008
HiWe use Sql Server 2000.Is there a way to copy and entire text file into a database tablewithout using a DTS ?
View 2 Replies
View Related
Nov 1, 2007
HI,
I just copied sm data from a table in a DB to a flat file which I have created previaouly in my documents in my local machine.
I used the following command.
C:Documents and Settingsshamen> dbname.tablename out flatfile.txt -T
Once I run this it prompted two questions asking pre-fix lenght and field terminator. I jsut accepted the default values.
After I run it, it copied data. But when I see the flat file , I did not see data in that file.
To where data has been copied?
it says 10000 rows succesfully bulk-copied to host file. Total received 6176000.
But I dont see data there now.
View 5 Replies
View Related
Jul 25, 2007
Is there any simple way to copy tables from one database to another in SQL Management Studio or VS 2005? I sometimes work split work between home and work and I often need to copy and table and its data (data, stored procedure, etc) to a different database, but having to create a new database then copy the data is a pain. Is there an easier way?
View 5 Replies
View Related
Sep 2, 2002
If I have to copy data from one database to a temporary database on the same server, which transaction log will be written to as a result? Will it be the DB that the command is run from, the source DB, or possibly tempdb's log?
Thanks
Derek
View 1 Replies
View Related
Oct 31, 2004
hi all,
i have a dts that copies table contents from remore server to local server.
remote table:
ItemID|name
1 |name1
2 |name22
3 |name33
local table:
ItemID|name
1 |name1
2 |name2
i want that the dts will only copy the new rows( aka. row with ItemID = 3) and leave the other rows a they are.
can any one help me create such a dts.
please help!
View 1 Replies
View Related
May 1, 2007
Hi All
Iam using sql 2005 and am new to it, I want to copy a table and data from server to another, how is this possible?
Many Thanks in advance....
View 2 Replies
View Related
Oct 16, 2007
Hi All,
I wish to copy between 2 tables in different databases.
( The source and destiantion tables already exist)
The source table has duplicate entries which i want to avoid from being copied to destination table.
How can i achieve this?
Thanks in advance..!!
Vishu
View 20 Replies
View Related
Jul 20, 2005
I have to setup some scheduled tasks to copy 3-4 tables from anSQL2000 database to DB2 v. 8.1.The job must run every night replacing all data.How can this be done - with standard utilities??Please be exact - I'm new to this.Thank for any help./Jep
View 1 Replies
View Related
Jan 25, 2007
Yet another very much newbie question I suspect€¦.
I want to know how I copy data from one field in one
DataBase to another field in another DataBase??
Basically I have an ASP driven forum, and want to upgrade to
a new ASP.NET forum€¦ Problem is obviously the DataBase structure is different
and I don€™t want to loose all the data from my current forum€¦ So I would have to pick which table field
needs to be copied to where in the new DataBase..
Is this easy to do??
I will be using SQLExpress 2005??
Thanks
View 1 Replies
View Related
Apr 3, 2006
I have the following table:
Table name: RR
columns:
Subject (varchar (35), Null)
Topic (varchar (35), Null)
RD (text, null)
RR (text, null)
Picture (varchar (50), Null)
Video (varchar (50), Null)
RRID (int, Not Null)
TSTAMP (datetime, Null)
RRCount (int, Not Null)
This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.
Example:
There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008
I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.
View 1 Replies
View Related
Feb 2, 2007
is there a way to copy a SqlDataSource Data into a dataset?
View 4 Replies
View Related
Mar 2, 2005
What iam trying to do is put the table name from one db into a variable and another one into another variable and pass them into my statement basicaly trying to bulk copy data from one table in a db and insert it into another db on the same server based on a condition
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20)
set @BDFR = 'Commander' + '.dbo.' + 'ClientComment'
set @BDTO = 'Test_Commander' + '.dbo.' + 'ClientComment'
set @EQID = '80_300_005'
insert into @BDTO
select * from @BDFR where Eqid = @EQID
View 2 Replies
View Related
Apr 15, 2015
We've had a new server set up with SQL 2012 and I'm in the process of moving data to it from a 2008 (SP2) server.
Details are as follows:-
2012 instance:- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
2008 instance:- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)
I don't want to do a backup/restore routine as there are collation conflicts on the 2008 server.I've created the database and tables on the 2012 instance and now I want to transfer the data from the 2008 instance to the 2012 one.
The 2012 instance has a linked server to the 2008 instance.I was trying to use sp_MSForEachTable (I know, it's old and will probably disappear shortly) but that doesn't seem to work properly because some of the columns have an Identity field set up.
Some of the tables have upwards of 10 million records in them and are quite sizeable.how I can achieve the transfer without a back-up/restore?
View 9 Replies
View Related
Oct 25, 2005
hi all
i have two databases on two different machines.
both databses r having same names.
i want to copy data from the table in other database to table in databse on my machine .
how can i do this.
i will be very thankful to receive help.
View 2 Replies
View Related
Apr 19, 2007
Can i do something like this
INSERT INTO CITIBANK.dbo.Contract (*)
FROM exec sp..
where sp is the stored procedure ?
Or do i need to specify all the colums where the * is ?
and if so is it the columns from the SP or the colums
from the destination table ?
View 4 Replies
View Related
Jul 23, 2005
Hi all!I have an application that needs to copy the database structure fromone database to another without using the "Generate SQL Script"function in Enterprise Manager. I'd like to do this from within astored procedure. Can someone recommend the best approach for this?I've seen references to using SQL-DMO from a stored procedure using thesp_OA* procs in other postings to this group but was wondering if therewas an easier way? Can I use bcp and then use xp_cmdshell from withinmy stored procedure? It's not clear to me from the documentationwhether bcp copies both structure and data or just data? Is there abetter way?Thanks in advance for any help!Karen
View 1 Replies
View Related
Sep 7, 2006
Hi. I need to move data from one database table to
another across database instances. A simple example of the typical
move would be:
[CODE]
INSERT into destination_db.dbo.table1
SELECT column1, column2, column3, column4 from source_db.dbo.table2
[/CODE]
My options are:
1. Create an SSIS package to perform the move.
2. Create sprocs and schedule the data move as jobs.
3. Write .NET code using sprocs to perform the move.
I'll have to move hundreds of thousands of records, so I want the
option that provides the best performance. I'm guessing that option 3
will be the slowest.
Thanks for the help!
View 4 Replies
View Related
Jul 24, 2006
I'm trying to copy a data base from one server to another. Both servers are running 2005 MSDE, on Win 2k3, the target server is 64 bit.
I'm using the Copy Database function, and ultimately it fails with the error in the event viewer in the targe machine:
Faulting application dtexec.exe, version 2005.90.1399.0, stamp 43500d1a, faulting module kernel32.dll, version 5.2.3790.1830, stamp 42438b79, debug? 0, fault address 0x0000000000027d0d.
I have tried both the Attach/Detach & the SMO method, and they both give me the same error. I have 'Repaired' the .NET framework on the target machine, with the same result.
Does anyone have any Suggestions? I really only need at this time to make one copy of the database.
Thanks
Phil
View 5 Replies
View Related
Aug 27, 2007
Hello,
I have a Data Flow Source that uses a SQL Command to pull data. In the SQL statement, I used CAST to change all varchar types to Nvarvchar to suit MS Access. I can preview the data from the source. In testing, the SQL statement only pulls about ten records.
I have a Microsoft 2000 Access database table as a destination. Data in each column in the table is required, and all columns have defaults.
I also have a grid data viewer set up. I have the DefaultBufferMaxRows set to 2 so that I can see data going across. When I execute this dataflow, no data is transfered to the Access database table. No data shows up in the dataviewer. There are no errors. The 'Execution Results' tab does not show errors, but indicates that zero rows were transfered. There are no warnings.
How do I begin to isolate the problem? The following is the SQL Statement in the Data Flow Source. Thank you for your help! - cdun2
DECLARE @CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)
--and fill it
INSERT INTO @CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+1,255))
FROM Collections_Staging
--Assign an ID to each block of data for each occurance of 'Reason:'
DECLARE @ID int
SET @ID = 1
UPDATE @CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END,
@ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END
--Then put the data together
SELECT --cast to Nvarchar for MSAccess
a.ColID,
CAST(a.ColValue as Nvarchar(30)) AS OrderID,
COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f.ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
END AS ReturnDate,
--COALESCE(i.ColValue,'') AS ReturnDate,
COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @CategoryTable a
LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'
WHERE a.ColCategory = 'Order ID:'
View 7 Replies
View Related
Aug 23, 2006
I am trying to bulk copy some XML data into a SQL and am generally doing quite well. The XML data I have been given has a child node called "name" which is the same as in the parent node as shown in the highlight of the XML source below. Now I can retrive the data in the parent node using
bulk.ColumnMappings.Add("name", "Name")
but I cannot get any of the data from the child node "Catagory" or " Catagories". Have you any suggestions on how I can get this data.
<?xml version="1.0" ?>
<products>
<product>
<ProductId>12345</ProductId>
<name>Productname</name>
<description>"This is some description text"</description>
<Categories>
<Category>
<name>Category type</name>
<merchantName>Category subtype</merchantName>
</Category>
</Categories>
<fields />
</product>
Etc…….
</products>
Many thanks in advance
Simon
View 1 Replies
View Related
Oct 10, 2006
Here is the scenerioI am trying to copy a database from a live sql server to a local server.I wanted to copy the tables, triggers, procedures, functions, usersSo I have "Copy tranfer of objects " in the DTS wizard.But the process fails informing that the users are not there in the local server and fails the process.Now what is my expectation is that why the user logins every thing is not copied to the local sever.Help would be more appreciated. Pretty urgent.
View 30 Replies
View Related
Oct 16, 2006
Hello I am developing a web application that will allow users to upload a .mdb file and from that file I need to populate an SQL database. I know the table name of the .mdb file, but I am unclear how to structure my data access layer correctly. How do I pull data from the .mdb file and once I have it how do i populate the SQL database?Any advice would be greatly appreciated.thanks!
View 1 Replies
View Related
