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 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 i achieve the functionality of External Procedure Transformation(Informatica) in SSIS

Hi,

I worked with informatica before and bit new to SSIS,

i have one simple question..

How can i achieve the same functionality as External Procedure Transformation (Informatica) in SSIS?

like..i want to use a DLL in my package which contains some set of business rules.

Please let me know.

Charles

Charles Luke wrote:

Hi,

I worked with informatica before and bit new to SSIS,

i have one simple question..

How can i achieve the same functionality as External Procedure Transformation (Informatica) in SSIS?

like..i want to use a DLL in my package which contains some set of business rules.

Please let me know.

Charles

Charles,

Its possible to build your own custom tasks and compoenents that implement whatever business rules you require. Go here in BOL for more info:

Integration Services Developer InfoCenter
(http://msdn2.microsoft.com/en-us/library/ms137709.aspx)

You may also wish to look at using the script component and script task. They also enable you to incorporate your own business rules however they are not as portable as custom tasks or custom components. But they ARE easier to build than custom tasks/components.

-Jamie

How can i achieve the functionality of External Procedure Transformation(Informatica) in SSI

Hi,

I worked with informatica before and bit new to SSIS,

i have one simple question..

How can i achieve the same functionality as External Procedure Transformation (Informatica) in SSIS?

like..i want to use a DLL in my package which contains some set of business rules.

Please let me know.

Charles

Charles Luke wrote:

Hi,

I worked with informatica before and bit new to SSIS,

i have one simple question..

How can i achieve the same functionality as External Procedure Transformation (Informatica) in SSIS?

like..i want to use a DLL in my package which contains some set of business rules.

Please let me know.

Charles

Charles,

Its possible to build your own custom tasks and compoenents that implement whatever business rules you require. Go here in BOL for more info:

Integration Services Developer InfoCenter
(http://msdn2.microsoft.com/en-us/library/ms137709.aspx)

You may also wish to look at using the script component and script task. They also enable you to incorporate your own business rules however they are not as portable as custom tasks or custom components. But they ARE easier to build than custom tasks/components.

-Jamie

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 assign value to cursor using sp_executesql procedure

What is error here when i declare cursor ?

declare curQueryVehicleHave cursor for
exec sp_executesql @.strQueryVehicleHave

@.strQueryVehicleHave this string contain a queryMaybe someone can offer a better alternative, but as far as I know you can't do it that way. You will need to place the results of the EXEC into a #temp table and then use the #temp table as the source of your cursor.

At the risk of looking like an idiot, this is my testing code:

DECLARE @.myQuery nvarchar(800)
SET @.myQuery = 'select div_code from division'
DECLARE @.div_code varchar(10)
CREATE Table #Temp (div_code varchar(10))
INSERT INTO #Temp (div_code) EXECUTE sp_executesql @.myQuery -- note that a table variable will not work here
DECLARE curQueryVehicleHave CURSOR FOR SELECT * FROM #TEMP
OPEN curQueryVehicleHave
FETCH NEXT FROM curQueryVehicleHave INTO @.div_code
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.div_code
FETCH NEXT FROM curQueryVehicleHave INTO @.div_code
END
CLOSE curQueryVehicleHave
DEALLOCATE curQueryVehicleHave
DROP TABLE #Temp

Terri|||I hate this about SQL Server. I'd like to see a syntax like

insert into TableName (Columns, ...)
select Columns, ...
from
exec StoredProc

Please SQL Server people?

How a storied procedure can read a table in MS Access?

Hi,

We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.

I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.

I've found info on how to add one record at a time but I need a more of a batch process approach.

Can you point me in the right direction?

Thanks,

Mary

You could use OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx) function for reading table in Ms Access:

Code Snippet

USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO

Also you could use OPENQUERY (http://msdn2.microsoft.com/en-us/library/ms188427.aspx) function for running queries at other databses, but you need to configure linked server first.|||

Thanks, I haven't gotten it quite working yet....took me a bit to turn on Ad Hoc Queries. I'm getting a could not be bound error on the table that is in SQL. If you have any ideas on how to fix that, sure would appreciate it.

-Mary

|||Never mind, I just forgot to alais the table...duh|||

It would be better to use a SQL Linked Server to the Excel file and use that instead of Access. What Access does behind the scenes in terms of query generation and calls to SQL Server should be outlawed.

Once the Excel sheet is linked, you can have a proc designed to do what you need with the data.

How a storied procedure can read a table in MS Access?

Hi,

We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.

I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.

I've found info on how to add one record at a time but I need a more of a batch process approach.

Can you point me in the right direction?

Thanks,

Mary

You could use OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx) function for reading table in Ms Access:

Code Snippet

USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO

Also you could use OPENQUERY (http://msdn2.microsoft.com/en-us/library/ms188427.aspx) function for running queries at other databses, but you need to configure linked server first.|||

Thanks, I haven't gotten it quite working yet....took me a bit to turn on Ad Hoc Queries. I'm getting a could not be bound error on the table that is in SQL. If you have any ideas on how to fix that, sure would appreciate it.

-Mary

|||Never mind, I just forgot to alais the table...duh|||

It would be better to use a SQL Linked Server to the Excel file and use that instead of Access. What Access does behind the scenes in terms of query generation and calls to SQL Server should be outlawed.

Once the Excel sheet is linked, you can have a proc designed to do what you need with the data.