SQL About AMO: copy

Showing posts with label copy. Show all posts

Monday, March 19, 2012

Access - SQL 2005

Hi Guys/Girls...
I'm using SQL 2005 integration services to copy data from an existing access
database. The access datbase contains a field, with the type of NTEXT. I am
trying to write the corresponding data to a table with a nvarchar(8000)
field.
I'm also trying to use the 'Data Conversion' data flow transformation,
however I am unsure what to convert the NTEXT to? I've tried a variety of
supported types, however I always seem to get an error stipulating that the
output stream stipulated does not support the NTEXT conversion.
Has anybody tried anything like this yet?
Thanks,
Justin> I'm using SQL 2005 integration services to copy data from an existing
> access database. The access datbase contains a field, with the type of
> NTEXT.
Access does not have a data type called NTEXT. This is gong to be MEMO in
Access, and I think Integration Services is telling you to use NTEXT in SQL
Server. I am going to suggest you use NVARCHAR(MAX) as the destination data
type.
A|||Hi Aaron,
I do not wish to import into a NTEXT field in my SQL2005 database, but would
prefer that Integration Services do the implicit conversion to a
Varchar(8000) using the 'Data Conversion' data flow transformation.
Thanks,
Justin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:enxdpaq%23FHA.984@.tk2msftngp13.phx.gbl...
> Access does not have a data type called NTEXT. This is gong to be MEMO in
> Access, and I think Integration Services is telling you to use NTEXT in
> SQL Server. I am going to suggest you use NVARCHAR(MAX) as the
> destination data type.
> A
>

Sunday, March 11, 2012

Accesing a database with password

Hello, I have a database and I need to copy and attach it in a customer SQL
server. But I don't want them to be able to see the tables structures or my
company's stored procedures. Is this possible to have the database in their
sql server and not allow them to see the data or tables. Or only with a
password be able to access the database. Like in Microsoft Access that you
can assign a password to the database and everytime I open the file it
prompts for the password.
Thanks in advance
JennyferdYou can "encrypt" the stored procedures (see BOL, Create Procedure "With
Encryption"), but there are tools out there that pretty handily defeat such
encryption. It's pretty much just enough to keep unmotivated honest people
honest. You could use Views to prevent people from accessing the underlying
tables directly, but on their box they will presumably have sa/administrator
access and will be able to view your table structures... I'm not sure of
any methods to hide that info... Although someone else here may know.
To be honest, to me it's a little counter-intuitive - I usually supply full
documentation on the database tables and structures to clients, since they
invariably try to generate reports, etc., on their own... to me it makes
more sense to give them the tools to access it the right way, set up
read-only views, etc., so they don't trash the system...
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uTp4scxLFHA.3420@.tk2msftngp13.phx.gbl...
> Hello, I have a database and I need to copy and attach it in a customer
> SQL
> server. But I don't want them to be able to see the tables structures or
> my
> company's stored procedures. Is this possible to have the database in
> their
> sql server and not allow them to see the data or tables. Or only with a
> password be able to access the database. Like in Microsoft Access that you
> can assign a password to the database and everytime I open the file it
> prompts for the password.
> Thanks in advance
> Jennyferd
>

acc97 switchboard error. 3146 Can someone look and tell a rookie what is wrong?

Onl;y one user has the problem.
I have given them a new copy of the switchboard but same startup error.
IOSDSQL 664-964 ENTER SQLSetStmtOption
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C
IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)
IOSDSQL 664-964 ENTER SQLExecDirect
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
IOSDSQL 664-964 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near '.'. (170)You may have better success posting this in one of the numerous
microsoft.public.access newsgroups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BrianMultilLanguage" <brian@.clilang.com> wrote in message
news:12k1s5d2cdmo10@.corp.supernews.com...
> Onl;y one user has the problem.
> I have given them a new copy of the switchboard but same startup error.
> IOSDSQL 664-964 ENTER SQLSetStmtOption
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C
> IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
> (SQL_SUCCESS)
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C (BADMEM)
> IOSDSQL 664-964 ENTER SQLExecDirect
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> IOSDSQL 664-964 EXIT SQLExecDirect with return code -1
> (SQL_ERROR)
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near '.'. (170)
>

