I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Friday, March 30, 2012
Help Import Export Wizard SQL Server 2005
Hi! I have XP sp2, with SQL server 2005 Developer Edition 9.00.3054.00 and also have Microsoft SQL Server Express Edition 9.00.3042.00, well my problem alway I want import data into any server from any data source I recive this message:
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
ADDITIONAL INFORMATION:
Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified. (System.EnterpriseServices)
Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified.
The system cannot find the file specified. (Exception from HRESULT: 0x80070002)
BUTTONS:
OK
I have this name space but, I try for many ways and I can't import data in my server, What can I do?
Thanks a lot.
Do you have your Dev edition and Express edition of SQL Server installed on the same machine?
This could be some sort of side by side problem. Could you try to install only the dev edition and then attempt the data import?
Thanks,
Bob
|||Hi! I find the problem... Well I have install frameWork 1.1 2.0 and 3.0 but in Assemblie cache have System.EnterpriseServices.dll version 1.0 so... There are 2 solutions..
1.- Uninstall FrameWork 1.1 and reinstall 2.0
2.- Use gacutil and register System.EnterpriseServices.dll from version 2.0 <- this work for me.
Thans a lot for every one.
Help Im a Noob - Updating a database entry.
Ok, so I work with a certain program which has a wide open back door into it's data tables. As a program administrator i can go into the actual program and change details of other users etc. However here's what i need to do...
A certain users security level needs to be reduced from administrator to a lower level. If i do that within the program it will automatically require that i change that users password, which i don't know.
The user is not to know that their access level has changed.
Backdooring the database through MSQuery and hunting online a little i found how to update information...
UPDATE User
SET Security = 4
WHERE UserID = 4
This works fine, however the users password is then scrambled, until an administrator edits their password for them.
The password itself is encrypted within the database.
Appearing something like "99171821E244877B4201003E0519EEA7"
It appears to be hexadecimal, but hey, i'm no system engineer. Strange thing is. that encryption represents the password "bill". When i execute the change on security level, the password "bill" becomes invalid, yet the encrypted string remains exactly the same.
What i need if at all possible is a how to... on forcing the database to not change the password field when the security level is updated. Any other suggestions would be most welcome. And of course plain english with syntax explanations would be nice as i am very much a junior, this has been my first SQL code execution, aside from queries.
Cheers,
OostersUnfortunately for you, this seems to be an application issue.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:|||Application problems seem pretty indiscriminant about what engines they affect to me. If an app uses SQL, and that SQL breaks, it breaks for any database that implements SQL at the same level. If an app uses database engine specific code (Oracle, Microsoft, DB2, etc), then that app will probably only run on its target database engine, but that is a failure in the application, not the database.
I think that the problem that Oosters has found is that multiple columns within the users table are used to derive (actually decrypt) the password. It would appear that the application designers wanted to prevent exactly the kind of change that Oosters is trying to make.
-PatP|||Unfortunately for you, this seems to be an application issue.
Indeed.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:
And yet you felt it was imperative to squeeze that into your post regardless.
Quality.
Would you pretty pretty please proceed to regal me with unsolicited opinions and stories about how linux cured your stepdad's brother's cousin's dogs' nephew's sister's case of terminal cancer, or perhaps the one about Microsoft releasing OS's laced with mind control algorhythms that only appear at a particular resolution and refresh rate?|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Meanwhile I think i know what's happening with the password. I think instead of the database automatically changing the password when i update the security level of a user, there may be another field to lock the password when i update security level, and this will only unlock when another password is offered. I can't even get around it by feeding the database it's own encryption string, which should force it to recognise a password change. I'm off to hunt for a password locking field.
I could destroy the integrity of the entire database at the touch of a button, but i can't change a single users security level.|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Because controlling access to your database server probably isn't their job. Who is going to keep the database running if everybody is completely locked out of it?|||One of the really fundamental elements of good security design is to limit the damage that can be done without detection. A fundamental way to achieve this is to require some form of intervention from one or more of the affected parties whenever a change is made. The example you've got is when a security level is changed, make that change require a password change.
This has little to do with the database. It is a fundamental requisite for good security management.
While I'm sure that it frustrates you, I see it as a good thing in the larger context because it makes an otherwise unannounced change visible to the affected party. While it might not be politically expedient, you shouldn't be able (from a systemic point of view) to take away (or grant) administrative privledges without the knowledge of the user.
-PatP|||Yes, In an ideal world, in an ideal database i shouldn't be able to change user security levels without their knowledge. This being said...
<RANT>
Considering the sensitivity of information as this is a financial database representing the business of the whole company, I shouldn't be permitted to backdoor and alter financial information, and yet as long as i make it balance, which can take a bit of figuring, the database security allows such changes.
</RANT>
No need to respond to this, thanks for the knowledge people.
regards,
Matt
A certain users security level needs to be reduced from administrator to a lower level. If i do that within the program it will automatically require that i change that users password, which i don't know.
The user is not to know that their access level has changed.
Backdooring the database through MSQuery and hunting online a little i found how to update information...
UPDATE User
SET Security = 4
WHERE UserID = 4
This works fine, however the users password is then scrambled, until an administrator edits their password for them.
The password itself is encrypted within the database.
Appearing something like "99171821E244877B4201003E0519EEA7"
It appears to be hexadecimal, but hey, i'm no system engineer. Strange thing is. that encryption represents the password "bill". When i execute the change on security level, the password "bill" becomes invalid, yet the encrypted string remains exactly the same.
What i need if at all possible is a how to... on forcing the database to not change the password field when the security level is updated. Any other suggestions would be most welcome. And of course plain english with syntax explanations would be nice as i am very much a junior, this has been my first SQL code execution, aside from queries.
Cheers,
OostersUnfortunately for you, this seems to be an application issue.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:|||Application problems seem pretty indiscriminant about what engines they affect to me. If an app uses SQL, and that SQL breaks, it breaks for any database that implements SQL at the same level. If an app uses database engine specific code (Oracle, Microsoft, DB2, etc), then that app will probably only run on its target database engine, but that is a failure in the application, not the database.
I think that the problem that Oosters has found is that multiple columns within the users table are used to derive (actually decrypt) the password. It would appear that the application designers wanted to prevent exactly the kind of change that Oosters is trying to make.
-PatP|||Unfortunately for you, this seems to be an application issue.
Indeed.
If it were a DBMS problem, then I would assume you are not using ORACLE RDBMS; such problems only occur in mediocre DBMS like SQL Server and others.
:eek:
And yet you felt it was imperative to squeeze that into your post regardless.
Quality.
Would you pretty pretty please proceed to regal me with unsolicited opinions and stories about how linux cured your stepdad's brother's cousin's dogs' nephew's sister's case of terminal cancer, or perhaps the one about Microsoft releasing OS's laced with mind control algorhythms that only appear at a particular resolution and refresh rate?|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Meanwhile I think i know what's happening with the password. I think instead of the database automatically changing the password when i update the security level of a user, there may be another field to lock the password when i update security level, and this will only unlock when another password is offered. I can't even get around it by feeding the database it's own encryption string, which should force it to recognise a password change. I'm off to hunt for a password locking field.
I could destroy the integrity of the entire database at the touch of a button, but i can't change a single users security level.|||In the midst of these response all i basically understood, was that the application designers didn't want me to do it. Why couldn't they just stop me backdooring the application in the first place.
Because controlling access to your database server probably isn't their job. Who is going to keep the database running if everybody is completely locked out of it?|||One of the really fundamental elements of good security design is to limit the damage that can be done without detection. A fundamental way to achieve this is to require some form of intervention from one or more of the affected parties whenever a change is made. The example you've got is when a security level is changed, make that change require a password change.
This has little to do with the database. It is a fundamental requisite for good security management.
While I'm sure that it frustrates you, I see it as a good thing in the larger context because it makes an otherwise unannounced change visible to the affected party. While it might not be politically expedient, you shouldn't be able (from a systemic point of view) to take away (or grant) administrative privledges without the knowledge of the user.
-PatP|||Yes, In an ideal world, in an ideal database i shouldn't be able to change user security levels without their knowledge. This being said...
<RANT>
Considering the sensitivity of information as this is a financial database representing the business of the whole company, I shouldn't be permitted to backdoor and alter financial information, and yet as long as i make it balance, which can take a bit of figuring, the database security allows such changes.
</RANT>
No need to respond to this, thanks for the knowledge people.
regards,
Matt
Help if possible
I have very limited skill in Access, but am working on a project for work. I have a database file that includes fields like item number and ticket number. There are currently identical records for both of these fields. I'm trying to create a query that dislplays a specific item only if it has the same ticket number as any other item.
I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql
I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql
Help Identifying Full table scans with objectname
Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Help Identifying Full table scans with objectname
Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Help I have gone brain dead
I have create a table called production. Where production information is entered on each shift for each day (3 shifts). When I first created the table, there was no need to track equipment without production. Now it is!! What I need to do is be able to query the table by line, shift and date for a date range and determine which lines, shifts and date are missing production data. The machines are numbered from 2 to 12 and the column name is LineNum. I have columns named Production, Shift, and EntryDate. I know that I am probably going to feel stupid when I see the fix, but for the life of me, I just don't see how to do it without recordset and then processing it in vbscript.
Thanks in advance,
LeeLee, I'm not sure if I eally understand the problem, but it sounds like the following
SELECT LineNum, Shift, EntryDate
FROM production
WHERE Production IS NULL
AND EntryDate BETWEEN @.begindate AND @.enddate
@.begindate and @.enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?|||If you can, post here the tables structure in order for us to have a good understanding of your situation.
ionut|||I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.
SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept
FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine
GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine
HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))
tblNumLine
NumLine
tblProd
LineNum
Shift
Production
EntryDate
Thanks,
Lee|||Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.
Thanks to all for your patience with an knucklehead,
Lee
Thanks in advance,
LeeLee, I'm not sure if I eally understand the problem, but it sounds like the following
SELECT LineNum, Shift, EntryDate
FROM production
WHERE Production IS NULL
AND EntryDate BETWEEN @.begindate AND @.enddate
@.begindate and @.enddate represent the date range you're searching for. This seems awfully simple, though, perhaps I'm misunderstanding the problem?|||If you can, post here the tables structure in order for us to have a good understanding of your situation.
ionut|||I keep playing until I found something that give me an "okay" way of getting the information. I created a table called NumLine with all possible lines in it then joined the two tables. This method returns me data (count) as long as the line has ran at least one shift. The problem is if the line does not run at least one shift, I will not know it. My manager said that he would deal with this short term.
SELECT DISTINCT Prod.ProDate, Prod.Line, Max(Prod.Shift) AS MaxOfShift, Count(Prod.Shift) AS CountOfShift, Prod.ProDate, Prod.Dept
FROM Prod RIGHT JOIN tblNumLine ON Prod.Line = tblNumLine.NumLine
GROUP BY Prod.ProDate, Prod.Line, Prod.ProDate, Prod.Dept, tblNumLine.NumLine
HAVING (((Prod.ProDate) Between 'MMColParamBeg' And 'MMColParamEnd') AND ((Count(Prod.Shift))<3) ))
tblNumLine
NumLine
tblProd
LineNum
Shift
Production
EntryDate
Thanks,
Lee|||Yes! When I thought over what I had accomplished by creating a new table and the structure of the query, end result NOTHING! I could have accomplished the same with a simple Select and Count(Shift) Where Count(Shift) < 3 Group by Shift. What I need is to be able to return missing production even if it is all 3 shifts.
Thanks to all for your patience with an knucklehead,
Lee
Subscribe to:
Posts (Atom)