Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







OLEDB ERROR MSDA PROVIDER WHILE EXECUTING THE QUERY


hi ,

 I got the problem while executing the query..

 

OLEDB ERROR MSDA PROVIDER WHILE EXECUTING THE QUERY..

  can any one gimme the solution


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Error While Executing Insert Statement On DB2 Using OLEDB Provider
Hi,

          I am using simple data flow task to move data from SQL Server to DB2 using OLEDB provider on both ends. I am using OLE DB Command task to execute inserts on DB2. Following is my statement.

Insert into MMTSTLIB.INTIIF (IIFDTA) values (?)

In column mapping, correct column is assigned to parameter and column data type is unicode. Data viewers attached show correct data.

However when executed, it gives following error.

[OLE DB Command [138]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "Microsoft DB2 OLE DB Provider"  Hresult: 0x80040E14  Description: "Null values are not allowed in column or variable IIFDTA. SQLSTATE: 23502, SQLCODE: -407".

 

Please help.

Saeed

View Replies !   View Related
Error 7399: OLE DB Provider 'Microsoft.Jet.OLEDB.4.0' Reported An Error.Authentication Failed.
I am trying to create an excel file using openrowset in ms sql 2000.but i get the following error when I try to create the file...My stored procedure code is below as well...Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.Authentication failed.What can I do?-- here is my codeCreate PROCEDURE MCA_SP_CREATE_EXCEL @File_Name as varchar(50) = ''ASBEGINSET NOCOUNT ONdeclare @File_Name as varchar(50)DECLARE @Cmd varchar(1000)DECLARE @fn varchar(500)DECLARE @provider varchar(100)DECLARE @ExcelString varchar(100)-- New File Name to be createdIF @File_Name = ''Select @fn = 'C:Test.xls'ELSESelect @fn = 'C:' + @File_Name + '.xls'-- FileCopy command string formationSELECT @Cmd = 'Copy C:Template.xls ' + @fn-- FielCopy command execution through Shell CommandEXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT-- Mentioning the OLEDB Rpovider and excel destination filenameset @provider = 'Microsoft.Jet.OLEDB.4.0'set @ExcelString = 'Excel 8.0;Database=' + @fn-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'') select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'') select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')SET NOCOUNT OFFEND

View Replies !   View Related
7399:OLE DB Provider 'SQL OLEDB' Reported An Error
After linking of remote server at 2nd end like under -

Open enterprise manager-
Linked server - 192.168.0.200
It is added and shown under link server , I just click on table under link server and go error -

Error - 7399:OLE DB Provider 'SQL OLEDB' reported an error

Thanking all of you in advance, I am looking for your valuable help

R.Mall

View Replies !   View Related
How Can I Avoid That The OLEDB Provider Intercepts The Error?
I have an stored procedure which is called by using ADO and OLEDB. I wish that when an error occurs (such as when a contraint is violated or trying to insert NULL in a not-null column) the error message is stored on an output parameter and returned to the client, without the error being raised by the OLEDB Provider.

How can I do that?

Thanks a lot in advance.

View Replies !   View Related
Microsoft OLEDB Provider Error (with SQL Server And ASP)
Dear all,

Still i am having problem with connecting sql server with ASP (Intranet).

Following is the code which i am using for the connection now,

Set conn=Server.CreateObject("ADODB.Connection")
conn.open "DSN=aspfirst;Uid=;PWD=;"


The error mesage , generated by this code is attached with this mail. Please check the attachment and hel me in the same


Graceson

View Replies !   View Related
SQL Server OLEDB Provider Support Error
I have seen this question being asked couple of time in this forum. Now I'm in a different environment, so got to ask it again :-)

I installed XP embedded
release version with SP1, tools, MSDE (using the same machine for
database server) then started following through the tutorial provided
with the CDs. The installation is completed without errors.

