SQL Server Data Access Forum

OPENROWSET with Excel 2007 -- 7330 Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Tue, 10 Mar 2009 19:18:12 Z

I am trying to use OPENROWSET with an Excel 2007 file. It seems to be partially working. Here is what I am doing:

select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Test.xlsx;HDR=YES',
'SELECT * FROM [DataLoad$]')

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Note that I do get back a results set with the correct field names from the Excel spreadsheet, but no rows!!

As an experiment I also saved the file in Excel 2003 format and the following query ran just fine, returning all rows:
select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Test.xls;HDR=YES',
'SELECT * FROM [DataLoad$]')

This is not really a good long term solution though, as I was not planning to switch back to Excel 2003 anytime soon.

So, any ideas on how to get this to work in Excel 2007?? I messed around with the query and the data, changing the queried ranges etc., but the results were the same every time.

Are there new extended properties I can fool around with? What else could be causing this?

help me

Sat, 17 Oct 2009 02:09:21 Z

Msg 7878, Level 16, State 1, Line 1
This "CREATE ENDPOINT" statement is not supported on this edition of SQL Server.2005

help me please

Unspecified Error (MS Visual Database Tools)

Sun, 05 Jun 2011 16:51:43 Z

I am trying to connect to sqlserver2008 from sqlserver2005 (sp4) but receiving following error message when I try to right clik and open table. I can open the table by quering it but cant open by right clicking it.

===================================

Unspecified error
(MS Visual Database Tools)

------------------------------
Program Location:

   at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.BrowseTable(Object dsRef, Object pServiceProvider)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.CreateDesigner(DocumentType editorType, DocumentOptions aeOptions, Urn parentUrn, IManagedConnection mc)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

Any Ideas ?

Farooq Jiwani.

Farooq Jiwani Microsoft E-Learning www.microsoft.com/elearning

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information

Sat, 04 Jun 2011 20:35:19 Z

Yeah, I know, this error is all over the net. And the answer is to include a primary key in your select statement. I did, and it still isn't working. Here are details.

SQL Server 2000, C# 2010, .NET Framework 4. I'm building a dataset with all three fields from my table, two of which define the primary key. When I try to update, I get the afore-mentioned error. Here's the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SQL_Update_Test
{
  class Program
  {
    static void Main(string[] args)
    {
      //Fill the dataset
      string ConnStringSS = "Data Source = Beta1; Initial Catalog = Kraemers_Rack; Integrated Security = SSPI";
      string Query = "select distinct ysnSelected, strUnitMeasure, intBatchNo from aSizeTest where intBatchNo = 10";
      SqlDataAdapter dAdapter = new SqlDataAdapter(Query, ConnStringSS);
      SqlCommandBuilder sqlCmd = new SqlCommandBuilder(dAdapter);
      DataSet dSet = new DataSet();
      dAdapter.Fill(dSet);

      //Make a change to the data
      for (int i = 0; i < dSet.Tables[0].Rows.Count / 2; i++)
      {
        if ((Boolean)dSet.Tables[0].Rows[i]["ysnSelected"] == true)
        {
          dSet.Tables[0].Rows[i]["ysnSelected"] = false;
        }
        else
        {
          dSet.Tables[0].Rows[i]["ysnSelected"] = true;
        }
      }

      //Update the dataset
      dSet.GetChanges();
      if (dSet.HasChanges())
      {
        int iChanged = dAdapter.Update(dSet);
        dSet.AcceptChanges();
        Console.WriteLine(iChanged + " rows updated");
      }
    }
  }
}

And here's SQL code to create and populate the table:

CREATE TABLE [aSizeTest] (
	[ysnSelected] [bit] NULL CONSTRAINT [DF_aSizeTest_ysnSelected] DEFAULT (0),
	[strUnitMeasure] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[intBatchNo] [int] NOT NULL ,
	CONSTRAINT [PK_aSizeTest] PRIMARY KEY CLUSTERED 
	(
		[strUnitMeasure],
		[intBatchNo]
	) WITH FILLFACTOR = 90 ON [PRIMARY] 
) ON [PRIMARY]
GO

insert into aSizeTest 
(ysnSelected, strUnitMeasure, intBatchNo) values (0, 'Unit1', 10)
insert into aSizeTest 
(ysnSelected, strUnitMeasure, intBatchNo) values (0, 'Unit2', 10)
insert into aSizeTest 
(ysnSelected, strUnitMeasure, intBatchNo) values (0, 'Unit3', 10)
insert into aSizeTest 
(ysnSelected, strUnitMeasure, intBatchNo) values (1, 'Unit4', 10)
insert into aSizeTest 
(ysnSelected, strUnitMeasure, intBatchNo) values (1, 'Unit5', 10)
insert into aSizeTest 
(ysnSelected, strUnitMeasure, intBatchNo) values (1, 'Unit6', 10)

This works great on SQL Server 2008, but errors on SQL Server 2000. Anyone have a clue why?

Thanks in advance,

Steve.

PROBLEM POPULATING TABLE COLUMNS

Mon, 30 May 2011 23:45:18 Z

Hi folks,

I am developing a windows form Database application with SQL server.
One of my database tables have columns with Data types as: uniqueidentifier and datetime.
The uniqueidentifier column has Default value or Binding as : (newid()) and
The datetime column has Default value or Binding as : (getdate()).

The problem am having is that, when populating the table through DataGridView, the two columns describe above do not
automatically populate the uniqueIdentifier and datetime values. Any help on how to go about this. Am really having a difficulty with this issue.

Thank you

SQL Server 2005 - Repair Master Database

Fri, 03 Jun 2011 14:36:13 Z

(Windows XP Professional SP3) Corrupted SQL Server MASTER Database - Windows automatic updates are failing for those having to do with SQL Server or Visual Studio. Recently, I was experimenting with exporting data from a Autodesk Revit application into the SQL server 2005 database on my local machine. Being a novice, I used the SQL "Master" database to export my data into. I'm inferring that I may have corrupted the MASTER so that Windows cannot successfully do its updates because I'm getting a failure on all updates having to do with SQL Server or Visual Studio. Other Windows updates seem to be OK. Do I need to repair the SQL master database? If so, how?

How to check if the connection droped?

Sat, 04 Jun 2011 15:38:16 Z

Hi guyz;

How can I check if the connection to sql server 2008 db hase been droped through my c# application?

18452 – user is not associated with a trusted SQL server connection

Fri, 03 Jun 2011 14:45:54 Z

Just installed SQL Server Management Studio 2008 R2. Server is (local)\sqlexpress and is set up with SQL Server and Windows Authentication mode. Under security I’ve created a login. Created a database and under that databases security I’ve added a new user (based on the login just created). Tried to set up a data source uses ODBC. Unser System DSN, tried to add a DSN. Selected SQL Server Native Client 10.0. Entered the DS name and description. Selected server (local). Selected authentication as with SQL Server and entered the login ID and password. Tried to connect and got failure – SQL server error 18452 – the user is not associated with a trusted SQL server connection. I’ve done this many times with SQL Server 2000 and 2005. What am I missing? Note that I can establish and ODBC connection with a remote database.

What is the equivalent datatype for datetime of SQL Server 2008 in ADO?

Mon, 30 May 2011 16:15:42 Z

What is the equivalent datatype for datetime of SQL Server 2008 in ADO?

The datetime datatype in SQL Server 2008 supports milliseconds.

I am trying to execute a stored procedure that accepts a datetime parameter, with an arg with milliseconds, as input/output value.

I am unable to convert the string that I pass in to the method as datetime value. When I do not pass in the millisecond values, the conversion happens correctly.

I see the conversion problem in the below method.

Set objpara2 = objCom.CreateParameter("datetime", adDate, adParamInputOutput, , "2011-01-01 11:01:01.123")

CREATE PROCEDURE
[dbo].[TestProc]
@time1 time, @datetime datetime output 

as 

begin 

    SET NOCOUNT
ON; 

    select
@datetime = datetime from ALLTimeTypes
where time =
@time1; 

end 

 

Private Sub Command1_Click() 

    Dim objCon As ADODB.connection 

    Dim objCom As ADODB.command 

    Dim objPara As ADODB.Parameter 

    Dim objpara2 As ADODB.Parameter 

    Dim objRS As ADODB.Recordset 

    Dim k As Integer 

 

    Set objCon
= New ADODB.connection 

    Set objCom
= New ADODB.command 

 

    objConConnectionString = "Provider=SQLNCLI10;" _ 

         & "Data Source=ES-89W87BS;" _ 

         & "Database=MASTER;" _ 

         & "Integrated Security=SSPI;" _ 

         & "DataTypeCompatibility=80;" _ 

         & "User ID=sa;" _ 

         & "Password=<redacted>;" 

 

    objCon.ConnectionString = objConConnectionString 

    objCon.Open         

    MsgBox "Connection opened" 

 

    With objCom 

        .CommandText =
"TestProc"    
'Name of the stored procedure 

        .CommandType = adCmdStoredProc  'Type
: stored procedure 

        .ActiveConnection = objCon.ConnectionString 

    End 
With 

 

    Set objPara
= objCom.CreateParameter("time1", adVarChar, adParamInput,
50, 
"02:02:02.3456123") 

    Set objpara2
= objCom.CreateParameter("datetime", adDate, adParamInputOutput,
, "2011-01-01 11:01:01.123") 

 

    objCom.Parameters.Append objPara 

    objCom.Parameters.Append objpara2 

 

    Set objRS
= objCom.Execute         

    objRS.Open 

    Do While
Not objRS.EOF 

        For k
= 0
To objRS.Fields.Count
- 1 

            Debug.Print objRS(k).Name
& ": "
& objRS(k).Value 

        Next 

        objRS.MoveNext 

    Loop         

    ...

Data via linked server not showing cyrillic characters

Mon, 09 May 2011 12:53:49 Z

Hi!

I've setup a linked server to a Pervasive V8 database. The database contains cyrillic characters. When I run the query below it runs but all string fields are not correct cyrillic/bulgarian.

EXECUTE('SET OWNER = ''x'';SELECT * FROM myTable') AT <myLinkedServer>;

I need to run the set-command so thats why I use the execute at-command.
I'm running a SQL Express 2005.
It shouldn't be anything OS or SQL specific since I can run a simple select 'солдат'-query and it shows without problem.
The linked server is setup of type MSDASQL pointing to a DSN.
Same DSN that Pervasive Control Center uses which works fine.
I've tried both true/false at the linked server setting "use remote collation" without success.

Any help in resolving my problem would be highly appreciated!

Problems connecting to SOAP endpoint

Fri, 03 Jun 2011 21:39:10 Z

Running SQL Server 2005 SP3. I have a SOAP Endpoint created, and am trying to connect to it via Android. I have been trying to fix things on the Android end, but it is now seeming like there are problems on the server side. Here is my SOAP Endpoint:

CREATE ENDPOINT [DroidTest2] 
	AUTHORIZATION [MYDOMAIN\cgtyoder]
	STATE=STARTED
	AS HTTP (PATH=N'/dt2', PORTS = (SSL, CLEAR), AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED), SITE=N'*', CLEAR_PORT = 88, SSL_PORT = 444, COMPRESSION=DISABLED)
	FOR SOAP (
WEBMETHOD 'DroidFunction'( NAME=N'[pubs].[dbo].[DroidFunction]'
, SCHEMA=DEFAULT
, FORMAT=ROWSETS_ONLY), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'pubs', NAMESPACE=N'http://tempuri.org', SCHEMA=STANDARD, CHARACTER_SET=XML)
GO

'DroidFunction' is a simple function which takes a string (varchar(20)) and returns another string (varchar(30)).

I can get the WSDL in a browser by going to http://localhost:88/dt2?wsdl or https://localhost:444/dt2?wsdl and using HTTP authentication. However, when I use SOAP validation utilities such as soapUI or Web Service Studio, it complains about the WSDL not being formed correctly. (soapUI can't even connect to the endpoint, but it has no problem with this public sample: http://www.w3schools.com/webservices/tempconvert.asmx.)

