Import queries

I am copying data from an Excel workbook/worksheet to a SQL Server 2005
data-mart database table (not previously existing) using the SQL Server 2005
Import/Export Wizard. I would like to do it using an import query that
recasts the data types, as opposed to copying the Excel data and letting the
Wizard set the data types. Below is a small sample of the code I'm trying t
o
use. I always get an error message (also below). What do I do to make this
work?
select
cast(REPORT_NAME as char(10)) as [Report_Name],
cast(SYSTEM as char(10)) as [System],
cast(PRIN as char(10)) as [Prin],
cast(AGENT as char(10)) as [Agent],
cast(BANK_NAME as char(50)) as [Bank_Name],
Report_Date as [Report_Date]
TITLE: SQL Server Import and Export Wizard
--
The statement could not be parsed.
ADDITIONAL INFORMATION:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005). (System.Data)You will get this error if you use a keyword or a reserved word as the table
or column name you are using.
Check it out with the list of keyowrds. You may want to try this link..
http://msdn.microsoft.com/library/d...getkeywords.asp
or try googling to get the latest list.
Hope this helps.|||No reserved words were column headings or table names. It still doesn't
work.
"Omnibuzz" wrote:

> You will get this error if you use a keyword or a reserved word as the tab
le
> or column name you are using.
> Check it out with the list of keyowrds. You may want to try this link..
> http://msdn.microsoft.com/library/d...getkeywords.asp
> or try googling to get the latest list.
> Hope this helps.|||Is all the data within the ranges specified.
if importing to char(10) does all data fit (only have 10 chars)?
"Mitch" wrote:
> No reserved words were column headings or table names. It still doesn't
> work.
> "Omnibuzz" wrote:
>|||Yes... I have been very careful with the cast statements.
"MattB" wrote:
> Is all the data within the ranges specified.
> if importing to char(10) does all data fit (only have 10 chars)?
> "Mitch" wrote:
>|||I discovered an easy solution to this problem. In the Import/Export Wizard
do not use a query to transfer the table. The wizard brings you to a point
where you can click on the EDIT button for the sheet or table selected. Thi
s
bring up a form that allows you to recast each column. It works beautifully
!
"Mitch" wrote:

> I am copying data from an Excel workbook/worksheet to a SQL Server 2005
> data-mart database table (not previously existing) using the SQL Server 20
05
> Import/Export Wizard. I would like to do it using an import query that
> recasts the data types, as opposed to copying the Excel data and letting t
he
> Wizard set the data types. Below is a small sample of the code I'm trying
to
> use. I always get an error message (also below). What do I do to make th
is
> work?
> select
> cast(REPORT_NAME as char(10)) as [Report_Name],
> cast(SYSTEM as char(10)) as [System],
> cast(PRIN as char(10)) as [Prin],
> cast(AGENT as char(10)) as [Agent],
> cast(BANK_NAME as char(50)) as [Bank_Name],
> Report_Date as [Report_Date]
> TITLE: SQL Server Import and Export Wizard
> --
> The statement could not be parsed.
> --
> ADDITIONAL INFORMATION:
> IErrorInfo.GetDescription failed with E_FAIL(0x80004005). (System.Data)
>

Import Puzzle

Hello -

I have three feeds from sources around the world, each coming in at a
separate time.
These feeds move into a large table (3GB) that is queried by managers.
The feeds are loaded sequentially, and then the previous day's feed
rows are deleted from the table (this is done so that the user's
application is never without data).

The issue is that the import takes a lot of time, as do the deletes.
These is hurting performance significantly. I attempted to fix the
problem by creating separate tables for each feed. I then created a
view with the original table's name and used UNION ALL's. My intention
was that as each feed came in, I'd alter the view with the new table's
name, and then truncate the older table. This met both goals of
concurrency and import/delete speed.

Unfortunately, this view seems to ignore the indexes on the underlying
tables, which devastates performance. I can't index the view, since
altering it makes the index less useful.

I'm looking for a different strategy for loading and deleting the
data, all without disruption to the applications. I'd appreciate any
suggestions...woodyb@.hotmail.com (Buck Woody) wrote in message news:<d4e6e94d.0311250951.76030a77@.posting.google.com>...
> Hello -
> I have three feeds from sources around the world, each coming in at a
> separate time.
> These feeds move into a large table (3GB) that is queried by managers.
> The feeds are loaded sequentially, and then the previous day's feed
> rows are deleted from the table (this is done so that the user's
> application is never without data).
? delete so you never be w/o data ?

