Showing posts with label written. Show all posts
Showing posts with label written. Show all posts

Tuesday, March 20, 2012

Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David

problem with functions and datetime parameters!

Hi all.
i've written a portion of sql code with a dtetime parameter that run very
very fast on a sql window, but when i create a function with the same code
the execution time is extremely long!
to recreate the same speed i found that i must declare a local variable
inside the scope of the function and then assign the variable passed to the
function.
does anyone had the same problem? there is a solution to this bug?
this is my code...
regards,
stefano
create function kp.getQuotaHWM (@.dd1 datetime)
returns float
as
begin
declare @.dd datetime
set @.dd = @.dd1
return (
... code of the function
)
endOn Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:

>Hi all.
>i've written a portion of sql code with a dtetime parameter that run very
>very fast on a sql window, but when i create a function with the same code
>the execution time is extremely long!
>to recreate the same speed i found that i must declare a local variable
>inside the scope of the function and then assign the variable passed to the
>function.
>does anyone had the same problem? there is a solution to this bug?
Hi stefano,
This is a known issue. Not exactly a bug - more an unwanted side effect
of a wanted feature.
Search this group (or the internet) for "parameter sniffing" to find
alll the details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo.
many thanks for your informations.
regards, stefano
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q5iff1pndv10nhhr32c650b9vb5evi0fkb@.
4ax.com...
> On Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:
>
> Hi stefano,
> This is a known issue. Not exactly a bug - more an unwanted side effect
> of a wanted feature.
> Search this group (or the internet) for "parameter sniffing" to find
> alll the details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 9, 2012

Problem with empty resultsets from SQL 2005 and RDO

We have an application written in VB6 using RDO that we're trying to migrate to SQL 2005 from 2000. We've encountered a problem where a stored procedure returns a resultset and calls another stored procedure that returns another resultset. If the second resultset does not contain any rows and the second stored procedure generates a warning message ("Warning: Null value is eliminated by an aggregate or other SET operation."), the MoreResults method fails in trying to access the second resultset.

If the second resultset is not empty or I modify the stored procedure to eliminate the warning message, then I am able to access the second resultset. The exact same code works against a SQL 2000 server with identical stored procedures and data, regardless of the the resutlset being empty or the warning message being generated. I can also execute the stored procedure calls from Query Analyzer and the results look identical in SQL 2000 and 2005, but somehow there's something different in how RDO sees the results.

Has anyone else seen this problem or have I missed something? SET ANSI_WARNINGS OFF would eliminate the message, but we use indexed views, which requires ANSI_WARNINGS ON.

Can you provide us with sample repro code for this issue?

Thanks
Suroor

Saturday, February 25, 2012

problem with DBLib access to SQL Server 2005

Hi all,
I'm having a (probably) fundamental problem executing an insert statement on
SQL Server 2005 from an app written in plain C, using the DBLib API. Please
DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just th
e
fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005.
All appears to go well for the init, login and begin transaction calls. The
first and any subsequent inserts however cause dbresults to return 2, which,
depending on where and what in the docs you want to believe, means
NO_MORE_RESULTS. In the context of an insert, that doesn't make much sense.
In any case, the data did not get inserted.
When I keep SQL Monitor open during those calls, I see the calls are being
made, the insert statements transferred, and when I copy/paste them into the
SQL Manager window and execute them, they work just fine, no syntax problems
there.
Here are a few code snippets, maybe somebody can see where I'm messing up.
the database init:
// Initialize DB-Library.
dbinit();
// Get a LOGINREC.
login = dblogin ();
DBSETLSECURE (login);
DBSETLAPP(login, "mydb");
DBSETLUSER(login, "TITAN\\user");
// Get a DBPROCESS structure for communication with SQL Server.
dbproc = dbopen(login, "Titan");
//switch the default database to the mydb DB:
dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
dbsqlexec (dbproc);
This part does not produce any error messages. I'm logging into sql server
as the logged in user, which should be the sa in my default environment.
Then, I initiate a transaction. Each transaction will consist of about
20'000 inserts:
strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
// Send the command to SQL Server and start execution.
dbcmd (dbproc, cBuf);
dbsqlexec (dbproc);
// Process the results.
rc = dbresults (dbproc);
if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
printf("Command failed: %s", cBuf);
dbexit();
exit(1);
}
This returns rc = 1 (i.e. SUCCEED).
Then I begin with the insert statement:
dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
rc = dbsqlexec (dbproc);
// Process the results.
rc = dbresults(dbproc);
if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
fprintf(stderr, "dbresults returned: %d\n", rc);
dbexit();
exit(1);
}
Now, rc = 2. Why? The same statement executed in the SQL Server Manager
window works just fine, the same statement in SQLite worked just fine as wel
l
(actually, the " were ' and had to be changed to " because SQL Server
complained about syntax).
This happens regardless of whether I start with a "BEGIN TRANSACTION" call
or not.
Cheers
- BaltBalt,

fastest way with the least overhead, and as such my method of choice and
currently still available with SQL Server 2005. <<<
Actually it is not still available with 2005 you have to get it from a
previous version to even use it. It is obsolete for a reason and your
excuse for using it simply will not cut it. Even if you get thru this
hurdle you will ultimately hit another. If you are trying to insert 20K rows
as fast as possible I suggest you use one of the newer bulk insert api's
instead. I know this is not what you wanted to hear but this is a loosing
proposition in the long run.
Andrew J. Kelly SQL MVP
"Balt" <Balt@.discussions.microsoft.com> wrote in message
news:CBB6F9F3-711F-4837-B831-C62DEA5F411A@.microsoft.com...
> Hi all,
> I'm having a (probably) fundamental problem executing an insert statement
> on
> SQL Server 2005 from an app written in plain C, using the DBLib API.
> Please
> DO NOT comment on the fact the DBlib is obsolete, I know it is. It's just
> the
> fastest way with the least overhead, and as such my method of choice and
> currently still available with SQL Server 2005.
> All appears to go well for the init, login and begin transaction calls.
> The
> first and any subsequent inserts however cause dbresults to return 2,
> which,
> depending on where and what in the docs you want to believe, means
> NO_MORE_RESULTS. In the context of an insert, that doesn't make much
> sense.
> In any case, the data did not get inserted.
> When I keep SQL Monitor open during those calls, I see the calls are being
> made, the insert statements transferred, and when I copy/paste them into
> the
> SQL Manager window and execute them, they work just fine, no syntax
> problems
> there.
> Here are a few code snippets, maybe somebody can see where I'm messing up.
> the database init:
> // Initialize DB-Library.
> dbinit();
> // Get a LOGINREC.
> login = dblogin ();
> DBSETLSECURE (login);
> DBSETLAPP(login, "mydb");
> DBSETLUSER(login, "TITAN\\user");
>
> // Get a DBPROCESS structure for communication with SQL Server.
> dbproc = dbopen(login, "Titan");
> //switch the default database to the mydb DB:
> dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
> dbsqlexec (dbproc);
> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
> strcpy_s(cBuf, sizeof(cBuf), "BEGIN TRANSACTION");
> // Send the command to SQL Server and start execution.
> dbcmd (dbproc, cBuf);
> dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults (dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> printf("Command failed: %s", cBuf);
> dbexit();
> exit(1);
> }
> This returns rc = 1 (i.e. SUCCEED).
> Then I begin with the insert statement:
> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
> rc = dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
> Now, rc = 2. Why? The same statement executed in the SQL Server Manager
> window works just fine, the same statement in SQLite worked just fine as
> well
> (actually, the " were ' and had to be changed to " because SQL Server
> complained about syntax).
> This happens regardless of whether I start with a "BEGIN TRANSACTION" call
> or not.
> Cheers
> - Balt
>|||Balt (Balt@.discussions.microsoft.com) writes:
> All appears to go well for the init, login and begin transaction calls.
> The first and any subsequent inserts however cause dbresults to return
> 2, which, depending on where and what in the docs you want to believe,
> means NO_MORE_RESULTS. In the context of an insert, that doesn't make
> much sense.
Actually, it does. An INSERT statement will return a rowcount (the
thing you see as a ("1 row affected") in Query Analyzer or Mgmt Studio,
unless SET NOCOUNT ON.

> //switch the default database to the mydb DB:
> dbcmd (dbproc, "EXEC sp_defaultdb 'sa', 'mydb'");
> dbsqlexec (dbproc);
This seems like a very funny thing to do. And dangerous. Changing the
default database for sa is something I never had the guts to try.
And why would you change the default database for sa, when you logged
in with integrated security?

> This part does not produce any error messages. I'm logging into sql server
> as the logged in user, which should be the sa in my default environment.
No! "sa" is an SQL login, and you login with Integrated Security. If you
have admin rights in Windows, you will have symin rights in SQL Server,
but you will not be sa.

> Then, I initiate a transaction. Each transaction will consist of about
> 20'000 inserts:
As Andrew said, consider using bulk operations instead.
Another alternative is to build an XML document of all the 20000
rows, send that to SQL Server and then shred it into the target table(s).
If you really want to send row-by-row, use a stored procedure that you
call through RPC instead. This is faster, as you get a cached query plan,
and there is no need for parsing the query each time.
And if you don't want to use stored procedures, at least call
sp_executesql through RPC instead. Hm, wait, sp_executesql insists
on parameters on nvarchar that DB-Library cannot handle. So that won't
fly.
Anyway, whatever, don't send 20000 INSERT statements over the wire. It's
simple ineffecient.

> dbcmd (dbproc, "INSERT INTO data (\"Field1\") VALUES (-12);");
For this to work, the setting QUOTED_IDENTIFIER must be ON. Which it is
by default with ODBC, OLE DB and SqlClient. But not with DB-Library. So
you would need to issue SET QUOTED_IDENTIFIER ON from your program. Or
use [] to quote your identifiers instead.
Then again, I told you not to send INSERT statements.

> rc = dbsqlexec (dbproc);
> // Process the results.
> rc = dbresults(dbproc);
> if (rc != SUCCEED && rc != NO_MORE_RESULTS) {
> fprintf(stderr, "dbresults returned: %d\n", rc);
> dbexit();
> exit(1);
> }
> Now, rc = 2. Why?
Because there are no more results. But not from the INSERT statment.
You failed to check the return code from dbsqlexec, but I bet it says
FAIL. Because you previous call to dbresults return SUCCEED, and this
means that you have not cleared out the previous command, so you
get an error when trying to start a new one. You should always loop
over dbresults until it returns FAIL or NO_MORE_RESULTS.
Finally, there are tons of limitations you run into when you use
DB-Library, so it is very difficult to recommend it. It is a very nice
client API once you have learnt to master it. Particularly, it does not
perform tricks behind your back. However, it certainly has a taste
of black-and-white television these days.
--
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