How can I convert DateTime to Date as Parameter?

Hi Guys! Need Help on this!! I am using a Datetime data type as my Parameter on my stored procedure in SQL Server 2005. I am also using Crystal Reports XI for my reporting using the stored procedure in SQL but my problem is that I want to use ONLY the DATE data type as my Parameter instead of using the datetime parameter in Crystal Reports! Since the SQL server does not have a Date data type, how can I convert this from DateTime to Only Date data type as my parameter?....Thanks!!

Use datatime data type and pass just date part from CR or strip off the time part wherever you are planning to use it.

declare @.d datetime

set @.d = getdate()

select dateadd(day, datediff(day, 0, @.d), 0)

go

AMB

|||

Thanks! but how do you pass just the date part from CR? Any idea would be greatly appreciated!! I can strip off the time part inside the stored procedure in SQL 2005 but CR is using the parameter which is datetime....

|||

Sorry about that, but I think that question could be answered better in a CR newsgroup. Try:

datetime(datepart("yyyy", {@.d}), datepart("m", {@.d}), datepart("d", {@.d}), 00, 00, 00)

AMB

|||Thanks AMB....that will work but that code is for the inside on the report...my problem lies in the parameter prompt window..how can I let the user only select the date without seeing the the time on the parameter prompt window?....|||

Sorry I have no idea. As I mentioned in my previos post, these questions would be better asked in a CR newsgroup.

AMB

How can I check if it is currently executing?

Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
RobertRobert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||We are currently using a process similar to this. but the problem is...
If the procedure for any reason crashes or does not complete, the "Currently
Running" flag does not get set back to False
Any Ideas?.?.?.
Robert
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message news:%23EuBy
TeTGHA.5496@.TK2MSFTNGP11.phx.gbl...
Robert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||Hi
Why does it "crash"? It shouldn't crash... what exactly happens? Do you t
hink there is a design or implementation flaw you need to address?
If the SP can be set-up as a job to be run by Server Agent, and if it is onl
y ever going to be run by the agent on a specified schedule, you could use s
p_jobhelp (or whatever its called) to check if the *job* has stopped or is s
till running...
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uCaLYdeTGHA.434
0@.tk2msftngp13.phx.gbl...
We are currently using a process similar to this. but the problem is...
If the procedure for any reason crashes or does not complete, the "Currently
Running" flag does not get set back to False
Any Ideas?.?.?.
Robert
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message news:%23EuBy
TeTGHA.5496@.TK2MSFTNGP11.phx.gbl...
Robert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||check out sp_getapplock in BOL.|||rmg66 (rgwathney__xXx__primepro.com) writes:
> Here's a good one.
> Does any one out there know how to tell if a "particular" (by name)
> stored procedure is currently being executed?
One could inspect sysprocess.sql_handle for every process, but if the
procedure has called an inner procedure you would not see it.
Anyway, Scott had the right answer for you: use Application locks.
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|||I'm sorry if I'm being dense. But I don't quite understand how this (sp_geta
pplock) is to be used.
Will it lock access to any db object (e.g. a particular stored procedure) un
til it is unlocked?
When I tested it. It didn't seeem to do what I expected...
I used it to lock a table object and then attempted to update the table. I d
id it inside a transaction.
I expected the table to reject the update but it didn't.
Am I missing something?
Can someone give me an example of how I could use this to prevent and stored
procedure to be called while it is currently executing?
Thanks, Robert
"Scott Morris" <bogus@.bogus.com> wrote in message news:ePqOKneTGHA.4264@.TK2MSFTNGP11.phx.gb
l...
> check out sp_getapplock in BOL.
>|||> Will it lock access to any db object (e.g. a particular stored procedure)
> until it is unlocked?
It doesn't really "lock" anything. It effectively sets a flag that can be
used as a gatekeeper. If not used correctly, it will not be useful.

> When I tested it. It didn't seeem to do what I expected...
> I used it to lock a table object and then attempted to update the table. I
> did it inside a transaction.
> I expected the table to reject the update but it didn't.
> Am I missing something?
Yes - the whole idea is to serialize processing. If you did not attempt to
execute the procedure simultaneously from multiple connections, then you
only tested the syntactical correctness of your usage (details of which you
did not post so we cannot determine if you used it correctly).

