adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

Add User/Set Roles in Code and Read Roles

Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?

I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.

After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.|||insert into sysusers values
('new_user_id', 0, 'new_role_name', NULL, 0x00, getdate(), getdate(), 'dbo', NULL)|||Originally posted by Enigma
Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.
sp_grantlogin, sp_grantdbaccess, sp_addrolemember, and sp_helpuser worked great. thanks.

Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

Add a where condition only if a parameter exists

I'm pretty new to TSQL. I'm trying to write a generic proc that returns
either ALL records from a table or (if a parameter is set to 1) or only a
sub-set of that table (if parameter is set to 0).
Here's an example of what I'm aiming for:
Create table contact (
contactID int,
FullName varchar (40),
IsActive bit)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
-- if @.ShowAll is null, return all records
--otherwise limit records to records where isActive = True
How can I conditionally add a WHERE clause based on the value of @.ShowAll?
Thanks!
- JohnnyI presume that ContactId is the prinary key of Contacts. If this is the case
,
skip the @.ShowAll parameter.
If @.ContactId is null then have it return all, otherwise have it return only
that contact:
SELECT *
FROM Contacts
WHERE ContactId = COALESCE(@.ContactId,ContactId)
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"JohnnyMagz" wrote:

> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||You need dynamic sql in this case
something like (untested)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
declare @.sql varchar(8000)
set @.sql = 'select * from contact'
if @.showall is not null
set @.sql = @.sql + ' where isactive = 1'
exec (@.sql)
go
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||Assuming isActive is never NULL and @.ShowAll is either NULL or 1.
...where isActive = isnull(@.ShowAll, 0) or isActive = @.ShowAll
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason

Ad hoc query running crazy slow.

Hi,
I have a job that execute a stored proc. It was running fine like 3
minutes to finish but a week ago it started to take 4 hours. Its using
ad hoc quries in that stored procedure. I reindex all the tables that
it was using. I check execution plan there was no tabloe scans. I
recompile the stored proc but no use. its sql 2000 with service pack 3
installed on it. Here is the query anyone has any clue what went wrong
suddenly. Link server is working fine.
i am running it like this
exec usp_MO_ActivityTradesBPS 'CORP'
CREATE PROCEDURE dbo.usp_MO_PositionsBPS
@.Load varchar(25) = null,
@.AsOfDate datetime
AS
Set @.Load = '%'
If @.Load is null or @.Load = ''
begin
Set @.Load = '%'
end
If IsNull(@.AsOfDate,'') = ''
Set @.AsOfDate = dbo.fn_BusinessDate(GetDate()-1)
If @.Load = 'SANFRAN'
begin
Set @.Load = '%'
SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
T.ACCOUNTTYPECD + T.CUSIPNR [key],
RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_Positions T
WHERE T.BRANCHCD = '001'
and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
SM.Bps_AcctNr
From MO_InvStrategyMap SM
Where SM.Bps_AcctNr is not null)
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
T.CUSIPNR, T.CURRENCYISOCD
end
Else
begin
SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
then T.MarketValAm/T.TDQuantity
else T.SecurityPriceAm end as SecurityPriceAm,
T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_PositionsHistorical T
JOIN MO_InvAccountMap AM
ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
JOIN (Select Distinct TraderCd
From MO_InvStrategyMap
Where PrimaryRecFl = 1
and (Desk like @.Load
or Category like @.Load
or SubCategory like @.Load
or TraderName like @.Load
or TraderCd like @.Load)) SM
ON SM.TraderCd = AM.Inventory
WHERE T.BusinessDate = @.AsOfDate
and AM.DataSource like 'BPS%'
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
end
GO
Assuming this is consistently reproduced, a few things you might want to
investigate:
- Do you have the original (3-minute) query plan? If so, what's different
from the current one?
- Looking at the query plan, where are you spending most of your time?
- During execution, are you seeing any resource constraints? E.g. > 5 avg
disk queue length, high avg disk sec/read,
- Are you seeing waits on cxpackets when you run dbcc sqlperf (waitstats)?
Might indicate parallellized queries waiting.
Suggest you dig into the query plan to see where it's spending most of its
time and check to ensure you don't have resource bottlenecks.
joe.
"DARR" <umariqbaldar@.gmail.com> wrote in message
news:1168992585.559292.8530@.s34g2000cwa.googlegrou ps.com...
> Hi,
> I have a job that execute a stored proc. It was running fine like 3
> minutes to finish but a week ago it started to take 4 hours. Its using
> ad hoc quries in that stored procedure. I reindex all the tables that
> it was using. I check execution plan there was no tabloe scans. I
> recompile the stored proc but no use. its sql 2000 with service pack 3
> installed on it. Here is the query anyone has any clue what went wrong
> suddenly. Link server is working fine.
> i am running it like this
> exec usp_MO_ActivityTradesBPS 'CORP'
>
> CREATE PROCEDURE dbo.usp_MO_PositionsBPS
>
> @.Load varchar(25) = null,
> @.AsOfDate datetime
> AS
> Set @.Load = '%'
> If @.Load is null or @.Load = ''
> begin
> Set @.Load = '%'
> end
> If IsNull(@.AsOfDate,'') = ''
> Set @.AsOfDate = dbo.fn_BusinessDate(GetDate()-1)
> If @.Load = 'SANFRAN'
> begin
> Set @.Load = '%'
> SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
> T.ACCOUNTTYPECD + T.CUSIPNR [key],
> RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
> BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
> T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
> T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
> T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
> T.DAILYNETPL, T.CURRENCYISOCD
> FROM BPS_Positions T
> WHERE T.BRANCHCD = '001'
> and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
> SM.Bps_AcctNr
> From MO_InvStrategyMap SM
> Where SM.Bps_AcctNr is not null)
> and (T.TDMTDSECFEE <> 0
> or T.TDCOmsnAm <> 0
> or T.TDQUANTITY <> 0
> or T.MarketValAm <> 0
> or T.MTDNETPL <> 0
> or T.MTDPL <> 0
> or T.LEDGERBAlanceAm <> 0
> or T.DAILYNETPL <> 0
> or T.TDDivIntAm <> 0
> or T.TDGRCREDITMTDOP7 <> 0)
> ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
> T.CUSIPNR, T.CURRENCYISOCD
> end
> Else
> begin
> SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
> rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
> T.CUSIPNR,
> T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
> then T.MarketValAm/T.TDQuantity
> else T.SecurityPriceAm end as SecurityPriceAm,
> T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
> T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
> T.DAILYNETPL, T.CURRENCYISOCD
> FROM BPS_PositionsHistorical T
> JOIN MO_InvAccountMap AM
> ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
> JOIN (Select Distinct TraderCd
> From MO_InvStrategyMap
> Where PrimaryRecFl = 1
> and (Desk like @.Load
> or Category like @.Load
> or SubCategory like @.Load
> or TraderName like @.Load
> or TraderCd like @.Load)) SM
> ON SM.TraderCd = AM.Inventory
> WHERE T.BusinessDate = @.AsOfDate
> and AM.DataSource like 'BPS%'
> and (T.TDMTDSECFEE <> 0
> or T.TDCOmsnAm <> 0
> or T.TDQUANTITY <> 0
> or T.MarketValAm <> 0
> or T.MTDNETPL <> 0
> or T.MTDPL <> 0
> or T.LEDGERBAlanceAm <> 0
> or T.DAILYNETPL <> 0
> or T.TDDivIntAm <> 0
> or T.TDGRCREDITMTDOP7 <> 0)
> ORDER BY AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
> T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
> end
> GO
>