Call ALTER LOGIN

I am trying to create a stored procedure to Call ALTER LOGIN based on the the username passed in. However, the Alter login statement chokes on any parameter. Is there a way I can alter sql logins from a web form ?

I try the following and it bombs

ALTER

LOGIN @.LoginNameWITH PASSWORD= @.Password

But this works

ALTER LOGIN 'TestUser' WITH PASSWORD = '123test'

I guess the alter login statement does not work with Parameters.

Any thoughts ?

EXEC('ALTER LOGIN ' + @.loginName + ' WITH PASSWORD= ' + @.Passrowd)

|||

I get an error when I set the password. Should I add double quotes to the password ?

|||

yes you are right. You need to escape the quotes..Sorry about that.

EXEC('ALTER LOGIN ' + @.loginName + ' WITH PASSWORD= ''' + @.Passrowd + '''')

|||Perfect Thanks!

Call AddRow() after all ProcessInputRow() are called in a Script Component

Hi Guys,

I am new to SSIS. Heree is the transformation I need to do. In database one, I have tables:

A

B

C

Where B is a detailed table of A, and C is a "derived" table of B. There is a one to one relationship between B and C, but there may be more than one record in B for each record in A.

In database two, we have the table structure:

AA

CC

And there is a one to one relationship between AA and CC. And I need to design a transformation to migrate data from database one to database two.

Table A(->AA) and B(->BB) will be easy, just one to one migration.

The mapping rule for table C(->CC) they decided was: I need to concate each record in B and C and for a record in CC. For example, suppose we have:

A1

B1(C1)

B2(C2)

A2

B3(C3)

B4(C4)

B5(C5)

Then we will have the following records in database two:

A_1

(B1+C1+B2+C2)

A2

(B3+C3 + B4+C4 + B5+C5)

I looked through all the stock data flow components, and it seems to me that none of them can perform this task, so I am thinking to design a Script transform component to do the task.

I have written the script:

in each ProcessInputRow() sub, I check record's foreign key to A, and if they are the same I concate the records, then I put them into a VB.NET collection. Once all the records are processed, in the PostExecute() function, I count the number of new rows, (in the above example 2), then I call AddRow to add the rows, by:

OutputBuffer.AddRow()

But this does not work, I got "Object reference not set to an instance of an object". It seems that in PostExecute, the OutputBuffer is not longer valid?

Help please! :)

Is there a better way of doing what I am trying to do?

Thanks!

Wenbiao

Wenbiao wrote:

But this does not work, I got "Object reference not set to an instance of an object". It seems that in PostExecute, the OutputBuffer is not longer valid?

That's right, the output buffer is no longer valid in PostExecute().

It IS available in CreateNewOutputRows() so you can create your output rows in there.

-Jamie

Call a Web Server or external program inside of report

How can I call a web service and/or an external program (not a
referenced assembly) from inside of Report Services 2005? Can I do
both? Thank you for your help.On Jan 31, 6:11 am, ieg...@.gmail.com wrote:
> How can I call a web service and/or an external program (not a
> referenced assembly) from inside of Report Services 2005? Can I do
> both? Thank you for your help.
I would advise building an assembly to call the web service/external
program and then referencing this assembly.

Call a WCF service from SQL CLR sproc

It is possible to call WCF from SQL CLR, can I add a web reference or use the proxy created with svcutil ?hi check out this link

http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6050.aspx

Call a WCF service from SQL CLR sproc

It is possible to call WCF from SQL CLR, can I add a web reference or use the proxy created with svcutil ?hi check out this link

http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6050.aspx

call a UDF from another server <> Authentication

Hello

I'm trying to call a UDF from another server with the following command:

select i fromopenquery([10.0.10.240],'[survey].[dbo].[ufnGetAxis_Ana] as i')

error:

[OLE/DB provider returned message: Invalid authorization specification]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Msg 7399, Level 16, State 1, Line 3

OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.

Can someone give me some advice on how to pass username and password to access an object on another server?

Many thanks!

hi, first thing is it the SQL Server or other one?

if it is SQL Server, then it quit easy, you need to create a Linked Server(Direct IP Address will not work) using

sp_addlinkedserver [ @.server= ] 'server' [ , [ @.srvproduct= ] 'product_name' ] [ , [ @.provider= ] 'provider_name' ] [ , [ @.datasrc= ] 'data_source' ] [ , [ @.location= ] 'location' ] [ , [ @.provstr= ] 'provider_string' ] [ , [ @.catalog= ] 'catalog' ] then use the openquery asOPENQUERY ( linked_server ,'query' )

linked_server

Is an identifier representing the name of the linked server.

'query'

Is the query string executed in the linked server. The maximum length of the string is 8 KB

now try

select i fromopenquery(<linked server name>,'[survey].[dbo].[ufnGetAxis_Ana] as i')

Regards,

Thanks.

Gurpreet S. Gill

|||

Many thanks Gurpreet

Are you sure about the syntax?
I get a syntax error when executing select i from openquery(myserver,'survey.dbo.ufnGetAxis_Ana(2,7) as i')

Many thanks!

Worf

|||

hi try this

select i FROMopenquery(ha9,'select pubs.dbo.myFunction() as i')

this is the command, where 'ha9' is linked server , at the remote end 'pubs' is the database name, 'dbo' is owner and 'myFunction' is the name of the function, in your case it should be

select i from openquery(myserver,'select survey.dbo.ufnGetAxis_Ana(2,7) as i')

NOTE: if your Remote Serever is SQL Server, then , name of the server is the Linked server name, also need to set the login & password.

Regards,

Thanks.

Gurpreet S. Gill

|||

It works Gurpreet!!

Many many thanks!!!

Worf

call a UDF from another server <> Authentication

Hello

I'm trying to call a UDF from another server with the following command:

select i from openquery([10.0.10.240], '[survey].[dbo].[ufnGetAxis_Ana] as i')

error:

[OLE/DB provider returned message: Invalid authorization specification]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Msg 7399, Level 16, State 1, Line 3

OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.

Can someone give me some advice on how to pass username and password to access an object on another server?

Many thanks!

hi, first thing is it the SQL Server or other one?

if it is SQL Server, then it quit easy, you need to create a Linked Server(Direct IP Address will not work) using

sp_addlinkedserver [ @.server= ] 'server' [ , [ @.srvproduct= ] 'product_name' ]

[ , [ @.provider= ] 'provider_name' ]

[ , [ @.datasrc= ] 'data_source' ]

[ , [ @.location= ] 'location' ]

[ , [ @.provstr= ] 'provider_string' ]

[ , [ @.catalog= ] 'catalog' ] then use the openquery asOPENQUERY ( linked_server ,'query' )

linked_server

Is an identifier representing the name of the linked server.

'query'

Is the query string executed in the linked server. The maximum length of the string is 8 KB

now try

select i from openquery(<linked server name>, '[survey].[dbo].[ufnGetAxis_Ana] as i')

Regards,

Thanks.

Gurpreet S. Gill

|||

Many thanks Gurpreet

Are you sure about the syntax?
I get a syntax error when executing select i from openquery(myserver,'survey.dbo.ufnGetAxis_Ana(2,7) as i')

Many thanks!

Worf

|||

hi try this

select i FROM openquery(ha9,'select pubs.dbo.myFunction() as i')

this is the command, where 'ha9' is linked server , at the remote end 'pubs' is the database name, 'dbo' is owner and 'myFunction' is the name of the function, in your case it should be

select i from openquery(myserver,'select survey.dbo.ufnGetAxis_Ana(2,7) as i')

NOTE: if your Remote Serever is SQL Server, then , name of the server is the Linked server name, also need to set the login & password.

Regards,

Thanks.

Gurpreet S. Gill

|||

It works Gurpreet!!

Many many thanks!!!

Worf

Call a storedproc in select from block

Hi everyone,

I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....

Select *

, (Exec MyStoredProc MyParam) as Field1

From Table1

It's complicated. You need to set up a (possible looped back) linked server and invoke it through the OPENROWSET() function. It is is explained here

http://www.sqlmag.com/Article/ArticleID/19842/sql_server_19842.html

You may need to enable the 'Ad Hoc Distributed Queries' sp_configure options in SQL 2005 (not sure ... I didn't try)

From a TSQL perspective, one of the main reasons we don't let you invoke procedures from inside queries is that we cannot determine the shape of the result set at the time we compile the query plan. Moreover, if the procedure has side effects, the semantics become unclear (e.g. resuls are different if you invoke once per row or once per query).

By using the "trick" above, the full distributed query machinery kicks into action which involves a lot of extra overhead not found in normal queries. Frankly, I'm not sure what the semantic is if you try something like

SELECT * FROM mytable JOIN OPENROWSET(..., 'EXEC myproc') ...

Perhaps you can try rewriting your code to use a table-valued function if it doesn't have side effects. This will give you better performance and predictable semantics.

Call a Stored Procedure within an SQL statement

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks

Quote:

Originally Posted by Shashi Sadasivan

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks

You can call a function from a SQL statement and not a STORED PROCEDURE.

Restrictions on calling a Function from a SQL statement:

1. Function should not have RETURN TYPE as BOOLEAN
2. Function should not perform any side effects. (ie No DML operations)|||

Quote:

Originally Posted by Shashi Sadasivan

Hi All,
I created a stored procedure which updates a specific row whose row ID is passed.
But what I want to do now, (which is a one time requirement) is to update all rows
is it possible to call this stored procedure within an sql statement and pass each row ID to it?

(would i have to go to cursor approach -- damn will have to learn that )
thanks

if it's a one time thing, can you just write a query in query analyzer to update the field you need? what field are you updating? what kind of update?

Call a Stored Procedure in another server

is it possible to call a stored proc if for example im in server1 then the proc i need is in server2... can i access that proc?

any help is much appreciated! tnx!As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR

Call a stored procedure from another stored procedure

How do I call a stored procedure from a stored procedure, returning a value?
With Getvalue being the name of the stored procedure,
I've tried:
Set @.value = (getvalue(p1, p2))
Set @.value = exec getvalue(p1, p2)
and various other syntax all keep getting "getvalue is not a function"
Thanks for your help."et" <eagletender2001@.yahoo.com> wrote in message
news:uf8qGGnpFHA.1444@.tk2msftngp13.phx.gbl...
> How do I call a stored procedure from a stored procedure, returning a
> value?
> With Getvalue being the name of the stored procedure,
> I've tried:
> Set @.value = (getvalue(p1, p2))
> Set @.value = exec getvalue(p1, p2)
> and various other syntax all keep getting "getvalue is not a function"
> Thanks for your help.
>
>
it's
Declare @.p1 Int
Declare @.p2 Int
Declare @.Value Int
exec @.value = getvalue @.p1, @.p2
Don't forget to correct the datatypes, but you should know that already.
Regards
Colin Dawson
www.cjdawson.com|||The problem is that you cannot make us your own syntax :)
declare @.p1 type,
@.p2 type,
@.value type
set @.p1 = 'value'
set @.p2 = 'value'
EXEC @.value = dbo.getvalue @.parmname1 = @.p1, @.parmname2 = @.p2
or
EXEC @.value = dbo.getvalue @.p1, @.p2
It is best to name the parameters to protect against change, but either way
works just fine.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"et" <eagletender2001@.yahoo.com> wrote in message
news:uf8qGGnpFHA.1444@.tk2msftngp13.phx.gbl...
> How do I call a stored procedure from a stored procedure, returning a
> value?
> With Getvalue being the name of the stored procedure,
> I've tried:
> Set @.value = (getvalue(p1, p2))
> Set @.value = exec getvalue(p1, p2)
> and various other syntax all keep getting "getvalue is not a function"
> Thanks for your help.
>
>|||You can only return an INT from a stored procedure:
DECLARE @.RC INT
EXEC @.RC = getvalue @.p1, @.p2
You can use an output parameter, however:
DECLARE @.out1 type
EXEC getvalue @.p1, @.p2, @.out1 OUTPUT
"et" <eagletender2001@.yahoo.com> wrote in message
news:uf8qGGnpFHA.1444@.tk2msftngp13.phx.gbl...
> How do I call a stored procedure from a stored procedure, returning a
value?
> With Getvalue being the name of the stored procedure,
> I've tried:
> Set @.value = (getvalue(p1, p2))
> Set @.value = exec getvalue(p1, p2)
> and various other syntax all keep getting "getvalue is not a function"
> Thanks for your help.
>
>|||Perfect! Thanks so much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23xoUFktpFHA.3244@.TK2MSFTNGP09.phx.gbl...
> You can only return an INT from a stored procedure:
> DECLARE @.RC INT
> EXEC @.RC = getvalue @.p1, @.p2
> You can use an output parameter, however:
> DECLARE @.out1 type
> EXEC getvalue @.p1, @.p2, @.out1 OUTPUT
>
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:uf8qGGnpFHA.1444@.tk2msftngp13.phx.gbl...
> value?
>|||Good guess :) I didn't even think about the int thing
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23xoUFktpFHA.3244@.TK2MSFTNGP09.phx.gbl...
> You can only return an INT from a stored procedure:
> DECLARE @.RC INT
> EXEC @.RC = getvalue @.p1, @.p2
> You can use an output parameter, however:
> DECLARE @.out1 type
> EXEC getvalue @.p1, @.p2, @.out1 OUTPUT
>
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:uf8qGGnpFHA.1444@.tk2msftngp13.phx.gbl...
> value?
>

Call a stored procedure from another SP?

Hello,

I'm trying to get a dynamic Web log report out of SQL Server 2000. I am sending all of my IIS activity to a SQL Server table called inetlog. I need to get a summary of downloads for particular files, and within a certain time period. So, I start by getting unique file names from the table by building a view:

CREATE VIEW UniqueFileNames
AS
SELECT TOP 100 PERCENT COUNT (t.target) AS NumDownloads,
t.target

FROM inetlog t

GROUP BY t.target
ORDER BY t.NumDownloads DESC

GO

Now that I've got this I can run my stored procedure which passes a number representing the days within which the downloads occurred:

CREATE PROCEDURE sp_DownloadsWithinNumDays

@.NumDays INT

AS

SELECT u.NumDownloads / 3 AS Downloads,
LEFT(u.Target,(LEN(u.Target)-4)) AS DocName,
UPPER(RIGHT((RTRIM(u.Target)),3)) AS DocType,
CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate

FROM UniqueFileNames u

INNER JOIN
inetlog i
ON u.target = i.target

WHERE DATEDIFF(DAY, i.logtime, GETDATE()) <= @.NumDays AND
(u.Target LIKE '%.pdf%' OR
u.Target LIKE '%.doc%' OR
u.Target LIKE '%.zip%')

GROUP BY
u.target,
u.NumDownloads

ORDER BY Downloads DESC

GO

This works just fine - I pipe my results to a Web page and I get a list of *.doc, *.pdf, and *.zip files downloaded within the past x days, including a count. The problem is that the count never changes. If I put 10 days in the SP, I get a list of files downloaded within the last 10 days, but the count shows ALL downloads of that file. If I put 5 days, I get the file downloads from the past 5 days, with the same counts (i.e., all downloads).

I know why this is happening: it's because the view that I join to is the count of all downloads, but what I can't figure out is how to dynamically generate this count so that the input parameter for the SP (num days) is used.

Sorry for the long post, but I'm getting myself confused, so I thought I'd be very explicit. Thanks for any help you can offer.

Dylan
dylan_thomas@.esri.comMy understanding:

You want the count of downloads in the last X days for each file downloaded in the last X days...

Forget the view and try this proc.... unless I missed something...

CREATE PROCEDURE sp_DownloadsWithinNumDays

@.NumDays INT

AS

DECLARE
@.var_FromDate as datetime

SET NOCOUNT ON

SET @.var_FromDate = DATEADD ( d , 0 - @.NumDays, GETDATE() )

SELECT COUNT(*) AS Downloads,
LEFT(i.Target,(LEN(i.Target)-4)) AS DocName,
UPPER(RIGHT((RTRIM(i.Target)),3)) AS DocType,
CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate

FROM inetlog i

WHERE i.logtime > @.var_FromDate AND
(i.Target LIKE '%.pdf%' OR
i.Target LIKE '%.doc%' OR
i.Target LIKE '%.zip%')

GROUP BY
i.target

ORDER BY 1 DESC

GO|||Andy - Fantastic! Runs faster, and gives me the result that I need. I'm very new to SQL Server (if you couldn't tell!), so it's good to see how you've used a variable in this procedure. Opens up a whole new world of possibilities for a SQL-based doc management system that I'm working on.

Thanks :-)|||CREATE PROCEDURE sp_DownloadsWithinNumDays
@.NumDays INT
AS
SELECT
u.NumDownloads / 3 AS Downloads
,LEFT(u.Target,(LEN(u.Target)-4)) AS DocName
,UPPER(RIGHT((RTRIM(u.Target)),3)) AS DocType
,CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate
FROM
(
SELECT Target,COUNT(Target) AS NumDownloads
FROM inetlog
WHERE DATEDIFF(DAY, logtime, GETDATE()) <= @.NumDays AND
(
Target LIKE '%.pdf%' OR
Target LIKE '%.doc%' OR
Target LIKE '%.zip%'
)
GROUP BY Target
) u
INNER JOIN inetlog i ON u.target = i.target
WHERE DATEDIFF(DAY, i.logtime, GETDATE()) <= @.NumDays AND
(
i.Target LIKE '%.pdf%' OR
i.Target LIKE '%.doc%' OR
i.Target LIKE '%.zip%'
)
ORDER BY Downloads DESC, Target ASC|||No worries!

Call a stored procedure from an SSAS action

Hello, I'm just wondering if anyone has every created an action that called a stored procedure. I've been looking into Proprietary actions of statement actions but I cannot find any documentation of examples. Is this possible? One option was to create a dummy ssrs report and then call some custom .Net code. But I want to avoid using a technology just as a work around. Any ideas?

thanks.

To call sproc from action is not a problem - the question is - what do you want to return from this sproc ? If you return a string to be used in URL action - then it will be trivial, just use sproc in the MDX expression. If you want to return rowset, it is a little bit more work, but should be possible too.|||

Actually, I don't need it to return anything. This is SQL Server sproc that I will pass a few parameters and it will execute an SSIS package. The only thing that I have read to accomplish this, is to create a small .net web app that will call this sproc, that way I can simply use a url reporting action and pass the parameters to the web app.

I am wondering if it is possible to call the SQL server sproc directly from a reporting action?

|||OK - I thought you were talking about SSAS stored procedure. There is no direct way to call SQL Server sproc, so either you do it through web app and implement an URL action, or you code SSAS stored procedure which calls SQL Server stored procedure.|||

Hi,

I decided to create an SSAS stored procedure that would call a SQL server stored procedure. I made a call to the sp in a URL action like this:

"Http://localhost/ReportServer/Pages/ReportViewer.aspx?/SampleReport/Report&Param1=" & AssemblyName!StoreProc()

The report would simply say the the procedure has been executed successfully.

This works great but the only problem is that the stored procedure fires as soon as you right click the cell. Is there a setting or something that I need to set so that the stored procedure only fires when the user clicks on the action?

Thanks in advance for your help.

|||

Hi,

Hi after some further reading I figure out what was going on. When right clicking on the cell the server would resolve all actions and return the resulting string to the client, therefore the result was that the ssas stored procedure was getting executed before the action was returned to the client. Solution: I changed the action type to "statement" and simply used a "Call StoredProcedure()" statement in the action expression.

Call a Stored Procedure from a SELECT Statement

Is there a way to call a stored procedure within a SELECT statement?
Example;
----
SELECT FirstName,
LastName,
(EXEC UniqueID_KEYGEN @.keyval output) AS UniqueID
INTO #tNewEmployee
FROM EmployeeTable
----
SELECT *
FROM #tNewEmployee
The return from the temp table would have a unique ID ready to insert into another table.
Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.
Thanks for your help in advance.

No you can not. Usually this type of thing is done by using user defined functions. So, ask your DBA to convert into a function, so you can execute it from your stored procedure|||

That is a real pisser ;P, why would he do that?
grrrrrrrrrrr

call a stored procedure for data push

Is it possible to call a stored procedure on the remote sql server to do a push (insert the data onto the main sql server)?

Yes, you can use SubmitSQL with any valid SQL server commands, that do not return rows.

call a stored procedure

How do you call a stored procedure using the "+" sign in the parameter:
exec test_sproc 'this is a ' + 'test'
Do I have to use a variable, like this?
declare @.string varchar
set @.string = 'this is a ' + 'test'
exec test_sproc @.string> How do you call a stored procedure using the "+" sign in the parameter:
> exec test_sproc 'this is a ' + 'test'
>
> Do I have to use a variable, like this?
Yes.
http://www.aspfaq.com/
(Reverse address to reply.)|||Why not ,
exec test_sproc 'this is a test' instead of
exec test_sproc 'this is a ' + 'test'. I am just curious.
"Aaron [SQL Server MVP]" wrote:

> Yes.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||> exec test_sproc 'this is a test' instead of
> exec test_sproc 'this is a ' + 'test'. I am just curious.
The OP probably simplified. Much more likely to be something like:
declare @.test varchar(32)
set @.test = 'holdup'
exec test_proc 'this is a '+@.test

call a stored proc from a stored proc

I need to call a stored select proc frmo an update stored proc. How do I do
it.
here is the proc that does the call
INSERT INTO tblFileRecords_Stage2 ( FirstName, LastName......)
SELECT CALLEDStoreproc.FirstName ,
CALLEDStoreproc.s.LastName, ....
FROM CALLEDStoreproc
The proc CALLEDStoreproc looks somehing like this
SELECT Min(SID, FirstName, LastName....
FROM tblFileRecords_tmp......The construct you'd use here would be INSERT ... EXEC() like:
INSERT tblFileRecords_Stage2 ( FirstName, LastName...... )
EXEC CALLEDStoreproc ;
Anith|||"Rick Morayniss" <RickMorayniss@.discussions.microsoft.com> wrote in message
news:B74B0E28-5C9E-4E0C-ADAD-AC228BF17BDE@.microsoft.com...
>I need to call a stored select proc frmo an update stored proc. How do I do
>it.
> here is the proc that does the call
> INSERT INTO tblFileRecords_Stage2 ( FirstName, LastName......)
> SELECT CALLEDStoreproc.FirstName ,
> CALLEDStoreproc.s.LastName, ....
> FROM CALLEDStoreproc
> The proc CALLEDStoreproc looks somehing like this
> SELECT Min(SID, FirstName, LastName....
> FROM tblFileRecords_tmp......
>
INSERT INTO tblFileRecords_Stage2 (sid, FirstName, LastName)
EXEC dbo.usp_procedure ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Call a store procedure from inside a function

Hello,
I am having a problem where I'm trying to call a store procedure from a
function, is it possible ?
JHello,
Sorry no can do, you can't call a store procedure from a function, sorry.
Peter
"Although prepared for martyrdom, I preferred that it be postponed."
Winston Churchill
"Julie" wrote:

> Hello,
> I am having a problem where I'm trying to call a store procedure from a
> function, is it possible ?
> J|||Dang, ok thanks Peter
J
"Peter 'Not Peter The Spate' Nolan" wrote:
> Hello,
> Sorry no can do, you can't call a store procedure from a function, sorry.
> Peter
> "Although prepared for martyrdom, I preferred that it be postponed."
> Winston Churchill
>
> "Julie" wrote:
>|||No, this is documented in Books Online. There are a number of things that yo
u aren't allowed in a
UDF. Calling regular stored procedures is one of them (xp_'s are OK, though.
.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Julie" <Julie@.discussions.microsoft.com> wrote in message
news:8D15C2A0-4A65-47ED-BAA1-6F8814C13DFC@.microsoft.com...
> Hello,
> I am having a problem where I'm trying to call a store procedure from a
> function, is it possible ?
> J|||No, it isn't possible. How about moving the logic out of your proc into
the function itself.
David Portas
SQL Server MVP
--|||Hi,
I think you can if you call the proc from within an openquery. But thats not
really in the spirit of what funstions are supposed to be for. Still, if its
really really necessary...
N
"David Portas" wrote:

> No, it isn't possible. How about moving the logic out of your proc into
> the function itself.
> --
> David Portas
> SQL Server MVP
> --
>

Call a SP many times in parallel

Hi,

Is there a way to call a SP several times in parallel from another SP?

I know I can create a DTS with several Tasks that work parallel. The issue is that I don't know how many parallel execution I need.

Thanks,

Assaf

No. Execution of the SP is always sequential.

call a sap remote-function-module from t-sql Stored-Proedure

Hello,

is it possible to call an sap remote-function-module directly from a stored-procedure in SQL-Server-2005?

If yes, where can I find an example?

Thanks

Gerd

Hi,

Never touched SAP, but can you run the remote function module from a DOS prompt? if so, could you use xp_cmdshell from the extended stored procedures?

Just an idea :)

Cheers,
Menthos

call a remote procedure

how can i call a remote procedure

with out using linked server

can i use open rowset or something

I need to return a temp table

have you tried creating a connection string to this server?

If not then have a look at the following thread, it has your scenario http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=974075&SiteID=1|||

hi barry,

I have an SP and i need this SP to call another SP

in one of my remote Sql server. I'm caaling the remote Sp

with my local Sp

thanks,

joey

|||Have you tried using a Full Qualified Name?

[servername\instancename].database.owner.object|||

those are used in conjuction of a linked server

i'm thinking of opendatasource or openrowset

|||

Joey:

Would it be acceptable to implment this with a "linked server" but without full linked server capability -- similar to the "remote server" of old SQL 7? You could do this by only allowing the "RPC" feature and NOT allowing the "Data" feature available from "linked server". I realize this is a compromise to your request but this might be a good option in this case. Then you could execute the procedures as described by Barry.

Dave

call a program (activex exe, dll or something) from a script

Is it possible to do this? I am evaluating a bunch of possible solutions to
a problem. Someone came up with this idea, but could not remember for
certain if it possible.
Thanks.1. Lookup the sp_OAxxxxxx routines in SQL Online Books and it will show you
how to create COM objects and call method and set properties
2. Lookup xp_cmdshell on how to launch processes
3. Write an extended stored proc to have tighter control over what is
happening (inside your proc you can call CreateProcess() API and do many
other things)
All three of these options have security/stability implications. Let me
know if you need more details on a specific option.
Mike
"Stephanie" <IwishICould@.NoWay.com> wrote in message
news:O8qomUBTFHA.2916@.TK2MSFTNGP15.phx.gbl...
> Is it possible to do this? I am evaluating a bunch of possible solutions
to
> a problem. Someone came up with this idea, but could not remember for
> certain if it possible.
> Thanks.
>

call a Oracle stored proc in SQL SERVER

We have set up Oracle database as a linked server in SQL Server.
We are able to access Oracle tables fine.

I am trying to call a Oracle stored procedure in SQL Server as follows:
declare @.p1 varchar(1000)
set @.p1 = 'HHH'
exec GENRET..OPS$GENRET.BOB_TEST_PROC @.p1

This is the message:
Server 'GENRET' is not configured for RPC.

Please help.
Thanks in advance
vv (vanishri16@.yahoo.com) writes:
> We have set up Oracle database as a linked server in SQL Server.
> We are able to access Oracle tables fine.
> I am trying to call a Oracle stored procedure in SQL Server as follows:
> declare @.p1 varchar(1000)
> set @.p1 = 'HHH'
> exec GENRET..OPS$GENRET.BOB_TEST_PROC @.p1
> This is the message:
> Server 'GENRET' is not configured for RPC.

exec sp_serveroption GENRET, 'rpc', true
exec sp_serveroption GENRET, 'rpc out', true

may be a start.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Call a function without used dbo prefix

Hi all,
do you know if is it possible to call a function without the dbo prefix :
select dbo.myfunction : works fine
select myfunction : return an error message 'myfunction is not a recognize
function'
I've searched on permission right but unsuccessffuly ...
Thank's for your help.
Nicolas.On Wed, 15 Dec 2004 07:59:08 -0800, Nicolas Fortier <Nicolas
Fortier@.discussions.microsoft.com> wrote:
>Hi all,
>do you know if is it possible to call a function without the dbo prefix :
>select dbo.myfunction : works fine
>select myfunction : return an error message 'myfunction is not a recognize
>function'
>I've searched on permission right but unsuccessffuly ...
>Thank's for your help.
>Nicolas.
>
Hi Nicolas,
This is not possible. You must always include the owner of a userdefined
function.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote:
> On Wed, 15 Dec 2004 07:59:08 -0800, Nicolas Fortier <Nicolas
> Fortier@.discussions.microsoft.com> wrote:
> >Hi all,
> >
> >do you know if is it possible to call a function without the dbo prefix :
> >
> >select dbo.myfunction : works fine
> >select myfunction : return an error message 'myfunction is not a recognize
> >function'
> >
> >I've searched on permission right but unsuccessffuly ...
> >
> >Thank's for your help.
> >
> >Nicolas.
> >
> Hi Nicolas,
> This is not possible. You must always include the owner of a userdefined
> function.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
Hugo, thank's for your answer.
BR
Nicolas.|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> You must always include the owner of a userdefined function.
Just out of curiosity, have you ever heard of a rationale for this? Seems
weird you don't have to include the owner of a stored procedure, but you do
have to include it for a UDF.|||On Wed, 15 Dec 2004 10:53:13 -0800, Mark Wilden wrote:
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
>> You must always include the owner of a userdefined function.
>Just out of curiosity, have you ever heard of a rationale for this? Seems
>weird you don't have to include the owner of a stored procedure, but you do
>have to include it for a UDF.
>
Hi Mark,
No, never. If I have to take a guess, I'd say it was to limit the
complexity of parsing the SQL.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Mark Wilden" <mark@.mwilden.com> wrote in message
news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> > You must always include the owner of a userdefined function.
> Just out of curiosity, have you ever heard of a rationale for this? Seems
> weird you don't have to include the owner of a stored procedure, but you
do
> have to include it for a UDF.
>
Probably performance.
While you don't need to do it for stored procs, it's generally a good idea.
>|||To distinguish between user-defined functions and system functions.
e.g. imagine if you have a function called error_message(), then you upgrade
your database to Yukon and your application is getting invalid values over
the place.
If you have to use a prefix, there's no chance of the engine trying to tell
the difference...
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> > You must always include the owner of a userdefined function.
> Just out of curiosity, have you ever heard of a rationale for this? Seems
> weird you don't have to include the owner of a stored procedure, but you
do
> have to include it for a UDF.
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uD%23oq6x4EHA.1596@.tk2msftngp13.phx.gbl...
> To distinguish between user-defined functions and system functions.
>
This doesn't really answer the question though.
What if you had a stored proc called error_message and then upgrade the
database to Yukon and your application is now calling the wrong stored proc
all over the place. :-)
(I think you're probably right, this has something to do with it, but why
wasn't this done for stored procs?)
> e.g. imagine if you have a function called error_message(), then you
upgrade
> your database to Yukon and your application is getting invalid values over
> the place.
> If you have to use a prefix, there's no chance of the engine trying to
tell
> the difference...
>
> "Mark Wilden" <mark@.mwilden.com> wrote in message
> news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> > "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> > news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> >
> > > You must always include the owner of a userdefined function.
> >
> > Just out of curiosity, have you ever heard of a rationale for this?
Seems
> > weird you don't have to include the owner of a stored procedure, but you
> do
> > have to include it for a UDF.
> >
> >
>|||Hi All,
to solve my initial problem, is it possible to create a synonym (like with
oracle) to doesn't use the owner in a call fucntion ?
"Greg D. Moore (Strider)" wrote:
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uD%23oq6x4EHA.1596@.tk2msftngp13.phx.gbl...
> > To distinguish between user-defined functions and system functions.
> >
> This doesn't really answer the question though.
> What if you had a stored proc called error_message and then upgrade the
> database to Yukon and your application is now calling the wrong stored proc
> all over the place. :-)
> (I think you're probably right, this has something to do with it, but why
> wasn't this done for stored procs?)
>
> > e.g. imagine if you have a function called error_message(), then you
> upgrade
> > your database to Yukon and your application is getting invalid values over
> > the place.
> >
> > If you have to use a prefix, there's no chance of the engine trying to
> tell
> > the difference...
> >
> >
> >
> > "Mark Wilden" <mark@.mwilden.com> wrote in message
> > news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> > > "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> > > news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> > >
> > > > You must always include the owner of a userdefined function.
> > >
> > > Just out of curiosity, have you ever heard of a rationale for this?
> Seems
> > > weird you don't have to include the owner of a stored procedure, but you
> > do
> > > have to include it for a UDF.
> > >
> > >
> >
> >
>
>|||> What if you had a stored proc called error_message and then upgrade the
> database to Yukon and your application is now calling the wrong stored
proc
> all over the place. :-)
Well, system stored procedures are prefixed with sp_, and the documentation
clearly advises against using the same naming scheme for user-defined
procedures. So I think their bases are covered there.
It probably would have been better if a similar tack was taken with
functions, e.g. fn_ for system functions, and find your own naming scheme
for your own. (Though many of the built-in functions were created without
any specific naming scheme, long before UDFs were introduced. And there
would have been a lot of backlash if people had to go back and change
DB_NAME() to fn_DBNAME(), for example.)
If there are other reasons than those already brought up in this thread, I'm
not aware of them. I could make stuff up, but in either case, it's not
going to do you any good because the implementation is not going to
change...
A|||No synonyms in SQL Server 2000.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Nicolas Fortier" <NicolasFortier@.discussions.microsoft.com> wrote in
message news:EA01FC2D-AACF-44F2-B7D6-F2929F5D2678@.microsoft.com...
> Hi All,
> to solve my initial problem, is it possible to create a synonym (like
with
> oracle) to doesn't use the owner in a call fucntion ?
> "Greg D. Moore (Strider)" wrote:
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:uD%23oq6x4EHA.1596@.tk2msftngp13.phx.gbl...
> > > To distinguish between user-defined functions and system functions.
> > >
> >
> > This doesn't really answer the question though.
> >
> > What if you had a stored proc called error_message and then upgrade the
> > database to Yukon and your application is now calling the wrong stored
proc
> > all over the place. :-)
> >
> > (I think you're probably right, this has something to do with it, but
why
> > wasn't this done for stored procs?)
> >
> >
> > > e.g. imagine if you have a function called error_message(), then you
> > upgrade
> > > your database to Yukon and your application is getting invalid values
over
> > > the place.
> > >
> > > If you have to use a prefix, there's no chance of the engine trying to
> > tell
> > > the difference...
> > >
> > >
> > >
> > > "Mark Wilden" <mark@.mwilden.com> wrote in message
> > > news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> > > > "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> > > > news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> > > >
> > > > > You must always include the owner of a userdefined function.
> > > >
> > > > Just out of curiosity, have you ever heard of a rationale for this?
> > Seems
> > > > weird you don't have to include the owner of a stored procedure, but
you
> > > do
> > > > have to include it for a UDF.
> > > >
> > > >
> > >
> > >
> >
> >
> >|||Thank's for your answer.
"Aaron [SQL Server MVP]" wrote:
> No synonyms in SQL Server 2000.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Nicolas Fortier" <NicolasFortier@.discussions.microsoft.com> wrote in
> message news:EA01FC2D-AACF-44F2-B7D6-F2929F5D2678@.microsoft.com...
> > Hi All,
> >
> > to solve my initial problem, is it possible to create a synonym (like
> with
> > oracle) to doesn't use the owner in a call fucntion ?
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:uD%23oq6x4EHA.1596@.tk2msftngp13.phx.gbl...
> > > > To distinguish between user-defined functions and system functions.
> > > >
> > >
> > > This doesn't really answer the question though.
> > >
> > > What if you had a stored proc called error_message and then upgrade the
> > > database to Yukon and your application is now calling the wrong stored
> proc
> > > all over the place. :-)
> > >
> > > (I think you're probably right, this has something to do with it, but
> why
> > > wasn't this done for stored procs?)
> > >
> > >
> > > > e.g. imagine if you have a function called error_message(), then you
> > > upgrade
> > > > your database to Yukon and your application is getting invalid values
> over
> > > > the place.
> > > >
> > > > If you have to use a prefix, there's no chance of the engine trying to
> > > tell
> > > > the difference...
> > > >
> > > >
> > > >
> > > > "Mark Wilden" <mark@.mwilden.com> wrote in message
> > > > news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> > > > > "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> > > > > news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
> > > > >
> > > > > > You must always include the owner of a userdefined function.
> > > > >
> > > > > Just out of curiosity, have you ever heard of a rationale for this?
> > > Seems
> > > > > weird you don't have to include the owner of a stored procedure, but
> you
> > > > do
> > > > > have to include it for a UDF.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

Call a function without used dbo prefix

Hi all,
do you know if is it possible to call a function without the dbo prefix :
select dbo.myfunction : works fine
select myfunction : return an error message 'myfunction is not a recognize
function'
I've searched on permission right but unsuccessffuly ...
Thank's for your help.
Nicolas.
On Wed, 15 Dec 2004 07:59:08 -0800, Nicolas Fortier <Nicolas
Fortier@.discussions.microsoft.com> wrote:

>Hi all,
>do you know if is it possible to call a function without the dbo prefix :
>select dbo.myfunction : works fine
>select myfunction : return an error message 'myfunction is not a recognize
>function'
>I've searched on permission right but unsuccessffuly ...
>Thank's for your help.
>Nicolas.
>
Hi Nicolas,
This is not possible. You must always include the owner of a userdefined
function.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Hugo Kornelis" wrote:

> On Wed, 15 Dec 2004 07:59:08 -0800, Nicolas Fortier <Nicolas
> Fortier@.discussions.microsoft.com> wrote:
>
> Hi Nicolas,
> This is not possible. You must always include the owner of a userdefined
> function.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
Hugo, thank's for your answer.
BR
Nicolas.
|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...

> You must always include the owner of a userdefined function.
Just out of curiosity, have you ever heard of a rationale for this? Seems
weird you don't have to include the owner of a stored procedure, but you do
have to include it for a UDF.
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:373253
On Wed, 15 Dec 2004 10:53:13 -0800, Mark Wilden wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com.. .
>
>Just out of curiosity, have you ever heard of a rationale for this? Seems
>weird you don't have to include the owner of a stored procedure, but you do
>have to include it for a UDF.
>
Hi Mark,
No, never. If I have to take a guess, I'd say it was to limit the
complexity of parsing the SQL.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Mark Wilden" <mark@.mwilden.com> wrote in message
news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
>
> Just out of curiosity, have you ever heard of a rationale for this? Seems
> weird you don't have to include the owner of a stored procedure, but you
do
> have to include it for a UDF.
>
Probably performance.
While you don't need to do it for stored procs, it's generally a good idea.

>
|||To distinguish between user-defined functions and system functions.
e.g. imagine if you have a function called error_message(), then you upgrade
your database to Yukon and your application is getting invalid values over
the place.
If you have to use a prefix, there's no chance of the engine trying to tell
the difference...
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:udo0s09hg55fna924ja8pavss40nqkekj1@.4ax.com...
>
> Just out of curiosity, have you ever heard of a rationale for this? Seems
> weird you don't have to include the owner of a stored procedure, but you
do
> have to include it for a UDF.
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uD%23oq6x4EHA.1596@.tk2msftngp13.phx.gbl...
> To distinguish between user-defined functions and system functions.
>
This doesn't really answer the question though.
What if you had a stored proc called error_message and then upgrade the
database to Yukon and your application is now calling the wrong stored proc
all over the place. :-)
(I think you're probably right, this has something to do with it, but why
wasn't this done for stored procs?)

> e.g. imagine if you have a function called error_message(), then you
upgrade
> your database to Yukon and your application is getting invalid values over
> the place.
> If you have to use a prefix, there's no chance of the engine trying to
tell[vbcol=seagreen]
> the difference...
>
> "Mark Wilden" <mark@.mwilden.com> wrote in message
> news:iOydnelPJfeqFV3cRVn-ow@.sti.net...
Seems
> do
>
|||Hi All,
to solve my initial problem, is it possible to create a synonym (like with
oracle) to doesn't use the owner in a call fucntion ?
"Greg D. Moore (Strider)" wrote:

> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uD%23oq6x4EHA.1596@.tk2msftngp13.phx.gbl...
> This doesn't really answer the question though.
> What if you had a stored proc called error_message and then upgrade the
> database to Yukon and your application is now calling the wrong stored proc
> all over the place. :-)
> (I think you're probably right, this has something to do with it, but why
> wasn't this done for stored procs?)
>
> upgrade
> tell
> Seems
>
>
|||> What if you had a stored proc called error_message and then upgrade the
> database to Yukon and your application is now calling the wrong stored
proc
> all over the place. :-)
Well, system stored procedures are prefixed with sp_, and the documentation
clearly advises against using the same naming scheme for user-defined
procedures. So I think their bases are covered there.
It probably would have been better if a similar tack was taken with
functions, e.g. fn_ for system functions, and find your own naming scheme
for your own. (Though many of the built-in functions were created without
any specific naming scheme, long before UDFs were introduced. And there
would have been a lot of backlash if people had to go back and change
DB_NAME() to fn_DBNAME(), for example.)
If there are other reasons than those already brought up in this thread, I'm
not aware of them. I could make stuff up, but in either case, it's not
going to do you any good because the implementation is not going to
change...
A

Call a DLL or EXE file from SQL Trigger

I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:

---

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

CREATE TRIGGER CallProgIfParentID

ON Toc

FOR INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

-- I want to be able to programmatically change '57660' from an ASPX page

EXEC

-- This is where the call function goes

GO

---

Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?

Thank you in advance

I have to warn you first: although calling external exes is possible, it is not recommended as there are at least a couple obvious drawbacks:

1. You would possibly lose data integrity because those external processes are NOT bound to the SQL transaction that triggers run in. For example, if the INSERT statement that fires the insert trigger is rolled back, regular trigger actions would be rolled back too, but those external processes would not. Similarly, triggers might not be able to detect errors that happen to those external processes and it ends up with the external exe failed but the trigger (as well as the firing statement) succeeded.
2. These processes will run outside of SQL Server, so you would lose total control of them. E.g. they might come back and compete with SQL Server for resources like CPU and memory.

I am wondering what kind of scenarios you have, but there got to be a better way to do it :)

Anyway, if you still decide to go with this route, you could call external exes from a trigger by:

- xp_cmdshell '<some exe>'
- For a *unsafe* CLR trigger, you could practically do anything, including calling external processes (e.g. Process class)

For you other question, yes, you can create triggers programmatically from wherever you can connect to the server.

|||

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57660')

EXEC master ..xp_cmdshell 'C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe 57660'

---

Thats what I have for my trigger now (The 57660 is a test ID number and isn't of much relavence), either the trigger isnt being called or the EXE isn't being called. I can't figure out why it's not working.

|||

I figured out what wasn't working and fixed it. Now I can't get a variable to append to the end of the command line. It keeps telling me the + isn't valid. Does anyone have any ideas?

---

ALTER TRIGGER SendMailIfParentID

ON Toc

AFTER INSERT

AS

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @.MyTocId varchar(12)

SELECT @.MyTocId = (SELECT TocId FROM inserted)

EXEC master ..xp_cmdshell '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + @.MyTocId

GO

|||Hi,

for debugging and better handling purposes, I would suggest first putting evverything in a varaible and executing this afterwards:

IF ((select ins.[ParentID] FROM inserted ins) = '57750')

DECLARE @.MyTocId varchar(12)

SELECT @.MyTocId = '"C:\Documents and Settings\michael\My Documents\Visual Studio Projects\Test\bin\Test.exe" ' + TocId FROM inserted

EXEC master ..xp_cmdshell @.command = @.MyTocId

Warning: Triggers are fired per statement not per row, you will in addition make sure that your trigger is able to handle multiple rows affected in a trigger. In further addition you will have to make sure that the command is not executed as no row is affected as the trigger is fired (as already said) on a statement basis (even if the affected rowcount is 0).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Call a Dll from Stored Procedure

Can you call a .dll from a stored procedure?
I am looking to use this as a trigger to run some other software.
I have a piece of software that I was going to run as a service when a
record is updated. But I have no way to trigger the service that something
has happened.
For example:
If a record is updated, I want a program to run that will run a web service.
If a stored procedure can call a .dll, that method could call the web
service.
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OtpHii7RGHA.4264@.TK2MSFTNGP11.phx.gbl...
> Can you call a .dll from a stored procedure?
> I am looking to use this as a trigger to run some other software.
> I have a piece of software that I was going to run as a service when a
> record is updated. But I have no way to trigger the service that
> something has happened.
> For example:
> If a record is updated, I want a program to run that will run a web
> service. If a stored procedure can call a .dll, that method could call the
> web service.
Look into extended stored procs. If you run C++ and select new project one
of the project types is extended store proc.
Michael|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OtpHii7RGHA.4264@.TK2MSFTNGP11.phx.gbl...
> Can you call a .dll from a stored procedure?
> I am looking to use this as a trigger to run some other software.
> I have a piece of software that I was going to run as a service when a
> record is updated. But I have no way to trigger the service that
> something has happened.
> For example:
> If a record is updated, I want a program to run that will run a web
> service. If a stored procedure can call a .dll, that method could call the
> web service.
> Thanks,
> Tom
>
In SQL 2005 you can put .NET code directly in a proc.
In SQL 2000 you can create your own extended proc or use the sp_OA
automation extended procs. Neither option is great and extended procs are
now deprecated. Use 2005 if you can.
Alternativey, you could just have your external code poll the table
regularly and action any changes based on a date-timestamp of new rows.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On second thoughts and in light of Michael's response, my reply deserves a
bit of clarification. When I say "not great" what I mean is that in my
experience the COM interface frequently leaks memory. That's based on
several brushes with the sp_OA procs in 2000. I don't have personal
experience of developing extended procs in C++ so I shouldn't comment on
those beyond what Books Online says about them (below). I have used some
third party XP (xp_smtp_sendmail) and not noticed any particular problem in
that case.
<quote>
Extended Stored Procedures
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use CLR Integration instead.
[...]
Extended stored procedures may produce memory leaks or other problems that
reduce the performance and reliability of the server. You should consider
storing extended stored procedures in an instance of SQL Server that is
separate from the instance that contains the referenced data. You should
also consider using distributed queries to access the database. For more
information, see Distributed Queries.
</quote>
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> On second thoughts and in light of Michael's response, my reply deserves a
> bit of clarification. When I say "not great" what I mean is that in my
> experience the COM interface frequently leaks memory. That's based on
> several brushes with the sp_OA procs in 2000.
It might be lousy implemenations of the particular COM methods you
have used.
But nevertheless, you were right on target. Writing extended stored
procedures, or calling your own COM methods through sp_OAxxx comes
with big warning signs. Performance is not fantastic, as there is some
context switching. But what is really ugly is that since the DLLs
are in-process, an execution error like an access violation will
crash the entire SQL Server.
On SQL 2005 writing a trigger in a CLR language might be the best choice.
But that depends on how long that web service takes. Triggers should
be swift and quick, since you are in a transaction. Triggers that runs
for several seconds in a busy system is a recipe for diasster.
On SQL 2000 the best is have the DLL to poll, or possibly start a job
with sp_start_job.
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|||My problem is we are not using Sql 2005.
I need to get something to work now. Is the Extended Stored Procedures the
same as Com Interface?
Also, the quote mentions CLR Integration. Is that the same thing as your
"NET code directly in a proc"?
Thanks,
Tom
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ONO%23At7RGHA.2176@.TK2MSFTNGP10.phx.gbl...
> On second thoughts and in light of Michael's response, my reply deserves a
> bit of clarification. When I say "not great" what I mean is that in my
> experience the COM interface frequently leaks memory. That's based on
> several brushes with the sp_OA procs in 2000. I don't have personal
> experience of developing extended procs in C++ so I shouldn't comment on
> those beyond what Books Online says about them (below). I have used some
> third party XP (xp_smtp_sendmail) and not noticed any particular problem
> in that case.
> <quote>
> Extended Stored Procedures
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use CLR Integration instead.
> [...]
> Extended stored procedures may produce memory leaks or other problems that
> reduce the performance and reliability of the server. You should consider
> storing extended stored procedures in an instance of SQL Server that is
> separate from the instance that contains the referenced data. You should
> also consider using distributed queries to access the database. For more
> information, see Distributed Queries.
>
> </quote>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9787723711E9Yazorman@.127.0.0.1...
> But nevertheless, you were right on target. Writing extended stored
> procedures, or calling your own COM methods through sp_OAxxx comes
> with big warning signs. Performance is not fantastic, as there is some
> context switching. But what is really ugly is that since the DLLs
> are in-process, an execution error like an access violation will
> crash the entire SQL Server.
What this is saying (and the MSDN article quoted by David) is that you might
have a bug in one particular technique therefore don't use that technique.
To me this just means greater caution should be used when using said
technique.
Michael|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:upCIsG8RGHA.4456@.TK2MSFTNGP14.phx.gbl...
> My problem is we are not using Sql 2005.
> I need to get something to work now. Is the Extended Stored Procedures
> the same as Com Interface?
No, they are 2 different techniques. Extended stored proc is where you write
code, usually in C++, to create a dll. You can add functions from the dll
similar to other stored procs. You could use this as a wrapper for your
existing dll. Have a look at, for eg, the xp_subdirs extended stored proc in
the master database, xp_subdirs is a function in xpstar.dll (you can see
this by double clicking the ex stored proc).
Com interface is something different. You can get sql2k to call functions on
an existing com interface. If your dll isn't com then this isn't a lot of
use to you unless you write a com wrapper.

> Also, the quote mentions CLR Integration. Is that the same thing as your
> "NET code directly in a proc"?
This method is not available to you in sql2k
Michael|||How time sensitive are your needs? As erland mentioned earlier,
triggers aren't really designed to be event-handlers; they should be
used for quick responses to data changes (such as low-level
validation). If you change several 1000 rows of data in a second, your
trigger needs to be able to fire that many times.
What is that you're trying to do? It sounds like some form of
event-handling, which should be done at a higher level than your data
source. If your needs are not time sensitive, than you may consider
polling your database periodically to see if your criteria is met, or
you might consider having your application call the web service when it
changes the database.
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1142384900.721926.212080@.p10g2000cwp.googlegroups.com...
> How time sensitive are your needs? As erland mentioned earlier,
> triggers aren't really designed to be event-handlers; they should be
> used for quick responses to data changes (such as low-level
> validation). If you change several 1000 rows of data in a second, your
> trigger needs to be able to fire that many times.
> What is that you're trying to do? It sounds like some form of
> event-handling, which should be done at a higher level than your data
> source. If your needs are not time sensitive, than you may consider
> polling your database periodically to see if your criteria is met, or
> you might consider having your application call the web service when it
> changes the database.
>
That may be the best bet.
I still need to look into the requirements as we are just starting to design
it. But as you say, having the App Call a Web Service might be the best
way.
Thanks,
Tom

> Stu
>

Call .NET web service from SQL Server

Hi,
I have a situation where I need to call a web service from the database (by an insert trigger on a table). Please let me know how I could do this.
Thanks in advance
RajIf you are in a low security installation, you could write a simple command line utility that will do what you need, and call it on your server user xp_cmdshell.

-PatP

Caliing a webservice from a trigger

Please,
is there a way i can execute a webservice from within a SQL Server 2k
Trigger? Sending and receiving XML values?
Tks,
Fabiano
This is not directly supported or recommended.
You probably could write your own custom code using an extended stored proc
or using sp_OA stored procs.
Best regards
Michael
"Fabiano" <a@.a.com.br> wrote in message
news:%23CzqpXqlEHA.1244@.TK2MSFTNGP15.phx.gbl...
> Please,
> is there a way i can execute a webservice from within a SQL Server 2k
> Trigger? Sending and receiving XML values?
> Tks,
> Fabiano
>
>

Calender in Reports 2K

One of my report has 2 input variables, StartDate and EndDate. Is that
possible that I can include a calender control in the report interface such
that user can choose the date rather than typing it in?
I come to know that :
That is not an option today (sure would be a nice improvement). If you
need this then you need to create your own web page and then integrate with
RS using URL or SOAP.
If it is so then can anyone help me with example how to use it.
will be very helpful if i get any attachment.
Thanks & Regards
Labhesh Shrimali
Bangalore -Cross posting to microsoft.public.sqlserver.reportingsvcs since this
question isn't related to security.
Hope this helps.
Dan Guzman
SQL Server MVP
"Labhesh Shrimali - Bangalore"
<LabheshShrimaliBangalore@.discussions.microsoft.com> wrote in message
news:86E50086-C732-4B90-B9F9-881A9EC0FD42@.microsoft.com...
> One of my report has 2 input variables, StartDate and EndDate. Is that
> possible that I can include a calender control in the report interface
> such
> that user can choose the date rather than typing it in?
> I come to know that :
> That is not an option today (sure would be a nice improvement). If you
> need this then you need to create your own web page and then integrate
> with
> RS using URL or SOAP.
> If it is so then can anyone help me with example how to use it.
> will be very helpful if i get any attachment.
> Thanks & Regards
> Labhesh Shrimali
> Bangalore -
>

Calender

One of my report has 2 input variables, StartDate and EndDate. Is that
possible that I can include a calender control in the report interface such
that user can choose the date rather than typing it in?No. That is not an option today (sure would be a nice improvement). If you
need this then you need to create your own web page and then integrate with
RS using URL or SOAP.
Bruce L-C
"Frank" <nospam@.nospam.org> wrote in message
news:eWgWxGxhEHA.3944@.tk2msftngp13.phx.gbl...
> One of my report has 2 input variables, StartDate and EndDate. Is that
> possible that I can include a calender control in the report interface
such
> that user can choose the date rather than typing it in?
>
>

CalendarTransform

when trying to install the calendarTransform component i receive following error:

the command "....\..\regcomponent calendarTransform" exited with code 1

any idea what it means?

It would help to know what the calendar transform component is.|||

KirkHaselden wrote:

It would help to know what the calendar transform component is.

http://www.microsoft.com/downloads/details.aspx?familyid=e603bde7-44bb-409a-890f-ed94a20b6710&displaylang=en|||There should be more to it than that message. If using the build event scroll up in the output window for a full message. Alternatively run the command by hand in a DOS window and see what comes out. My guess is that you have not assigned a strong name key file, see the Signing tab of the project properties. You cannot GAC a component unless it is signed.|||

darren:

after running the command in DOS by hand it works !!

thanks for your help.

peter

Calendars - Hierachy issue

I have date dimension that uses a date_skey as the primary key (format = 20070101). I have both fiscal and calendar hiearchies. My problem is that date based functions (ie YTD and ParallelPeriod) work with my regular calendar hierachy but not the fiscal hierachy. I have the following fields:

Year (type years)

Quarter (type quarters)

Month (type months)

Week (type weeks)

Date (type date)

FiscalYear (type FiscalYears)

FiscalQuarter (type FiscalQuarters)

FiscalMonth (type FiscalMonths)

FiscalWeek (type FiscalWeeks)

date_skey (type regular)

Calendar Hierachy:

Year (type years)

Quarter (type quarters)

Month (type months)

Date (type date)

and

Fiscal Hierachy:

FiscalYear (type fiscalyears)

FiscalQuarters(type fiscalquarters)

FiscalMonths (type fiscalMonths)

Date (type date)

Am I using date and date_skey incorrectly in this setup?

Any recommendations are welcome.

Based on results from the Adventure Works Date dimension, which has both Calendar and Fiscal heirarchies, you may need to use more explicit versions of MDX time series functions for the Fiscal hierarchy. For example, instead of YTD([Date].[Fiscal].CurrentMember), try PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember), etc.|||This was the right answer - YTD, MTD would not work on my Fiscal Calendar hierarchy but I was able to use PeriodsToDate in place of them. Thanks

Calendar table SELECT

I want to use a stored procedure and a calendar table to determine:
1) Is it the first business day of the month?; and
2) What was the last business day of the previous month?
CREATE PROCEDURE procTest
AS
--psuedo code
Is today the first business day of the month? If yes then determine the last
business day of the previous month and do something with that date; If no
end the procedure.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Calendar] (
[CalDate] [smalldatetime] NOT NULL ,
[BusinessDay] [char] (1) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051201','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051202','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051203','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051204','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051205','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051206','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051207','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051208','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051209','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051210','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051211','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051212','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051213','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051214','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051215','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051216','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051217','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051218','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051219','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051220','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051221','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051222','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051223','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051224','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051225','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051226','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051227','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051228','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051229','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051230','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051231','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060101','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060102','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060103','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060104','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060105','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060106','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060107','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060108','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060109','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060110','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060111','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060112','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060113','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060114','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060115','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060116','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060117','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060118','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060119','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060120','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060121','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060122','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060123','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060124','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060125','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060126','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060127','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060128','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060129','N')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060130','Y')
INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060131','Y')Try:
declare @.d datetime
set @.d = convert(char(8), getdate(), 112)
if exists (
select *
from dbo.calendar as c1
where CalDate = @.d
and BusinessDay = 'Y'
and not exists (
select *
from dbo.calendar as c2
where BusinessDay = 'Y'
and c2.CalDate < c1.CalDate
and c2.CalDate >= convert(char(6), @.d, 112) + '01'
)
)
select max(CalDate)
from dbo.calendar
where BusinessDay = 'Y'
and CalDate >= convert(char(6), dateadd(month, -1, @.d), 112) + '01'
and CalDate < convert(char(6), @.d, 112) + '01'
go
AMB
"Terri" wrote:

> I want to use a stored procedure and a calendar table to determine:
> 1) Is it the first business day of the month?; and
> 2) What was the last business day of the previous month?
>
> CREATE PROCEDURE procTest
> AS
> --psuedo code
> Is today the first business day of the month? If yes then determine the la
st
> business day of the previous month and do something with that date; If no
> end the procedure.
> Thanks to anyone who could help.
> CREATE TABLE [dbo].[Calendar] (
> [CalDate] [smalldatetime] NOT NULL ,
> [BusinessDay] [char] (1) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051201','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051202','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051203','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051204','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051205','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051206','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051207','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051208','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051209','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051210','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051211','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051212','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051213','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051214','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051215','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051216','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051217','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051218','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051219','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051220','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051221','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051222','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051223','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051224','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051225','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051226','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051227','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051228','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051229','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051230','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051231','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060101','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060102','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060103','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060104','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060105','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060106','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060107','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060108','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060109','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060110','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060111','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060112','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060113','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060114','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060115','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060116','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060117','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060118','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060119','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060120','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060121','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060122','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060123','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060124','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060125','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060126','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060127','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060128','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060129','N')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060130','Y')
> INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060131','Y')
>
>|||Thanks!
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:40481F08-FA9D-497A-AE1A-761DDF7FC229@.microsoft.com...
> Try:
> declare @.d datetime
> set @.d = convert(char(8), getdate(), 112)
> if exists (
> select *
> from dbo.calendar as c1
> where CalDate = @.d
> and BusinessDay = 'Y'
> and not exists (
> select *
> from dbo.calendar as c2
> where BusinessDay = 'Y'
> and c2.CalDate < c1.CalDate
> and c2.CalDate >= convert(char(6), @.d, 112) + '01'
> )
> )
> select max(CalDate)
> from dbo.calendar
> where BusinessDay = 'Y'
> and CalDate >= convert(char(6), dateadd(month, -1, @.d), 112) + '01'
> and CalDate < convert(char(6), @.d, 112) + '01'
> go
>|||Or if you fancy CLR, your could do something like below (and no tables):
declare @.today TDate
set @.today = TDate::Today
declare @.fbd TDate
declare @.lbdPriorMth TDate
set @.fbd = TDate::GetFirstBusinessDayOfMonth(@.today
)
set @.lbdPriorMth = TDate::GetLastBusinessDayOfMonth(@.today.AddMonths(-1))
select @.fbd.ToString() as FirstBizDayThisMonth, @.lbdPriorMth.ToString() as
LastBizDayPriorMonth
William Stacey [MVP]
"Terri" <terri@.cybernets.com> wrote in message
news:dqjbqv$m62$1@.reader2.nmix.net...
|I want to use a stored procedure and a calendar table to determine:
|
| 1) Is it the first business day of the month?; and
|
| 2) What was the last business day of the previous month?
|
|
| CREATE PROCEDURE procTest
| AS
| --psuedo code
|
| Is today the first business day of the month? If yes then determine the
last
| business day of the previous month and do something with that date; If no
| end the procedure.
|
| Thanks to anyone who could help.
|
| CREATE TABLE [dbo].[Calendar] (
| [CalDate] [smalldatetime] NOT NULL ,
| [BusinessDay] [char] (1) NOT NULL
| ) ON [PRIMARY]
| GO
|
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051201','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051202','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051203','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051204','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051205','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051206','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051207','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051208','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051209','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051210','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051211','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051212','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051213','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051214','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051215','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051216','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051217','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051218','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051219','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051220','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051221','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051222','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051223','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051224','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051225','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051226','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051227','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051228','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051229','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051230','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20051231','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060101','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060102','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060103','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060104','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060105','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060106','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060107','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060108','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060109','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060110','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060111','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060112','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060113','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060114','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060115','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060116','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060117','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060118','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060119','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060120','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060121','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060122','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060123','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060124','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060125','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060126','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060127','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060128','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060129','N')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060130','Y')
| INSERT INTO Calendar (CalDate,BusinessDay) VALUES('20060131','Y')
|
|
||||CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY
date_type INTEGER NOT NULL
CHECK (date_type > 0));
Instead of a Boolean flag, why not invent a numeric code that tells you
the type of date (work day, holiday, reporting, etc.)? This is very
flexible and makes the code extremely easy to write. Think in tersm of
data and not complex code.|||What if a day is both a holiday and a workday? (e.g. National holiday
but the company is open for business.)
--CELKO-- said the following on 1/17/2006 3:45 PM:
> CREATE TABLE Calendar
> (cal_date DATETIME NOT NULL PRIMARY KEY
> date_type INTEGER NOT NULL
> CHECK (date_type > 0));
> Instead of a Boolean flag, why not invent a numeric code that tells you
> the type of date (work day, holiday, reporting, etc.)? This is very
> flexible and makes the code extremely easy to write. Think in tersm of
> data and not complex code.
>|||<kidding>
I think he wants you to use a bitmask in these cases. Like 1 = wend, 2 =
holiday, 4 = reporting
</kidding>
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Steve Beach" <ohbillie--@.GGGGGGGGGGGGGGGGmail.com> wrote in message
news:OlgNyU8GGHA.3532@.TK2MSFTNGP14.phx.gbl...
> What if a day is both a holiday and a workday? (e.g. National holiday but
> the company is open for business.)
> --CELKO-- said the following on 1/17/2006 3:45 PM: