Question about sp_addscriptexec

Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:

> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]

Question about relationships - "cycles or multiple cascade paths" error

I have 2 tables:
Table: UserTable
Field: Id (pk)
Field: FullName
Table: MarriageTable
Field: MarriageId (pk)
Field: Husband
Field: Wife
The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
valid users from the UserTable.
In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
MarriageTable.Wife. But, the Diagram has trouble saving the Wife
relationship. Any ideas? Am I doing this correctly?
Here's the error:
'UserTable' table saved successfully
'MarriageTable' table
- Unable to create relationship 'FK_MarriageTable_UserTable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_MarriageTable_UserTable1' on table
'MarriageTable' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
Thanks.
On Wed, 29 Dec 2004 10:01:20 -0500, VB Programmer wrote:

>I have 2 tables:
>Table: UserTable
>Field: Id (pk)
>Field: FullName
>Table: MarriageTable
>Field: MarriageId (pk)
>Field: Husband
>Field: Wife
>The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
>valid users from the UserTable.
>In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
>UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
>MarriageTable.Wife. But, the Diagram has trouble saving the Wife
>relationship. Any ideas? Am I doing this correctly?
Hi VB Programmer,
Simple point and click interfaces can be great if you don't know the nitty
gritty details and don't want to know them. However, when dealing with a
complex DBMS such as SQL Server, it's best to do know the nitty gritty
details. You should get used to defining all your tables and constraints
in pure SQL.
CREATE TABLE Users (UserID int NOT NULL,
FullName varchar(60) NOT NULL,
PRIMARY KEY (UserID)
)
CREATE TABLE Marriages (MarriageID int NOT NULL,
Husband int NOT NULL,
Wife int NOT NULL,
PRIMARY KEY (MarriageID),
UNIQUE (Husband, Wife),
FOREIGN KEY (Husband)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Wife)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE
)
Run this in Query Analyzer and you'll be 100% sure that you didn't
accidentally create a foreign key "in the wrong direction".
Unfortunately, you'll still get an error. This is a limitation in SQL
Server 2000. If you use the cascades option on foreign key constraints,
SQL Server demands that there never be more than one cascading path from
any table to any other table. You'll have to modify at least one of your
foreign key constraints to do ON DELETE NO ACTION ON and ON UPDATE NO
ACTION (the default options). You'll also have to use another solution if
your applications requires cascading behaviour. Possible solutions are to
encapsulate all logic in a stored procedure or to use a trigger.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question about pulling Price from multiple tables.

Pricing is a little confusing at my work. My works database from the point of sale has multiple places for price to be. Right now the tables in question are in a SQL database. I am trying to create a user defined function so I can get a list of prices for specific items and for the customer logged into the web page. The tables look like this:

Inventory: ItemID, ItemCompany, Description, ListPrice, PublicPrice
ContractID: ContractNum, ItemID, ItemCompany, MinQty, ContractPrice
Customer: CustomerNumber, CustomerDepartment, Contract1 – Contract4

ContractNum '99' is a global contract to all customers. I was able to get the price for this generic contract price, but not for contracts customer may have.

Here is what I have so far (with an example item already being pulled up). I am at a loss on how to get the customers contract price with the same function.

SELECT Inventory.ItemNumber, Inventory.Company, Inventory.Description,
 Contracts.QuantityAS MinContractQty,COALESCE (Contracts.ContractPrice, Inventory.WhlCatalogPrice)
AS Price, Inventory.WhlCatalogPriceAS ListPrice
FROM InventoryLEFTOUTER JOIN ContractsON Inventory.ItemNumber = Contracts.ItemNumberAND Inventory.Company = Contracts.CompanyAND'99' = Contracts.ContractNumber
WHERE (Inventory.ItemNumber = N'444')AND (Inventory.Company = N'035')

Assuming my grasp of your schema is correct, is there a reason why this wouldn't work?

