I have a small archiving SSIS pacakge that I use to archive off old orders. I was OLEDB and found that I would be deleting about 1 order (plus all its relational records) a second. Try to find some speed I tried the ADO.net connetion and turn pooling on with a min of 10. Now I am getting about 100 order records deleted a second.
Has any one else found that that ADO.net connection is faster than the OLEDB (I would have thought it would be the other way around).
The
Control flow items I am using are the Execute SQL Task and the queries are like this
Insert into arcprod.wh1.pickdetail
Select * from wh1.pickdetail where status='9' and orderkey = @Pram1 and
pickdetailkey not in (select pickdetailkey from arcprod.wh1.pickdetail)
...
delete from wh1.pickdetail where orderkey = @Pram1
I have written a package which accepts variables for the server, initial catalog & table name.
I execute sql to drop the following stored procedure, then following sql statement to create it.
================================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE procedure [dbo].[SP_CreateMatchProc] @sTable varchar(300) as BEGIN SET NOCOUNT ON declare @cmd nvarchar(2000) set @cmd = '''' Set @cmd = 'SELECT REPLACE(field1 + field2 + field3 + field4 + field5, '' '', '''') AS dBString ' + 'FROM ' + @sTable + ' ORDER BY <table>_ID COLLATE Latin1_General_CI_AS' exec (@cmd) END GO
Then in the Oledb source (validateexternalmetadata = false) I use "sqlcommand from variable" with a variable value of "SP_CreateMatchProc '<tableName>'"
The package runs fine in the IDE regardless of variable values, but when I created a batch file which calls dtexec I get a failure:
Error: 2007-10-04 08:46:42.82 Code: 0xC0202005 Source: Data Flow Task OLE DB Source [310] Description: Column "dBString" cannot be found at the datasource. End Error Log: Name: OnError Start Time: 2007-10-04 08:46:42 End Time: 2007-10-04 08:46:42 End Log Log: Name: OnError Start Time: 2007-10-04 08:46:42 End Time: 2007-10-04 08:46:42 End Log Error: 2007-10-04 08:46:42.82 Code: 0xC004701A Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Source" (310) failed the pre-execute phase and returned error code 0xC0202005. End Error
with the ValidateExternalMetadata set to TRUE I get
Error: 2007-10-04 09:21:35.20 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "OLE DB Source" (10621)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". End Error
the most notable thing I see there is that it looks like a different ID (310) with out the validation and (10621) with it.
I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:
SELECT TB1.product, TB2.product FROM TB1 JOIN (select distinct product from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse ON TB2.product LIKE '%' + TB1.product+'%'
which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?
Select website from wce_contact where uniqueid like
'O,U4Ba-l2Nmc' or uniqueid like 'O90kja8,aofc' or uniqueid like 'OIiZnaAHj1fc' or uniqueid like 'oho9mc2HA4fc' or uniqueid like 'ogJrMa.YV9mc' or uniqueid like 'PNocYaQtA6fc' or uniqueid like 'NLWTfcUSbXec' or uniqueid like 'O_eHjaNhPtfc' or uniqueid like 'OK(Qkau,H(fc' or uniqueid like 'nYcUfcCQbXec' or uniqueid like 'O-R5oa2C-2fc' or uniqueid like 'O-R5oa2C-2fc' or uniqueid like 'nPYhfat5eJec'
Is there anyway i can select these contacts without having to copy and paste 'or uniqueid like' numerous times?
Hi,I have a new job. It needs to drop and re-create (by insert) a tableevery night. The table contains approximately 3,000,000 (and growing)records. The insert is fine, runs in 2 minutes. The problem is thatwhen I create the indexes on the table, it is taking 15-20 minutes.There is one clustered index and 11 non-clustered. This is a lookuptable that takes many different paremeters, so it really needs theindexes for the user interface to run efficiently. However, thedatabase owners aren't keen on a job taking 20 minutes to run everynight.Any ideas?
I can't find the answer to the following question.
Why is Visual Studio quicker then the ReportServer.
I've got a report with more then 20 parameters and several are muli-select.
When I'm opening the report in Visual Studio it appears with in one or two seconds whereas I open it on a reportserver it takes 8 to 15 seconds to open or to refresh. After that I can enter the report parameters and then the report is running in approximately the same time.
I know that the refresh is by default, so I can't change that, but the users have problems with wating 15 seconds between entering the parameter values.
The development machine is 2,8 GHz P4, the reportserver is a 4 proc 16 GB databaseserver. The development machine is just being used editing the rdl in Visual Studio.The databases are kept on the database server.The network speed is 100mbps.
Is the only alternative to build my ASPX and reportviewer component inside of that APSX.
I sure would like a quicker way to get to a script's code, then:
1. double-click 2. ctrl+tab to switch to script item in list 3. click on "Design Script" button.
I'm constantly editing scripts and I would LOVE to be able to right-click, "Design Script", bypassing the silly dialog. I've tried creating a macro with no success. While recording, when I click on "Design Script" from the dialog, it actually brings up the macro script in the editor. This task is frequent enough, that it deserves its own context menu entry.
Hi Everyone, Am a third year student doing work placement. Could anyone please give me clues on how to go about extracting data from a LDAP and then into an SQL database?
1 A defined subset of data is to be extracted from GDS on a nightly basis, 2 Then imported into a SQL database for quick & easy retrieval. 3 A web interface is required to present data retrieved from the SQL database.
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
I've got a package which reads a text file into a table and updates another. I set up configurations so that I could import it into the SSIS store on both my dev and live servers. Now, I'm getting this error. I tried removing the configs and am still getting it.
I've been through each step and everything looks okay. Does anyone have any idea (a) what's wrong, (b) how to localise the error or (c) get any additional information? Or do I just have to recreate the package from scratch?
Error at PartnerLinkFlatFileImporter: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at PartnerLinkFlatFileImporter [Log provider "SSIS log provider for SQL Server"]: The connection manager "" is not found. A component failed to find the connection manager in the Connections collection.
I'am sure this is an old quesiton but I've spent the whole morning trying to find the answer
What are the string functions suported by Jet in OLEDB queries? In particular, I am desperately looking for a replace function to remove thousands separators from imported text file
We have a brand new sql 2k server. Its all up to date on service packs(both win2k and SQL).I went in to enterprise manager and added a new db. I can run sprocsand raw sql against the new db just fine from Query Analyzer.When I try to make a dts package that refers to my new db I get thefollowing error:Error desription: deferred prepare could not be completed.Could not locate entry in sysdatabases for database 'mydbname'. Noentry found with that name. Make sure that the name is enteredcorrectly.OK, so I look in sysdatabases and see 'mydbname'. Just in case im anidiot... I actually copied the db name from that table and paste it inmy dts window.My dts package is using an "execute SQL task" to call a stored proc.To be ecxact:USE mydbnameGOEXEC MyProcWhen I click on the parse button, is when I get that error message.I have deleted the db, restarted the server, and recreated the db witha slightly different name. I can replace mydbname with pubs ornorthwind and all is ok. It just doesnt like new db's.Any help would be greatly appreciated.Thanks,Dave
I've found a bug in SQL CE 3.0/3.1. This has been confirmed to be present by other users of this and the microsoft.public newsgroups, both in the Windows CE versions and the desktop x86 versions. I got this response:
Laxmi Narsimha Rao ORUGANTI MSFT wrote:
Can you please provide the simplified repro with full VS Solution you have thru our blog: http://blogs.msdn.com/sqlservercompact/contact.aspx. First contact us and then we will exchange directly thru mail.
And that's where it has ended (after I contacted the team via the blog - no response). Other forum users have suggested that I post here repeatedly until I get a response, so that's what I'm doing. Apologies.
Is anyone using OLEDB instead of ODBC? If so why did you choose to use OLEDB vs ODBC? Our application is written in VB and we have a developer that believes switching to OLEDB from ODBC would be beneficial to us. Please give any information that you have! Thanks.
I have a DTS package which is using OLEBD provider for Oracle, it was working fine. I tried to open this package and I got this error message "There was a problem retrieving the list of OLE DB Providers"
I tried to intall MDAC8.2, it didn't help, should I reinstall SQL Server?
I am working on a large ASP application(s) which uses SQL Server 2K on the back end, ADO 2.7 for Data Access. The current connection strings are setup to use older ODBC drivers. I wanted to change them to OLEDB for performance gains and better support in the future.
The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.
Has anyone else gone through this process?
Is there a comprehensive listing of those methods that are not compliant between these two drivers?
My problem: I have developed an application based on sqloledb to access a SQLserver application.
One of the operations is to update certain column of a certain table. This works fine. But with a specific catalog (database) of a specific customer the update hangs for ever on the execution of that sql sentence. Size of the mdb file is around 1 Gb. The funny thing is that if I execute the same sql sentence (same record and same catalog) from the Query Analyzer ( that I belive it uses odbc ) the operation is done. The update sql sentence does an update on a non-indexed column with the criteria ( where ...) using a index column (non-clustered) I have tried the sqlmaint to rebuild indexes and check integrity and no special error report is given.
Also another funny behaviour is that if I stop and I restart sqlserver, the update of that specific record fails, but it continues of the following records to be updated.
This is not executed inside a transaction. Candidate records to be updated are stored in memory (maximum 1000), and one by one are updated.
Does anyone know an specific tool to integrity or to monitor what is going on? I have tried the profiler , what can I monitor to detect this lock?
i'm use this code ,in SQL2005 std and ACCESS database, it work
but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()
Exception : OleDbException
0x80040E30L
DB_E_BADTYPENAME
Code Snippet
OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");
od.Open();
OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@billno,@checkouttime,@finalprice,@handle,@ischeckout,@memo,@paymode,@trick)", od);
I am pursuing a course on Data Warehousing & Data Mining as part of my Post-Graduation degree. I got an introduction to Microsoft's OLEDB for DM as part of the course, which got me interested for experiencing it hands-on.
I developed a traditional Database application for my college's library using the .NET 2.0 framework, which is running fine.
Now, I want to see the power of Data Mining in general & OLEDB for DM in particular, to help me mine information & patterns from the mostly transactional data generated by the library daily, like the rush hours, the average issue duration for a book, books that are issued more frequently, books that are issued to students from multiple streams etc. etc.
Now, I have heard MSOLAP provider, ADOMD data provider, SQL Server Analytical Services, OLEDB for DM libraries etc. are required for Data Mining. Now, what exactly is each of these tools? Are they independent servers for DW & DM like SQL Server for DBs, or they are libraries for extending SQL Server itself? Moreover, are separate libraries required for OLAP & Mining?
I have SQL Server Express Edition. I believe that they are extensions for SQL Server. So, will they work with Express Edition? If not, are their Express Editions available (like OLEDB for DM Express!!!!) that are compatible with SQL Server Express.
Thanx in advance if you take time to answer so many questions...
I want to use SQL 2005 to import data from Visual Foxpro 6.0 free table / database into SQL 2005. Whethter I should install the latest Microsoft OLE DB Provider for Visual FoxPro 9.0 SP1 or previous mdac ???
I generated an ATL OLEDB Consumer with the VS 2005 wizard. The table is very simple and is located on my local SQL Server. It has two varchar columns and no keys or constraints. I can modify, insert and delete rows with no problem.
Now I want to use bookmarks. I added the following to the accessor class:
Now I can delete but I can no longer modify or insert. The error is DB_E_ERRORSOCCURED and the status of the two columns after a setdata or insert is DBSTATUS_E_UNAVAILABLE. This is the only error information available.
When I comment out the bookmark entry from the column map, I can modify, insert and delete again. The problem appears to be in how IRowsetChange handles bookmarks. I tried adding bookmarks to other tables using the above 3 steps and they also refuse to modify or insert with bookmarks enabled.
The bookmarks appear to be correct, as I can do a successful MoveToBookmark for any row in the table.
My environment: Windows XP SP2, VC++ 2005, SQL Server 2005 Express, WTL 8.0
I am porting a VB.NET application using Access as its backend to use SQL Express as its new backend. The application uses .NET 2.0 OleDbProvider to connect to the database.
The porting of the database itself to Sql Express is nearly complete, now I need to modify the application. So, does it really make sense to modify it to use Native Sql Client instead of OleDb in terms of effort & benefits???
Moreover, if I continue using OleDb, would I need to make such small changes to embedded Sql statements like using single quotes around strings or dates instead of double quotes or pound symbols that are currently being used for String or date data respectively???
