Launch Subscription via VB Code

I have created a subscription via Report Manager & would like to launch it
via vb code or rs script on any given date or time. That is, even though the
subcription may have a set date/time, via CODe I would launch it & modified
its date & time as necessary.
--
EdgarNot particularly elegant, but you can do this in the following way:
1) create a shared schedule that fires once, in the past. Save the Schedule
ID you get back
2) create a subscription that is triggered by this shared schedule.
3) use some code to call the FireEvent method and submit the
TimedSubscription event as the EventType and the schedule ID from (1) as the
MatchData.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message
news:045CCA4A-B712-489D-8609-068642154128@.microsoft.com...
>I have created a subscription via Report Manager & would like to launch it
> via vb code or rs script on any given date or time. That is, even though
> the
> subcription may have a set date/time, via CODe I would launch it &
> modified
> its date & time as necessary.
> --
> Edgar|||Lukasz,
I tried your solution & everything works fine except that I get an error
saying that the subscription cannot be found. Even though the subscription
ID is listed within SQL Server. Below is the output I get with the error. I
also Echo the subscription ID & the line so I can see the ID which later I
find in the subscriptions table in SQL Svr. Is there a particular way to
format the subscriptionID in order for RS to see it?
The Current Subscription is: 0d49d301-ecd8-43fc-8319-21ec19e82f77
==========================================System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: The subsc
ription cannot be found. The subscription identifier that is provided to an
operation cannot be located in the report server database. -->
Microsoft.ReportingServices.Diagnostics.Utilities.Subscrip
tionNotFoundException: The subscription cannot be found. The subscription
identifier that is provid
ed to an operation cannot be located in the report server database.
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.GetSubscriptionProperties(String Subsc
riptionID, String& Owner, ExtensionSettings& ExtensionSettings, String&
Description, ActiveState& A
ctive, String& Status, String& EventType, String& MatchData,
ParameterValue[]& Parameters)
at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message,
WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] param
eters)
at
Microsoft.SqlServer.ReportingServices.ReportingService.GetSubscriptionProperties(String Subsc
riptionID, ExtensionSettings& ExtensionSettings, String& Description,
ActiveState& Active, String&
Status, String& EventType, String& MatchData, ParameterValue[]& Parameters)
at ____ScriptClass.Main()
The command completed successfullysql

Launch a URL from TSQL code [URGENT]

Hi Folks,

I am a SQL Server DBA but I am new to real TSQL programming. I have a question as explained below and any help (soon) will be appreciated.

I want to invoke a web page from within a TSQL code:
1) Is it possible to do this?
2) If yes, how?
3) Can I call a web page from command prompt?

Please help me on this - this is quite urgent for me.

Thanks a lot!

Astha1) I was able to invoke the webpage by typing this at the command prompt

c:\Program Files\Internet Explorer\iexplore.exe" http://www.google.com

however somehow the same not working when i tried it using xp_cmdshell ...

SQL gurus ... any ideas why ??|||Thanks Enigma :)

This really helped and was fine with what I needed. However, when running this from xp_cmdshell - I guess you should use with CMD .

Thanks again.

Astha

Latvian(1062) as full-text indexing/querying language in SQL2005

Hello.

We're using MSSQL server 2005, here in Riga, Latvia.
I've next question - is latvian (code 1062) language will be supported as full-text indexing language in MSSQL 2005?

Thank you.
? If you have your own stemmer and word breakers yes. But otherwise no. -- Hilary CotterDirector of Text Mining and Database StrategyRelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com <Helge Shep@.discussions.microsoft..com> wrote in message news:5f561573-aeb9-42b5-a6b7-22790cbf049a@.discussions.microsoft.com...Hello.We're using MSSQL server 2005, here in Riga, Latvia.I've next question - is latvian (code 1062) language will be supported as full-text indexing language in MSSQL 2005? Thank you.

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

sql

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Date Stored Proc Updated??

Is there such a date/time?

I see the Created date on the list of stored procs, but really want a
Date Last Updated. After changing code for 3 hours, I tend to forget
which procs I've worked on, and which need to be move to production.
any simple way to keep track of the last procs played with?

thanks in advance...
john@.ViridianTech.comDo you mean you've used ALTER PROC and you want to see when it was
ALTERed? If so, then this isn't currently possible in MSSQL (although
it is in SQL 2005).

In any case, you should hopefully be using some sort of source control
system to store your object creation scripts, and a deployment process
which would help to track your changes. You might also want to consider
a database comparison tool, which can quickly show you the differences
between databases.

Simon|||Actually, we're not using any source control on the stored procs, like
we do on the project source code. How would you do that? is there
something in MSSQL for that? the .NWT IDE for VB make it easy to
integrate with Source Safe, what do you use for stored procs, views and
table creation scripts? You advice would be much appreciated.

john|||<John@.ViridianTech.com> wrote in message
news:1120484863.856415.206040@.o13g2000cwo.googlegr oups.com...
> Actually, we're not using any source control on the stored procs, like
> we do on the project source code. How would you do that? is there
> something in MSSQL for that? the .NWT IDE for VB make it easy to
> integrate with Source Safe, what do you use for stored procs, views and
> table creation scripts? You advice would be much appreciated.
> john

Personally, I simply check code out of VSS and work with it in Query
Analyzer - there's no source control integration in the MSSQL tools
themselves. I believe Visual Studio has some sort of support for SQL code
and VSS, although I don't use VS often myself, so I may be wrong about that.

Even using just QA and VSS, a few scripts can make things easier - the
Customize menu in QA allows you to pass a few useful parameters to batch
files or other programs, so it's not too difficult to script checking in and
out of VSS (SQL 2005 has source control integration in the Management
Studio).

Erland has an interesting toolset for working with SQL source code and VSS,
written in Perl, which might be worth looking at if you want to develop your
own solution or just need some ideas about managing and deploying SQL code:

http://www.abaris.se/abaperls/index.html

Simon|||(John@.ViridianTech.com) writes:
> Actually, we're not using any source control on the stored procs, like
> we do on the project source code. How would you do that?

You just do it!

> is there something in MSSQL for that? the .NWT IDE for VB make it easy
> to integrate with Source Safe,

Hrmpf! Nothing in Visual Studio is easy. (I understand less and less of
it for each new version they come out with.) And in our shop, you may
use the SourceSafe integration for the VB code, but if you mess up,
our build people will tell you to stop doing it.

The absolutely best too to work with SourceSafe is the VSS Explorer.

> what do you use for stored procs, views and
> table creation scripts? You advice would be much appreciated.

Actually, we don't even use QA for editing, but use Textpad instead,
simply because it's a better editor.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you're not going to have access to a source control tool or
methodology any time soon, here's a workaround; but there's nothing
foo-proof about this, so you still have to be careful:

Just drop and create your stored procedures instead of altering them.
This modifies the crdate in sysobjects, which is reflected in
Enterprise Manager.

drop procedure proc_mytest
go

create procedure proc_mytest
as
< whatever
hth,

victor dileo|||vjdileo (vic_technews@.yahoo.com) writes:
> If you're not going to have access to a source control tool or
> methodology any time soon, here's a workaround; but there's nothing
> foo-proof about this, so you still have to be careful:
> Just drop and create your stored procedures instead of altering them.
> This modifies the crdate in sysobjects, which is reflected in
> Enterprise Manager.

And there is actually a way of detecting that a procedure have
been altered. sysobjects.schema_ver is incremented with 16 each
you alter the procedure.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Guys, there really is a great way of working with your SQL code in just
the same way as you do for your application code and it's called DB
Ghost (www.dbghost.com). It's the ONLY SQL Server tool on the market
that can build a brand new database from a set of object creation
scripts taking care of all dependencies. This database is then used by
D Ghost to compare and upgrade any target database.

The upshot of our approach is that you have ALL database objects as
'create' scripts under source control and just modify those. You work
in a familiar manner and the source control system becomes your friend
rather than a necessary evil. Most other approaches to having SQL in
source control involve having to do two things for each update:
1. Update the create script.
2. Write an ALTER script.

DB Ghost does away with the second of these not just for stored
procedures but for EVERY database object.

Honestly, you may think this approach cannot work but it does and our
customers use phrases like 'religious experience' when they talk about
it. You just have to take the time to 'get' it...|||We just do a generate sql script for all procs (separate file for each)
and add them to a source safe project and use that through visual
studio. Pretty easy and works great. If you make the project a database
project in visual studio it will let you execute them from there as
well.

Large updates and locks

Hi there,
I am looking over some code in a project I just joined and found
something that I am not sure about. The are processes which update or
delete many rows of data and the approach chosen to perform these
updates and deletes was through cursors. The setup looks something
like this:
Example:
DECLARE Orders CURSOR FOR select order_id from order where order_Date +
10 < getdate();
loop
begin
fetch orders into @.OrderId;
delete orderdetail where order_id = @.OrderId;
end;
Instead of:
delete orderdetail from orderdetail join orders where orders.order_date
+ 10 < getdate();
The motivation for it is that the locks will be more granular and other
processes will be able to access the tables instead of being locked out
for the duration of the process. The cursor approach is obviously
slower by few orders of magnitude to execute but that aside does this
approach make sense?
ThanksThis might make sense in some cases. Batching data modifications is a
common method of getting around locking issues. But whether it's necessary
or not is difficult to know based on the information you've given so far.
What percentage of the rows are being deleted each time? Is there an index
to support the deletion? Currently, your predicate (order_date + 10 <
getdate()) cannot use indexes, so the answer to the latter question is no.
You may have better luck with:
DELETE FROM orderdetail
WHERE EXISTS
(
SELECT *
FROM orders
WHERE orders.order_id = orderdetail.order_id
AND orders.order_date <= GETDATE() - 10
)
This will be able to take advantage of an index on the order_date column,
and therefore may perform better (and take more granular locks) than a
non-cursor approach using (order_date + 10).
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"boblotz2001" <boblotz2001@.yahoo.com> wrote in message
news:1124715871.853196.33820@.g44g2000cwa.googlegroups.com...
> Hi there,
> I am looking over some code in a project I just joined and found
> something that I am not sure about. The are processes which update or
> delete many rows of data and the approach chosen to perform these
> updates and deletes was through cursors. The setup looks something
> like this:
> Example:
> DECLARE Orders CURSOR FOR select order_id from order where order_Date +
> 10 < getdate();
> loop
> begin
> fetch orders into @.OrderId;
> delete orderdetail where order_id = @.OrderId;
> end;
> Instead of:
> delete orderdetail from orderdetail join orders where orders.order_date
> + 10 < getdate();
> The motivation for it is that the locks will be more granular and other
> processes will be able to access the tables instead of being locked out
> for the duration of the process. The cursor approach is obviously
> slower by few orders of magnitude to execute but that aside does this
> approach make sense?
> Thanks
>