SELECT ContractID.ContractPrice, Inventory.DescriptionFROM Customer, ContractID, InventorWHERE (Customer.Contract1 = ContractID.ContractNumORCustomer.Contract2 = ContractID.ContractNumORCustomer.Contract3 = ContractID.ContractNumORCustomer.Contract4 = ContractID.ContractNum)AND ContractID.ItemID = Inventory.ItemID

This should get all the prices and descriptions for the items that have a contract number. Add another AND clause to the WHERE and narrow it down to the CustomerID you want. You can add the global contract by addind 99 = ContractID.ContractNum in the WHERE statement (put it in the parentheses).|||The only problem I am running into is if there is no contract price under '99' or under one for the customer, it would not bring up the item. The '99' group is select items, the contract price just applies to everyone. Thanks for the reply.|||Can you post some sample data too?|||

Sure. I think I have all of the different situations that might come up.

Contract Table:
"ItemNumber","Company","ContractNumber","ContractPrice","Qty"
"01541","NAT","BB",$4.99,1
"01541","NAT","DD",$4.50,1
"26300","CRD","DD",$6.00,1
"26300","CRD","BB",$6.29,1

Customers:
"CustomerNumber","Department","CustomerName","Contract1","Contract2","Contract3","Contract4"
" 15576","","Company1","DD","","",""
" 15577","","Company2","BB","","",""
" 15601","","Company3","","",,""
" 15602",,"Company4","C1","DD","",""

Product Table:
"ItemNumber","Company","Description","WhlCatalogPrice"
"06342","MEA","BOOK,NOTE,SCIENTIFIC,11X8.5",$11.69
"24133","SAN","PEN,LIQUID EXPRS 8PK,AST",$18.50
"26300","CCS","CASE,DRIVE,NEO-SPORT,BK",$30.73
"33350","KMW","DOCK,ENTERTAINMENT,IPOD 500",$162.50
"01541","NAT","CLIPBOARD,LTR,RECY,PLAST,RD",$7.35
"01593","COX","WIPE,DISINFECTING,CLOROX",$52.80
"26300","CRD","Item 26300",$9.95
"20021","IVR","INKCART,MFC3100, 950YD,BK",$16.79
"45005","CCS","TOWER,CD,ONETOUCH,40 CAP",$64.95
"5500","TOP","FORM,P/OUT,1PT,8.5X11,35C",$128.47
"5500","LAK","HEATER,RADIATOR,GY",$199.95
"5500","OSP","CHAIR,MANAGERIAL,MESH,BK",$355.00|||

Tealc:

The only problem I am running into is if there is no contract price under '99' or under one for the customer, it would not bring up the item. The '99' group is select items, the contract price just applies to everyone. Thanks for the reply.

I'm not quite getting what you're trying to do. Can you explain it again for me?

Thanks.

|||

I ment to add these to the contract table:

"06342","MEA","99",$9.69,1
"24133","SAN","99",$16.99,1

I am looking for the function to return both "List Price" (which is WhlCatalogPrice), and a price specific to the '99' group or customer contract if they have any.

Thanks for everyones reply so far.

|||

SELECT i.Itemnumber, i.Company, i.Description,COALESCE(Contract1.MinQty,Contract2.MinQty,Contract3.MinQty,Contract4.MinQty,GlobalContract.MinQty) AS MinContractQty,COALESCE(Contract1.ContractPrice,Contract2.ContractPrice,Contract3.ContractPrice,Contract4.ContractPrice,GlobalContract.ContractPrice,Inventory.WhlCatalogPrice) AS Price,I.WhlCatalogPrice AS ListPrice

FROM Inventory i

CROSS JOIN Customer cust

LEFT JOIN Contracts Contract1 ON Contract1.ContractNum=cust.Contract1 AND Contract1.ItemID=i.ItemID

LEFT JOIN Contracts Contract2 ON Contract2.ContractNum=cust.Contract2 AND Contract2.ItemID=i.ItemID

LEFT JOIN Contracts Contract3 ON Contract3.ContractNum=cust.Contract3 AND Contract3.ItemID=i.ItemID

LEFT JOIN Contracts Contract4 ON Contract4.ContractNum=cust.Contract4 AND Contract4.ItemID=i.ItemID