Are there known issues here that I am missing? What should I try next? (I realize that this is deprecated in Server 2008; I have a ton of time invested here that I would prefer not go to waste but if writing an ASP.NET web app is the only way I am going to be able to connect, then...)

Linker server to Oracle returns ORA-01017 when using login mapping

Fri, 27 May 2011 18:28:54 Z

Hello,

I'm configuring linked server to Oracle 11g on ms sql 2005 using OraOLEDB. On "Security page", when I use "Be made using this security context:" and provide oracle login and password everything is working. When I try to use "Local server login to remote server login mapping:" and use local sql server login and the same oracle login and password I receive "ORA-01017: invalid username/password; logon denied". What am I doing wrong?

Thanks,

Igor.

Windows 7 RC ODBC Access driver

Fri, 01 May 2009 15:19:35 Z

Many apologies for posting here, but it's the nearset I can find. I am running the Windows 7 RC (x64) and need the MS Access 32-bit ODBC driver in order to connect to an in-house application. The driver doesn't appear to be shipped with Windows 7 and I cannot find anywhere to download it.

On further inspection, the 32-bit SQL Server ODBC driver appears not to ship either and I need that as well.

Will SQLTransaction Roll back if I didn't call Transaction.Commit() ?

Fri, 03 Jun 2011 07:03:41 Z

HI all,

I have some functions use SQLTransaction assigned with some SQLCommand, and exception happened, if i didn't rollback and directly return the fucntion without calling SQLTransaction.Commit(), will there be any possible problems?

Using(SQLConnection conn = New SQLConnection(string))

{

var Tran = Conn.BeginTransaction();

return;

Tran.Commit();

}

many thanks.

James

SQL Server Remote access through SSMS, Error: 10054 & Error: 64

Mon, 04 Apr 2011 03:59:50 Z

I am remotely accessing the client's SQL Server but encountered below error.

When connect through SSMS for the first time, below error shows:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

When I try the second time, we have below error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

Background:

1. All firewall settings are disabled on my PC.

2. I can ping and telnet the client's IP and Port.

3. Have tested the connection on client side network, it works fine.

Anyone could help?

Exec query on workstation!

Thu, 02 Jun 2011 09:18:21 Z

Hi all!

I try execute this on Client with Administrator account right, non default Administrator account of W7

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\TEST1\Tem.XLS',
'SELECT * FROM [Sheet1$]')

but error occured:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 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 "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

When I execute with default Administrator account of W7, it no problem.

I installed AccessDatabaseEngine, MS Office 2007, MS SQL Express 2005

So, how must i do to exec it with any account in client?

Thanks!

SQL Server 2008: Extract from DB2 Linked Server with IBM DB2 Ole provider

Tue, 31 May 2011 08:55:45 Z

I am part of a dedicated data warehouse (DW) team which primary source system is a DB2 mainframe. We have optimized our DW Historic layer by using a linked server to DB2. We are using SSIS as ETL-tool but have a dynamic package that creates T-SQL "Insert XX Openquery('LinkedServer', SQL)" syntax. We have a brand new server setup - with a dedicted 20 TB SAN, 4 quadcore CPU and 256 Gb rams which generally performs great.

However, we are having some performance issues extracting data from DB2 to our Data warehouse servers using syntax like this:

INSERT INTO DBDL2001.ZA06KS03HIST

(ARBLONART, DWANDTIP, FHANUM, ABPNUM, VFTODRNUM, ABPTXT, NK_KS03, ARBKORTID, ARBPRS, VFTARNKDE, MEKLONNUM, ARBVREART,

VFTRABPCT, PAPABPLIN, TIMSAT, ARBBIT, NUMSKDNUM, DMS_LINID, SAGA_LINID, SAGA_CLAIM_NO, SAGA_FDEPCT_IMP, SAGA_FDEPCT_FHA,

SAGA_FDEPCT_KUN, OPROPEIDF, OPRTIP, ANDOPEIDF, ANDTIP, SAGA_DAMAGE_CAUSE, VFTFAKDMA, ORIGIN, CODE, OBJECT_ID, BILFAC, LAC,

PACKET_REF, WSC, SAGA_PRICE ,dwSourceKey, dwExtractPeriodFromTs, dwExtractPeriodToTs, dwInsertJobId)

SELECT * FROM OPENQUERY(PRODDB2,

'SELECT ARBLONART, DWANDTIP, FHANUM, ABPNUM, VFTODRNUM, ABPTXT, NK_KS03, ARBKORTID, ARBPRS, VFTARNKDE, MEKLONNUM, ARBVREART,

VFTRABPCT, PAPABPLIN, TIMSAT, ARBBIT, NUMSKDNUM, DMS_LINID, SAGA_LINID, SAGA_CLAIM_NO, SAGA_FDEPCT_IMP, SAGA_FDEPCT_FHA,

SAGA_FDEPCT_KUN, OPROPEIDF, OPRTIP, ANDOPEIDF, ANDTIP, SAGA_DAMAGE_CAUSE, VFTFAKDMA, ORIGIN, CODE, OBJECT_ID, BILFAC, LAC,

PACKET_REF, WSC, SAGA_PRICE ,

(ltrim(rtrim(cast(FHANUM as varchar(100))))||''¤''||ltrim(rtrim(cast(VFTODRNUM as varchar(100))))||''¤''||ltrim(rtrim(cast(NK_KS03 as varchar(100)))))

as dwSourceKey, ''1900-01-01 00:00:00.000'' as dwExtractPeriodFromTs, ''2011-04-05 10:20:42.080'' as dwExtractPeriodToTs, 2320 as dwInsertJobId

FROM prod.ZA06KS03HIST with ur ')

The source tabels has 25 mio. rows and it takes 2 hours to extract, which is terrible considering that is takes minutes if we do a simple SSIS source destination package.

Do any have some ideas where the bottlenecks might be or how to optimize it.

kind regards

Rasmus

ODBC 3700 error

Thu, 02 Jun 2011 13:17:23 Z

Hi,

     I am trying to connect to MS-SQL SERVER 2008 R2 using ODBC in php, It connects to master but dispaly error 3700 for other database. Can any one help me sort the issue?
here is the code I use
$conn=odbc_connect('caldsn','','');
    if(!$conn)
    {
        echo"<br />1 Error connecting to MsSql ".odbc_error() ;
    }

Windows 7 SP1 Slows IP connection to SQL Server

Tue, 31 May 2011 17:36:21 Z

Hello,

The installation of Win7 SP1 causes IP connections to remote SQL Server 2005 database to become extremely slow. Queries that previously took 1/2 second now take 2 minutes. The slowness is experienced within the VS 2010 environment as well as connecting with SSMS.

Does anyone know the fix for this?

Thank you,

Craig

Between Condition in C# Express

Thu, 02 Jun 2011 22:23:57 Z

Hello guys,

What I have in my form is this: after the user selects his height from drop down menu, he enters his weight in a text box... then the result should be retrieved from database where the selected height has a specific range of weight.

For example: if his Height 1.397 M, his weight should be between 80-102 LB.

Therefore I made 4 columns in my database ( Height, Minimum Weight, Maximum Weight, Risk Percent).

I want to make a query whereas the weight between this range.

Select Risk from table where weight between Minimum Weight and Maximum Weight

Your help would be grateful..

Thanks,

Denver,CO

Range in Database using C# Express

Mon, 23 May 2011 18:35:38 Z

Hello dudes :)

I need your help as always. I do not know how to use a range datatype in the database of C# Express. For example, if I have in my table two columns one of them for the hight (PK) and the second is the weight. If the user entered in the textbook his weight and hight, it will be searched in the database if this weight is for this height. For example if you're 5 inches, your weight should be 22lb - 38 lb for example.

Any help would be grateful.

Thanks always,

Denver,CO

Can SAP BI connect to SQL Server 2005 and read tables and columns NOT in upper case?

Fri, 25 Apr 2008 03:14:22 Z

An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?

Visual Studio .Net 2005 and sqlServer 2008

Thu, 02 Jun 2011 12:28:32 Z

Will I be able to connect to a sql Server 2008 database with Visual Studio .Net 2005 (Professional Edition)?

My operating system is Windows 7 (Ultimate Edition).

Need Help in SQL Bulk Insert

Tue, 31 May 2011 08:53:29 Z

Hi Friends,

Could any one help me to insert data from the file which contains header , acutal data to be insert and footer

please find the file format below for your referance.

START-OF-FILE
RUNDATE=20110527
REPLYFILENAME=CADMarketPrice.out
FIRMNAME=dl426512
DATEFORMAT=yyyymmdd
COMPRESS=yes
PROGRAMNAME=getdata
PROGRAMFLAG=one-shot
SECMASTER=yes
CLOSINGVALUES=yes
DERIVED=yes
START-OF-FIELDS
FX-RATE
END-OF-FIELDS
TIMESTARTED=Fri May 27 02:37:22 EDT 2011
START-OF-DATA
CME|993|1|
CHF|928|1|
LOR|658|1|
TEM|428|1|
SAE|158|1|
END-OF-DATA
TIMEFINISHED=Fri May 27 02:37:25 EDT 2011
END-OF-FILE

I need to insert the data which is present in between 'START-OF-DATA' and 'END-OF-DATA' Tags in the table.

Could any on help me on this,

Thanks in advance

SQL Server crashes when remote query fails

Mon, 17 Aug 2009 12:29:45 Z

Setup: I have a linked server setup on SQL Server 2005 which is pointing to an Oracle DB. The linked server has RPC (Both RPC & RPC Out) enabled.

Problem: When a query throws an exception on the remote server (Oracle DB) the SQL Server instance crashes. The logs say that the crash was due to some problem with the RPC call. Is there a way in which I can prevent the entire server to collapse but also use RPC over my linked server.

We appear to be encountering lock escalation with our application. How do we eliminate this behaviour?

Tue, 24 May 2011 21:15:36 Z

We have a rather large Java application (using JDK 1.6 and Microsoft SQL Server JDBC Driver 2.0, version 2.0.1803.100) interfacing with SQL Server 2008. Our isolation level is set to "committed read".

Our application is designed to chain together a series of services to process a given request. Each service in the chain is invoked by inserting a record in a designated table. This is done in the same database transaction as the work done by the individual service. It is critical for our application that the locks are kept at the row level.

With some volume, we are finding that two services in the chain are competing for database locks. We believe that SQL Server is escalating the locks from row to page even though we have specified lock hints on all our insert, update, delete and select-for-update SQLs, as follows ...

insert - with rowlock
update - with rowlock, holdlock
delete - with rowlock
select-for-update - with updlock, rowlock, holdlock

Our tables use identity columns. Could this be the cause of the escalation? Could it be due to an under-powered server?

SQL Query on Excel Sheet Returns only 255 chars

Wed, 13 May 2009 09:16:12 Z

Hi

I have used the foll. SQL Query to Select Data from Excel Sheet

SELECT [Description] FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\SourceExcel.xls', Sheet1$)

Where [Description] is a column in .xls file

The SQL Query Runs without any Errors but gets only 255 chars as output.
My Description Column contains more Than 255 chars .How to get more than 255 chars as output ???

Plz help....

Thanks
anliS

Regards anliS www.businessinsightsconsulting.com

System.Data.SqlClient.SqlException: Login failed for user

Mon, 30 May 2011 19:10:19 Z

I have an ASP application running on SQL Server 2008 R2, I get this error when users attempt to access what I am assuming is the SQL database, I have added the user in question. However I still get the error below and the Stack Trace. I am pretty new to SQL server and could use some help.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'shunts'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Login failed for user 'shunts'.]
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6277312
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
  System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
  System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
  System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +6292062
  System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6291977
  System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +352
  System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +831
  System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +49
  System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6293782
  System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +78
  System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1922
  System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +89
  System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6296606
  System.Data.SqlClient.SqlConnection.Open() +300
  System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +150
  System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +4602223
  System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +3667415
  System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +189
  System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +202
  System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +225
  System.Web.UI.WebControls.Login.AttemptLogin() +166
  System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +93
  System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +52
  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3691

Microsoft OLE DB Provider for ODBC Drivers 80004005

Mon, 30 May 2011 04:22:05 Z

Hello guys,

I have this specific error when I tried to connect to a database:

Microsoft OLE DB Provider for ODBC Drivers 80004005
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/Libraries/Data/dtaAccess.asa, line 60

The weird thing is, there is one database that has this error. I have five databases, with the same connection string, but it works fine. This database is the only problem. Is there I need to figure out in SQL Server Management Studio, on why this one database does not work with connection string.

Thanks,

error 18452, Login failed for user Reason: Not associated with a trusted SQL Server connection.

Mon, 05 Sep 2005 05:21:11 Z

Hi all,
I have two servers running windows 2003 and both having sql server 2000.
I want to establish a linked server between all of them.
i created them . but now when accessing the data from those server the following error is being displayed

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'manoj'. Reason: Not associated with a trusted SQL Server connection.

i have enabled mixed mode authentication.even then its not displaying the intended result.

please respond is urgent.

thanks.

SQL Server Agent Log error

Wed, 25 May 2011 17:45:01 Z

Hi,

We just migrated databases from SQL 2005 to SQL 2008 R2 cluster environment. There are no issues with the migrated databases and all SQL jobs are running normally without any error. However, following errors are logged frequently in SQL Server Agent Error Logs.

2011-05-23 14:30:04 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2011-05-23 16:00:03 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]

The named pipe is enabled on the server. There was no alias set up on The SQL Agent Properties. I added the cluster name as the alias but still getting the same error. The SQL Server Agent is functioning correctly; however, the error message is annoying. Any help will be appreciated.

Thanks,

Bhuwan

Sharing Object between forms in C# Express

Tue, 31 May 2011 16:49:44 Z

Hello every body,

I've question and I need your help. How can I create a sharing object between many forms in C# Express?

Thanks,

Denver,CO

Connect to SQL Server 2005 express From C#.Net Windows Application Via Internet

Fri, 27 May 2011 05:47:12 Z

I've developed a windows application using C#.Net(VS 2008) which uses MS Sql Server 2005 as server. It is running properly in LAN. I'm using SQL Server authentication to connect the DB. SO the connection String looks like

"initial catalog=TestDB;data source=" compName/SQLExpressRAD;Connect Timeout=30;User Id=sa;Password=123456;"

MS SQL Server 2005 installed in a PC running Windows Vista which is work station having a static IP address. So First I tried to connect from SQL Server Management Express using the following settings.

SQL Server Authentication

User Name sa

Password abcdef

Server Name 10.xx.x.xx/compName/SQLExpressRAD,51267

I've added the port 51267 in server, TCP/IP enabled, and created the rule in firewall to open the port..

Referred :- http://msdn.microsoft.com/en-us/library/cc646023.aspx

But I was unable to connect it. Now, my request is "How can I connect to that server from my application?". Please give the instructions to configure the DB and the proper Connection String..

Note:- Is there anything else I must change in my application in order send and receive data via internet. I came a cross something like serialization using XML or something..

Hebrew characters using MSDAORA

Thu, 26 May 2011 10:22:07 Z

Hi,
I have successfuly set-up a connection from Sql Server 2008 R2 using the MSDAORA provider to link and transfer data between an Oracle database to SQL Server. I am able to access the records in Oracle, however, all the fields that contain Hebrew characters appear as gibberish. I changed the font in Tools->Options->Fonts and colors to a appropriate Hebrew font, but this didn't help.

Has anyone an idea how this issue can be solved?

TIA,
Lance

Error: 26 through service, but not iis or exe

Mon, 30 May 2011 08:36:36 Z

Hello,

I'm getting the following error when trying to connect to SQL Server 2008 R2 named instance through a service running under Local System, but not when accessing it through an exe or asp.net page using the same connection string:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

The SQL Server is running on the same machine as the service, exe, and IIS. I've also tried named pipes and I still get the same problem (Error 40 though instead of 26). Sql Browser is running and returned information when I ran portqry.

Any help would be appreciated.

TechNet Version on LAN/WAN

Sun, 29 May 2011 17:15:09 Z

I have installed the Technet 64 bit version of SQL Server 2008 R2 on Windows Server 2008 R2. While I can query Adventureworks in Management Studio, I cannot make an ODBC connection to it from any other box on my LAN (Windows 7 Pro and some Macs using Actual Technologies drivers). Port 1433 is open and pointed correctly, and the named instance is set to accept TCP/IP connections. I am starting to think that the Technet version just won't allow external connectivity.

I have 3 questions:

1 - How can I tell what version I have and then find out what it allows?

2 - How can I verify that the OCBC driver on the Windows 7 box is the correct one? It is SQL Server Native client 10.0. When I try to set up a DSN, it sees the server (i.e., the name of the machine) but not the named instance. It times out when I try to connect to the server.

3 - If the current version is not, in fact, sufficient, which of the millions of versions would I need to purchase? My goal is to use it for my own development use, but allow single connections from either side of the firewall.

Thank you,

Rebecca

Add Connection Server Name - Visual Studio 2008

Fri, 27 May 2011 21:35:30 Z

I am having problems connecting Visual Studio 2008 to SQL Server 2008 R2 Express. The Server Name doesn't appear in the Server name box of Add Connection, even after trying to refresh. Windows 7 has been fully updated except for SP1, so I'm not sure that is the problem.

Looking at other forums, it appears that there is a known problem here?!

SQL Connection ALways Fail on the First Try

Sun, 29 May 2011 12:50:56 Z

Hi,

I' am having issues with SQL Server 2008 R2. It is installed on a Virtual Machine within an SBS DOmain . It is strange that there is always a Time out Expired Error on the Initial Try of My Applications when trying to Connect to the SQL Server instance. Tried it also using SQLCMD and the same thing at first there is a Time Out Error and then the nExt or Third Try it will be OK and it will work smoothly again until i close the Application that im using. But when i Tried Again to Open An Application that access the SQL Server instance the same thing again Time Out Error. It Seems that the problem exists on the Initial Connection try of Every Application that i Have.

Does anyone experienced such problems. And what is actually wrong. Im using a Named SQL Instance.

Join two tables and pivot results from one

Fri, 27 May 2011 20:20:51 Z

I have been struggling with this for hours. I have to join two tables and pivot the results from the 2nd table

TableA
========
ClaimID - unique identifier
ClaimNumber
InjPartySSN

TableB
=========
ClaimID - not unique
ICDDiagnosisCode - not unique
SequenceNumber - not unique

*But the combo of the ClaimId + SequenceNumber would be unique

select A.ClaimId, ClaimNumber, InjPartySSN, ICDDiagnosisCode
from TableA A, TableB B
Where A.ClaimId = B.ClaimId

The simple solution would be to join the two tables by ClaimID and if the Claim has multiple ICDDiagnosisCodes then there would be a row for each one. But that is not what the customer wants. The customer wants to see each Claim only once and if there are multiple ICDDiagnosisCodes, then place them in a new column on the same row.

I don't know if this helps, but the max number of ICDDiagnosisCodes they can have on a claim is 19.

If anybody can help me, I would truly appreciate it.

odbc connection re establishes after application pool recycle

Sun, 29 May 2011 06:52:31 Z

I have a web service application which connects to databases through odbc sql native client and SQL Server drivers. We have application pool recycling scheduled every day night. But not too often after the scheduled restart, the application stops connecting to database throwing the error 08001 with the message unable to connect to data source. But the problem disappears when I recycle the application pool manually. I dont understand why is is not connecting to the data source. If the issue is with the sql server, how it connects after app pool recycle?