acc97 switchboard error. 3146 Can someone look and tell a rookie what is wrong?

Onl;y one user has the problem.
I have given them a new copy of the switchboard but same startup error.
IOSDSQL 664-964 ENTER SQLSetStmtOption
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C
IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
(SQL_SUCCESS)
HSTMT 0A0B1EC8
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)
IOSDSQL 664-964 ENTER SQLExecDirect
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
IOSDSQL 664-964 EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0A0B1EC8
UCHAR * 0x09FB18C0 [ -3] "SELECT
"dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
"dbo"."New_OnCall" ORDER BY "Lang" \ 0"
SDWORD -3
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Line 1:
Incorrect syntax near '.'. (170)You may have better success posting this in one of the numerous
microsoft.public.access newsgroups.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BrianMultilLanguage" <brian@.clilang.com> wrote in message
news:12k1s5d2cdmo10@.corp.supernews.com...
> Onl;y one user has the problem.
> I have given them a new copy of the switchboard but same startup error.
> IOSDSQL 664-964 ENTER SQLSetStmtOption
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C
> IOSDSQL 664-964 EXIT SQLSetStmtOption with return code 0
> (SQL_SUCCESS)
> HSTMT 0A0B1EC8
> UWORD 0 <SQL_QUERY_TIMEOUT>
> SQLPOINTER 0x0000003C (BADMEM)
> IOSDSQL 664-964 ENTER SQLExecDirect
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> IOSDSQL 664-964 EXIT SQLExecDirect with return code -1
> (SQL_ERROR)
> HSTMT 0A0B1EC8
> UCHAR * 0x09FB18C0 [ -3] "SELECT
> "dbo"."New_OnCall"."Lang","dbo"."New_OnCall"."Int:No" FROM
> "dbo"."New_OnCall" ORDER BY "Lang" \ 0"
> SDWORD -3
> DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Line 1:
> Incorrect syntax near '.'. (170)
>

Tuesday, March 6, 2012

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

I want my trigger to copy the newly added records from our ERP table...The ERP table contains all records for materials receiving and returning, sales orders, and purchases....I want my trigger to only copy those newly added records for materials receiving and returning...After studying the table, I managed to get the keys on how to get only the records for materials receiving and returning...So my trigger goes like this

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

SELECT @.ID = ID,
@.DATUM = DATUM,
@.SUPCODE = CRDNR,
@.ITEMCODE = ARTCODE,
@.QTY = AANTAL,
@.PONUM = BKSTNR_SUB,
@.UNIT = UNITCODE,
@.JENTRY = REKNR,
@.LINK = LINKEDLINE,
@.TYPE = OORSPRONG,
@.SUBTYPE = TRANSSUBTYPE
FROM GBKMUT

IF (@.PONUM IS NOT NULL)
BEGIN
IF (@.JENTRY <> 2140) the
BEGIN
IF (((@.TYPE = 'R') AND (@.SUBTYPE <> 'J') AND (@.LINK IS NOT NULL)) OR ((@.TYPE = 'U') AND (@.SUBTYPE = 'J') AND (@.LINK IS NULL)))
BEGIN
INSERT INTO I_DUMMY VALUES(@.ID, @.DATUM, @.SUPCODE, @.ITEMCODE, @.QTY, @.PONUM, @.UNIT, @.JENTRY, @.LINK, @.TYPE, @.SUBTYPE,'I')
END
END
END

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In a TRIGGER, there are two virtual tables, one named inserted, and one named deleted. The inserted table has new rows for inserts, and new values for updates. The deleted table has rows that were deleted, and old valued for updates.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

IF @.@.ROWCOUNT = 0
RETURN
INSERT INTO I_DUMMY
SELECT
ID,
DATUM,
CRDNR,
ARTCODE,
AANTAL,
BKSTNR_SUB,
UNITCODE,
REKNR,
LINKEDLINE,
OORSPRONG,
TRANSSUBTYPE,
'I')
FROM inserted i
WHERE ( BKSTNR_SUB IS NOT NULL
AND REKNR <> 2140
AND ( ( OORSPRONG = 'R'
AND TRANSSUBTYPE <> 'J'
AND LINKEDLINE IS NOT NULL
)
OR ( OORSPRONG = 'U'
AND TRANSSUBTYPE = 'J'
AND LINKEDLINE IS NULL
)
)
)

|||

Oh i see..So you mean I have to copy the newly added records from the virtual table inserted into my destination table I_Dummy....Thanks for that info...I really thought the trigger would automatically know what are the newly added fileds on my main table...Well on what I have understood from you, it will be stored on a virtual table...

By the way, a follow-up question regarding the virtual table...For example, my ERP table got a newly added record...Tha record will then be stored to the virtual table INSERTED...then my trigger would copy that record into the table I_Dummy....What if a new record will be added again? Will my trigger still copy the record it had already added plus the new record added again?

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

If another user inserts a row into the table at almost the same exact moment, the TRIGGER will fire (for that user) and the inserted table will be created and available to the TRIGGER, containing ONLY the rows inserted by this user.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

To confirm what I have understood, suppose i have only one user, she inserted a new record...the INSERTED table will be created and would contain the newly inserted record (newrecord1)...based from my trigger's code, record1 will be copied to I_Dummy table...

If my user(the same user) would insert another record, the INSERTED table for that user would now contain record1 and record2...But how about my I_Dummy table? Will it still copy record1? or just record2?

|||

You have almost got the idea.

Each time the TRIGGER fires (On each separate INSERT) a new inserted table is created, containing ONLY the rows from that specific INSERT. It does not contain previous rows from previous inserts by the same user. It does not contain rows from inserts from other users. It does not contain rows from a previous INSERT at all.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

About Triggers

Hi..I'm still new to topics about triggers..

The scenario is like this:

CREATE TRIGGER INSERT_TO_DUMMY ON [dbo].[gbkmut]
FOR INSERT
AS

DECLARE @.ID INT,
@.DATUM DATETIME,
@.SUPCODE CHAR(12),
@.ITEMCODE CHAR(30),
@.QTY FLOAT(8),
@.PONUM CHAR(20),
@.UNIT CHAR(8),
@.JENTRY CHAR(9),
@.LINK UNIQUEIDENTIFIER,
@.TYPE CHAR,
@.SUBTYPE CHAR

My problem is that the I_DUMMY table does not copy the newly added record...It copies a single record and stores it about 38 times....What would seem to be the problem in my code?

I have created this code by doing right-click on the GBKMUT table then click in to Manage Triggers in the SQL Server Enterprise Manager.

Hope anyone could help me on this...If you have further questions regarding my inquiry, feel free to say so...

Thanks a lot...

A common mistake for those new to working with TRIGGERs is trying (or assuming) that the data must be handled one row at at time.

In your situation, it seems that you just want to add all rows from the inserted table into the BbkMut table, based upon certain criteria.

I also suggest a variation to your naming convention. If you name Triggers and Stored Procedures with the primary affected table as the first part of the name, they will be easier to find.

(This is untested, but should point you in the right direction.)

CREATE TRIGGER GbkMut_I_To_Dummy
ON dbo.GbkMut
FOR INSERT
AS

|||

here's to illustrate my question

SCENARIO 1

MAIN TABLE

rec1

rec2

rec3

newrec1

INSERTED

newrec1

I_DUMMY

newrec1 -copied through trigger

SCENARIO2

MAIN TABLE

rec1

rec2

rec3

newrec1

newrec2

INSERTED

newrec1

newrec2 -will it be like this or newrec1 will automatically be deleted?

I_DUMMY

newrec1 -copied through trigger

newrec1 -will it be like this or only newrec2 will be copied?

newrec2

|||

When any process inserts a row into the table, the TRIGGER will 'fire'. At that moment, the inserted table is created and available to the TRIGGER and contains ONLY the newly inserted rows.

Each user has a separate inserted table that contains ONLY THEIR inserts.

|||

You have almost got the idea.

It ONLY contains the rows that were inserted and caused the TRIGGER to fire.

|||

so now I get it...thanks for your help...i'm going to try the code you have given me, i'll just edit some stuffs in there, then i'll write feedback in here after testing it...thanks a lot again

|||hi arnie...still the code does not copy the right record...i'll still try to work on my triggers...thanks for all of the information you provided....

about transaction replication initialization

Hi Guys,
In transaction replication, before seting up the replication, do we need to
keep same copy for Publisher and Subscriber. That is mean do we need to
create all objects in Subscriber database which need to replicate from
Publisher in advance or I don't need to create and init snap shot will create
those objects in Subscriber. for example, if I want to replicate a table, do
I neet to create this table in Subscriber ahead? Or init snap shot agent
will do that. Thanks.
By default, there is no requirement to create anything on the subscriber. It
is sometimes beneficial to do a nosync initialization, in which case what
you are saying is pertinent, but this is usually because the snapshot files
are so large and/or the data already exists onthe subscriber so we take
advantage of it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. But I have other question is: I have test replication
environment, where it was worked fine. But after I delete one table in
subscriber, it causes error that the table in subscriber is not existed. why
it was not created again automately? Thanks.
"Paul Ibison" wrote:

> By default, there is no requirement to create anything on the subscriber. It
> is sometimes beneficial to do a nosync initialization, in which case what
> you are saying is pertinent, but this is usually because the snapshot files
> are so large and/or the data already exists onthe subscriber so we take
> advantage of it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||It isn't really designed for you to be editing the subscriber's schema in
this way. To reinitialize this table, drop the subscription to the table
then drop the article. Add the article then add the subscription, start the
snapshot agent and it'll be sent over.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Iter,
Schemas are not maintained but rather are modified when the schema at the
publisher changes.
It is assumed schema changes will not be modified at the subscriber.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Thank you very much. But I have other question is: I have test
> replication environment, where it was worked fine. But after I delete
> one table in subscriber, it causes error that the table in subscriber
> is not existed. why it was not created again automately? Thanks.
> "Paul Ibison" wrote:
|||Thank you guys. If I want to modify the schemas in the publisher, e.g. add
new columns, how can I make changes in subscriber to make them identical?
Thanks
"Simon Sabin" wrote:

> Hello Iter,
> Schemas are not maintained but rather are modified when the schema at the
> publisher changes.
> It is assumed schema changes will not be modified at the subscriber.
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>
|||It largely depends on what version of SQL Server you are currently using.
There is also a difference between altering a table's existing column and
adding or dropping columns. For the latter on SQL Server 2000 please take a
look at sp_repladdcolumn/sp_repldropcolumn. For SQL Server 2005 or changing
existing columns please take a look at these articles:
http://www.replicationanswers.com/AddColumn.asp
http://www.replicationanswers.com/AlterSchema2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Thursday, February 16, 2012

about partition copy

hello,

I want to copy all or part data in one partition to another partition,how can I do?

do you mean a partitioned table partition? If so search out Kimberley Tripp's whitepaper on partitioning and read the sliding window scenario part. It's very easy at meta data level to move from one table to another.

|||

thank you for your answer, there have some difficulties,The database have hundred of partitioned tables,and have many relations,My purpose is to backup,export,import,copy some partition. How to do this efficienly,transationly?

|||

Your question is very general. Let me try to answer them here

(1) backup: lowest granularity is filegroup. There is no backup functionality at partition level. You can ofcourse create one dedicated filegroup for each partition to acchive it but it may not what you want to do

(2) You can always export using BCP all the rows in a specific partition.

(3) I believe BCP in also works on paritioned table.

|||thank you for your answer, (2)(3) will help me.

about partition copy

hello,

I want to copy all or part data in one partition to another partition,how can I do?

|||

Your question is very general. Let me try to answer them here

(2) You can always export using BCP all the rows in a specific partition.

(3) I believe BCP in also works on paritioned table.

|||thank you for your answer, (2)(3) will help me.

Saturday, February 11, 2012

About DTS Import Wizard

I use the DTS Import Wizard to copy one database to another. I notice that
it does the importing of tables and data alphabetically.
This causes some problems for me because I need some tables to be copied in
certain order due to the relationships and constraints.
Is there a way for me to use DTS Import but in the table order that I
define?
Thank you.Bob,
If the Copy SQL Server Objects task doesn't tranfer the tables in the order
required then try:
1. Create a DTS package and transfer the tables in the order required -
using presidence.
2. ALTER TABLE disable or drop the constraints prior to transfer then
recreate once the transfer is complete. The constraints can be scripted via
EM. Note: This approach requires that none of the data be modified on the
source or destination until the constraints have been reenabled or recreated
or the data may get out-of-synch and the creation of the constraints may
fail.
HTH
Jerry
"Bob" <spamfree@.nospam.com> wrote in message
news:uE%23Lkc50FHA.2076@.TK2MSFTNGP14.phx.gbl...
>I use the DTS Import Wizard to copy one database to another. I notice that
> it does the importing of tables and data alphabetically.
> This causes some problems for me because I need some tables to be copied
> in
> certain order due to the relationships and constraints.
> Is there a way for me to use DTS Import but in the table order that I
> define?
> Thank you.
>|||Thanks Jerry.
I think the DTS package is what I need. Can you go through how I go about
creating a DTS package?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyBIbx50FHA.1040@.TK2MSFTNGP14.phx.gbl...
> Bob,
> If the Copy SQL Server Objects task doesn't tranfer the tables in the
order
> required then try:
> 1. Create a DTS package and transfer the tables in the order required -
> using presidence.
> 2. ALTER TABLE disable or drop the constraints prior to transfer then
> recreate once the transfer is complete. The constraints can be scripted
via
> EM. Note: This approach requires that none of the data be modified on
the
> source or destination until the constraints have been reenabled or
recreated
> or the data may get out-of-synch and the creation of the constraints may
> fail.
> HTH
> Jerry
> "Bob" <spamfree@.nospam.com> wrote in message
> news:uE%23Lkc50FHA.2076@.TK2MSFTNGP14.phx.gbl...
that
>

Thursday, February 9, 2012

about copy a new stored procedure to all databases