LEFT JOIN Contracts GlobalContract ON GlobalContract.ContractNum=99 AND GlobalContract.ItemID=i.ItemID

WHEREcust.CustomerNumber=@.CustID

|||That query seems to be working, but I keep getting duplication. I am going to look at each of the tables to make sure there is no duplication in them. I am still trying to learn what is what in the database from this new to me point of sale system (DDMS).sql

Question about multiple subscriptions

Hello,

Ok we are finally get close to testing SQL 2005 with SQL Express 2005:). So I have a publication named hh3_100_pub. We currently ghost each unit so the instance name and database names are the same. My question is we have 20 possible subcribers. Does each subsriber need to:

1. Have a different instance name?
2. Have a different database name?

What would I do to add another subscriber with the same instance name and
database name?

code below

--BEGIN: Script to be run at Publisher
'B-DB\BDBSERVER'--
use [sandiego] --database to set as publication
exec sp_addmergesubscription @.publication = N'hh3_100_pub', --the name given
to the publication during the creation of the publication via the wizard
@.subscriber = N'vm2ksqlexpress\hh3', --name of the subscriber client
instance
@.subscriber_db = N'wip', --name of the subscriber db
@.subscription_type = N'pull',
@.subscriber_type = N'global',
@.subscription_priority = 0,
@.sync_type = N'Automatic'
GO

--BEGIN: Script to be run at Subscriber
'vm2ksqlexpress\hh3'--
use [wip]
exec sp_addmergepullsubscription
@.publisher = N'B-DB\BDBSERVER',
@.publication = N'hh3_100_pub',
@.publisher_db = N'sandiego',
@.subscriber_type = N'Local',
@.subscription_priority = 0,
@.description = N'',
@.sync_type = N'Automatic'
Posting the response from another thread:
-->

A subscription needs to be on a database on a server.
So on a server, you can have multiple databases and each of these databases
can have subscriptions from a publication.
If you have multiple servers/instances, you can have the same named database
on each of them and then each such database can have a subscription from a
publication

Since you cannot have mutiple databases of the same name, you cannot have
subscription.
Either you will have to use different database names on the same server or
different servers/instances with same database names.

question about hierarchical dimensions

Hi,

I have a cube that has a "Cost Centre" dimension and a "Product" dimension. Both of these are hierarchical and have multiple levels. I've been asked to write a report which will show a breakdown by "product" & "cost centre" for a certain measure. I wrote the following query which does what I wanted...

SELECT
NON EMPTY { Hierarchize([Cost Centre].[L01 Cost Centre Name].members) } ON COLUMNS,
NON EMPTY { Hierarchize([Product].[High Level Product Name].members) } ON ROWS FROM [MyCube]
WHERE ([Measures].[Costs])

I've now been asked to show the cost centes in groups which are not in the hierarchy. For example, lets say that I have the following Cost Centres members in level 2 of my dimension...

- Level 1 Cost Centre
- Debt Markets Origination PMO & Securitisation
- Other Debt Market
- Flow Credit Trading
- Other T&IP
- Aviation Capital
- Project & Export Finance

I've been told that the cost centres belong to another level not defined in the hierarchy...

- Level 1 Cost Centre
- Origination
- Debt Markets Origination PMO & Securitisation
- Other Debt Market
- Sales
- Flow Credit Trading
- Other T&IP
- Banking
- Aviation Capital
- Project & Export Finance

My question is, if "Origination", "Sales" and "Banking" don't exist in my Cost Centre hierarchy, is it possible to create them in AS somehow or is it possible to select them with MDX somwehow?

Thanks for any help, I hope my question makes sense.

Lachlan

What you asked for is the same as grouping pivot table data inside Excel. Under the hood, Excel creates a session cube with grouping dimensions.

|||

Wow thats sounds complicated but it looks like it's the answer. It might be easier for me to just insert the new level into the dimension data itself, if I am allowed to do that :)

Thanks for your help.

Lachlan

Question about getting data from multiple tables

I have a report that receives a Soc. Sec. No. parameter. The first table I
need data from is keyed on SSN. This table also has Id. column.
I need to use the value in the ID column to retreive data from a different
data table for another part of the report.
From a design point of view how should I approach this.
--
DonThis will most likely work, depending on the nature of your report:
Simply join your tables in your source query (data tab). You can group your
layout based on data in the first table. Or you can base different parts of
the report on different source queries, but make both queries filtered on
the same data.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A2E283F5-EFDA-487A-B03E-D5469582871A@.microsoft.com...
>I have a report that receives a Soc. Sec. No. parameter. The first table I
> need data from is keyed on SSN. This table also has Id. column.
> I need to use the value in the ID column to retreive data from a different
> data table for another part of the report.
> From a design point of view how should I approach this.
> --
> Don

Question about get multiple records !

I have table with format :

ID Name ParentID
1 England 0
2 Canada 0
3 Manchester 1
4 Chelsi 1
5 Arsenal 1
6 Canada_1 2
7 Canada_2 2
8 MU_1 3
9 MU_2 3
10 MU_1_1 8
...

Now , I write a function which to get information ,such as :
England -- Manchester --MU_1--MU_1_1(*)
My Function :
FunctionGetInformation(ID as int16) as string
' my code here
End function

My problem : (With ID=10)
When i pass this ID , i have to get string which format :(*)
So , what is " my code here " ?
Thank any suggestions ,any advices ...
And thank you very much.

Could you explain what you are looking for in a bit more detail?

|||

I store data in "tree" form .
Example :
+England--Manchester--Manchester1--Manchester1_1 .....
+A--A1--A1_1--A1_1_1 ....
so on !
And i design above table.
My problem :
When i know of node of tree , i can not identify path from root .
such as , i know Manchester1_1 , how can indentify the string :
+England--Manchester--Manchester1--Manchester1_1 .....
You understand me !!
Thank you very much .

|||

Any one help me ? or my bad explaining !
Thank you very much.

|||

CREATE FUNCTION dbo.fnSplit
(
@.List nvarchar(2000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@.SplitOn,@.List)>0)
BEGIN
INSERT INTO
@.RtnValue
(
value
)
SELECT
LTRIM(RTRIM(SUBSTRING(@.List,1,CHARINDEX(@.SplitOn,@.List)-1)))
SET @.List = SUBSTRING(@.List,CHARINDEX(@.SplitOn,@.List)+LEN(@.SplitOn),LEN(@.List))
END
INSERT INTO
@.RtnValue
(
Value
)
SELECT LTRIM(RTRIM(@.List))
RETURN
END

and then use sp or dynamic sql like this

SELECT {something}
FROM {sometable}

INNER JOIN dbo.fnSplit({coma delimited list of ids},{delimter-in this case comma) A ON [dbo].sometable.your id= A.Value

|||

thanh for your guide !! (very much )
It's perfect if you have some comment or explainning what you writting !!
Thank you .

|||I did not really read what you need, other then the heading "get multiple records". This above allows you to pass in a comma delimited list of IDs and return the records back from the stored procedure.|||

There is probably a better way of doing this, and I'm coding this from memory, so some of it may not be 100% correct, but the idea is sound.

function GetInformation(ID as int16) as string

if id<>0 then

dim conn as new sqlConnection(...)

dim cmd as new sqlCommand("SELECT * FROM MyTable WHERE ID=" & ID.tostring)

conn.open

dim dr as SqlDataReader =cmd.Execute(conn)

dim parentID as string =dr("ParentID")

dim n as string=dr("Name")

dr.close

conn.close

return GetInformation(parentID) & n & "--"

else

return ""

end if

end function

|||Thank all !!
To Motley :
You put connection string in function ! and call it each loop !
Performance is good ?|||

It's not the best thing to do, no. In my projects I would have put the connection someplace else. Depending on the dataset size, I might have read the whole thing either into a dataset or a strongly typed collection, and did all my processing there. Depends on how much you need to process and how much data there is.

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks
<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegr oups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/de...qlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David

Question about ad hoc Compiled Plans in syscacheobjects

When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David