As soon as I started adding component, or configuration I see this
error message, "SQL Server OLEDB provider support error" "Unable
to load SAL Server OLEDB povider resource DLL. The application cannot
continue."

What is the problem here? Your help is appreciated.

Sherry

View Replies !   View Related
Parameterized Query For Microsoft OLEDB Provider For Oracle Using OLE DB Source In SSIS
Hi,

 

Urgent Help required..........

Can anyone explain me steps how to parameterized query to send oracle.

If you know any other control which help to do this rather than OLEDB source.

Please let me know.

 

THanks

View Replies !   View Related
OLE DB Provider &&"Microsoft.Jet.OLEDB.4.0&&" For Linked Server Error
Hello all,
 
I want to create a table in MS-Access. So I have written the following query.
 



Code Snippet
select * from Openquery(KKACC, 'select * Into Temp from Table select 1 as col1')
 
 



But I am getting the following error.
 
 



Code Snippet
 
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC" returned message "Syntax error in FROM clause.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * Into Temp from Table select 1 as col1" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC".
 
 




How can I resolve this error. Does any one have any work arround for this..?
 
Thanks
-- Krishna

View Replies !   View Related
OLEDB Provider
Hi,

Does anyone know a good OLEDB provider for Oracle 7 other than the one of Microsoft and Oracle.

Thank

Martin

View Replies !   View Related
OLEDB Provider For SAS
Is there an OLEDB provider for SAS? Does anyone know where I could get one? Does it ship with the SAS installation? Thanks!

View Replies !   View Related
ERROR: Cannot Get The Column Information From OLE DB Provider &&"Microsoft.Jet.OLEDB.4.0&&" For Linked Server&&"(null)&&"
Hi,

I have been trying to export data from a Microsoft Excel 2007,  Tester.xlsx to a SQL Server 2005 table  as :

SELECT * INTO CUSTMAS FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:Tester.xls;Extended Properties=Excel 8.0')...[Providers$]

I have an error:

Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)"

View Replies !   View Related
OLEDB Provider Connectiontimeout
Hi,
if you establish a connection to SQL-Server (2000) you can provide a value for connection timeout (default is 15). For several reasons I have to set this value on 240. The problem is:
After building the connection the adoconection shows a value of 180 for this property. No idea where the OLEDB-Provider gets this value !!???
What is the reason for regretting my given value ?
Thanks in advance for your help

Regards, Alex

View Replies !   View Related
Has Anyone Used The DTSFlatFile OLEDB Provider With ADO?
Has anyone used the DTSFlatFile OLEDB provider with ADO?
What properties do you need to set for a delimited file in order to use it?

Darryl

View Replies !   View Related
SSRS With IBM's OLEDB Provider For DB2
 

Hi,
 
I am using SSRS connecting to DB2 via IBM OLEDB provider for DB2. When I view the reports by clicking the link from report server, it does not show the latest data. But when I refresh the report, it shows the latest data. I feel this is a potential problem from SSRS as the same provider works fine with Crystal reports.

 
 
Can anyone throw lights on this?
 
Thanks,
S Suresh

View Replies !   View Related
Oracle OleDb Provider As Source
Guys,

I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.

The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

After this happens, if I go into an OLE DB Source within a DFT, I get the following:

No disconnected record set is available for the specified SQL statement.

Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:

The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009

The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:

1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.

2. Hardcode the connection string in the dtsx XML-behind.

3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.

4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).

5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.


Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.

Thanks,

 

Rick

View Replies !   View Related
Microsoft OLEDB Provider Oracle
Hi,

Previously i was using oracle8i with ssis.ssis was working fine.later i have upgraded 8i to oracle 9i.now when ever i try to establish a new connection using Microsoft OLEDB Provider for Oracle.i am getting the following error "Oracle error occurred, but error message could not be retrieved from Oracle" .but i am able to access the oracle 9i database thru pl/sql and toad. what could be the problem?

Thanks

Jegan.T