> The issue is that the import takes a lot of time, as do the deletes.
> These is hurting performance significantly. I attempted to fix the
This is probably due to your managers querying the data and applying
shared read locks -- while you're trying to insert/delete.

> problem by creating separate tables for each feed. I then created a
> view with the original table's name and used UNION ALL's. My intention
> was that as each feed came in, I'd alter the view with the new table's
> name, and then truncate the older table. This met both goals of
> concurrency and import/delete speed.
Did you put NOLOCK on the view?

> Unfortunately, this view seems to ignore the indexes on the underlying
> tables, which devastates performance. I can't index the view, since
> altering it makes the index less useful.
Which version of MSSQLSERVER are you running? Only 2000 has indexed
views.

> I'm looking for a different strategy for loading and deleting the
> data, all without disruption to the applications. I'd appreciate any
> suggestions...
Ideally you would have 2 separate tables: "real data" and a nice
pretty one for your managers. But if you don't have space etc...,
make sure you put NOLOCKS or "set transaction isolation level read
uncommitted" on every stored procedure and view your managers use.

Hey don't sweat the small stuff. Everything is small stuff. Good
luck.
-- Louis|||"Buck Woody" <woodyb@.hotmail.com> wrote in message
news:d4e6e94d.0311250951.76030a77@.posting.google.c om...
> Hello -
> I have three feeds from sources around the world, each coming in at a
> separate time.
> These feeds move into a large table (3GB) that is queried by managers.
> The feeds are loaded sequentially, and then the previous day's feed
> rows are deleted from the table (this is done so that the user's
> application is never without data).
> The issue is that the import takes a lot of time, as do the deletes.
> These is hurting performance significantly. I attempted to fix the
> problem by creating separate tables for each feed. I then created a
> view with the original table's name and used UNION ALL's. My intention
> was that as each feed came in, I'd alter the view with the new table's
> name, and then truncate the older table. This met both goals of
> concurrency and import/delete speed.
> Unfortunately, this view seems to ignore the indexes on the underlying
> tables, which devastates performance. I can't index the view, since
> altering it makes the index less useful.
> I'm looking for a different strategy for loading and deleting the
> data, all without disruption to the applications. I'd appreciate any
> suggestions...

The two parts that often slow down inserts, updates and deletes are the
indexes and transaction log.

For transactions. If data protection is not required, since you can just
import the feeds again and they are useless after one day, then maybe you
could use a # temp table. The tempdb is more ram based so it works much
faster and has less transaction logs.

For indexes, why not drop the indexes before you do the import and delete
and put them back later. This is much faster than having the (b-tree)
indexes do their balancing tricks after each row is added.

If you can't drop the indexes then consider a low fill factor on the indexes
so they have plenty of space the grow without rebalancing.|||Quick thought off the top of my head.

Generate each table (x3). Create a view of these and generate a new
table from this to combine the data. Then create a final table for
your guys to work with. You can fully control the indexes that is on
the final table. You can mess about with the data in each of the three
sets. You will see the performance issues when creating the combined
table, but the users won't. The move from that into the one to be
reported on should be reasonably OK in comparison as you are simply
moving bulk data, not combining three sets and moving them at the same
time.

HTH

louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311251501.3e74e293@.posting.google.com>...
> woodyb@.hotmail.com (Buck Woody) wrote in message news:<d4e6e94d.0311250951.76030a77@.posting.google.com>...
> > Hello -
> > I have three feeds from sources around the world, each coming in at a
> > separate time.
> > These feeds move into a large table (3GB) that is queried by managers.
> > The feeds are loaded sequentially, and then the previous day's feed
> > rows are deleted from the table (this is done so that the user's
> > application is never without data).
> ? delete so you never be w/o data ?
> > The issue is that the import takes a lot of time, as do the deletes.
> > These is hurting performance significantly. I attempted to fix the
> This is probably due to your managers querying the data and applying
> shared read locks -- while you're trying to insert/delete.
> > problem by creating separate tables for each feed. I then created a
> > view with the original table's name and used UNION ALL's. My intention
> > was that as each feed came in, I'd alter the view with the new table's
> > name, and then truncate the older table. This met both goals of
> > concurrency and import/delete speed.
> Did you put NOLOCK on the view?
> > Unfortunately, this view seems to ignore the indexes on the underlying
> > tables, which devastates performance. I can't index the view, since
> > altering it makes the index less useful.
> Which version of MSSQLSERVER are you running? Only 2000 has indexed
> views.
> > I'm looking for a different strategy for loading and deleting the
> > data, all without disruption to the applications. I'd appreciate any
> > suggestions...
> Ideally you would have 2 separate tables: "real data" and a nice
> pretty one for your managers. But if you don't have space etc...,
> make sure you put NOLOCKS or "set transaction isolation level read
> uncommitted" on every stored procedure and view your managers use.
> Hey don't sweat the small stuff. Everything is small stuff. Good
> luck.
> -- Louis

Import problem with varchar(max) field

I'm trying to import some Assessor data from a text file into a table and the field for Legal Description (column 2 in the source text file) in the table is of data type varchar(max) because some of the data goes over the 8K size. I get an error on the first row of importing that refers to column 2 (see 'Initial errors' below). I read the related post and changed the size of input column 2 to 8000 and got this error. Finally I set the size of the of input column 2 to 4000 and it ran. So I'm thinking there is a limit on the size of varchar data that can be imported. Just want to clarify what that limit is and how I might go about importing this data.

Thanks, John

Error with input column 2 set to size of 8000:

Setting Destination Connection (Error)

Messages

Error 0xc0204016: DTS.Pipeline: The "output column "Column 2" (388)" has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)

Initial errors:

Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Column 2" (18)" failed because truncation occurred, and the truncation row disposition on "output column "Column 2" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "\\Scux00\assrdumps\SQLServerDB\exportsql.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - exportsql_txt" (1) returned error code 0xC0202092. 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

There is a limit on the size of unicode character strings at 4000. Non-unicode character strings have a limit of 8000. Ensure that you don't have type conversions between varchar and nvarchar.|||

I'm trying the SQL Server Import and Export Wizard. I've set the DataType to most of the fields as 'string[DT_STR]' and the Unicode box is unchecked, but when I look at column mappings after clicking 'Edit Mappings', all the columns say they're type nvarchar on the screen and it doesn't appear to be something I can change. If I highlight a column on that screen it shows the setting that I set in the Advanced screen for the text file, ie .. Column 2 string[DT_STR](4000). Since I'm getting the limit of 4k it must be because of this. I'm not sure how to modify it. I'll save the package as a file and open it up in BID Studio ... john

Import Performance file into SQL Server table

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
This would be a very help to resolve this issue.
Thank You,
Dan
Hi
You can schedule the start/stop of performance monitor from within Perfmon
itself. This also allows you to run a script when you close it.
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:265b501c46109$08226bd0$a301280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> This would be a very help to resolve this issue.
> Thank You,
> Dan
sql

Import Performance file into SQL Server table

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
This would be a very help to resolve this issue.
Thank You,
DanHi
You can schedule the start/stop of performance monitor from within Perfmon
itself. This also allows you to run a script when you close it.
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:265b501c46109$08226bd0$a301280a@.phx
.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> This would be a very help to resolve this issue.
> Thank You,
> Dan

Import Performance file into SQL Server table

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics. Then I will DTS file into a SQL
Server table.
This would be a very help to resolve this issue.
Thank You,
DanHi
You can schedule the start/stop of performance monitor from within Perfmon
itself. This also allows you to run a script when you close it.
John
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:265b501c46109$08226bd0$a301280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics. Then I will DTS file into a SQL
> Server table.
> This would be a very help to resolve this issue.
> Thank You,
> Dan

Import partial Sql table from Excel spreadsheet

I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql.

Does anyone have any suggestion what I did wrong or what is the right way of doing this?

Thanks a lot.

Mia

Try the thread below for all you need, post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1442470.aspx

|||Thank you. I will take a look at the posts/articles and let you know.

Import Outlook/ACT etc contacts to SQL Server table

Guys
Does anyone know of a 3rd party module/plug-in for VS or even some
downloadable code that enables the import of Outlook/ACT and similar contact
information through an ASP.NET page into a SQL Server table with ongoing
1-click button sync.

I need to replicate something similar to the Intellisync tool used in Yahoo
Mail.

regards
AndyOutlook OWA is implemented with RPC(Remote Procedure Call), ANSI SQL has been on RPC since 1969 so if you look into .NET System .NET which deals with Networking and Sockets you can write the code. In C# The complete reference has a chapter on System .NET. I would also run a search on MSDN for OWA Web implementation sample code. SQL Server Ports are TCP 1433 and UDP port 1434. Hope this helps.

Kind regards,
Gift Peddie|||Andy,
I need to do the same thing. Maybe we can team up on something?

ScAndal|||Similar thread:http://forums.asp.net/thread/44602.aspx

Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.
Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools
sql

Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools

Import only yhe changed data

I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table.
THX
InonIt would be bit difficult to import without a time field, which is key to compile the data.

If your task is about changed data then why not consider replication.|||Sure, but you need a staging environment...

1. bcp (I can't abvide DTS unless the data is in Excel or Access, even then...) in to a stage table
2. Write 3 sql statements to compare new data with old...determine, based on keys, which data was added, which data was deleted, and which data was updated..

3. Then INSERT, DELETE and UPDATE those sets of data...

Got a link somewhere...

Hold on...|||Here's the code:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28305|||You can use the BINARY_CHECKSUM function to determine whether data was changed.sql

import one table from one database to another database

if i have one table from database A, and i want to copy the table to database B, then how to do it in SQL server management studio express? is it i have to make sql query?

try

select * into databaseB.dbo.TableA
from databaseA.dbo.TableA

or go to database B and run

select * into TableA
from databaseA.dbo.TableA

This will not copy constraints only data.

if it will not work right click on database and from task select import or export and select source destination , table to copy and run created SSIS package.

|||

i don't try yet the query. anyway, i right click on the database but i don't find any task that saying select import or export?

|||

i don't try yet the query. anyway, i right click on the database but i don't find any task that saying select import or export?

|||

Right click on your database in Object Explorer and in menu select tasks and next import export, Sorry it does not work in Management studio express, nut if you have SP1 and integration developments studio installed you can do it inside it.

import of multiple CSV files in one MS SQL table

Hi all,

I have de following application to do :

I receive several .csv files from another application in a determined folder
of my PC.

Those files are named with the format log1.csv logs2.csv logs...
The number of file is variable but the internal format is always : time_sec;level
So the files content a field that may be used as unique key in the target database.

I'm trying to build a DTS package that should import periodically
all the CSV's present in the folder and then destroy them if done
successfully.

Apparently its not so simple than I supposed. I have always to give the name
of the table I want to import.

any idea?"Laurent" <autplc@.hotmail.com> wrote in message
news:e8bac87e.0411100552.5af177ca@.posting.google.c om...
> I receive several .csv files from another application in a determined
folder
> of my PC.
> Those files are named with the format log1.csv logs2.csv logs...
> The number of file is variable but the internal format is always :
time_sec;level
> So the files content a field that may be used as unique key in the target
database.
>
> I'm trying to build a DTS package that should import periodically
> all the CSV's present in the folder and then destroy them if done
> successfully.

Two Options:

Option 1:
Rename the file to a temporary name (in an ActiveX task) and use the
temporary name for the Data Pump

Option 2:
Use a Dynamic Properties Task to change the Data Source Name in the Data
Pump Task.

Regards,
Jim

Import of Data from Access DB

Hello,

I'm trying to import some data from a table in an Access Database into my SQL Server.

It's one field I'm having a problem with and I don't know how to get round it.

Basically the field in Access is called "DueToStart" and is a "Date/Time" type field.
It shows values as "09:00:00" etc.

When I try to import this table into SQL, I get the following message, and I don't know how to get round it.

"Error as destination for row number 1. Errors encountered so far in this task: 1.
Insert Error, column 3('DueToStart', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification."

Does the access column contain only time?

|||Yes is does|||Is there a target table in the SQL Server or is it created automatically when you import the table? The Import Export Wizard in SQL Server automatically creates a table when the table does not exist when importing. Either the table destination exists or is created by the import wizard, check that the definition of the column in the SQL Server is datatime and not smalldatetime.|||

It seems that the column contains only time information and no date imformation. As SQL Server knows only combined data types (e. g. DT_DBTIMESTAMP), I wonder what date it should insert. Maybe 1/1/1753 but maybe that is the problem.

What about a viewer for the output of the data source?

|||Alas_gr - The table already exists in the new SQL DB. The format is smalldatetime and this is what it needs to be.

Anonymous - What do you mean by "viewer for the output data source" ? Sorry but I'm brand new to this and trying to find my way blindly !
|||

I think the problem occurs because the value in your Access table that is either too small or too large for the SQL data type. So try to switch it to datetime, and do the import, and see if it works.

As for the viewer, it can be useful only if you are using Visual Studio BI to design a package. If so, in the data flow designer, if you right click on the connector between the source (access) and the destination (sql server) there is a choice (Data Viewers). So next time you run the package you can see what data you are importing.

|||

This thread deals with an similar issue. In short the date time column from access had to be placed in a SSIS string variable and then casted to date/time.

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

Import Multi-Row Record Text File with DTS

I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21ACDTS questions don't get a lot of answers on this forum. Perhaps most developers feel as I do, that DTS is a kluge solution of several different poorly integrated technologies. If you are lucky, my posting this reply will shoot your post back to the top of the list queue and somebody proficient in DTS will give you an answer.

Otherwise, I use DTS only for what it is best at; transfering and importing raw data. I avoid using DTS for transforming data, prefering instead to load the data into staging tables in my database and then run stored procedures to transfer it to the main database schema. I think one of the big advantages of this is that you separate the import process from the transform process, allowing you to flag and retain records that fail your business rules.|||Originally posted by eoffshore
I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

You don't need VB for this. It would be faster to create a staging table with one column. You want the column to be a char so you don't lose your fixed length. Have a select statement push it into a second staging table with char, but big enough for all four rows.

Then, you can either use a final parsing statement to divide it up, or export to a fixed length text file. It should be quicker to do it this way in sets then to parse through in VB where you'll have to parse through each line and length section.|||In your case it looks like you have records with five elements, where each element is a separate line in your raw data.

One solution to this is to import each record into a staging table as a single string of characters. A possible layout for your staging table would be:

RecordID (Identity)
GroupID (Int)
RecordType (Int)
RecordString (Varchar(500))
ImportErrors (Varchar(100))

As the data is imported into the staging table a RecordID is created to maintain the order.

Next you run a query(s) against your table to identify the RecordType for each record. For instance, maybe all the type 2 records start with 'HOST'

Lastly, group the records like this:

Update StagingTable
set GroupID = SubTable.GroupID
from StagingTable
inner join
(select StagingTable.RecordID, Min(GroupRecords.RecordID) GroupID
from StagingTable
inner join StagingTable GroupRecords
on StagingTable.RecordID >= GroupRecords.RecordID
and GroupRecords.RecordType = 1) SubTable
on StagingTable.RecordID = SubTable.RecordID

Now you have identified your imported records and their relationships to eachother, and you can load them into your schema. It's not neat or pretty, but it works. For each step you can flag problems by appending an error message to the ImportErrors field.

Import multiple text files into Single table

How to import multiple text files (residing in single folder) into SQL Server table? I know how to import single file but not sure how multiple files could be loaded? Pls. guide.

Thanks,

HShah

I am assuming that thefiles all have the same structure.

There are a number of options availableto you. See here:

Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

-Jamie

sql

IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

Import MS-Excel to SQL-Server

Greetings All,

I have a excel file which is originally a sqlserver table that was
exported as a excel file. I have added more data to this excel file
and now want to import it again to its original table,i.e, it will
overwrite current data in the table but with no change in the schema.
How should I handle the issue of PKs in the current table that will be
over-written. I know sqlserver dose not adjust PKs when data is
over-written, like my case.

MTIA,
Grawsha<grawsha2000@.yahoo.com> wrote in message
news:1108999543.155717.259460@.o13g2000cwo.googlegr oups.com...
> Greetings All,
> I have a excel file which is originally a sqlserver table that was
> exported as a excel file. I have added more data to this excel file
> and now want to import it again to its original table,i.e, it will
> overwrite current data in the table but with no change in the schema.
> How should I handle the issue of PKs in the current table that will be
> over-written. I know sqlserver dose not adjust PKs when data is
> over-written, like my case.
> MTIA,
> Grawsha

I'm not sure what you mean by "overwritten" - do you mean you want to delete
all rows from the table, then load the .xls? If so, just use DELETE or
TRUNCATE TABLE to empty the table before loading the file. If you mean
something else, then you should give some more details, preferably including
the CREATE TABLE statement for your table, so we can see what your key
actually is.

Simon|||Simon

Yes I want to delete the all rows from the table (employees). But the
problem is there are a PK (Person ID) that I use to relate it to a FK
in another table (sales) . I would assume the Enerprise Manager would
give an error if I try to delete the rows.

My question is, should I remove the relation first, delete the rows,
load the file and then create the relation again?

Grawsha|||If your target table is referenced by a foreign key from another table,
you'll get an error if you delete from it, unless you created the key
with ON DELETE CASCADE (assuming MSSQL 2000 - you didn't mention which
version you have), in which case the referencing rows will be deleted
also.

As you suggest, you can load the data by dropping the foreign key then
recreating it, but this will leave orphaned rows in the referencing
table, unless your .xls contains data for all the current rows in the
target table. It isn't clear from your description if your .xls
contains updated rows for existing PK values, or if it contains
entirely new PK values and rows, or both. You might want to consider
another approach, which is to create a staging table with the same
structure as your target table, load the .xls into it, then INSERT and
UPDATE the data in the target table - this may be closer to what you
really need.

/* Add new rows */
insert into dbo.Target (col1, col2, ...)
from dbo.Staging s
where not exists (select * from dbo.Target t
where s.PK = t.PK)

/* Update values for existing rows */
update dbo.Target
set col1 = s.col1, col2 = s.col2, ...
from dbo.Staging s
join dbo.Target t
on s.PK = t.PK

If this doesn't help, I suggest that you post CREATE TABLE statements
for your tables, INSERT for some sample data, and then a few rows of
data from your .xls, to show what your data looks like and what you
expect to happen - descriptions by themselves are usually unclear.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Import lots of decimals, problem on the 18th

Hello,

I'm trying to import data from an Excel sheet into a table. Not all of them is imported.

Exl: 0,000801054857569349 becomes
Sql: 0,000801054857569350 when it is imported.

The column in SQL-Server is defined as DECIMAL 28.18, should take all 18 numbers i thought. Tried 28.19 also but it only added another zero at the end.

I've tried importing via DTS and manually import,same result both times.

Any suggestions?Did you try to import text data from an Excel and convert it onto decimal in SQL Server?|||DECIMAL with precision 28 and scale 18. Tried with scale 19 but only got one more zero at the end.|||The cell in Excel is probably a float. Depending on how it is rendered there will be a point where a given source (float) value will produce different target values for different target data types. This is a problem as old as floating point numbers.

Floats are good for measurements, but lousy for counts. What you are seeing could be the difference caused by that quality of a float.

-PatP|||The cell shows up as "Double" if I look at it in design view in an Access DB that has a link to it.

Hope the beancounters can accepts this fact that the number doesn't show up EXACTLY as it is in Excel. I've tried to maually insert the value with all 19 decimal and it shows ok.

I've seen this problem show up when I'm using a C++ application to insert values from Excel into SQL Server via ODBC also. And there the value is picked as a string and then used to build a SQL-Statement. I will get the same values as with the manual import into SQL Server.

Thanks for the comment.

One more workday, then it's Christmas!|||Floats are good for measurements, but lousy for counts.

Huh?

How is a datatype that's imperfect good for anything?|||Floats (reals and doubles) are perfect for dealing with measures, because floats work like measurements work... Counts (INT, MONEY, DECIMAL, etc) are perfect for counts, because they actually are counts. A measurement is an approximation that has some desired degree of precision, and is quick and easy to perform calculations of practically infinite scope at the desired degree of precsion. A count is exact, but fundamentally limited in terms of the kind of calculations it can do and the scope of values it can represent.

When you're dealing with money, you need a count. When you're dealing with computational measures such as C or e, or figuring anything to do with astronomy or weather, counts are almost worthless and floats are appropriate.

You can't use either tool to efficiently do the jobs that the other is built to handle... You can beat either of them into submission and they can pinch-hit for one another, but they are really two different classes of tools even though people sometimes confuse floats and counts when they don't grok them both.

-PatP

import large table from mysql

I'm trying to migrate a mysql database to sql server 2000 (then I'm
going to upgrade that to 2005). I downloaded myODBC and was able to
import most of the tables, however when I try to import the largest
table (about 18GB) it fails by running out of memory. The machine I'm
using has 4 gigs of memory in it, so I don't think it's the machine,
however SQL Server 2000 Standard can only take up to 2 gigs of memory
which I think is the problem. How can I work around this? I'm
thinking of trying to use mysqldump to dump pieces of the table at a
time and import it that way, but i think that will take an
unreasonably long time.
Thanks for any help!
What method are you using to load the data from the MySQL database to the MS
SQL database?
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:

> I'm trying to migrate a mysql database to sql server 2000 (then I'm
> going to upgrade that to 2005). I downloaded myODBC and was able to
> import most of the tables, however when I try to import the largest
> table (about 18GB) it fails by running out of memory. The machine I'm
> using has 4 gigs of memory in it, so I don't think it's the machine,
> however SQL Server 2000 Standard can only take up to 2 gigs of memory
> which I think is the problem. How can I work around this? I'm
> thinking of trying to use mysqldump to dump pieces of the table at a
> time and import it that way, but i think that will take an
> unreasonably long time.
> Thanks for any help!
>
|||On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> What method are you using to load the data from the MySQL database to the MS
> SQL database?
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
Hi,
Thanks for the response.
I'm using myODBC to connect to the mysql server through an ODBC
connection and Data Transformation Services to import the data.
Dylan
|||You can set the transformation to batch the load, which might release the
memory stress.
I would probably export the data using a mySQL tool, then BCP the data into
the SQL Server.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"dylan" wrote:

> On May 30, 3:06 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Hi,
> Thanks for the response.
> I'm using myODBC to connect to the mysql server through an ODBC
> connection and Data Transformation Services to import the data.
> Dylan
>
|||On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> You can set the transformation to batch the load, which might release the
> memory stress.
> I would probably export the data using a mySQL tool, then BCP the data into
> the SQL Server.
> --
> Denny
> MCSA (2003) / MCDBA (SQL 2000)
> MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
> Microsoft Office SharePoint Server 2007: Configuration)
> MCITP (dbadmin, dbdev)
> "dylan" wrote:
>
Ok, thanks. I'll look for the batching option, and if that doesn't
work I'll look at bcp. I've never used that but it looks easy enough.
|||Hello,
Use the below steps:-
1. In the MySQL Side extract the data from table to a comma seperated text
file
2. Copy the file to SQL Server machine
3. Create the table structure with out indexes and triggers
4. Load the table using BCP IN or BULK INSERT with batch commit options.
5. Create indexes and triggers
6. If you database is in FULL recovery mode, make sure you backup the
transaction log every 2 minutes or so while loading the data.
Thanks
Hari
"dylan" <dylan.roehrig@.gmail.com> wrote in message
news:1180554251.701728.181170@.k79g2000hse.googlegr oups.com...
> On May 30, 3:40 pm, mrdenny <mrde...@.discussions.microsoft.com> wrote:
> Ok, thanks. I'll look for the batching option, and if that doesn't
> work I'll look at bcp. I've never used that but it looks easy enough.
>
|||On May 30, 10:03 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> Use the below steps:-
> 1. In the MySQL Side extract the data from table to a comma seperated text
> file
> 2. Copy the file to SQL Server machine
> 3. Create the table structure with out indexes and triggers
> 4. Load the table using BCP IN or BULK INSERT with batch commit options.
> 5. Create indexes and triggers
> 6. If you database is in FULL recovery mode, make sure you backup the
> transaction log every 2 minutes or so while loading the data.
> Thanks
> Hari
> "dylan" <dylan.roeh...@.gmail.com> wrote in message
> news:1180554251.701728.181170@.k79g2000hse.googlegr oups.com...
>
>
>
Thanks Hari, I'm running into a problem dumping the data into a a
comma separated text file though. Is it possible to do that when the
data in the source table is a blob?