It€™s always said that using the SQL Server Destination adapter is much faster when loading data to a SQL Server database. My question is, if I have a SQL 2000 database, can I still use the SQL Server Destination adapter, or is it only for SQL 2005 db? Is OLE DB Destination adapter my best bet? My data files(flat files), SSIS package and destination SQL 2000 db are all in the same physical server.
View 1 RepliesHi,
I have conditional split on a boolean variable, when the value is true, case1 output goes to Oledb Destination, defaukt Output goes to Sql Server Destination.
When I run the package with value "true" the package runs, but when i run the package with bool value "false", the data is inserted through oledb but Sql Server destination fails.
Can any one tell me Why.
[SQL Server Destination [292]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I don't have any linked server.
thanks
Dharmbir
Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Thanks a lot
Mark
Hi,
Will someone please tell me what is the difference between Destination adapter and bcp in Sql server 2005 ?
Thanks and Regards
Altaf Nizamuddin
Hi All,
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
Why would it do this?
Thanks
Mike
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
Quick question...
I have a conditional split transform in a dataflow...
One of the outputs of the conditional split will take records that are just not needed...
Do I have to send that output into the Trash Destination adapter? Or can I just let those records hang?
Will there be a memory leak if I don't use a trash adapter?
I'm just thinking about if I move the package I'd have to go reinstalling the trash adapter every time...
Thanks
I would Like to know whether anyone tried to SSIS from SQL to AS400.
I am getting Exception when I set destination adapter ( for As400).
Whats the acceptable value .
Hi,
Will someone please tell me how to use
"Destination adapter in store procedure" .............
I'm using Sql server 2005, instead of using BCP i have to use
"Sql server destination adapter" in my store procedure,
Is it possible to implement destination adapter in store procs,
if it is yes please let me know how
Thanks and Regards
Altaf Hussain Nizamuddin
I didn't want to deal with any truncation issues so I edited the SQL created by the Import Wizard. I made the fields VARCHAR instead of NVARCHAR and changed the size from the default 50 to 250.
Now I have a triangle warning on the OLE DB Destination control. So I am wondering, is it limited to only handling 50 characters?
IanO
I am writing a custom task to import data from delimited files into SQL tables. I use the standard Flat File Source adapter, a custom transformation to add a URN column and a filename column to the data and the standard OLEDB Destination Adapter.
Most of my test data files work fine except for ones with a lot of columns (around 350 columns). I get an error when I call the ReinitializeMetaData() method for the destination adapter.
Q1) Is there a restriction on the number of columns (or data row size) that can be imported into an OLEDB Destination Adapter?
Q2) The reason I use this adapter rather than the SQL Server Destination Adapter is that I need to set the destination table name using a variable. I don't believe I can do this with the SQL Server Destination Adapter. Is this the case?
Q3) Anyone know of a better/alternative way of acheiving the above? One way I have thought of is to create a custom destination adapter using the SQL Server Destination adapter as the base but I'm not sure whether this is a) possible and b) worth the hassle.
Hope someone can help...
Cheers
Nick
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0
View 1 Replies View Related
Problem description
There are two sql servers with the same database in different locations with replication running on them. Users in the two locations insert or update date on there own database through an ms access application . The data are replicated to both servers.
Someone wanted a copy of the database and they dropped replication, but users continue to modify and add data on both servers and so replication could not be back again without merging the two databases as one again and restarting replication.
My job was to merge the databases and make replication work and after allot of work all is fine.
I copy and attached the database on my pc that I set up as a server and did the job. Then I went back on both servers that had replication running before I attached the database and set the subscriber and publisher and all was fine to that point.
The database has six groups with permissions on them and many database users on each location. Both the groups and the users show on the database.
I create a new login and user and I add him to the groups to get the permissions.
Now back on a user pc I login with the new user name run the ms access application and try to add some new records, but I get errors that I cannot insert null values to two of the fields in several tables in the database.
This was not happening before and the database was recording the values in those fields namely the user name and the date that the new record was created.
After some reading I found out that I might need to set the groups and permissions on the views and tables from the beging but Im not an expert.
Any ideas?
Thanks allot
George
Hey All:
I was totally confused.
When designing the SSIS dataflow part, firstly , i tried SQL Server Destination because my target server is a sql server.
then execute the task with failure.
Then i tried to use OLE DB DESTINATION instead of SQL Server Destination.
This Dataflow worked.
i can not figour out why.
By the way , i used the connection is OLE DB.And i choosed OLE DB source as the datasource cuz i can not find SQL server datasource.
Who can tell me some reasons for this?
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.
I'm not sure the best way to do this.......
Can anyone enlighten me?.....
I am working on a project that was written in Visual Studio 2003 using asp.net and C#. The project accesses a SQL Server 2000 database. I need to test the application. What I want to do is to copy the database over to a test server. The problem is that the test server only has SQL Server 2005 on it. Would it be a problem if I backed up the database using SQL Server 2000 and then recreated it on the server as a SQL server 2005 project? Would my source code accept the new database even though it would now be in sql server 2005?
Hi,
I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error.
Here is the error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Here is the Environment:
App Server:
Windows Server 2003 Standard Edition
Inside Company's Firewall/ Network
Database Server:
Windows Server 2000 Advanced Edition
SQL Server 2000 SP4
Remote Connections to the Server is checked
Enable Protocols: Named Pipes & TCP/IP
TCP/IP Port: 1402 (I don't know why it isn't the default of 1433)
The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition.
I can ping the db server from the app server using the IP address.
I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server).
I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP?
What do I tell the network admin to change?
Any help would be appreciated.
Thanks,
Oran
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
How do I solve this problem?
I need to write TSQL script that will insert, update, delete rows in a production db from new, updated and deleted data from staging db. They are both in different servers and I can only use TSQL. Basically, the production DB needs to be synchornized with the staging DB.
I was thinking using dynamic sql, cursors, and linked servers. Any ideas?
Jim
Hi,
I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.
I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):
"C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe" /file e: estimport.dtsx /set Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename
and here is the error I get:
Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.
I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...
Any help would be appreciated :)
Good day!
I am getting a bizard error here...
I am trying to drop replication off a database, and funny enough went trough all the steps and I am still getting the error when I try to Disable publishing.
I also did an EXEC sp_removedbreplication 'databasename'
Any ideas?
Hello. I am attempting to add a distribution database to an instance of SQL Server on my local PC. I am using SQL 2005 Developer Edition. I am able to set the instance up as a distributor using the following command:
exec sp_adddistributor @distributor = N'computernameinstancename'
I use the following command to create the distribution database:
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData',
@data_file_size = 4, @log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData',
I get the following error when executing that command:
Msg 14430, Level 16, State 1, Procedure sp_adddistributiondb, Line 214
Invalid destination path C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData.
It's not due to permissions on that particular folder. I have tried using other folders, all with the same results. Also, I am an Administrator on the PC, so I have full permissions. I have tried running the SQL Server services as myself and Local System. Same results both times. Co-workers with apparently the same setup are able to create the distribution database without a problem.
Has anybody had this type of problem?
Thanks,
Evan
hi I have this asp sql statement which works fine but I want to use an spl adapter instead as I am enabling paging in details view. I am stuck in one place. How do I declare @listing as I have done in the select parameter in the asp sql statement? Where in ths sql adapter do i declare it? listing value is derived from a dropdown box when the user makes a selection.
This is the sql statement i want to declare in the adapter.<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" SelectCommand="SELECT UserDetails.UserId, UserDetails.FirstName, UserDetails.LastName, UserDetails.City, aspnet_Membership.Email FROM UserDetails INNER JOIN
aspnet_Membership ON UserDetails.UserId = aspnet_Membership.UserId WHERE Category = @Listing">
<selectparameters>
<asp:controlparameter name="Listing" controlid="SearchListingDropDownList" propertyname="SelectedValue"/></selectparameters>
This is the adapter so far which gives me an error to declare scalable value @listing. How do i incoporate the select parameters below?
Function getTheData() As DataTable Dim DS As New DataSet()
Dim strConnect As New SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ConnectionString)Dim objSQLAdapter As New SqlDataAdapter("SELECT aspnet_Membership.UserId,UserDetails.FirstName + ' ' + UserDetails.LastName AS 'Customer',UserDetails.City, aspnet_Membership.Email FROM aspnet_Membership INNER JOIN UserDetails ON aspnet_Membership.UserId = UserDetails.UserId WHERE Category = @Listing", strConnect)objSQLAdapter.Fill(DS, "aspnet_Membership,UserDetails")
Return DS.Tables("aspnet_Membership,UserDetails").Copy
End Function
www.mylookmeup.co.uk
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables.
How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.
Hi All
I have Computer is setuped SQL Server 2000 and SQL server 2005 when I restore SQL Server 2005 it's OK but when it's not OK when I restore in SQL Server 2000. The Error as follows:
Please help me to solve this problem. Thanks
We have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.
We have the following setup:
SQL Server 2000
- DatabaseA
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseA
- DatabaseB
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseB
SQL Server 2005 Standard
-asdfUser is NOT Setup as a user yet.
-We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.
-We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.
-We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.
-When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.
I assume this is a bug... any word on a fix?
Does Sql Server Compact Edition 3.5 support RDA synchronization with Sql Server 2000 database?
Hello:
I have an old database created in SQL Server 2000,
now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams.
Please advise on how to make it work....
Thank you
I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer. Anyone knows why this is happening?
View 3 Replies View Related
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.