View Replies !   View Related
Cannot Connect Using ODBC Or OLEDB Provider
HI, I installed SQL Server 2005 as a named instance. While I can connect to my server using SQLCMD or SSMS, SSIS; I cannot create a DSN entry that can connect to it, use OLEDB or connect using OSQL. I get the following message when I try to connect to the server using these technologies:

"Cannot generate SSPI context"

I also have a SQL Server 2000 named instance on my PC and I do not have these kind of problems (connecting using ODBC, OLEDB or OSQL).

Is there a configuration setting that I am missing?

Thank you,

Ccote

View Replies !   View Related
OleDb Provider Templates Problem
 

Hello,
 
I have a question regarding oledb templates in Visual Studio 2005. For some time I was trying to get through but I can't. Can anyone tell me how can I read the properties of the DataSource class being inside Rowset or Command class?
 
I mean: DataSource class is creating Session class. Session is creating Rowset or Command and so on.. But how can I read the INIT_DATASOURCE property of the DataSource class while for example a Command is called?
 
Could you give me a sample of code which retrieves the INIT_DATASOURCE property in the Execute method of the

CRowsetImpl class?
 
 

class ColedbRowset :


public CRowsetImpl< ColedbRowset, ColedbWindowsFile, ColedbCommand>

{



public:



HRESULT Execute(DBPARAMS * params/*pParams*/, LONG* pcRowsAffected)

{


????
}
}

View Replies !   View Related
RC1 Oledb Provider Missing Registry Key
The RC1 installer (and the previous beta too) doesn't write in the following registry key:

[HKEY_CLASSES_ROOTCLSID{32CE2952-2585-49a6-AEFF-1732076C2945}OLE DB Provider]
@="Microsoft SQL Server CE 3.0 OLE DB Provider for Windows"


so this provider doesn't appear on the oledb "Data Link Properties" panels. Manually entering the above key solves the problem. The last summer CTP version has been done this trick.

View Replies !   View Related
How Do I Compact SDF With SQL Mobile 3.0 OLEDB Provider With VC++
 

The interface ID IID_ISSCECompact has been removed from ssceoledb30.h thus causing a compile error on this statement:

 hr = m_pIUnknownProv->QueryInterface(IID_ISSCECompact, (void **) &pISSCECompact));

So how does one compact a database using the 3.0 OLEDB Provider and VC++?

Thanks

JEK

 

 

View Replies !   View Related
A Transport-level Error Has Occurred When Receiving Results From The Server.(provider:TCP Provider,error:0-The Handle Is Invalid
Hi,



I am using SQL Server 2005,



while trying to retrieve data from the database; I am getting the following
error:



 A transport-level error has occurred when receiving results
from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)



 But I am getting this error randomly.







 Can some one help me out?
 Waiting for your response



 Sudhakar

View Replies !   View Related
A Transport-level Error Has Occurred When Receiving Results From The Server. (provider: TCP Provider, Error: 0 - The Handle Is I
Hi,

Basically the error that I am getting is in our test automation when running as non-admin on the box (regular user). We use .Net C# SQLConnection class to connect to SQL express 2005 impersonating with admin credentials. After getting the connection we try to execute a select command and it some time fails with following error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The handle is invalid.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader()

This happen only in the non-admin scenario mentioned above. Any idea what is triggering this?

Thanks in advance for all replies.

View Replies !   View Related
Oledb Provider Having Problem In MS SQL Server 2005
Hi,
I try to access the table "UserGroup" in my local sql server 2005 database. However I get this error message"Invalid object name 'UserGroup'. "I'm using MS Visual Studio 2005 and MS Sql Server 2005. I did install the latest Sql Native. In fact I just migrate from ASP.NET to ASP.NET 2.0 . It is working if my connection string is point to my "online host" without any error.'Online Connection String - Working<add key="OLEDB" value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abc;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx;Connect Timeout=3600"/>
 'Local Connection String - Not working  (SQL Authentication)<add key="OLEDB" value="Provider=SQLNCLI;Trusted_Connection=No;UID=calvin;Initial Catalog=mydatabase_db;Data Source=CALVINNB; Password=xxxxxx"/>OR(Windows Authentication)<add key="OLEDB" value="Provider=SQLNCLI;Server=CALVINNB;Database=mydatabase_db;Trusted_Connection=yes"/>Is the Oledb provider having problem in MS SQL Server 2005?
 Any help would be more appreciate.Calvin
 

View Replies !   View Related
Could Not Resolve Service Name - Oracle 8 And MS OLEDB Provider
Trying to used DTS from SQL Server 7 to import a table from Oracle 8 DB to SQL Server. SQL Server has Oracle 8 connectivity installed and a database instance setup. Connection using the instance works from Oracle Enterprise manager and all Oracle tools. The MS OLEDB provider for Oracle fails to connect : could not resolve service name. I have this working on a different server with the same setup (SQL Server 7 SP 2 + All Oracle 8.0.5 connectivity).

WHAT AM I MISSING?

View Replies !   View Related
Missing Oracle OLEDB Provider In SSIS
Windows 2003(64bit) server, SQL 2005 Server(64bit), Oracle client 10G rel2 (64bit) is installed. But when I am going to create a datasource to the Oracle database the Oracle OLEDB Provider is missing, the only Providers avaliable is from Microsoft.

I have tried to install ODAC (64bit) with no result.

Anyone who have come across this problem and how do I do to resolve it?

BTW! Is there a way to try communication with the OLEDB provider from the commandprompt?

View Replies !   View Related
Ad Hoc Access To OLE DB Provider 'Microsoft.Jet.OLEDB.40' Has Been Denied.
Hi all,

 

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

 

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

 

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

 

Please help.

 

Dhiraj

View Replies !   View Related
How Do I Get The CLSID For SQL Server 2005 OLEDB Provider?
 

How do I get the CLSID for SQL Server 2005 OLEDB Provider?  The following code yields the CLSID for 2000 Provider

        m_hr = CLSIDFromProgID(L"SQLOLEDB",&clsid); // SQL Server

Thanks

JEK

 

View Replies !   View Related
Problems Executing Stored Procedure OLEDB
<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2   etc.. etc..  UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure.  If I try to parse the Query I get the error "The Query failed to parse.  Attempted to read or write protected memory.  This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas?  I've tried just about everything I can think of short of building a whole new package...

View Replies !   View Related
Convert Query From OLEDB To ODBC (Receiving Error)
I have the following query for my site which was functioning great using the OLEDB driver. Unfortunately, my client's web host forced us to use ODBC and it is no longer working   Any suggestions for what I should do?

Here is the query:
SELECT t.*
FROM (SELECT TOP 10 itemnum, itemnum FROM (
        SELECT TOP 100 itemnum, itemnum
        FROM(CatView)
          WHERE [CATALOG PAGE] LIKE 'SM%'
          ORDER BY itemnum ASC) AS foo
     ORDER BY itemnum DESC)  AS bar INNER JOIN CatView AS t ON bar.itemnum = t.itemnum
ORDER BY bar.itemnum;

Here is the error i'm now receiving:

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'itemnum' could refer to more than one table listed in the FROM clause of your SQL statement.
I know it was working great before, so i'm not sure what to do.

Michael

View Replies !   View Related
Oracle Oledb Provider Not Registered In Local Machine
Hi,

I am trying to establish a connection to an Oracle database using the following code in a script task:


Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?
I do not know how to resolve the said error. 
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.
Please help.
Thanks.

View Replies !   View Related
Aggregate Function Not Supported In SSAS OLEDB Provider?
 

I am writing a Reporting Services report pointed at an Analysis Services data cube, and in order to have more control over the MDX, I needed to switch my SSRS data source from the .NET Provider to the OLEDB provider (from ".NET Framework Service Provider for Analysis Services" to "OLEDB Provider for Analysis Services", that is).  I'm also entering my MDX in as an expression in the data source.
 
I noticed that when I summarize this data in the table footer on the report, the summary values that use the Aggregate function (=aggregate(fields!fieldname.value) for example) are blank.
 
I'm assuming that since the OLEDB provider is an "older" data provider for SSAS data and the Aggregate function was new in 2005 with the .NET provider, the OLEDB provider doesn't support the Aggregate function.  I was wondering if someone could confirm that this is the case?
 
Also, is there any other functionality that is not supported by OLEDB that would be supported by the newer .NET provider (aside from the ability to generate fields, parameters, and use the MDX query designer, of course)?
 
Thanks.

View Replies !   View Related
OLEDB Provider For DB2 - Record Locking &&amp; Open Sessions
We have a linked server configured on our SQL Server 2005 server that points to our iSeries.  A step in a scheduled job successfully updates data in tables on the iSeries using an update query and the 4-part naming convention (job reports success and updates can be verified), but it appears to leave locked records and open connections on the iSeries.  No open sessions showing in SQL Activity Monitor.
 
The linked server is configured using Microsoft's OLEDB Provider for DB2.  Are there additional settings that can be configured on the linked server to prevent this behavior or is this an issue that need needs to be addressed on the iSeries?  A timeout setting perhaps (not sure if this fixes the record locks though)?
 
Any help is greatly appreciated.
 
KS

View Replies !   View Related
Error While Executing Simple Query :(
Hi,

I am using this query to execute but it didn't

declare @AccountID numeric(3,0)
set @AccountID = 101
select * from AccountTest
WHERE AccountID IN
(CASE
WHEN @AccountID = 101 THEN (100,101)
ELSE @AccountID
END)

I m getting error like

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.

Pls help me out

T.I.A

View Replies !   View Related
How To Create A Linked Server In SQL2k5 To A SQL2k That Uses The SQL OLEDB Provider
I have a SQL Server 2005 Express SP2 Server. By using the SQL Server Management Studio Express, I want to create a Linked Server pointing to a SQL Server 2000 SP4 Server. But I want the configuration to use the OLEDB Provider for SQL instead of the SQL Native Client:
Linked Server: MyRemoteServer
Provider: Microsoft OLE DB Provider for SQL Server
Product Name: SQL Server
Datasource: MyRemoteServer
Provider String: "Provider=SQLOLEDB; Persist Security Info=True; Integrated Security=SSPI; Trusted_Connection=Yes;"

However, when I press "Ok" the Linked server just created always uses the SQL Native Client.

Is there a way to create a linked server to a SQL Server 2000 that uses the SQL OLEDB Provider?

View Replies !   View Related
Error H4000 Error While Executing The Query ???????????
 

Hi all
my problem is as follows : when i try to configure an ODBC DataAdapter using the configuration wizard
 
i use the query builder to select some fields from multiple tables , the displayed info depends in a user input , when i define the input parameter by writing(= ?) in the criteria column of the input field and continues , it displays an error
 
Error H4000 Error while executing the query , Error near ')' near character (214 for example).
 
so please if any one knows the solution to this error reply soon
 
Thanks a lot
 
Mina Samy

View Replies !   View Related
Error When An OLEDB Source Points To An OLEDB Destination.
Hi all,

   I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.

But i got the below error:

Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow TaSK: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

 

 

View Replies !   View Related
Could Not Execute Query Against OLE DB Provider 'MSIDXS' Error
I have created a stored procedure for searching particular keywords in a MS Word or PDF docs using Index Server and processes the results through the SQL Server. So, it links both Index Server and SQL Server When I execute this stored proc from my ASP.NET page i'm getting this error. Could not execute query against OLE DB provider 'MSIDXS' My Sql Connection object in my .net page is SqlConnection objConn = new SqlConnection("Server=MISC\MISC;Database=sapresources;User ID=sap;Password=sapres;"); I tried different ways but not able to understand where the problem is but It works fine when i execute in my Query Analyzer using Exec SelectIndexServerCVpaths @searchstring='mykeyword'
Can anybody help ? Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths ( @searchstring varchar(100) ) AS SET @searchstring = REPLACE( @searchstring, '''', '''''' ) DROP VIEW sapresources.FileSearchResults EXEC ('CREATE VIEW sapresources.FileSearchResults AS SELECT * FROM OPENQUERY(FileSystem,''SELECT Directory, FileName,  DocAuthor, Size, Create, Write, Path FROM SCOPE('''' "c:inetpubwwwrootsap-resourcesUploads" '''') WHERE FREETEXT(''''' + @searchstring + ''''')'')') SELECT * FROM CVdetails C, sapresources.FileSearchResults F  WHERE C.CV_Path = F.PATH AND C.DefaultID=1 GO

View Replies !   View Related
Error Executing Non Query: Timeout Expired
I was having some errors from the webpage accessing the OLlinks table in the database.
Error executing non query: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace:   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()   at System.Data.SqlClient.SqlDataReader.get_MetaData()   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)   at System.Data.SqlClient.SqlCommand.ExecuteReader()   at admin_admOLEditLinks.selectData(String strID) in e:wwwroothomeadminadmOLEditLinks.aspx.cs:line 101DateTime:5/23/2007 1:14:10 PMSource:http://www.myDomain.comiAdmin/admOLEditLinks.aspx?ID=3ErrorMessage:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
I kept getting the above error so then I try to access the table directly inside of MS SQL Server Management Studio and then I recieved the following error:
SQL Execution Error.
Executed SQL statement: SELECT lnkID, linkFromID, linkToID FROM OLlinks
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the opration or the server is not responding.
 
Open any other table works fine except this table only. Any help is much appreciated.

View Replies !   View Related
Error Executing Dynamic Select Query
Hi,
 
i have problem executing the dynamic query.
 
I have straight forward query as below and works fine
 

declare @count bigint

declare @varcount varchar(max)

set @varcount ='5'

If convert(bigint, @varcount) <> 4

print ' not 4 '

else

print 'Its 4'

 
 
Here is my dynamic query. The number of records (@No ) and the table name ( @table ) will be available for me as parameters to my stoped proc
 
 

declare @count bigint

declare @varcount varchar(max)

declare @tempTable varchar(max)

declare @vsSql varchar(max)

declare @No bigint

set @No = 5

set @table = 'sam'

set @varcount = ''

select @vsSql = 'declare @varcount varchar(max); select @varcount = count(*) from ' + @table + '; If convert(bigint,@varcount) <> ' + @No + ' raiserror(' +'mismatch, 11,1' +')'

 
 
When executed it throws the follwing error
 

Msg 8114, Level 16, State 5, Line 10

Error converting data type varchar to bigint.

 
 
Can anyone point out what to change in the query to work
 
~mohan

View Replies !   View Related
Microsoft OLEDB Provider For DB2 Performance Issue (Missing Where Clause On DB2 Site)
Hi!
I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:
 
SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A
WHERE FIELD_A='ABC'                                   
 
I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the €œwhere clause€?:
 
SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A
 
So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on €¦ !
 
If anyone out there has an idea of what could be wrong, please let me know!
 
 
NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.  
 

View Replies !   View Related
The 'Microsoft.Jet.OLEDB.4.0' Provider Is Not Registered On The Local Machine. (System.Data)
I am on an XP Pro 32-bit laptop, running SQL Server 2005 Developer Edition.  I want to import an Excel spreadsheet into one of my databases using (formerly known as) DTS.  The Excel spreadsheet was created in 2003 (I have Office 2007 installed on this laptop).  Whenever I try and do any OleDb import, I get the error:
 
TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. (System.Data)
------------------------------
BUTTONS:
OK
------------------------------


 
I cannot install/reinstall MDAC because I have the latest version.  What else can I try?
 
As additional information, I also get the error when trying to import anything into or export anything out of Access 2007.  I'm pretty sure MDAC is awful, but I still need OleDb providers for much of the work I am doing.

View Replies !   View Related
Error :(provider: Named Pipes Provider, Error: 40 - Could Not Open A Connection To SQL Server) (Microsoft SQL Server, Error: 53)
Hi,

   I am trying to connect to my SQL Server 2005 but it gave me following error message.

 


TITLE: Connect to Server
------------------------------

------------------------------
ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: 53)

 

So, Please help me to solve this problem.

 

tnks.

View Replies !   View Related
A Connection Was Successfully Established With The Server, But Then An Error Occurred During The Login Process. (provider: Shared Memory Provider, Error: 0 - No Process Is On The Other End Of The Pipe.)
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
use [master]Go
-- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO
-- Switching to our databaseuse [DatabaseName]GO
CREATE SCHEMA schemaname AUTHORIZATION usernameGO
ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO
/* * Creating two new roles. We're not going to set the necessary permissions  * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users  * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]  to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]  to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'

View Replies !   View Related
Severe Error After Installing SQL2005 SP2 Query To Linked Server Foxpro With Oledb
After installing sql2005 sp2 a simple select query to a linked server reports the following error message:

Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command.  The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command.  The results, if any, should be discarded.
Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.

The linked server is a Visual FoxPro database.

After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.

View Replies !   View Related
Error While Executing A Query(a Particular String Pattern) On MS Sql7.0
Hello there
I have got an error as

"Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated."

while executing a query on MS Sql server7.0 as under....
"
--insert into documents (original,document,address,new) values ('fdfdfd','dsdsdfs','http://localhost:8081','truee')
".
I found that the particular sequence of "://......:" is creating problem.as i tried other options also but when the sequence like "/**...../" comes it returns the same errror.even if i use two blanks in place of any of these two characters it returns the same error.
I have tried different combination of these characters as under....

--insert into documents (original,document,address,new) values ('fdfdfd','dsdsdfs','http://localhost:8081','truee')

--insert into documents (original, document, address, new) values ('fdfdfd','dsdsdfs','http://localhost:8081','true')

--insert into documents (original, document, address, new) values ('fdfdfd','dsdsdfs','http:##localhost:8081','true' )

please suggest on it if it would be an easy one.
thanks Dinesh

View Replies !   View Related
Error While Executing A A Query String Using EXEC Statement
Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query



Code Snippet

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'
 
INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a
 
 
 
To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below






Code Snippet
 
@Category  - Will be passed as a parameter to the stored proc
 

DECLARE @vsBulkSQL VARCHAR(MAX)

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'
 
SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'
 
Print @vsBulkSQL - This prints the folliwing statement
 

INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a
 
 
Exec @vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.
 
 




Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same
 
~Mohan

View Replies !   View Related
Connecting To Sql Server 2000 On Godaddy Error: (provider: Named Pipes Provider, Error: 40 - Could Not Open A Connection To SQL Server)
Facts:1. I am using my account on Godaddy trying to connect to my sql server db.2. I can get into my db using user id and password.3. Godaddy's help file shows this for a connection string: connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE="
&db_name & ";UID=" & db_username & ";PWD=" &
db_userpassword4. My connection string: "Server=whsql-v09.prod.mesa1.secureserver.net;uid=dbasolutions;pwd=***;database=DB_87972;"  & _                    "Trusted_Connection=False providerName=System.Data.SqlClient"{password **** out}5. Get error message  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 tried to email Godaddy's help desk with this information. There first response: We do not allow remote connections to our sql server database.There second response was to read the help file!Does anybody have any suggestions?  Thanks in advance,bswanson     

View Replies !   View Related
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View Replies !   View Related
Connection Error From ASP.Net Under IIS: Provider: Named Pipes Provider, Error: 40
We experience the 40 error when our Asp.net code runs under IIS and uses named pipes to a sql server 2005 named instance.

 

We've tried all suggestions found on blogs and KB articles such as making sure the named pipes protocol is enabled, making sure the Browser service is started, making sure Allow Remote Connections is enabled, making sure we're not using Asp.Net 2.0 membership providers, and the list goes on and on.

 

We boiled it down to a simple test scenario. We open a SqlConnection  to the sql 2005 box in the code with the intention of binding a datareader to a gridview, but it never gets that far because the connection.Open() fails.

 

If we run this test application as a virtual directory in IIS and Asp.Net 2.0, the connection.Open() fails.

 

If we run this test application from Visual Studio .Net using Cassini (PWS), the connection.Open() succeeds and binds to the gridview.

 

What could be causing the connection to fail under IIS but succeed under Cassini? We've tried setting IIS to run as a domain user and with digest authentication enabled. That made no difference.

 

Any help or insight is appreciated!

View Replies !   View Related
OLEDB Provider=MSDAORA SQL Configuration TABLE In SSIS And Password Not Been Transfered (Project Real Drama)
dear list can anyone figure out a workaround as to why OLEDB Provider MSDORA cannot store passwords
I have all the info stored in a table (Project Real Best Practise)
The user id I have stored in string ConfiguredValue gets transfered to OLEDB Provider MSDORA named (SQL_REAL_Source_myoradb) but not the password
To workaround this bug only with MSDORA can aynonne sugest a setting I should use in package security
ie the default is EncryptSensitiveWithUserKey?
 
thanks Dave
 

CREATE TABLE [admin].[Configuration](
 [ConfigurationFilter] [nvarchar](255) NOT NULL,
 [ConfiguredValue] [nvarchar](255) NULL,
 [PackagePath] [nvarchar](255) NOT NULL,
 [ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]

 
ConfiguredValue = Provider=MSDAORA.1;Password=abc;User ID=myuserid;Data Source=myoradb;Persist Security Info=True

 
PackagePath = Package.Connections[SQL_REAL_Source_myoradb].ConnectionS

View Replies !   View Related
SQL 2005 : Provider: Named Pipes Provider, Error: 40 - Could Not Open A Connection To SQL Server
Hi to all,


We have 4 client machine and 1 server machine, that server have all the sql server 2005 database€™s.


A project developed from client1 with Asp.net 2005 now I need to access that project from client2 so I type http://My Ip address/Folder Name/home.aspx in my browser here when the sql connection is open(SqlConnection.Opn())  then I meet this problem


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)


Please reply me.

View Replies !   View Related
Provider: TCP Provider, Error: 0 - An Existing Connection Was Forcibly Closed By The Remote Host.
Hi,

One of our customers is getting this error about 3-4 times a day, in an .NET application that runs 24/7:

(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

They have several App servers, Windows 2003 SP1 32 bit... connecting to several SQL 2005 SP 1 servers, 64 bit. All of their 4 application servers have had this error with 3 or 4 different SQL instances running on 4 different SQL servers.

There is a firewall in between but that does not seem to be the problem. We use the same firewall setup in dozens of environments without any issues like this, and the firewalls are not imposing any maximum TCP session times.

There are lots of people reporting this same problem with SQL 2005 only - it does not seem to be an issue with the same applications running against SQL 2000.

Any hints as to how to fix these errors? I am going to try adjusting the Keep Alive setting in the SQL client config to 10000 instead of 30000 but judging by the number of people getting this error, this seems to be some sort of bug with .NET's connection pooling and SQL 2005.

People are saying the easiest way to reproduce this is to do a SQL query.... unplug the network cable and plug it back in again... then run another query. They say it causes this error every time, but I have not tested this since we are only seeing this problem in a production environment.

Thanks for your help,
Jeremy

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved