How to use Microsoft Data Link(Udl) type file to connect with SQL Server using SQL Native Client,If i use OLDEDB Connection i can make connection using UDL file with Database ,but how can i use a file for a connection using SQL Native Client.Is there any other data link file that can be used to connect with SQL server using SQL Native Client.
View 5 RepliesOn Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
Hi
i connect to remote Integration Service. i configure server for remote connection( on component services and DCOM config , ... ) .and now i can connect to Integration Service remotely and correctly.
but when i expand Stored Package and then click to expand MSDB this error will hapen:
login failed for user ... .(microsoft sql native client )
please help me
thanks in advance
Hi there,
I have a Data Flow task which uses an XML File Source with six parellell Outputs, each going firstly to a Data Conversion Task, then the results of each end in a SQL Server Destination Object. (All using the SQL Native Client)
To eplain this further, the Xml file contains 6 different types of elements, the Dataflow splits out each type of element and processes them into different tables. The Data Transformation object exists only because the XML fields are Uni-code and the table fields are VarChar not nVarChar.
Initially using this setup I found that the Connection would timeout using the SQL Native Client so I changed the Timout on the Destination Objects to 0. This fixed the problem to some degree, however at present I can run the Pakage using the Visual Studio enviroment and everything works fine, no problem. When I run the Dtsx file using the SQL Server Agent, I end up getting the error below...
Error: 2007-12-14 14:33:19.16 Code: 0xC0202009 Source: Import XML File to SQL SQL - CP [2746] Description: 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: "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
I understand that this error is somewhat of a 'catch all' and that the way the Native SQL Server Connection object works makes Error Capturing difficult. I have tried a few things which I will list as I'm sure they will be suggested...
I have played around with the 'MaxInsertCommitSize' property of the SQL Server Destination Objects to no avail (IE, changing to 50000, 10000, 1000 all of which resulted in the same problem)
I am running the ssis pakage from the server which is the destination
As mentioned above the Timeout on the SQL Server Destination Objects is set to 0
What I have already mentioned and still don'tt quite understand is that I can run the job successfully from the Visual Studio enviroment but as a job run off the SQL Server it fails...
Can
Hi~.
First, is it possible to access(read/write) to the excel,csv or my-own-formatted file using OLE DB(SQL Native Client)?
If possible, how?
Hi!
I've a computer with W2K SP4 installed. Previously SQL Express 2005 worked but according to the user it "just stopped working".
When selecting database on the server in the "Data Link Properties"-window he is given the following error message:
"Microsoft Data Link" "Login failed. Catalog information cannot be retrieved"
followed by
"Microsoft Data Link Error" "Connection failure"
Note: He is not given the error message "Server does not exist or access denied!"
- The SQL service is started (can be stopped and restarted without problem)
- I've re-installed SQL Express.
- I've checked and is certain the server name is correct (i.e. computernameSQLEXPRESS)
Any ideas here because I'm getting desperate?
Thanks in advance,
Peter Hartlén
Dear all,
we have tables with many image columns. We fill these image columns via ODBC and SQLPutData as described in MSDN etc (using SQL_LEN_DATA_AT_EXEC(...), calling SQLParamData and sending the data in chunks of 4096 bytes when receiving SQL_NEED_DATA).
The SQLPutData call fails under the following conditions with sqlstate 08S01
- The database resides on SQL Server 2000
- The driver is SQL Native Client
- The table consists e.g. of one Identity column (key column) and nine image columns
- The data to be inserted are nine blocks of data with the following byte size:
1: 6781262
2: 119454
3: 269
4: 7611
5: 120054
6: 269
7: 8172
8: 120054
9: 269
The content of the data does not matter, (it happens also if only zero bytes are written), nor does the data origin (file or memory).
All data blocks including no 7 are inserted. If the first chunk of data block 8 should be written with SQLPutData the function fails and the connection is broken. There are errors such as "broken pipe" or "I/O error" depending on the used network protocol.
If data no 7 consists of 8173 bytes instead of 8172 all works again.
(Changing the 4096 chunk size length does not help)
Has anybody encountered this or a similar phenomenon?
Thank you
Eartha
I am starting to see this error appear randomly? This is on an application that uses ASP, but its been working fine for months... I'm using SQLExpress 2005 on Win2003 IIS6Microsoft SQL Native Client error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.I'm trying to figure out why its happening all of a sudden? Any ideas very appreciated...
As long as the output file is written to the local client, it completes successfully. But after changing the UNC path to another server with or without fully qualified names it is unable to open BCP host data-file. (sample shown below)
-----------------------------------------------------------------
DECLARE @FileNamevarchar(2000),
@bcpCommandvarchar(2000)
SET @FileName =REPLACE('\stladminy_drive ransfer
maffrandauthors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand ='bcp "SELECT * FROM FKIAcctReceivable.dbo.PurchaseOrder" queryout "'
SET @bcpCommand =@bcpCommand + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
---------------------------------------------------
xp_cmdshell has been enabled and the user has sysadmin rights.
Anybody have the same problem and willing to help?
The below error happens when trying to use the SSIS Transfer SQL Server Objects Task to transfer objects. Related to (Bug?? Transfer SQL Server Objects Task - error when copying Views )?
--------------------
Error: 0xC002F325 at Transfer Data (NEW) Pre, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Task failed: Transfer Data (NEW) Pre
Hi try to connect with driver {SQL Sever Client} but i can't
I'm have SQL Server 2005 Express SP2
Error connecting to the ODBC server:[Microsoft][SQL Native Client][SQL Server] Changed database context to..
Please Help me
Thanks
Hello,I'm trying to install the SQL Server 2005 Standard edition and while I can easily install the first part of the package, the
document.write(L_LINK_RUNINSTALL_VAR_TEXT)
Server components, tools, Books Online, and samples, when I click
to install the Run the SQL Native Client Installation Wizard it tells me this:This Microsoft sql server native client package is not supported on the current processor type
I have Windows XP64bit with IIS running on 32bit now. Why does it do that and how I can get around it ? :) Thanks.
We scheduled lot of SSIS packages on 64 bit server and most of the time they are running fine. Some times we are getting below error message on any random package. When we re-run its working fine to success. This is repeating once in a week on different packages.
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_TblObject'. Cannot insert duplicate key in object 'dbo.tblObject'."
Please let me know if you have any solutions?
I have tried this on several different operating systems (VISTA and XP ) and several versions of SQL NATIVE CLIENT including 2005.90.3042.0
I have a 64 bit "SQL NATIVE CLIENT" connection that fails. The exact same connection and code succeeds on 32 bit.
My customer and I prepend "oledb:" to the connection string and it starts working.
FAILS on 64 bit:
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Wrigley_Test;Data Source=MONGO;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RIPTIDE;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
WORKS
oledb:Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Wrigley_Test;Data Source=MONGO;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RIPTIDE;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
I debugged my code to the point that I know it is happening when I call CreateAccessor for my SQL statement.
m_hr=m_pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, GetCols(), (m_pDBBinds+IsBookmarked()), 0, &m_hAccessor, NULL);
Error:
Microsoft SQL Native Client: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Does anyone have any suggestions?
I cannot connect to my SQL2005 server using the old SQL ODBC drivers, I have to use the Native client drivers. The database I am trying to connect to is a SQL 2000 db I just attached. Its owner is a SQL user login, which works fine and can connect remotely.
Thoughts?
Possibly related: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96732
Hi,
I have two server instances of SQL on my server MYSRVR: default (SQL 2000) and named SQLX (SQL 2005).
I've been using these strings ok for the default (SQL 2000):
"Server=MYSRVR; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
"Server=192.168.1.128; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
and these for the named (SQL 2005)
"Server=MYSRVRSQLX; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
"Server=192.168.1.128SQLX; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
But then adding the SQL port 1433 works for the default:
"Server=MYSRVR,1433; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
"Server=192.168.1.128,1433; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
But this, for named (SQL 2005) actually connects to default. The SQL port is causing the name SQLX to be ignored.
"Server=MYSRVRSQLX,1433; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
"Server=192.168.1.128SQLX,1433; Database=mydb; Network=dbmssocn; Integrated Security=SSPI "
Anyone any ideas?
With "bcp MyDatabase.dbo.MyTable out C:MyFile.Dat -n -T" command line, I could get an exported data file. And I can also import this file into MyTable using 'BULK INSERT MyDatabase.dbo.MyTable FROM 'C:MyFile.dat' WITH (DATAFILETYPE='native');' query statement.
Now, I want to make my own data file just like made by bcp above. Although I could make file of 'char' type, 'native' type file is needed for performance and other reasons. And the format file should not be used.
Any one help?
Guys,
I want to know how to use Microsoft Data Link File with all the
information of the database,username,password to connect to SQL SERVER 7.
Where is this file stored ?
Any feedback will be appreciated.
Thanks
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
I have developed an application in C#, and it uses both a SQL server database, and as part of its operation it opens up a Foxpro database. I am using Odbc to connect to the fox pro database and everything wortks just fine on my devleopment machine. However when I deploy my application I am getting the following error text when ever I create the form that does the Odbc work.
************** Exception Text **************
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.Odbc, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.Data.Odbc, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
I have absolutely no clue why I am getting this error. I have insured that the latest MDAC was installed ( it is XP Pro so no updates there ) I have made sure that I have the latest Fox Pro Odbc driver installed as well and I can create the DSN just fine.
Can anyone point me where to look next ? Im truly stumped here.
Thanks in advance for any and all input!!!
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.
A test installation script I ran on my dev rig seems to have broken the OLE DB Provider for Sql Server. The uninstall must have deleted a DLL or unregistered a lib. When I try to connect to a SQL database from this machine now get the following error:
==
Microsoft Data Link Error
Test connection failed because of an error in initializing provider. Unspecified error.
==
It is not the connection string because this was working fine prior to me running the test installation script.
Can you give advice on how to repair the SQL Server data provider for WinXP SP2?
I have tried to re-install MDAC 2.8 with no success due SP2 diallowing this. I uninstalled SP2 and re-installed MDAC 2.8 but the problem remains.
I'm all out of ideas, so any help would be appreciated.
Thanks!
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
View 78 Replies View RelatedHi,
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Thank you
I am thinking I must be doing something wrong..
I have dozens of packages that work as follows (high level... not listing all the steps just those relevant to this question)
- Get list of files in directory
- Join list to list of already imported files
- Those not imported put into an ADO.Net object
- Loop through ADO.Net record (which contains the filename) and import each file.
I just set the connection string of the flat file to be the variable in the loop (expressions.. connection string). Pretty standard stuff. Now I tried to do the same with a file connection (not a flat file) becuase I have a source that is from a mainframe and I had to write a custom source script and its not working. Basically the source script uses
oRead = oFile.OpenText(Me.Connections.FileConnection.ConnectionString)
And it opens the same file over and over (not ever changing as the ConnectionString expression changes like it does for flat files) and imports it even though I have verified the loop is correctly looping through all the different files.
Any thoughts as to what I am doing wrong?
Hello,
I was hoping someone had this problem and has a solution... :)
Some background info:
SQL 2005 Enterprise x64
Windows 2003 R2 x64 Enterprise (clustered)
Oracle client 10.2.0.3 (latest as of 2/2/07)
The issue I'm having is I have a link server setup within SQL 2005 connecting to a Oracle 8 database server. The Oracle client I'm using is 10.2.0.3 (latest version from Oracle). From the SQL 2005 server I'm able to query the remote oracle server through the link server I had setup if I query the table directly. If I perform a query to a view on the Oracle server I'm able to see the colum names, but not the data. This only happens when I'm using the Oracle client, if I use the MS client for Oracle it works fine OR if I use SQL Plus.
Now, you're probally asking, why not just use MS's driver... Well, I would If I could, but Microsoft hasn't provided a 64 bit driver for Oracle which I need to create the link server in SQL.
Any help would be appreciated.
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
Hi there,
I have inherited a databse and am building a new website to go wiht it.
There is a file upload page which will upload images to a directory. I need to insert into the database retrieve the id just added then upload the image renaming it in the format locID(QueryString)_ImageID(retrieved from database).jpg
The page has a file upload control and a button.
I am trying to write my code behind so that when the button is clicked it inserts location id into the images table retrieves Image id. Renames the file and uploads it to the images folder.
II think i need to call the routine from another routine for the button click but the signatures are different, where am i going wrong? or for that matter have i been pissing into the wind for the last 4 hours?
CODE BEHIND
Imports System.Data
Imports System.Data.SqlClientPartial Class admin_Add_Images
Inherits System.Web.UI.PageProtected Sub UploadImage(ByVal Sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim LocationId As String = Request.QueryString(ID)
' create a new SqlConnectionDim NewConn As New SqlConnection
NewConn = New SqlConnection("server=desktopsqlexpress;uid=xxxxxx;pwd=xxxxxxx;database=MYLOCDEV") 'OleDbConnection i
' open the connection
NewConn.Open()Dim MyInsert = New SqlCommand("INSERT into image([LocationID]) VALUES (@LocationID); SET @NewId = Scope_Identity()")
NewConn.Close()
If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
'RENAME THE FILEDim newid As Integer = e.Command.Parameters("@NewId").Value
Dim fn As String = (LocationId & "_" & newid & ".jpg")Dim SaveLocation As String = Server.MapPath("oicImages") & "" & fn
Try
File1.PostedFile.SaveAs(SaveLocation)Response.Write("The file has been uploaded.")
Catch Exc As ExceptionResponse.Write("Error: " & Exc.Message)
End Try
ElseResponse.Write("Please select a file to upload.")
End If
End SubProtected Sub Submit1_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Submit1.Click
UploadImage()End Sub
End Class
Hi,
I have a flat file, comma-delimited, with strings in double-quotes.
In the connection manager for the file, I have specified that the Text Qualifier = ""
However, in the preview tab, it still shows the strings as surrounded by the quotes, e.g. "mycol1" whereas it should show mycol1 without the quotes.
Next, when I examine the data in the database after the load, it's messed up there also.
"mycol1" ends up in the database as "mycol1
"mycol2" ends up as "mycol2
This is not right.
I have format set to delimited, header row delimiter crlf, etc.
Any ideas?
Thanks
Hi,
I have a package A which is copied from another existing package B as most of the data structure and ETL mappings are same.
What I need to change in Package A is to change the file in Flat File Connection Manager. I can change it in the conneciton manager editor. However, it is automatically changed back to the previous one everytime when I try to Save All or run the package.
I also tried to copy/paste this Flat File Connection Manager in the same package but samething happen. The package file is not set 'Read Only" so anything else can Saved well except for the File name in connection manager.
Is this a bug? It would be very appreciated if anyone can give me any idea about this.
Thanks,
Jenny
Hi,
I have a task to traverse a folder of CSV files of same format and then populate into one sql server table.
Is there a way where I can change the source CSV file name runtime using FOR EACH loop container for flat file connection manager ?
any help would be much appriciated.
Thanks,
Furrukh Baig
Hi,
I'm attempting to use a variable to define the file name in my flat file connection (for error files in my SSIS package).
I have it defined as follows:
@[User::ValidRootFolder]ConditionConditionSourceError.csv
However it throws an error 'the filename, directory name, or volume label syntax is incorrect'.
If I put the value of the variable in explicitly it works, so I know my variable name is correct - am I just not calling the variable properly?
Thanks