SSIS Errors During Connection To Pervasive V9.11
May 23, 2007
We just upgraded our accounting packages database from Pervasive 8.1 to V9.11. MY SSIS extract packages were working just fine. Now I am getting several erros stating that some columns cannot be found:
For example, in the [sysdtslog90] table, I have these errors:
Column "CreditLimitAmt" cannot be found at the datasource. or
Column "RsrvRptky" cannot be found at the datasource. (from a different pkg)
The table schemas did not change, the columns still exist. The SQL I use also runs just fine in the client interface.
Has anyone experienced this? The bottom line is that now the extract packages are broken.
TIA.
View 7 Replies
ADVERTISEMENT
Aug 7, 2006
Hello,
I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :
SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0
SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.
Linked server name : TestPervasive
Provider : Microsoft OLE DB Provider for ODBC Drivers
Product name: Pervasive
Data source: PervasiveDB
Location: PervasiveDB
Catalog : DataDB
I get the following error when I try to query the data to test if the linked server I just created is successful:
query : SELECT * FROM testpervasive.DataDB..schedule
error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "TestPervasive" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TestPervasive".
Anybody can help me on this one? I would greatly appreciate it !!
View 3 Replies
View Related
Aug 28, 2006
In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.
In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net ProvidersOdbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DBSQL Native Client) to the SQL database. Both databases reside on the same machine.
I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.
Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.
This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie
View 5 Replies
View Related
Nov 27, 2006
I dont know why but.. I keep getting an sql error here..
private void button1_Click(object sender, EventArgs e)
{
SqlConnection sqlcon = new SqlConnection(
@"Server=(local)sqlexpress;Integrated Security=True;" + "Database=mydb" );
sqlcon.Open();
}
this is the erro that I keep getting....
{"Cannot open database "mydb" requested by the login. The login failed.
Login failed for user 'XLORDT\xlordt'."} now.. Im using sqlexpress the one that comes with visual studio.. what do i need to do to fix this?
View 6 Replies
View Related
Jan 11, 2007
I'm attempting to configure a new Microsoft OLE DB Provider for DB2 connection using the provided Data Access Tool and Wizard. I'm configured for DB2MVS TCP/IP and have my Catalog and other defaults set. When I go to connect I get the following:
Could not connect to data source 'New Data Source':
An internal network library error has occurred. A network level conversational protocol error has occurred. SQLSTATE: HY000, SQLCODE: -343
The port I've configured is what is used when we use the IBM OLE DB and ODBC drivers.
I turned on tracing and tested the connection again and recieved only the following in a file that got named DB2MSG1.ATF. We have been struggling with this for awhile, any help is greatly appreicated.
� �-� � �����/�0�>�P�messagesync.h�DRDADriverConnect�DRDA AR message: Name: PRCCNVRM, Severity: Error, Diagnostic: DSNLZSPA L, Database: DB2DEVP�þÊ0�:���
View 1 Replies
View Related
Oct 9, 2006
I have a Merge-Pull subscription setup between two SQL 2005 databases. While watching the status messages display for the subscription, I am constantly seeing the following message being displayed:
"The merge process could not connect to the Publisher '{server}:{database}'. Check to ensure that the server is running."
It will display for anywhere between 15 seconds to a few minutes, then it will display the standard "Waiting 60 seconds..." message which is what I'm used to seeing when all is working well.
There doesn't seem to be any network issues that I can identify. I ran a quick script to run ping continuously against the publisher server and verified that the server was constantly "pingable", even when the above message was displayed (which, by the way, was being displayed by using SSMS while connected to the publisher).
Does anyone know what would cause this message to appear all the time - for extended amounts of time, with occasional glimpses of a non-error status?
View 6 Replies
View Related
Dec 31, 2007
When the databse file's "copy to output" property is set to "copy always" my data is not saved and when I
change it to "do not copy" it gives me the error "cannot attach the database file", and does not open my connection.
I dont know what to do.....
View 1 Replies
View Related
Apr 24, 2007
I'm currently using database mirroring as a hot-standby, without a witness. Everything appears to be running fine, but I'm receiving intermittent errors that appear to state that my servers are loosing connectivity, thus disabling (inactivating) the mirror until the connection has been reestablished.
The errors are listed below, basically, I note a block of log entries for each mirrored database stating that mirroring is inactive for the database(s), next Error 1474, next the connection error, then another block of log entries for each mirrored database stating that the mirroring session is once again active.
Database mirroring is inactive for database 'NetRS'. This is an informational message only. No user action is required.
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://SQL01NRS:7024'.
Database mirroring is active with database 'Telephony' as the principal copy. This is an informational message only. No user action is required.
I have confirmed that the sessions are in sync, so am not concerned about the data - but would love to get rid of these errors / disable / enable. I'm assumming that if I had setup a Witness, that I would see the mirrored database flip-flopping back and forth between Principal and Mirror.
Please let me know if I can provide any additional details to assist ...
View 5 Replies
View Related
Jul 19, 2001
Hi,
Is it possible to run MS SQL 7 on the same server with Pervasive SQL?
View 1 Replies
View Related
Feb 6, 2008
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel source will fail.
I have it so that for each iteration if the transform was successful the file is moved to an archive directory, and if it fails the file is moved to a different directory.
But I don't want the package to be marked as failed. For the control flow tasks I have set the individual components to FailPackageonFailure = False, and for the Data Flow tasks I have set ValidateExternalMetadata = False.
It no use to set the MaxErrorCount higher because I can't guarantee how many files will be processed and how many might fail.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
Thanks
View 6 Replies
View Related
Feb 28, 2008
I have written a CLR function in C# to access a sql server different from the current server where I have deplyed the assembly.
DataSet ds = new DataSet();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
string connString = "Data Source=SQLSERVER;Initial Catalog=DATABASE;User ID=userid;Password=password;";
SqlConnection sqlConn = new SqlConnection(connString);
sqlCmd.Connection = sqlConn;
sqlConn.Open();
try
{
SqlDataAdapter sda = new SqlDataAdapter();
sqlCmd.CommandText = "StoredProcedureName";
sqlCmd.Parameters.Add("@Param1", SqlDbType.VarChar);
sqlCmd.Parameters["@Param1"].Value = sParam1;
sda.SelectCommand = sqlCmd;
sda.Fill(ds);
}
finally
{
sqlConn.Close();
}
return ds;
I was getting the following error
"Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
so I tried doing the following
SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();
and then I get this error
"Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
Any idea how to remedy this ?
Thanks in advance
- Kan
View 4 Replies
View Related
Jul 20, 2005
I am using Maximizer Enterprise 8.0 as my companies CRM solution. Currentlywe are using the included Pervasive SQL that shipps with the product. Isthere someone out that who could tell me if there are any performancebenefits to switching the Pervasive Engine with a Microsoft SQL engine.RegardsJesus
View 5 Replies
View Related
Sep 9, 2006
Hello,I have a Win2K3 Server with SS2005 developers edition. I am working on aWindows XP Pro workstation which has SQL Server 2000 installed as well asthe SQL Native Client. I'm using an MS Access ADP to connect to the serverand for some reason it's extremely slow, even to the point of throwing timeout errors and "can't generate SSPI context" messages. I've hit the MSwebsite and found info on the SSPI error, but none of the items thatgenerate the error apply to my situation. I've tried using the surface areamanager to change the connection to name pipes, name pipes and tcpip etc,but no luck.Is there anything I should be looking at or any known issues that wouldaffect this kind of performance?Thanks!Rick
View 2 Replies
View Related
May 15, 2008
Hi,
I have an reporting services (SSRS 2005) installed locally and I am trying to access it locally through : http://localhost/reportserver, but all I get back is this page.
localhost/ReportServer - /
Microsoft SQL Server Reporting Services Version 9.00.3054.00
Also when I try to connect to reporting services through SSMS I get the following error message.
Cannot Connect to Server ServerNameSQL2005
Unable to connect to the remove server (Microsoft.SQLServer.Management.UI.RSClient)
No connection could be made because the target machine actively refused it(System).
About my set up, I have SQL Server 2005 (SP2) installed in a named instance called SQL2005, I have reporting services set up to run under local system and I have gone through the RS config tool and everything in there appears to be correct. U double checked in IIS that the report server folder is "ReportServer" and not "ReportServer$SQL2005". I have set up full read write permissions to all the virtual directories, to my domain account which is also a local administrator. The user logs in under windows authentication (marked in the directory security tab in IIS, for the virtual directory & the default website).
Whenever I start the service however I get the following errors in the windows application log:
Report Server (SQL2005) cannot create the trace log C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__05_15_2008_10_27_52.log.
&
Report Manager cannot create the trace log C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServerWebApp__05_15_2008_10_27_07.log.
Also I am unable to deploy my SSRS 2005 project from BI studio with more errors, I get the following exception thrown in a windows. (I am trying to deploy to the root "ReportServer" folder, not a sub folder.
A connection could not be made to the report server http://localhost/reportserver.
System.Web.Services.Protocols.SoapException: Server was unable to process request ---> System.InvalidOperationException: Unable to generate a temporary class (result=1)
error: CS2001 "Source File "C:WindowsTempc-zsc7th.0.cs" could not be found.....
I don't have sharepoint installed, I do have the dashboard designer for Performance Point Server 2007 installed though.
I have been on this thing for hours, I have tried appending "$SQL2005" on to the end, stopping and starting RS Service multiple times. Change all the permssions in IIS so that the user can full control over the IIS virutal directory. I am at a loss at what else I could try.
Any Idea's?
Thanks!!
View 1 Replies
View Related
Apr 21, 2008
Hi all-
I setup a new ODBC connection to MS SQL Server 2000 on Windows XP and keep getting the following errors when I try to
either register a new SQL Server 2000 group within Enterprise Manager or test the new ODBC connection:
Current configuration
- MS SQL Server 2000
-Windows XP
-ODBC 3.85.1117
Errors:
I. Within SQL Server 2000 Enterprise Manager
Error: SQL Server connection open
II. When I test the ODBC connection:
Microsoft SQL Server ODBC Driver Version 03.85.1117
Running connectivity tests...
Attempting connection
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.
TESTS FAILED!
Now here is what has me totally confused:
I can use the command line ISQL to access the database on the server and IP is all correct and port is available.
Any tips how to get this ODBC working?
Thanks
Ben Prusinski
View 1 Replies
View Related
Oct 29, 2007
For some reason, when I try to delete old DB connections from the connection manager, it causes compilation errors because it is not removing the references from the meta data. I get Object ID errors where it is still trying to find the connection after it has been deleted. I have tried going to the code behind and searching then deleting the invalid reference, but that still does not work. I have a connection for 2 totally different databases that are no longer being used in the SSIS package and I would like to get rid of them.
Suggestions?
View 5 Replies
View Related
Aug 1, 2007
hi,
I am trying to use SSIS package to trasfer data and hitting road block for
[SQL Server Destination [37]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'GlobalDTSQLIMPORT' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
How can i resolve this!!
thanks
View 2 Replies
View Related
Dec 11, 2006
My company is trying to determine if they need to upgrade from our current Platinum accounting application that uses pervasive SQL V8 to another version of Platinum that uses SQL server 2000.
My manager wants a in-depth analysis comparing pervasive SQL V8 to SQL server 2000.
I have searched the web extensively but information is scarce.
Are there any articles or case studies that do an indepth analysis comparing both databases? Can someone please help me. It would be greatly appreciated.
View 2 Replies
View Related
Apr 10, 2008
How can i format date in pervasive sql?
The day in my table is stored as "yyyymmdd"
I want to search for records with curdate().
select Curdate() returns 'dd/mm/yyyy'
Any help will be appreciated.
Thanks in advance
View 3 Replies
View Related
Feb 25, 2008
I am trying to write a stored procedure for the first time. I am using pervasive 9.0. I am tying together a secure desktop messaging system with a web application. I will be passing the data that matches what the desktop app needs to fill in the tables.
The issue is that the desktop app uses tow tables to do messaging. One table (PDSMSGC) that stores the relevant information about the message: sender, recipient, notes, phone number, etc. The other table (MEMOS) stores the message text and references back to the "PDSMSGC" table (The memos table in the desktop app is used for more than just the messaging system.)
So both tables have unique IDs that are referenced in both tables. For example:
MEMOS.ParentID = PDSMSGC.MessageID
PDSMSGC.MemoID = MEMOS.MemoID
here are the column definitions:
My question is: Does this seem like a properly formed set of commands and can you think of a better way to do this?
Here is what I have written so far:
CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR, in :PatientID UINTEGER, in :PatientName CHAR, in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);
BEGIN
DECLARE :NewMemoID UINTEGER;
DECLARE :NewMessageID UINTEGER;
INSERT INTO PDSMSGC (ToOper, MsgFrom, LinkID, PrimaryID, Phone, MemoID, DateTaken, TimeTaken, TakeOper, BeenRead) VALUES (:RecipientID, :PatientName, '2', :PatientID, :PatientPhone, :PatientID, CurDate(), CurTime(), 'WEB', '5');
INSERT INTO MEMOS (ParentID, FieldName, MemoText) VALUES (:PatientID, 'Xholder', :MemoText);
SELECT :NewMemoID = MemoID FROM MEMOS WHERE MEMOS.ParentID = :PatientID AND MEMOS.FieldName = 'Xholder' AND MEMOS.MemoText = :MemoText;
SELECT :NewMessageID = MessageID FROM PDSMSGC WHERE PDSMSGC.MsgFrom = :PatientName AND PDSMSGC.PrimaryID = :PatientID AND PDSMSGC.MemoID = :PatientID AND PDSMSGC.TimeTaken = CurDate() AND PDSMSGC.TakeOper = 'WEB' AND PDSMSGC.BeenRead = '5';
UPDATE MEMOS SET ParentID = :NewMessageID, FieldName = 'pmc:MemoID', MemoText = :MemoText WHERE MEMOS.MemoID = :NewMemoID;
UPDATE PDSMSGC SET ToOper = :RecipientID, MsgFrom = :PatientName, MemoID = :NewMemoID, BeenRead = '0' WHERE PDSMSGC.MessageID = :NewMessageID;
END;
View 2 Replies
View Related
Aug 23, 2006
Hi All,Thanks in advance for the help.We are considering a new Project. This project is going to be totallyweb based.Currently my company is using Pervasive SQL 2000i for there ERP needs.Will the existing Pervasive SQL 2000i itself serve the purpose ORshould I consider moving to MS SQL Server.Do assist me in terms of development also i.e. Is it easier to codewith Pervasive SQL 2000i or with MS SQL Server.RegardsSamson
View 1 Replies
View Related
Jul 20, 2005
Hiis anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ?ThanksMarcin Sobolewski
View 1 Replies
View Related
Nov 28, 2007
Is it possible to run MSDE and Pervasive SQL on the same machine ?
If possible but not recommended I'd really appreciate the reasons why it should not be done.
If not possible I'd also really appreciate hearing why.
Thanks in advance.
View 4 Replies
View Related
Aug 7, 2006
Has anyone successfully imported data from pervasive over the .net managed odbc bridge? Our admin has set up a odbc connection that works through Excel, but in SSIS the connection manager errors when trying to connect at runtime (the "test connection" button in the connection wizard reports that everything is ok though).
View 4 Replies
View Related
Aug 3, 2006
Hi,
As I was developing my SSIS package, I created several variables and tasks ( FTP, WMI Reader Task ). I am now cleaning up, deleting unwanted variables and connections in the design window. I save and build the package and when I load the package, I get warnings that these variables are referenced but can't find them and errors that the WMI connection is not found.
When a package calls a sub-package, it stores the absolute path of the child package in its dtsx xml file in a Connection String property. How annoying !!! . When I deploy this to another machine with a different file structure, it becomes a problem. Why can't it store the path relative to the parent package, which would be typically in a sub-directory under the parent ?
These last 2 days have been nothing but frustration and my deadline is slipping. Any help is appreciated.
Thx,
-chiraj.
View 13 Replies
View Related
May 25, 2007
Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.
I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.
After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.
Creating a Foreach Loop Container:
1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:ClientsDep CommBEABEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.
I. Created Excel Connection:
Created excel connection called, €œDynamic Excel Connection Manager,€? that initially pointed to one of the excel workbooks.
Went to the connection properties by right clicking the connection manager.
Expanded Expressions and clicked the ellipsis button to bring up property expressions
Chose Connection String in the Property.
Clicked the Expression Ellipsis button.
Put the following inside the Expression multi line text box:
A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties="" + @[User::ExtProperties] + """
Clicked the Evaluate Expression button to get the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
Clicked Ok button
Inserted a Data flow task inside the foreach loop container.
II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:
Set the Foreach loop container Delay Validation to true.
Set the Data Flow Task Container Delay Validation to true.
Set the Dynamic Excel Connection Manager Delay Validation to true.
Set the SQL Server Connection Manager Delay Validation to true.
Set the Package Delay Validation to true.
Package Locale ID set to English
Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.
Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".
I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile]
I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
I ran the package and received the following error in part: OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'E:ClientsDep CommBEABEA_Test_SourcePersonnelExpense_OCCs_051007.xls'."
I did not find anything helpful when I searched for the above errors and would very much appreciate anyone€™s assistance on this issue as this issue needs to be taken care of ASAP.
Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?
Your assistance in this matter is truly appreicated!
Thanks!!
Lee
View 7 Replies
View Related
Sep 10, 2007
I have been getting a recurring error while running the folowing script from an SSIS package. I have bolded the parts that I think may be of use. I didn't know if this would be a T-SQL or SSIS question, but thanks in advance for help.
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "declare @dbname varchar(200)declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
" failed with the following error: "Line 15: Incorrect syntax near '2003'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Execute SQL Task 1
View 4 Replies
View Related
Nov 27, 2006
We have deployed an SSIS package successfully to production. We needed to apply SP1 to fix a different issue and now have encountered a new problem. We have numerous Data Reader Sources in different Data Flow Tasks that connect to a IBM iSeries (DB2) source. Pretty simple extracts that have worked fine in the past. They pump the data into staging tables on the SQL2K5 instance running the package (64-bit).
After we applied SP1 however, all of the Data Reader tasks fail AFTER they successfully copy the records with the following error.
[iSeries Invoice Details [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)
If I delete the source and destination and recreate identical transforms, they work fine, but I don't feel like rebuilding all of the extracts. Any ideas! The problem occurs in all environments that we've tried.
TIA,
Michael Shugarman
P.S. I just tried the SP2 CTP, but that doesn't fix the problem.
View 2 Replies
View Related
Sep 20, 2006
When I have an alternet Data Flow in an event handler, caused by a record failing to be inserted due to a unique-key constraint violation, does this increment the number of errors, counting towards the MaximumErrorCount? How can I NOT count it as an error?
The thing is, I need to insert 300,000+ records each day, and some may be duplicates from data already in the table. So I set a unique key constraint on the table, and if during the load, it fails, it will trigger an alernate data flow to load the error records into another table. But if someone tries to load a file that already has been loaded, for example, all the records would be duplicates, which would be equivelant to 300,000+ errors, and I don't want to keep setting the MaximumErrorCount property higher and higher.
Is there any way to treat the error as "being handled" in the dataflow, so therefore doesn't treat it as an error? Or conversely, can I set the MaximumErrorCount property to 0 or -1 to accept all errors, no matter how many?
View 6 Replies
View Related
Sep 24, 2007
Hello,
I'm getting the following error running a package...
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message
ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
09/24/2007 15:00:00,Hourly Extract From OReSA,Error,0,INHSCTSTTOMVM82,Hourly Extract From OReSA,(Job outcome),,The
job failed. The Job was invoked by Schedule 7 (Hourly). The last step to run was step 1 (OReSA
Extract).,00:00:59,0,0,,,,0
09/24/2007 15:00:01,Hourly Extract From OReSA,Error,1,INHSCTSTTOMVM82,Hourly Extract From OReSA,OReSA
Extract,,Executed as user: INENVts_hia. hod call failed. End Error Error: 2007-09-24 15:00:58.93 Code:
0xC0047017 Source: dtProduceExtractFiles DTS.Pipeline Description: component "ole_srcExtractDB" (22) failed
validation and returned error code 0xC020801C. End Error Error: 2007-09-24 15:00:58.93 Code: 0xC004700C
Source: dtProduceExtractFiles DTS.Pipeline Description: One or more component failed validation. End Error
Error: 2007-09-24 15:00:58.93 Code: 0xC0024107 Source: dtProduceExtractFiles Description: There were
errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started:
3:00:01 PM Finished: 3:00:58 PM Elapsed: 57.75 seconds. The package execution failed. The step
failed.,00:00:58,0,0,,,,0
But it works on other machines and other SSIS packages are running ok on the offending box.
Any ideas?
Thanks in advance,
Tony.
View 4 Replies
View Related
Sep 10, 2007
I have been getting a recurring error while running the folowing script from an SSIS package. I have bolded the parts that I think may be of use. I didn't know if this would be a T-SQL or SSIS question, but thanks in advance for help.
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "declare @dbname varchar(200)declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
" failed with the following error: "Line 15: Incorrect syntax near '2003'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Execute SQL Task 1
View 5 Replies
View Related
Feb 4, 2008
Hi, I'm trying to set up an SSIS which uses a few VB scripts. I'm having an issue with the connections.
I have an existing SSIS which runs a few SQL stored proceedures and this works fine.
The script I am having an issue with returns the following error
'
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at ScriptTask_dbe60b31acf14201b5f012a4f1c608c2.ScriptMain.GetAppParameterCMD(String strParamaterName) in dts://Scripts/ScriptTask_dbe60b31acf14201b5f012a4f1c608c2/ScriptMain:line 49
at ScriptTask_dbe60b31acf14201b5f012a4f1c608c2.ScriptMain.Main() in dts://Scripts/ScriptTask_dbe60b31acf14201b5f012a4f1c608c2/ScriptMain:line 27
'
connection string is as follows.
ConnectionInfo= "Data Source=SQLM002Cdev;User ID=SQLCallManUser;Initial Catalog=Callmanager;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;
I have checked the UserID has the correct permissions on the dbo
Any ideas?
View 1 Replies
View Related
Jul 20, 2006
Hi All,
I am having a couple of problems with an SSIS package. The first is whenever I add a Data Viewer to one of my data flow tasks it crashes Visual Studio. This happens every single time. I have tried rebooting, but that didn't help. The second error I am getting is whenever I call ComponentMetaData.GetErrorDescription(Row.ErrorCode) from within a data flow script task it generates a "Catastrophic Failure" and indicates that it occurred when calling the GetErrorDescription method. I am not sure if something is corrupted or what the issue is. Does anyone have any insight?
Thanks,
Justin
View 3 Replies
View Related
