I am trying to use the ADO.NET bridge to connect to an old database on RISC6000 via an ODBC connection, but I receive an error.
So, I would use a script component to retrieve data directly from an ODBC connection, using a stored procedure.
Any sample code ?
Any idea ?
Thanks
You can use the DataReader source in the data flow as another option connecting to an ODBC source.so im trying to connect to an odbc source and use ado.net to pass some sql queries and then write back into this odbc connection. i am aware that ssis does not have direct capabilities to do this, but i wanted to see if anyone knew of generic help docs/url's that show how to do this? i am new to ssis, and am just trying to get general information.
See Douglas post here:The code is also in SQL Books Online.|||
to be honest with you, i did see that thread you mention above, probably the only thread with realistic information. I am looking for something specifically in C#.
I am also wondering if there is any way to not have the SQL Command line filled for the ODBC connection source and use ODBC and a combination of ADO.net? Anyone have success with this?
I did search books online for the particualar phrase mentioned in the above thread..not too helpful.
Just wondering if anyone has had success writing C# ado.net to read and write using ODBC as a source?
thanks!
|||If that's what you are after then I would try a dev forum if I were you: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=12&SiteID=1
-Jamie
sqlI'm using sql serser 2000, CRecordset, CDatabase, ODBC, VS.NET 03, VC++
How cat I get from my data base ntext or nvarchar value?
if I get varchar everything is ok, but not nvarchar..(or text field type).
I need help please with this.
maybe the solusion is to cast from ntext to text?
How can I casting this in my statment?
// activity as ntext (16)
SqlString = "SELECT activity FROM tasks WHERE status_id=1";
What is the code which works for you in the case of varchar? And what doesn't work exactly with nvarchar?
Please provide an excerpt and perhaps we could find a solution.
Thanks,
I'm using sql serser 2000, CRecordset, CDatabase, ODBC, VS.NET 03, VC++
How cat I get from my data base ntext or nvarchar value?
if I get varchar everything is ok, but not nvarchar..(or text field type).
I need help please with this.
maybe the solusion is to cast from ntext to text?
How can I casting this in my statment?
// activity as ntext (16)
SqlString = "SELECT activity FROM tasks WHERE status_id=1";
What is the code which works for you in the case of varchar? And what doesn't work exactly with nvarchar?
Please provide an excerpt and perhaps we could find a solution.
Thanks,
quote:
> Consider using a DSN-less connection. Then there is nothing to
quote:
> 313295 HOW TO: Use the Server Name Parameter in a Connection String to
> Specify
> http://support.microsoft.com/?id=313295
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Hey
I have developed a asp.net 2 system on my local machine using a local sql2000 server.
Now i'm trying to deploy it to a windows 2003 server, using a sql2000 server on another windows 2003 server. I thought the only thing i had to do was to create a ODBC connection to the sql server, and change the connectionstring. But no :)
What kind of changes do i need to do. Do i need to change the dataprovider.
I get this error:
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)
But i don't use a sql 2005 server.
Pleace help :)
When I got this error the reason was SQL Server service in SQL Server 2000 was off but I had SQL Server 2005 installed also so check to see if your SQL Server service is on. Hope this helps.|||
I can connect to the database through enterprise mananger, and if i create an ODBC connection. So i'm quite sure the service is running.
But i'm not sure how to connect to a shared sql server on another server. Do i need to use ODBC? Do i need to change some providers? How would the connectionstring look like.
Thanks
|||No you don't need ODBC to connect to SQL Server because the ODBC standard is very old. Try the link below for connection strings. Hope this helps.
http://www.connectionstrings.com
|||That's what i thougt to. But i can only get my application to work when using my local sql server. If i try to use a sql server on another server i get this error.
But is this correct:
Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
Where Aron1 is servername(not sql server name)??
Pubs is the database.
Is this enough information?
Thanks for your help.
|||Aron1 is the SQL Server instance name and all the information you need is in that site but if you need to know more about connection and SQL Server ANSI SQL permissions try the link below for a Microsoft provided tutorial. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp
|||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)
Change to the TCP/IP Provider and see if your problem goes away.
|||Found my problem. A firewall on the server was blocking this kind of traffic.
This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.
Below is the section of code that fails:
<code>
Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader
Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()
_SQLCommandRequest.CommandText = _spReportParms
_SQLCommandRequest.CommandType = CommandType.StoredProcedure
' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader
' make sure that we have something first.
If Not drReportParameters Is Nothing Then
' find out if we have any rows returned.
If drReportParameters.HasRows Then
' read each of the rows looking for the respective value.
While drReportParameters.Read
' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then
' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select
End If
End While
End If
End If
Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)
Finally
' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If
End Try
End Sub
</code>
Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?
_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.
Steven
|||
Understood. Thanks for the explanation.
So all I have to do is make everything local, non-shared then right?
Except for the main sub procedure declaration below which has to remain shared right?:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _
Am I on the right track?
|||Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.
|||
Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.
This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.
Below is the section of code that fails:
<code>
Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader
Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()
_SQLCommandRequest.CommandText = _spReportParms
_SQLCommandRequest.CommandType = CommandType.StoredProcedure
' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader
' make sure that we have something first.
If Not drReportParameters Is Nothing Then
' find out if we have any rows returned.
If drReportParameters.HasRows Then
' read each of the rows looking for the respective value.
While drReportParameters.Read
' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then
' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select
End If
End While
End If
End If
Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)
Finally
' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If
End Try
End Sub
</code>
Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?
_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.
Steven
|||
Understood. Thanks for the explanation.
So all I have to do is make everything local, non-shared then right?
Except for the main sub procedure declaration below which has to remain shared right?:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _
Am I on the right track?
|||Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.
|||
Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.
