SQLNCLI Provider Problem? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQLNCLI Provider Problem?

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2010-02-27 : 06:24:54

I'm not sure what's going on, but I ran into a problem today.

I currently run a website with data on an SQL 2008 Server. The website is in ASP classic with a connection string of:

Provider=SQLOLEDB;Data Source=(ip addy);User Id=(myUser);Password=(myPwd);Initial Catalog=(dbName);

I have many stored procedures which return data in XML format (for XML EXPLICIT)

All this works well!

My hosting company is changing their servers. The new SQL servers do not connect with the provider SQLOLEDB. I can connect to the database instead with SQLNCLI as the provider.

Everything works great if I return recordsets. However, for the pages which return XML, I have noticed that I get an error like so:

A red box displaying:
The page contains the following error:
error on line 1 at column 120: internal error
Below is a rendering of the page up to the first error.

That's it. When I look at the source code for the page, I get my first line as:

<?xml version="1.0" encoding="utf-8" ?>

the next two lines seems like a jungle of chinese letters mixed with other unreadable character.

Has anyone seen this before? What's going on? I started playing around with RESPONSE.CODEPAGE to see if that works (currently at 65001. I'd like to post more data, but hopefully someone has seen this and can point me in the right direction.

Please let me know if you have any idea/clue to what's going on and/or a possible solution.

Thanks.

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 06:48:07

"Provider=SQLNCLI10" any better?

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2010-02-27 : 06:52:39

quote:
Originally posted by Kristen

"Provider=SQLNCLI10" any better?

sorry, failed to mention, yes I tried that too. ALong with other option at http://www.connectionstrings.com/sql-server-2008

=)

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2010-04-20 : 12:00:13

Still having this problem. I have been searching on and off and still have not found a solution to this problem.

I am posting images so you can see what I'm talking about.

Quicky things I noticed: I have 2 webservers. One hosted by dedicated hosting company and one I have full access to. They both have SQL Server 2008, IIS7, Windows Server 2008. When I connect to either database via connection string like this: Provider=SQLOLEDB (or SQLNCLI10);Data Source=(ip addy);User Id=(myUser);Password=(myPwd);Initial Catalog=(dbName); I get the error images below. HOWEVER, when I connect to either database through DSN (DSN=DSNname;User Id=(myUser);Password=(myPwd);Initial Catalog=(dbName);) it works just fine! Another thing I noticed, both connection strings work if the database is on SQL Server 2000.

Browser:

View Source:

The page calls a stored procedure like this:

select top 10
    1 as tag
    , null as parent
    , column1 as [item!1!column1!element]
    , column2 as [item!1!column2!element]
from
    table1

for XML EXPLICIT

Also, if I remove 'for XML EXPLICIT' I do get a readable recordset result, though it is not what I want.

I hope someone can shed some light.

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

Kristen
Test

22859 Posts

Posted - 2010-04-20 : 12:15:25

Long shot: Are you retrieving the ResultsSet with a conventional ResultSet object, or a Stream object? I think XML needs a Stream doens't it? (Might have worked in the past ... of course)

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2010-04-20 : 12:37:46

I'm still using an ASP Classic code. Here it is:

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
	.ActiveConnection = dbcMain
	.CommandText = "name of proc"
	.CommandType = adCmdStoredProc
	
	.Parameters.Append .CreateParameter("@RetVal", adInteger, adParamReturnValue, 4)
	.Parameters.Append .CreateParameter("@Level", adInteger, adParamInput, 4, Level)
End With

Set rsItems = Server.CreateObject("ADODB.Recordset")
With rsItems
	.CursorLocation = adUseClient
	.CursorType = adOpenStatic
	.LockType = adLockBatchOptimistic
	Set .Source = cmd
	.Open
	Set .ActiveConnection = Nothing
End With

If NOT rsItems.BOF AND NOT rsItems.EOF Then
	
	OutputXMLQueryResults rsItems,"items"
	
End If

Set rsItems = Nothing
Set cmd = Nothing

Sub OutputXMLQueryResults(RS,RootElementName)
	Response.Clear
	Response.ContentType = "text/xml"
	Response.Codepage = 65001
	Response.Charset = "utf-8"
	Response.Write "<?xml version=""1.0"" encoding=""utf-8"" ?>"
	Response.Write "<" & RootElementName & ">"
	While Not RS.EOF
		Response.Write RS(0).Value
		RS.MoveNext
	WEnd
	Response.Write "</" & RootElementName & ">"
	Response.End
End Sub

(edit) In any case, I'll look into using stream. I have never used it. If you have any good places you know I should start, please let me know. Thanks.

(edit 2) Just to be clear, the output this page is supposed to display is in XML format. ie:

<?xml version="1.0" encoding="utf-8" ?>
<items>
  <item>
    <column1>value1</column1>
    <column2>value2</column2>
  </item>
  <item>
    <column1>value1</column1>
    <column2>value2</column2>
  </item>

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources