CLR test script SELECT returns no row data

Hi,

The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"

I get the result as:
Name Address
- -
No rows affected.
(1 row(s) returned)

But not the actual row is displayed whereas I would expect to see something like:

Name Address
- -
John Doe
No rows affected.
(1 row(s) returned)

I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.

Why no results in first case?

Thanks,
Bahadir
You maybe still have the transaction open and uncommitted, thats why you don′t see the actual row.

HTH; Jens SUessmeyer.

http://www.sqlserver2005.de

Client Web Browser & Remote SQL Script Error?

Hello all,

I know that this is a long shot, but I have a problem which someone
reading this group *may* just be able to shed some light on.

We have a new internal personnel planner/attendance system in place. It
uses a web interface to allow members of staff to select their site
location for any week, request leave and record absences.

The server-side scripting is composed of VB/ASP and Javascript which
ultimately queries & writes to an MS SQL Server 2000 database via an
ODBC connection between webserver (IIS6.0).

Here is the problem and I *know* that it sounds unlikely/impossible but
we carried out exhaustive tests.

-> I open a web browser on my local PC (XP Pro SP2)
-> Login to the web-based planning system
-> Update information
-> Submit Changes

At this point I get a script error:

--error message--

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ')'.

/staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108

------

Which is fair enough as it probably points to a problem with the SQL
statement being passed by the .asp page.

***BUT*** I _only_ get this message when I access the page via a browser
from certain PCs!

I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.

I have obviously ensured that it isn't any kind of browser caching issue.

I have no issues using scripted-based web pages in general from the PC
in question.

I have checked the regional settings to ensure that I am using a
standard character set.

I *can* submit database changes via web browsers from most other PCs on
our network, without incurring the error message.

Even when I get the error message, the values are written to the db and
the information is updated.

The other PCs from which I am experiencing the same difficulty are
servers running Win2000 & Win2003 respectively.

Is there any kind of OS/User Profile setting which would mean that
scripts running on a remote webserver would fall over when trying to
perform an SQL write?

Could it be some kind of character encoding issue, which means that the
parameters that are received by the .asp script and then written via the
SQL statement are mangled in any way?

I can't see that there is any kind of local setting that would influence
whether remote scripts would or would not cause an error such as this.

If anyone has any ideas, has encountered anything similar or can shed
light on a system-specific setting or feature that may cause this,
please post your thoughts.

Yours Gratefully

GuyIf the server-side code path is identical, I suggest you focus on the client
browser settings. My guess is that you have client-side script that doesn't
execute under certain configurations so data isn't posted back as expected.
This causes your server-side script to generate an invalid SQL statement
like 'INSERT INTO MyTable VALUES()' and results in the error you describe.

There are other clues in your post that you aren't following Best Practices
so you might want to revisit the items below.

Rather than ODBC, consider using the OLE DB provider directly. For Windows
Authentication:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI"

For SQL Authentication:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User
Id=MyLogin;Password=myPassword1"

Consider using parameterized SQL Statements rather than build SQL statement
strings. This is more secure, provides better performance and eliminates
the need to double-up embedded quotes and format dates. For example:

myCommand.CommandText = "INSERT INTO MyTable VALUES(?)"
Set myIntegerParameter = myCommand.CreateParameter( _
"@.MyIntegerParameter", 3, 1)
myCommand.Parameters.Append myIntegerParameter
myIntegerParameter.Value = Request("UserValue")

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Guy Debord" <laugh@.life.org> wrote in message
news:tyDIf.45673$Rw6.23215@.newsfe7-gui.ntli.net...
> Hello all,
> I know that this is a long shot, but I have a problem which someone
> reading this group *may* just be able to shed some light on.
> We have a new internal personnel planner/attendance system in place. It
> uses a web interface to allow members of staff to select their site
> location for any week, request leave and record absences.
> The server-side scripting is composed of VB/ASP and Javascript which
> ultimately queries & writes to an MS SQL Server 2000 database via an
> ODBC connection between webserver (IIS6.0).
> Here is the problem and I *know* that it sounds unlikely/impossible but
> we carried out exhaustive tests.
> -> I open a web browser on my local PC (XP Pro SP2)
> -> Login to the web-based planning system
> -> Update information
> -> Submit Changes
> At this point I get a script error:
> --error message--
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108
> ------
> Which is fair enough as it probably points to a problem with the SQL
> statement being passed by the .asp page.
> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!
> I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.
> I have obviously ensured that it isn't any kind of browser caching issue.
> I have no issues using scripted-based web pages in general from the PC
> in question.
> I have checked the regional settings to ensure that I am using a
> standard character set.
> I *can* submit database changes via web browsers from most other PCs on
> our network, without incurring the error message.
> Even when I get the error message, the values are written to the db and
> the information is updated.
> The other PCs from which I am experiencing the same difficulty are
> servers running Win2000 & Win2003 respectively.
> Is there any kind of OS/User Profile setting which would mean that
> scripts running on a remote webserver would fall over when trying to
> perform an SQL write?
> Could it be some kind of character encoding issue, which means that the
> parameters that are received by the .asp script and then written via the
> SQL statement are mangled in any way?
> I can't see that there is any kind of local setting that would influence
> whether remote scripts would or would not cause an error such as this.
> If anyone has any ideas, has encountered anything similar or can shed
> light on a system-specific setting or feature that may cause this,
> please post your thoughts.
> Yours Gratefully
> Guy|||Dan,

Thanks for taking the time to offer up your suggestions.

Using parameterized SQL statements looks like it may well help to
eliminate some of the errors which are easy to make using lengthy
statement strings.

Thanks for the tip.

The OLE DB versus ODBC issue is one that I have one that I will address.

As for the the client-side script suggestion, I'll dig deep and let you
know if I can find any likely suspects.

Thanks again,

Guy|||Guy Debord (laugh@.life.org) writes:
> At this point I get a script error:
> --error message--
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108

I've seen plenty of those pages on the web!

> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!

Well, rather than looking at the PCs, look at the SQL instead. Set up a
trace with Profiler, and see what is being sent to SQL Server. Include
the error events and StmtStarting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Client Tools for SQL Express

I am looking for information on Client tools for SQL Express. I am mainly looking for a way to script out the objects and then Add them back into a new file. I wish to do this so that I can ship the SQL Scripts for an application so that the user can load them up once distributed, and also to allow me to create the objects and load them at design time. I used to be able to have a common set of tables that I would use inside applications, and when working on a project load them if they were needed.
One of the other reasons is the problem with the Beta2 and RC engines and the changes that have been made. I know that the two versions do not work well together and I want to be able to script out the objects and put them into either the RC or in turn the RTM Version.
I know that there are the management tools that ship with the full sql product (Not Express), but I fell that there should not be a need to install these as there should be some command line tools that can do the job. I have seen the command line tool on the MSDN Download site, but could not see how to script out the objects.

Maybe I could use the SQLCMD tool but I can not work out how to connect to the mdf file as I do not have a server running as it is the express edition. (Even though it ships with the express edition).I'm not sure I understand the following statement: "Maybe I could use the SQLCMD tool but I can not work out how to connect to the mdf file as I do not have a server running as it is the express edition."

In order to use SQL Server Express the SQL Server service must be running. Once it's running you can connect using SQLCMD (sqlcmd -S .\SQLEXPRESS -E).

Best of luck,
Dan|||Thanks for that, I have been able to connect to the SQL Express engine using the SQLEUtil (I think thats it) that you can download from the Microsoft downloads site, but still trying to script out the database objects.

What I have started looking at is the SQL-SMO System (http://www.ircomm.net/blogs/mykre/archive/2005/10/18/615.aspx), with this I hope to develop a system that I can use to script out the Database objects (Table structure and all). This way I will be able to use this system to store my SQL Scripts in a source code management system, or publish the scripts that I develop with my applications.

One of the main reasons at present is that I have been running Beta 2 of the framework at home, and am now running the RC version at work. When I tried to move an application from home to work the MDF Files where not compatible and I needed to recreate them. As the Release draws near I am worried as I have Beta 2 database files that I am working with and when new version is released how am I going to recreate the files.

I also can for see a problem here at work which I have posted in the following post, http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=111170.

Client timeout expired, settings on 10hrs on server, how change cl

A client has to insert many files with bulk insert. I start it using windows
scripting host with VB Script and ole db provider for SQL Server. works good
and fast. Only really big files will take several minutes to terminate.
How can I set the client timeout?
I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
be installed on windows 2003 server) all on same server. Because scripts are
interacting with sql-server (I'm looking foreward to sql server 2005!!!)
I have seen, some have the same problem, but I have not seen solutions.
Do I have to use ODBC instead of OLE DE?
Thanks for any help!
See if this helps:
http://www.aspfaq.com/show.asp?id=2066
Andrew J. Kelly SQL MVP
"Urban" <urban@.nospamplease> wrote in message
news:AA015201-3F8A-465D-9A45-317870105454@.microsoft.com...
>A client has to insert many files with bulk insert. I start it using
>windows
> scripting host with VB Script and ole db provider for SQL Server. works
> good
> and fast. Only really big files will take several minutes to terminate.
> How can I set the client timeout?
> I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
> be installed on windows 2003 server) all on same server. Because scripts
> are
> interacting with sql-server (I'm looking foreward to sql server 2005!!!)
> I have seen, some have the same problem, but I have not seen solutions.
> Do I have to use ODBC instead of OLE DE?
> Thanks for any help!