Dear all
i have been updating a new stored procedure to all database if it has
that procedure.
i have write something like to test with one database only but it
still fail.
it said i havent declare CID salary.
i cant figure out why and how. hope anyone have experience can help me
out.
Maybe can i update a procedure for a database and then copy them all
to other database.
thanks i really hope someone can help out for this
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'cal_sal')
Begin
ALTER PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
Begin
xxxxxxxxxxxxxxxxxxxxxxx
/* it is the long statement*/
xxxxxxxxxxxxxxxxxxxxxxx
END
ENDCREATE/ALTER PROCEDURE must be the only statement in the batch so
CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
followed by unconditional CREATE:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
BEGIN
DROP PROCEDURE [dbo].[cal_sal]
END
GO
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
BEGIN
/* it is the long statement*/
END
GO
GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>|||Thanks Dan
you have really help me out
but while i would like to execute the statement, it said Create/Alter
procedure must be the first statement
why was this happen?
so my testing statement is like below:
use test
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END
and finally i would like to use, is this possible ?
exec sp_MSforeachdb
'use [?]
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'
On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> CREATE/ALTER PROCEDURE must be the only statement in the batch so
> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> followed by unconditional CREATE:
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> BEGIN
> DROP PROCEDURE [dbo].[cal_sal]
> END
> GO
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> BEGIN
> /* it is the long statement*/
> END
> GO
> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <KelvinWon...@.gmail.com> wrote in message
> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>
> > Dear all
> > i have been updating a new stored procedure to all database if it has
> > that procedure.
> > i have write something like to test with one database only but it
> > still fail.
> > it said i havent declare CID salary.
> > i cant figure out why and how. hope anyone have experience can help me
> > out.
> > Maybe can i update a procedure for a database and then copy them all
> > to other database.
> > thanks i really hope someone can help out for this
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > ALTER PROCEDURE [dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > Begin
> > xxxxxxxxxxxxxxxxxxxxxxx
> > /* it is the long statement*/
> > xxxxxxxxxxxxxxxxxxxxxxx
> > END
> > END- -
> - -|||thanks so much
i also got that problem
On 8 1 , 10 47 , NEMA <realja...@.gmail.com> wrote:
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alterproceduremust be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
> > CREATE/ALTERPROCEDUREmust be the only statement in the batch so
> > CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> > followed by unconditional CREATE:
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > BEGIN
> > DROPPROCEDURE[dbo].[cal_sal]
> > END
> > GO
> > CREATE PROCEDURE[dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > BEGIN
> > /* it is the long statement*/
> > END
> > GO
> > GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
> > GO
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > <KelvinWon...@.gmail.com> wrote in message
> >news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> > > Dear all
> > > i have been updating a newstoredprocedureto all database if it has
> > > thatprocedure.
> > > i have write something like to test with one database only but it
> > > still fail.
> > > it said i havent declare CID salary.
> > > i cant figure out why and how. hope anyone have experience can help me
> > > out.
> > > Maybe can i update aprocedurefor a database and thencopythem all
> > > to other database.
> > > thanks i really hope someone can help out for this
> > > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > > 'cal_sal')
> > > Begin
> > > ALTER PROCEDURE[dbo].[cal_sal]
> > > (@.CID [varchar[50]],
> > > @.Salary [float] output)
> > > AS
> > > IF @.CID = 1234
> > > Begin
> > > xxxxxxxxxxxxxxxxxxxxxxx
> > > /* it is the long statement*/
> > > xxxxxxxxxxxxxxxxxxxxxxx
> > > END
> > > END- -
> > - -- -
> - -|||put a GO before Create can fix the problem
but it still cannot use sp_MSforeachdb as it said there are syntax
error on create and end|||In article <1185968371.785331.165490@.x40g2000prg.googlegroups.com>,
KelvinWongYW@.gmail.com says...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
add it to model so included in all new databases created from then
forward
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||thanks Pete
but how about the exisiting databases?
On 8 2 , 1 57 , Pete Berry <PeteBe...@.Caltech.edu> wrote:
> In article <1185968371.785331.165...@.x40g2000prg.googlegroups.com>,
> KelvinWon...@.gmail.com says...
>
> > Dear all
> > i have been updating a newstoredprocedureto all database if it has
> > thatprocedure.
> > i have write something like to test with one database only but it
> > still fail.
> > it said i havent declare CID salary.
> > i cant figure out why and how. hope anyone have experience can help me
> > out.
> > Maybe can i update aprocedurefor a database and thencopythem all
> > to other database.
> > thanks i really hope someone can help out for this
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > ALTER PROCEDURE[dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > Begin
> > xxxxxxxxxxxxxxxxxxxxxxx
> > /* it is the long statement*/
> > xxxxxxxxxxxxxxxxxxxxxxx
> > END
> > END
> add it to model so included in all new databases created from then
> forward
> --
> Graham (Pete) Berry
> PeteBe...@.Caltech.edu- -
> - -|||> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
You need a GO batch separator before the CREATE. SQL Server tools will send
the preceding batch of statements whenever a GO is encountered.
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
You'll need to wrap the batches individually with EXECUTE. Assuming you
want to skip system databases, try something like:
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
DROP PROC [dbo].[cal_sal]'')
EXEC(''CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'')
END
'
This technique is a bit of a kludge, though. IMHO, it's better to create a
script file and run for each database using OSQL or SQLCMD.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
>
>
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> CREATE/ALTER PROCEDURE must be the only statement in the batch so
>> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
>> followed by unconditional CREATE:
>> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> 'cal_sal')
>> BEGIN
>> DROP PROCEDURE [dbo].[cal_sal]
>> END
>> GO
>> CREATE PROCEDURE [dbo].[cal_sal]
>> (@.CID [varchar[50]],
>> @.Salary [float] output)
>> AS
>> IF @.CID = 1234
>> BEGIN
>> /* it is the long statement*/
>> END
>> GO
>> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <KelvinWon...@.gmail.com> wrote in message
>> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>>
>> > Dear all
>> > i have been updating a new stored procedure to all database if it has
>> > that procedure.
>> > i have write something like to test with one database only but it
>> > still fail.
>> > it said i havent declare CID salary.
>> > i cant figure out why and how. hope anyone have experience can help me
>> > out.
>> > Maybe can i update a procedure for a database and then copy them all
>> > to other database.
>> > thanks i really hope someone can help out for this
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > ALTER PROCEDURE [dbo].[cal_sal]
>> > (@.CID [varchar[50]],
>> > @.Salary [float] output)
>> > AS
>> > IF @.CID = 1234
>> > Begin
>> > xxxxxxxxxxxxxxxxxxxxxxx
>> > /* it is the long statement*/
>> > xxxxxxxxxxxxxxxxxxxxxxx
>> > END
>> > END- -
>> - -
>|||Thanks
i am thinking of is there a way to loop through each database and then
Alter the store procedure by using OSQL or SQLCMD
however i cant find a command to loop and some database may not have
that stored procedure
On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust be the first statement
> > why was this happen?
> You need a GO batch separator before the CREATE. SQL Server tools will send
> the preceding batch of statements whenever a GO is encountered.
> > and finally i would like to use, is this possible ?
> > exec sp_MSforeachdb
> > 'use [?]
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> You'll need to wrap the batches individually with EXECUTE. Assuming you
> want to skip system databases, try something like:
> EXEC sp_MSForEachDB '
> USE ?
> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
> BEGIN
> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
> DROP PROC [dbo].[cal_sal]'')
> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'')
> END
> '
> This technique is a bit of a kludge, though. IMHO, it's better to create a
> script file and run for each database using OSQL or SQLCMD.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "NEMA" <realja...@.gmail.com> wrote in message
> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>
> > Thanks Dan
> > you have really help me out
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust be the first statement
> > why was this happen?
> > so my testing statement is like below:
> > use test
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> > (@.CID int,
> > @.Salary float output)
> > AS
> > IF 2=2
> > BEGIN
> > select * from customer
> > END
> > and finally i would like to use, is this possible ?
> > exec sp_MSforeachdb
> > 'use [?]
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> > (@.CID int,
> > @.Salary float output)
> > AS
> > IF 2=2
> > BEGIN
> > select * from customer
> > END'
> > On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> > wrote:
> >> CREATE/ALTERPROCEDUREmust be the only statement in the batch so
> >> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> >> followed by unconditional CREATE:
> >> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => >> 'cal_sal')
> >> BEGIN
> >> DROPPROCEDURE[dbo].[cal_sal]
> >> END
> >> GO
> >> CREATE PROCEDURE[dbo].[cal_sal]
> >> (@.CID [varchar[50]],
> >> @.Salary [float] output)
> >> AS
> >> IF @.CID = 1234
> >> BEGIN
> >> /* it is the long statement*/
> >> END
> >> GO
> >> GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
> >> GO
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> <KelvinWon...@.gmail.com> wrote in message
> >>news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> >> > Dear all
> >> > i have been updating anewstoredprocedureto all database if it has
> >> > thatprocedure.
> >> > i have write something like to test with one database only but it
> >> > still fail.
> >> > it said i havent declare CID salary.
> >> > i cant figure out why and how. hope anyone have experience can help me
> >> > out.
> >> > Maybe can i update aprocedurefor a database and thencopythem all
> >> > to other database.
> >> > thanks i really hope someone can help out for this
> >> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => >> > 'cal_sal')
> >> > Begin
> >> > ALTER PROCEDURE[dbo].[cal_sal]
> >> > (@.CID [varchar[50]],
> >> > @.Salary [float] output)
> >> > AS
> >> > IF @.CID = 1234
> >> > Begin
> >> > xxxxxxxxxxxxxxxxxxxxxxx
> >> > /* it is the long statement*/
> >> > xxxxxxxxxxxxxxxxxxxxxxx
> >> > END
> >> > END- -
> >> - -- -
> - -|||Are you saying that you don't know which databases need the stored
procedure?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1186030181.537666.23780@.x40g2000prg.googlegroups.com...
> Thanks
> i am thinking of is there a way to loop through each database and then
> Alter the store procedure by using OSQL or SQLCMD
> however i cant find a command to loop and some database may not have
> that stored procedure
>
> On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust be the first statement
>> > why was this happen?
>> You need a GO batch separator before the CREATE. SQL Server tools will
>> send
>> the preceding batch of statements whenever a GO is encountered.
>> > and finally i would like to use, is this possible ?
>> > exec sp_MSforeachdb
>> > 'use [?]
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> You'll need to wrap the batches individually with EXECUTE. Assuming you
>> want to skip system databases, try something like:
>> EXEC sp_MSForEachDB '
>> USE ?
>> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
>> BEGIN
>> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
>> DROP PROC [dbo].[cal_sal]'')
>> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
>> (@.CID int,
>> @.Salary float output)
>> AS
>> IF 2=2
>> BEGIN
>> select * from customer
>> END'')
>> END
>> '
>> This technique is a bit of a kludge, though. IMHO, it's better to create
>> a
>> script file and run for each database using OSQL or SQLCMD.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "NEMA" <realja...@.gmail.com> wrote in message
>> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>>
>> > Thanks Dan
>> > you have really help me out
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust be the first statement
>> > why was this happen?
>> > so my testing statement is like below:
>> > use test
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> > (@.CID int,
>> > @.Salary float output)
>> > AS
>> > IF 2=2
>> > BEGIN
>> > select * from customer
>> > END
>> > and finally i would like to use, is this possible ?
>> > exec sp_MSforeachdb
>> > 'use [?]
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> > (@.CID int,
>> > @.Salary float output)
>> > AS
>> > IF 2=2
>> > BEGIN
>> > select * from customer
>> > END'
>> > On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
>> > wrote:
>> >> CREATE/ALTERPROCEDUREmust be the only statement in the batch so
>> >> CREATE/ALTER cannot be conditional. Instead, consider a conditional
>> >> drop
>> >> followed by unconditional CREATE:
>> >> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> >> 'cal_sal')
>> >> BEGIN
>> >> DROPPROCEDURE[dbo].[cal_sal]
>> >> END
>> >> GO
>> >> CREATE PROCEDURE[dbo].[cal_sal]
>> >> (@.CID [varchar[50]],
>> >> @.Salary [float] output)
>> >> AS
>> >> IF @.CID = 1234
>> >> BEGIN
>> >> /* it is the long statement*/
>> >> END
>> >> GO
>> >> GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
>> >> GO
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> <KelvinWon...@.gmail.com> wrote in message
>> >>news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>> >> > Dear all
>> >> > i have been updating anewstoredprocedureto all database if it has
>> >> > thatprocedure.
>> >> > i have write something like to test with one database only but it
>> >> > still fail.
>> >> > it said i havent declare CID salary.
>> >> > i cant figure out why and how. hope anyone have experience can help
>> >> > me
>> >> > out.
>> >> > Maybe can i update aprocedurefor a database and thencopythem all
>> >> > to other database.
>> >> > thanks i really hope someone can help out for this
>> >> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> >> > 'cal_sal')
>> >> > Begin
>> >> > ALTER PROCEDURE[dbo].[cal_sal]
>> >> > (@.CID [varchar[50]],
>> >> > @.Salary [float] output)
>> >> > AS
>> >> > IF @.CID = 1234
>> >> > Begin
>> >> > xxxxxxxxxxxxxxxxxxxxxxx
>> >> > /* it is the long statement*/
>> >> > xxxxxxxxxxxxxxxxxxxxxxx
>> >> > END
>> >> > END- -
>> >> - -- -
>> - -
>

Monday, March 19, 2012

Access - SQL 2005

Sunday, March 11, 2012

Accesing a database with password

acc97 switchboard error. 3146 Can someone look and tell a rookie what is wrong?

acc97 switchboard error. 3146 Can someone look and tell a rookie what is wrong?

Tuesday, March 6, 2012

About Triggers

About Triggers

about transaction replication initialization

Thursday, February 16, 2012

about partition copy

about partition copy

Saturday, February 11, 2012

About DTS Import Wizard

Thursday, February 9, 2012

about copy a new stored procedure to all databases

about copy a new stored procedure to all databases

about copy a new stored procedure to all databases

SQL About AMO

Blog Archive

About Me