> Can someone give me an example of how I could use this to prevent and
> stored procedure to be called while it is currently executing?
BOL has some important information. In addition, you can search the
newsgroups (as well as the internet) for additional information and
examples. Note that it will not PREVENT a "... stored procedure to be
called..."; it can be used to coordinate simultaneous executions of the
procedure in whatever manner you desire.|||sp_getapplock doesn't lock any data inside the database, it is just there fo
r *you* to use to define
a lock using SQL Servers lock semantics and release the lock at will.
ALTER PROC p AS
BEGIN TRAN
EXEC sp_getapplock @.Resource = 'myLock',
@.LockMode = 'Exclusive'
RAISERROR('Before', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:20'
EXEC sp_releaseapplock @.Resource = 'myLock'
COMMIT
RAISERROR('After', 10, 1) WITH NOWAIT
GO
EXEC p
--Now, within 20 seconds, execute this from another connection
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uTWo0qoTGHA.2276@.tk2msftngp13.phx.gbl...
> I'm sorry if I'm being dense. But I don't quite understand how this (sp_ge
tapplock) is to be used.
> Will it lock access to any db object (e.g. a particular stored procedure)
until it is unlocked?
> When I tested it. It didn't seeem to do what I expected...
> I used it to lock a table object and then attempted to update the table. I
did it inside a
> transaction.
> I expected the table to reject the update but it didn't.
> Am I missing something?
> Can someone give me an example of how I could use this to prevent and stor
ed procedure to be
> called while it is currently executing?
> Thanks, Robert
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message news:ePqOKneTGHA.4264@.TK
2MSFTNGP11.phx.gbl...
>|||Thanks All
and thanks Tibor for that the example of how it works...
Robert
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:eL
TjZKpTGHA.196@.TK2MSFTNGP10.phx.gbl...
> sp_getapplock doesn't lock any data inside the database, it is just there
for *you* to use to define a lock using SQL Servers lock
> semantics and release the lock at will.
> ALTER PROC p AS
> BEGIN TRAN
> EXEC sp_getapplock @.Resource = 'myLock',
> @.LockMode = 'Exclusive'
> RAISERROR('Before', 10, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:20'
> EXEC sp_releaseapplock @.Resource = 'myLock'
> COMMIT
> RAISERROR('After', 10, 1) WITH NOWAIT
> GO
> EXEC p
> --Now, within 20 seconds, execute this from another connection
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uTWo0qoTGHA.2
276@.tk2msftngp13.phx.gbl...
>

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Hi

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

Or

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Or

How Can I defined the new Data type ?

Or

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

GO

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

After deploying this proc, parameter types are uniqueidentifier, nvarchar(max) and varbinary(max).
nvarchar(max) should be used instead of ntext, and varbinary(max) instead of image types accordingly. Ntext and image are deprecated.

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

--

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Hi

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

|||Let's just make sure we're all on the same page here. From your response above I'm not usre if you realize (if you do I apologize) that ntext/text and image in SQL 2005 are being deprecated and eventually will go away (they are there now only for backward compatibility). They are being replaced with the nvarchar(max)/varchar(max) and varbinary(max) datatypes. These new datatypes have the same storage capabilities as the old ntext and image, but are much easier to work with

So in SQL 2005 you would use the nvarchar(max) type to store larger text date and varbinary(max) to store a large file. Subsequently, when you use VS to create SQCLR assemblies and stored procs you can use the SqlChars and SqlBinary datatypes in order to get automatic mapping to ntext(max) and varbinary(max) during deployment.

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Hi

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

Or

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Or

How Can I defined the new Data type ?

Or

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

GO

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

After deploying this proc, parameter types are uniqueidentifier, nvarchar(max) and varbinary(max).
nvarchar(max) should be used instead of ntext, and varbinary(max) instead of image types accordingly. Ntext and image are deprecated.

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

--

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Hi

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

|||Let's just make sure we're all on the same page here. From your response above I'm not usre if you realize (if you do I apologize) that ntext/text and image in SQL 2005 are being deprecated and eventually will go away (they are there now only for backward compatibility). They are being replaced with the nvarchar(max)/varchar(max) and varbinary(max) datatypes. These new datatypes have the same storage capabilities as the old ntext and image, but are much easier to work with

So in SQL 2005 you would use the nvarchar(max) type to store larger text date and varbinary(max) to store a large file. Subsequently, when you use VS to create SQCLR assemblies and stored procs you can use the SqlChars and SqlBinary datatypes in order to get automatic mapping to ntext(max) and varbinary(max) during deployment.

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas

How can I catch all errors of the stored at the same time?

I have a stored prcedure . In the stored I wrote 3 SQL statements, one is OK but 2 other statements have error as:

1. Invalid column name 'F2'

2. Invalid object name '##_152008049'.

I put the stored inside try block and catch error in catch block as the following. But I always catch only the first error : invalid column name F2 . How about the second statement?

How can I catch all the errors when I put the stored in try block. Now I don't want to add try..catch inside the store for each statement.

Begin try

exec mystored

End try

begin catch

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

end catch

You are only getting the first one, because when you encounter the first error, it will fall through to the catch block. Any statements after the error don't even get executed.|||Not all errors are of the same kind, there is a difference between statement abort and batch abort errors. But as the previous poster already said, there is no way to return to the next statement after the catch block was handled.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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.For it is a lot of ways... true way and another ones...
Check inserted(input) data before ... insert update delete through sp... Using "INSTEAD OF
" triggers for checking yours data... client side errors handling ... ADO have got Errors collection...

http://msdn.microsoft.com/
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_errorformats_0tpv.asp

MSDN
Handling Errors in Visual C++
In COM, most operations return an HRESULT return code that indicates whether a function completed successfully. The #import directive generates wrapper code around each "raw" method or property and checks the returned HRESULT. If the HRESULT indicates failure, the wrapper code throws a COM error by calling _com_issue_errorex() with the HRESULT return code as an argument. COM error objects can be caught in a try-catch block. (For efficiency's sake, catch a reference to a _com_error object.)
Remember, these are ADO errors: they result from the ADO operation failing. Errors returned by the underlying provider appear as Error objects in the Connection object's Errors collection.
The #import directive only creates error-handling routines for methods and properties declared in the ADO .dll. However, you can take advantage of this same error-handling mechanism by writing your own error-checking macro or inline function. See the topic Visual C++ Extensions for examples.

MSDN
How Does ADO Report Errors?
ADO notifies you about errors in several ways:
ADO errors generate a run-time error. Handle an ADO error the same way you would any other run-time error, such as using an On Error statement in Visual Basic.
Your program can receive errors from OLE DB. An OLE DB error generates a run-time error as well.
If the error is specific to your data provider, one or more Error objects are placed in the Errors collection of the Connection object that was used to access the data store when the error occurred.
If the process that raised an event also produced an error, error information is placed in an Error object and passed as a parameter to the event. See Chapter 7: Handling ADO Events <pg_ado_eventhandling.htm> for more information about events.
Problems that occur when processing batch updates or other bulk operations involving a Recordset can be indicated by the Status property of the Recordset. For example, schema constraint violations or insufficient permissions can be specified by RecordStatusEnum values.
Problems that occur involving a particular Field in the current record are also indicated by the Status property of each Field in the Fields collection of the Record or Recordset. For example, updates that could not be completed or incompatible data types can be specified by FieldStatusEnum values.

How can i automate the emailing of the result set as a file.

I want to create a job that runs a stored procedure and
present the result set in txt. The administrator has to be
informed of sucess and also the result must be emailed to
the administrator. This will be happening on weekly basis.
I have created the stored procedure and the job that
informs the Administrator. The problem is how can i
automate the emailing of the result set as a file with the
alerting after running the job. Please help.What about something like
master..xp_sendmail
@.recipients = 'Allan Mitchell',
@.query = 'Exec byRoyalty 100',
@.dbuse = 'pubs',
@.attach_results = 'true'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Naz" <milnaz@.hotmail.com> wrote in message
news:3e9c01c37c2f$ca20f310$a501280a@.phx.gbl...
> I want to create a job that runs a stored procedure and
> present the result set in txt. The administrator has to be
> informed of sucess and also the result must be emailed to
> the administrator. This will be happening on weekly basis.
> I have created the stored procedure and the job that
> informs the Administrator. The problem is how can i
> automate the emailing of the result set as a file with the
> alerting after running the job. Please help.

How can I always close a text file from a CLR Stored Procedure even when the process is killed?

I have a C# SQL 2005 .net stored procedure which scrubs a text file looking for characters not in a range of characters and replacing them with another character. This works fine except when the process is killed. When this happens the file handle of the file being scrubbed is not released. I use a try catch finally block when opening the file and the output file. The finally section fiushes the output file and closes all files and streams but still when I go to access the file again or use the file in explorer it says the file is still in use. Should I be handling this some other way? How do I know the files will always be closed correctly.

This behavior could happen because that when you kill the process, your finally section may not be called. Eventually, the file handle will be released when garbage collector kicks in.

How can default schema change in stored procedure ?

Hello,

How can default schema change in stored procedure ?

For Example:

There are two user 'User1', 'User2' in TestDb Database.
These users default schema is same name, like 'User1's default schema is 'User1', and 'User2's default schme is 'User2'.
And each users have 'Table1' table, like [User1].[Table1], [User2].[Table1]

In this enviroment,
query 'SELECT * FROM [Table1]' refer default schema of execute user.
like 'User1' execute 'SELECT * FROM [User1].[Table1]'.

But if dbo create a stored procedure below, default schema doesn't work.

CREATE PROCEDURE SelectTable1
AS
SET NOCOUNT ON
SELECT * FROM Table1
GO

When User1/User2 execute this stored procedure, error happend because Table1 not found.

So, I want to change default schema in stored procedure to current users default schema.
EXECUTE AS CALLER is change current user principal only, this doen't change default schema.

Regards,

This is not possible to do in TSQL right now without using dynamic SQL for the query inside the stored procedure. For EXECUTE AS CALLER the unqualified object names resolve against the schema for the owner of the SP and not the caller. This is known issue and there have been requests to provide the facility to resolve object name against the invoker of the SPs. Oracle for example allows you to specify this when creating PL/SQL SPs.

How can control Transactions for creating Stored Procedure ?

I create StringBuilder type for

concating string to create a lot of stored procedure at once

However When I use this command

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

------- This any command

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

on any command

If I use

Create a lot of Tables

such as

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE TABLE [dbo].[Table1](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It correctly works.

But if I need create a lot of Stored procedure

as the following code :

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE PROCEDURE [dbo].[DeleteItem1]
@.ProcId Int,
@.RowVersion Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId AND
[RowVersion] = @.RowVersion
END

CREATE PROCEDURE [dbo].[DeleteItem2]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

CREATE PROCEDURE [dbo].[DeleteItem3]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It occurs Error ???

Please help me

How should I solve them ?

the stored procedure create

CREATE PROCEDURE ..

have to be first in T-SQL command batch. You can do what you need by inserting each single procedure code into varchar(max) variable and run this code using EXEC command like

DECLARE @.lcCommand as varchar(max)

SET @.lcCommand ='CREATE PROCEDURE PROC1 .....'

EXEC (@.lcCommand)

SET @.lcCommand ='CREATE PROCEDURE PROC2 .....'

EXEC (@.lcCommand)

remember if you procedure definition is longer than 8000 chars split it into chunks not longer than 8000 chars to prevent errors( for some reason string passed to varchar(max) in single assign is cut at 8000 position if longer than 8000 chars)

how can check the time?

I have a stored procedure and I want to run a command based on time so if the time is after 8pm the charge the client US$ 10 otherwise US$ 8

Code Snippet

charge = case when datepart(hour, getdate())>=20 then 10 else 8 end

|||

That covers it up to midnight. Most likely you will also want to continue past midnight up to some early morning time.

If so, then something like this: (building on phdiwakar's suggestion) to charge $10 between 8 PM and 6 AM

Code Snippet

Charge = CASE
WHEN ( datepart( hour, getdate()) >= 20
OR datepart( hour, getdate()) <= 5
) THEN 10
ELSE 8
END

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?
stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

how call stored procedure when user click on report line?

have a way to add ability to report, to do somthing, like delete
record, by calling Stored procedure when user click on some line within
the report?Hey I feel reports should be meant for reporting/Viewing. You should not
provide something like delete or modify options from reports. Then it becomes
an entry screen. I hope you will agree on this point.
Amarnath
"mtczx232@.yahoo.com" wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
>|||mtczx232@.yahoo.com wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
RS can call a stored procedure, or any arbitrary SQL with a side-effect
such as deleting a record.
Your SQL should return a result set as well as deleting the record. You
could navigate to another report, passing a parameter containing the id
of the parameter to delete. This "deleting" report would display
instead of the main report, which could look a bit funny.
Alternatively you could navigate to a URL of an ASP or ASPX page that
does the job. Ideally you would set the target so the web page
displayed as a pop-up with a message like "record xx deleted" instead
of replacing the whole report.

How are transactions managed for web services

Hi,
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
Hello McGeeky,

> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGeeky
http://mcgeeky.blogspot.com
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
> Hello McGeeky,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
>
|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>

How are transactions managed for web services

Hi,
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]Hello McGy,

> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGy
[url]http://mcgy.blogspot.com[/url]
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
> Hello McGy,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGy" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
>|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGy" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>

How are text fields stored.. ?

Greetings..
Please help prove I'm right about something. Text fields
in SQL 2000 ~ do text and vchar fields truncate all
whitespace, if the data does not fill up the declared
field length?
E.G.:
vchar Fullname (255)
Put some data into it:
Dr. Jones Warnabe
Will the field in memory be 255 characters or about 17
characters?
Also ~ where would such information BE?
Thank you in advance!
"Mike rucker" <ruckerm@.emaseniorcare.org> wrote in message
news:300301c51e8c$745b1c70$a601280a@.phx.gbl...
> Greetings..
> Please help prove I'm right about something. Text fields
> in SQL 2000 ~ do text and vchar fields truncate all
> whitespace, if the data does not fill up the declared
> field length?
> E.G.:
> vchar Fullname (255)
> Put some data into it:
> Dr. Jones Warnabe
> Will the field in memory be 255 characters or about 17
> characters?
Per BOL, for varchar [(n)], storage size is the actual length in bytes of
the data entered, not "n" bytes.

> Also ~ where would such information BE?
SQL Books Online > Index > varchar data type / text data type

> Thank you in advance!
>
|||thank you! When in doubt... dig deeper in tfm.. ?
>--Original Message--
>
....

How are text fields stored.. ?

Greetings..
Please help prove I'm right about something. Text fields
in SQL 2000 ~ do text and vchar fields truncate all
whitespace, if the data does not fill up the declared
field length?
E.G.:
vchar Fullname (255)
Put some data into it:
Dr. Jones Warnabe
Will the field in memory be 255 characters or about 17
characters?
Also ~ where would such information BE?
Thank you in advance!"Mike rucker" <ruckerm@.emaseniorcare.org> wrote in message
news:300301c51e8c$745b1c70$a601280a@.phx.gbl...
> Greetings..
> Please help prove I'm right about something. Text fields
> in SQL 2000 ~ do text and vchar fields truncate all
> whitespace, if the data does not fill up the declared
> field length?
> E.G.:
> vchar Fullname (255)
> Put some data into it:
> Dr. Jones Warnabe
> Will the field in memory be 255 characters or about 17
> characters?
Per BOL, for varchar [(n)], storage size is the actual length in bytes o
f
the data entered, not "n" bytes.

> Also ~ where would such information BE?
SQL Books Online > Index > varchar data type / text data type

> Thank you in advance!
>|||thank you! When in doubt... dig deeper in tfm.. ?
>--Original Message--
>
...

How are text fields stored.. ?

Greetings..
Please help prove I'm right about something. Text fields
in SQL 2000 ~ do text and vchar fields truncate all
whitespace, if the data does not fill up the declared
field length?
E.G.:
vchar Fullname (255)
Put some data into it:
Dr. Jones Warnabe
Will the field in memory be 255 characters or about 17
characters?
Also ~ where would such information BE?
Thank you in advance!"Mike rucker" <ruckerm@.emaseniorcare.org> wrote in message
news:300301c51e8c$745b1c70$a601280a@.phx.gbl...
> Greetings..
> Please help prove I'm right about something. Text fields
> in SQL 2000 ~ do text and vchar fields truncate all
> whitespace, if the data does not fill up the declared
> field length?
> E.G.:
> vchar Fullname (255)
> Put some data into it:
> Dr. Jones Warnabe
> Will the field in memory be 255 characters or about 17
> characters?
Per BOL, for varchar [(n)], storage size is the actual length in bytes of
the data entered, not "n" bytes.
> Also ~ where would such information BE?
SQL Books Online > Index > varchar data type / text data type
> Thank you in advance!
>|||thank you! When in doubt... dig deeper in tfm.. ?
>--Original Message--
>
...