properties on stored procedure

Hey

I have a problem viewing properties on my stored procudures, when i right click there is no properties button like in the other versions, is that a bug ?

hi,

if you mean "extended properties" of a specified object, you can access them right clicking the relative object in SQL Server Management Studio (or SQL Server Management Studio Express) and accessing the last "tab" (Extended Properties tab.. ok, it' no longer a tab, but hope you understand what I mean )

regards

Properly test for existence of a temporary stored procedure

How do I test for the existence of a temporary stored procedure (or get its
id)?
Testing for existence of a regular stored procedure is easy:
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp' and id = ... and
objectproperty( ... isprocedure) ...)
Testing for existence of a temporary table is easy:
if object_id('tempdb.dbo.#temp') <> 0
And testing for existence of a temporary stored procedure seems easy, too:
if object_id('tempdb.dbo.#sp') <> 0
Only, if a table exists in tempdb that has the name #sp, the object ID
returned is that of the table, not the sp.
This *probably* won't cause me a problem, but I don't like writing code that
relies on probablies. How can I do a simple test for existence of a temporar
y
stored procedure so I can drop it before recreating it?
Thanks,
Erik
Elbisrever isn'''t.
"Erik Eckhardt" wrote:

> How do I test for the existence of a temporary stored procedure (or get it
s
> id)?
> Testing for existence of a regular stored procedure is easy:
> IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp' and id = ... and
> objectproperty( ... isprocedure) ...)
> Testing for existence of a temporary table is easy:
> if object_id('tempdb.dbo.#temp') <> 0
> And testing for existence of a temporary stored procedure seems easy, too:
> if object_id('tempdb.dbo.#sp') <> 0
> Only, if a table exists in tempdb that has the name #sp, the object ID
> returned is that of the table, not the sp.
> This *probably* won't cause me a problem, but I don't like writing code th
at
> relies on probablies. How can I do a simple test for existence of a tempor
ary
> stored procedure so I can drop it before recreating it?
> Thanks,
> Erik
> --
> Elbisrever isn'''t.
Erik,
Can you use something like this:
IF EXISTS (
SELECT 1 FROM tempdb.dbo.sysobjects
WHERE name like '#sp____%' -- All temp objects start with the
name followed
-- by a bunch of underscores and a
unique number.
AND xtype = 'P' -- Stored procedure
)
PRINT 'Stored Proc #sp exists'
ELSE
PRINT 'Stored Proc #sp DOES NOT exist'|||"LA_DBA" wrote:

>
> "Erik Eckhardt" wrote:
>
> Erik,
> Can you use something like this:
> IF EXISTS (
> SELECT 1 FROM tempdb.dbo.sysobjects
> WHERE name like '#sp____%' -- All temp objects start with the
> name followed
> -- by a bunch of underscores and a
> unique number.
> AND xtype = 'P' -- Stored procedure
> )
> PRINT 'Stored Proc #sp exists'
> ELSE
> PRINT 'Stored Proc #sp DOES NOT exist'
>
Dear LA_DBA,
That won't work because it will only tell me if someone on the server
somewhere in some database has a stored procedure by that name existent. It
doesn't tell me for my current connection/session.|||IF OBJECT_ID('tempdb..#tbl') IS NOT NULL ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Erik Eckhardt" <egamlaprov@.liamtoh.moc(reverse each section)> wrote in mess
age
news:EFB75273-3DA1-4C04-B6D5-7F3EB9942885@.microsoft.com...
> "LA_DBA" wrote:
>
> Dear LA_DBA,
> That won't work because it will only tell me if someone on the server
> somewhere in some database has a stored procedure by that name existent. I
t
> doesn't tell me for my current connection/session.|||You can supply a second parameter to object_id, 'P'
for stored procedure, 'U' for table etc.
if object_id('tempdb.dbo.#sp') <> 0
becomes
if object_id('tempdb.dbo.#sp','P') <> 0|||Mark,
Thanks for the info on this undocumented parameter (at least in SQL Server
2000 BOL). The interesting thing is, when a temp table exists by the same
name, object_id('tempdb.dbo.#blah', 'P') returns a NULL. So rather than 'P'
specifying an object of type P, it simply requests that whatever object it
finds first... if it's NOT type 'P', return NULL instead.
I guess the only solution is just to attempt the drop and trap for and
discard any error about nonexistence.
How did you learn about this parameter? What could I do to expose the code
behind the object_id function?
Elbisrever isn'''t.
"markc600@.hotmail.com" wrote:

> You can supply a second parameter to object_id, 'P'
> for stored procedure, 'U' for table etc.
> if object_id('tempdb.dbo.#sp') <> 0
> becomes
> if object_id('tempdb.dbo.#sp','P') <> 0
>|||Tibor,
Thanks for the reply, but you evidently failed to read my post carefully.
Erik
Elbisrever isn'''t.
"Tibor Karaszi" wrote:

> IF OBJECT_ID('tempdb..#tbl') IS NOT NULL ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Erik Eckhardt" <egamlaprov@.liamtoh.moc(reverse each section)> wrote in me
ssage
> news:EFB75273-3DA1-4C04-B6D5-7F3EB9942885@.microsoft.com...
>|||Oh... and you cannot drop the stored procedure until the temp table is
dropped. You must drop them in the order you created them (at least in my
testing).
Oh well, I'm probably worrying over nothing... who's going to be created a
temp table that exactly matches the name of my temp sp? No one...
Elbisrever isn'''t.
"markc600@.hotmail.com" wrote:

> You can supply a second parameter to object_id, 'P'
> for stored procedure, 'U' for table etc.
> if object_id('tempdb.dbo.#sp') <> 0
> becomes
> if object_id('tempdb.dbo.#sp','P') <> 0
>|||> How did you learn about this parameter?
Honestly can't remember, possibly spotted it in a system
stored procedure some time ago.sql

proper way to drop publication?

Hi There

According to BOL, the correct procedure for deleting a publication are as follows:
These must be run on the publication database.

Execute sp_dropsubscription to delete all snapshot subscriptions.

Execute sp_droppublication to delete the publication and all of its articles.

My only issue is after step 1 the subscription still exists at the subscriber and it marked as failed (does not exist), i always have to go delete the subscription afterwards at the subscriber. AM i missing something? WHy does sp_dropsubscription not do this? Or is there another sp i should be running first?

Thanx

Hi Dietz,

The actual step to cleanup subscription depending on the subscription is push or pull. Basically, for push, you need to call sp_dropsubscription at publisher followed by sp_subscription_cleanup at subscriber. For Pull, you need to call sp_droppullsubscription at subscriber followed by sp_dropsubscription at pubisher.

Please refer to BOL topic "How to: Delete a Pull Subscription (Replication Transact-SQL Programming)" and "How to: Delete a Push Subscription (Replication Transact-SQL Programming)".

Thanks,|||Great thanx Peng, i wonder why they do not mention any of those sp's under the How to Delete Publications and Articles (Transact-SQL) BOL topic.
Also i cannot find

"How to: Delete a Pull Subscription (Replication Transact-SQL Programming)" and "How to: Delete a Push Subscription (Replication Transact-SQL Programming)".in BOL only TSQL references not Replication Transact sql programming.

I am using SS2000 not SS2005?

Thanx again|||Sorry, dietz. I made a bad assumption to think your system is SQL 2005. My previous post really means for SQL 2005.

In SQL 2000 BOL, there is similar topic on how to drop subscription, depending on pull/push of the subscription. You probably already see it in "How To"->"Transact SQL" section.

Peng
sql

Proper use of IF statement in stored procedure?

I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:

Cannot use empty object or column names. Use a single space if necessary.

So, what's the correct way of doing the following?

IF @.filename <> ""
BEGIN
UPDATE Experimental_Images SET contentType = @.contentType, filename = @.filename WHERE (id = @.iconNo)
ENDThe problem you are having is that you are using double quotes when youshould be using single quites. The rest of your statements are fine.

What do you mean exactly by "if the parameter is empty". If youmean if it contains an empty string or a NULL, try it like this:

IF ISNULL(@.filename,'') <> ''

If you mean it contains a NULL, try this:

IF @.filename IS NOT NULL

And if you mean it contains an empty string, try it like this:

IF @.filename <> ''

Proper syntax for bulk insert?

Hi,

I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.

How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?

Here is what I'm doing so far:

CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO

Thanks for any help,SELECT * INTO myDB..Table FROM oneDB..Table|||Thanks Brett,

Now it's giving me an error that says 'object Individual is already in the database.'

Have I placed the select * in the right place?

CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)

as

select * into individual from FROMGDPersonnelByDivision

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO

Thanks,|||insert into db1..Individual (fields)
select fields
from db2..Individual

This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?|||Itmay take forever though. If 'select into' is enabled on db1 I'd use Brett's approach by dropping Individual from db1 and using select * into... from ...|||insert into db1..Individual (fields)
select fields
from db2..Individual

This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?

fields...oye...|||OK, OK. I am being lazy. I admit it. ;-)

Still, it is slightly better than

insert into table
select *
from other_table|||Thanks guys, I DO need all the help I can get...

MCrowley, I am creating an appliction for two users. Once a week, or whenever, I want the user to be able to import people and info from the Personnel db into the Individuals table. The field names are different.

At this point, I don't mind slow, I just want to see it work. So now I've got:

CREATE Procedure Insert_From_Personnel

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
select emp_num,frst_name,last_name,[division id]
from FROMGDPersonnelByDivision

GO

I wiped out everything to start from scratch. I ran the SP and got the 'string or binary data would be truncated' error. Ran it again and got the 'violation of PK_Individual' error. There isn't any data in the table yet, how can it be violated?

Just to see if I've got it right, I took the PK off and it worked! It was pretty speedy, too. I'll try to write something on the vb.net side to Update if it sees the same emp_num and Insert if that emp_num doesn't exist.

One last thing... how can I say if it DOESN'T exist INSERT in the SP?

Thanks for your help!|||if not exists (select...)|||I'm getting closer, I hope. I've tried the if exists all over the place and weeded it down to only one 'incorrect syntax' near from FROMGDPersonnelByDivision

What the heck am I missing?!

CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name,last_name,[division id])

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

from FROMGDPersonnelByDivision
GO

Can I have one more hint, please?|||Yeah, I agree, the combination is finite and you're close ;)

CREATE Procedure Insert_From_Personnel
as
if not exists (select 1 from FROMGDPersonnelByDivision where <something> = <something>) INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
else
print 'Record already exists'
return (0)
GO|||rdjabarov,

Help me out here. Why isn't this working?

When I use your example (simplified):
CREATE Procedure Insert_From_Personnel

as

if not exists (select emp_num,frst_name, last_name, [division id] from FROMGDPersonnelByDivision where emp_num = emp_num)
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

GO

It gives me the 'incorrect syntax near ')' after the last DivisionID, as if it needs something more.

After looking around on the web and in my Murach book, I tried putting values back in and using 'default values'. But then it has problems with the 'as'. As if it does not want VALUES using the 'if not exists'. What does it need? I'm not finding much info about combining 'if not' with 'insert into'.

Thanks for any help!|||The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.

-PatP|||Hmmmm... OK,

The select is there. It makes sense in english;

if this doesn't exist: (the data from these columns, from THAT table) then INSERT it into THIS table

>> (select emp_num,frst_name, last_name, [division id] from >>FROMGDPersonnelByDivision where emp_num = emp_num)

Will I have better luck using 'WHERE NOT EXISTS'? Is there a difference?

Thanks,|||How about:INSERT into individual (
IndividualID, FirstName, LastName
, DivisionID
) SELECT
emp_num, frst_name, last_name
, [division id]
FROM FROMGDPersonnelByDivision
WHERE NOT EXSISTS (SELECT *
FROM individual AS b
WHERE b.InidividualID = FROMGDPersonnelByDivision.emp_num)-PatP|||Pat! Pat! It worked!

I was just trying to manipulate the code you gave for the 'Create Trigger?' post. I knew that the a.xxx and b.xxx sounded like where I should go. I didn't know I could just use a 'b'.

Thank you SO much! I'll be using this all over the place at work, so it's very important I get this concept down.|||Well, I am glad someone has a better eye sight than me ;)

Proper Procedure to change servername of replicated environemnts?

Hi There

Firslty this is a question concerning sql server 2000 hope this is not just a 2005 forum?

I need to change the servername of a few of our sql 2000 database servers.
I know the procedure to change the name and reconfigure sql with the new servername.
What i am concerned about is that these databases are being replicated via a remote distributor.
Obviously the subscriptions stipulate the servernames , i am concerned about what will happen to replication when the server name is changed i have a feeling replication will fall over.
Does anyone have a good link or white paper on proper procedure to change server names on replicated sql server databases.
I will also need to change the server name of the remote distributor.

This whole procedure could be tricky and i am not 100% sure about the proper procedure and i cannot find anything useful in BOL.
I am hoping all i have to do is stop certain replication agents , change the subscription info and start them again but i am really not sure.

Any help would be appreciated.

Thanx

Sean,

Server rename does not support replication. You can script replication from Enterprise Manager or SQL Server Management Studio, drop it, rename the server, and then rerun the script after modifying it with search and replace to use the new name.

-Matt|||Hi Matt

I have found a BOL reference basically you do have to disable replication, so subscriptions will have to be reinitialzed afterwards.
ALso i have foubd that when you script replication the sql it produces in littered with syntactical errors.

But i have made a plan.

Thanx for the advise.

prompt user from stored procedure?

I have a stored procedure that moves specific data from several tables from a database on one server to several others (using a bunch of openrowset queries). I don't have an app to call this procedure - it's usually runs from the query analyzer and the parameter values for the procedure are passed in from there.

The procedure currently skips over recipient tables that are already populated with the data that's being moved. I want the ability to either skip the table or delete the existing records from the recpient table data based on the user's response. Therefore, I somehow need to prompt the user to get a response when the data already exists in the existing table.

Is there any way to prompt the user from a stored procedure, or do I have to re-develop the procedure in DTS or write and application?You need to write an application. SQL Server has no built in interface for interacting with the user.
Your simplest approach might be to create an Access Data Project tied to your SQL Server database. Then you could easily create forms and vb code to do what you want.|||That's what I initially thought. Thanks.

Promp for info when executing stored procedure

Hello,
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge Mendes
Try this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.codecomments.com ***

Promp for info when executing stored procedure

Hello,
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge MendesTry this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.developersdex.com ***

Promp for info when executing stored procedure

Hello,
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge MendesTry this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.codecomments.com ***

Programmertically create and execute stored procedure in SMO

Hi all,

I need to programmertically create and execute stored procedure in SMO, without registering it on the database. I also need to be able to load a file containing a stored procedure and execute it, using SMO.

Can someone show me how? A C# sample would be greatly appreciated.

Thanks in advance.

Hi,

a simple sample would be:

StoredProcedure sp = new StoredProcedure("SomeDatabase","usp_Somesp","SomeSchema");

sp.TextBody = "SELECT 'SomeData'";

Server s = new Server("SomeServer");

s.Databases["SomeDatabase"].StoredProcedures.Add(sp);

s.ConnectionContext.ExecuteNonQuery("usp_Somesp");

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

I appreciate you help, but this is the error I get:

Error 1 'Microsoft.SqlServer.Management.Smo.StoredProcedureCollection' does not contain a definition for 'Add'

|||

Good morning ( for me 9:34 )

Look at this link http://msdn2.microsoft.com/en-us/library/ms162553.aspx

The Add is automatic when you use the Create method if you use the constructor

sp = new StoredProcedure(DataBaseName,StoredProcedureName)

Excuse me for my english

Have a nice day

|||Thank you very much.

programmatically schedule the package for later execution in store procedure -

Hello,
Is it possible programmatically schedule the DTS package for later execution in store procedure or maybe in trigger?When you say later execution, please be more specific and describe the scenario you are trying to apply this to.|||Originally posted by rnealejr
When you say later execution, please be more specific and describe the scenario you are trying to apply this to.

For example, user sets flag thru user interface. This action updates some field in table. I would like to have an "update" trigger for this table that will schedule the DTS package to be executed at night. There is some option to do it in VB but it will be cool to perform this thru store procedure.|||You can use the sp_add_schedule or sp_update_schedule - however, a job must already exist for the DTS package.|||Thank you. It's msdb..sp_update_jobschedule.

Thanks again!|||Happy to help.

programmatically restore a database

I recently had to find ways to programmatically backup and restore an
SQL Server Express database.
For backup, I found a one-liner stored procedure that does the job
(BACKUP DATABASE Toy to disk = 'c:\Projects\Toy.bak'.
My question is, is there something as simple for a restore operation?

Thanks in Advance

-KostasYes, use : RESTORE DATABASE MyNwind FROM MyNwind_1 (i've taken this from
BOL) for more involved examples check BOL

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<kskartsiounis@.yahoo.com> wrote in message
news:1144224541.969405.253280@.g10g2000cwb.googlegr oups.com...
> I recently had to find ways to programmatically backup and restore an
> SQL Server Express database.
> For backup, I found a one-liner stored procedure that does the job
> (BACKUP DATABASE Toy to disk = 'c:\Projects\Toy.bak'.
> My question is, is there something as simple for a restore operation?
> Thanks in Advance
> -Kostas|||Pardon my ignorance, but what is BOL?|||BOL is the SQL Server Books Online documentation.

Note you may need to specify additional RESTORE options in some situations.
For example, database files will be restored to the original locations by
default. If you are restoring to a machine with a different drive/folder
structure, you'll need specify different file paths using the WITH MOVE
option.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<kskartsiounis@.yahoo.com> wrote in message
news:1144229322.586326.67220@.u72g2000cwu.googlegro ups.com...
> Pardon my ignorance, but what is BOL?|||We use

RESTORE DATABASE DBName FROM DISK='d:\files\Backups\DBName.bak' WITH
REPLACE

Programmatic Rendering C# - passing Oracle credentials

What am I doing wrong here? I am trying to programmatcially run a report that
uses an Oracle stored procedure where the Oracle credentials would be
prompted if I ran interactively. The program (C#) compiles OK but I get an
'Object reference not set to an instance of an object' error when assigning
the credential property. The report runs interactively and I can
programmatically pass other parameters other than oracle credentials.
DataSourceCredentials[] credentials = new DataSourceCredentials[1];
credentials[0].DataSourceName = "MyDataSource";
credentials[0].UserName = "MyUser";
credentials[0].Password = "MyPassword";
data = _rs.Render("/MyFolder/MyReport","PDF", null, null, returnValues,
credentials , null, out encoding, out mimeType, out parametersUsed, out
warnings, out streamIds);
Any help appreciated.Sorry - just spotted it , I was missing a
credentials[0] = new DataSourceCredentials();
prior to assigning properties
"Joe" wrote:
> What am I doing wrong here? I am trying to programmatcially run a report that
> uses an Oracle stored procedure where the Oracle credentials would be
> prompted if I ran interactively. The program (C#) compiles OK but I get an
> 'Object reference not set to an instance of an object' error when assigning
> the credential property. The report runs interactively and I can
> programmatically pass other parameters other than oracle credentials.
> DataSourceCredentials[] credentials = new DataSourceCredentials[1];
> credentials[0].DataSourceName = "MyDataSource";
> credentials[0].UserName = "MyUser";
> credentials[0].Password = "MyPassword";
> data = _rs.Render("/MyFolder/MyReport","PDF", null, null, returnValues,
> credentials , null, out encoding, out mimeType, out parametersUsed, out
> warnings, out streamIds);
> Any help appreciated.

Programatically View / Stored Procedure with N fields

We have a table that has 10 fields of which say 5 are either non textual. We
want to have a resultant view with 7 fields, namely 1 numeric field and the
5 non-numeric fields (textual) and one additional field for the non textual
field name. In the example below the last 5 field names are Prod1, Prod2,
Prod3, Prod4, Prod5.
E.g.
Original
SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
Result
SA, Sales, Man1, Actual, Prd1, Jan, 100
SA, Sales, Man1, Actual, Prd2, Jan, 200
SA, Sales, Man1, Actual, Prd3, Jan, 300
SA, Sales, Man1, Actual, Prd4, Jan, 400
SA, Sales, Man1, Actual, Prd5, Jan, 500
SA, Sales, Man1, Actual, Prd1, Jan, 1
SA, Sales, Man2, Actual, Prd2, Jan, 2
SA, Sales, Man3, Actual, Prd3, Jan, 3
SA, Sales, Man4, Actual, Prd4, Jan, 4
SA, Sales, Man5, Actual, Prd5, Jan, 5
In essence the size of the table will end up as
n records * 5 fields.
Thanks in advance.Avril
I'm not sure understood you.
Have you tride using UNION ALL to get what you wanted?
"Avril" <Avril@.ppp.com> wrote in message
news:effVz3oZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> We have a table that has 10 fields of which say 5 are either non textual.
> We want to have a resultant view with 7 fields, namely 1 numeric field and
> the 5 non-numeric fields (textual) and one additional field for the non
> textual field name. In the example below the last 5 field names are Prod1,
> Prod2, Prod3, Prod4, Prod5.
>
> E.g.
> Original
> SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
> SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
> Result
> SA, Sales, Man1, Actual, Prd1, Jan, 100
> SA, Sales, Man1, Actual, Prd2, Jan, 200
> SA, Sales, Man1, Actual, Prd3, Jan, 300
> SA, Sales, Man1, Actual, Prd4, Jan, 400
> SA, Sales, Man1, Actual, Prd5, Jan, 500
> SA, Sales, Man1, Actual, Prd1, Jan, 1
> SA, Sales, Man2, Actual, Prd2, Jan, 2
> SA, Sales, Man3, Actual, Prd3, Jan, 3
> SA, Sales, Man4, Actual, Prd4, Jan, 4
> SA, Sales, Man5, Actual, Prd5, Jan, 5
> In essence the size of the table will end up as
> n records * 5 fields.
> Thanks in advance.
>|||Avril,
In SQL Server 2005, you can use the UNPIVOT operator for this.
In SQL Server 2000, you can use a CROSS JOIN like this:
select
column1, column2, column3, column4,
'Prd' + Nch as Prd,
column5,
case Nch
when 1 then Prod1 when 2 then Prod2 when 3 then Prod3
when 4 then Prod4 when 5 then Prod5 end as LastColumn
from yourTable
cross join (
select '1' as Nch union all select '2' union all select '3'
union all select '4' union all select '5'
) as F
There may be some typos, since you didn't give the create table
statements and sample data as INSERTs to test with, and you
will have to fix column names for the columns whose names
you didn't provide.
Steve Kass
Drew University
Avril wrote:

>We have a table that has 10 fields of which say 5 are either non textual. W
e
>want to have a resultant view with 7 fields, namely 1 numeric field and the
>5 non-numeric fields (textual) and one additional field for the non textual
>field name. In the example below the last 5 field names are Prod1, Prod2,
>Prod3, Prod4, Prod5.
>
>E.g.
>Original
>SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
>SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
>Result
>SA, Sales, Man1, Actual, Prd1, Jan, 100
>SA, Sales, Man1, Actual, Prd2, Jan, 200
>SA, Sales, Man1, Actual, Prd3, Jan, 300
>SA, Sales, Man1, Actual, Prd4, Jan, 400
>SA, Sales, Man1, Actual, Prd5, Jan, 500
>SA, Sales, Man1, Actual, Prd1, Jan, 1
>SA, Sales, Man2, Actual, Prd2, Jan, 2
>SA, Sales, Man3, Actual, Prd3, Jan, 3
>SA, Sales, Man4, Actual, Prd4, Jan, 4
>SA, Sales, Man5, Actual, Prd5, Jan, 5
>In essence the size of the table will end up as
>n records * 5 fields.
>Thanks in advance.
>
>|||Steve
Thanks that worked for me. Now if I wanted to extend that to say
If Column3 = 'Man2' then multiply the amounts by 0.15 or
If Column3 = 'Man3' then multiply the amounts by 0.3
How would this affect the statement below.
Column1 Column2 Column3 Column4 Column5
Prd1 Prd2 Prd3 Prd4 Prd5
SA Sales Man1 Actual
Jan 100 200 300 400 500
SA Sales Man1 Actual
Jan 1 2 3 4 5
SA Sales Man2 Actual
Jan 100 200 300 400 500
SA Sales Man2 Actual
Jan 1 2 3 4 5
SA Sales Man3 Actual
Jan 100 200 300 400 500
SA Sales Man3 Actual
Jan 1 2 3 4 5
select
column1, column2, column3, column4, column5,'Prd' + Nch as Prd,
case Nch
when 1 then Prd1
when 2 then Prd2
when 3 then Prd3
when 4 then Prd4
when 5 then Prd5
end as Amount
from dbo.Sheet1$
cross join (
select '1' as Nch union all select '2' union all select '3'
union all select '4' union all select '5'
) as F
Thanks in advance.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAu7oipZGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Avril,
> In SQL Server 2005, you can use the UNPIVOT operator for this.
> In SQL Server 2000, you can use a CROSS JOIN like this:
> select
> column1, column2, column3, column4,
> 'Prd' + Nch as Prd,
> column5,
> case Nch
> when 1 then Prod1 when 2 then Prod2 when 3 then Prod3
> when 4 then Prod4 when 5 then Prod5 end as LastColumn
> from yourTable
> cross join (
> select '1' as Nch union all select '2' union all select '3'
> union all select '4' union all select '5'
> ) as F
> There may be some typos, since you didn't give the create table
> statements and sample data as INSERTs to test with, and you
> will have to fix column names for the columns whose names
> you didn't provide.
> Steve Kass
> Drew University
> Avril wrote:
>|||On Mon, 24 Apr 2006 06:55:21 +0100, Avril wrote:

>Steve
>Thanks that worked for me. Now if I wanted to extend that to say
>If Column3 = 'Man2' then multiply the amounts by 0.15 or
>If Column3 = 'Man3' then multiply the amounts by 0.3
>How would this affect the statement below.
(snip)
>select
>column1, column2, column3, column4, column5,'Prd' + Nch as Prd,
>case Nch
>when 1 then Prd1
>when 2 then Prd2
>when 3 then Prd3
>when 4 then Prd4
>when 5 then Prd5
>
END * CASE Column3
WHEN 'Man2' THEN 0.15
WHEN 'Man3' THEN 0.3
ELSE 1

>end as Amount
>from dbo.Sheet1$
>cross join (
>select '1' as Nch union all select '2' union all select '3'
>union all select '4' union all select '5'
> ) as F
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Thanks
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:vmgq42tjek1ur7ru4velvtv3hiqnv7gekq@.
4ax.com...
> On Mon, 24 Apr 2006 06:55:21 +0100, Avril wrote:
>
> (snip)
> END * CASE Column3
> WHEN 'Man2' THEN 0.15
> WHEN 'Man3' THEN 0.3
> ELSE 1
>
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP

Programatically running the SQL Server Agent

I have a scenerio where I am using a Push Merge Subscription set with "Run
On Demand". How can I programatically (Stored procedure) run the SQL Agent
Job that synchronizes the Subscriptions?
Thanks,
RSHHave a look at sp_start_Job.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O%23tZgcf9FHA.3660@.TK2MSFTNGP09.phx.gbl...
>I have a scenerio where I am using a Push Merge Subscription set with "Run
>On Demand". How can I programatically (Stored procedure) run the SQL Agent
>Job that synchronizes the Subscriptions?
> Thanks,
> RSH
>

Programatically find out stored procedures properties

Hi all,
How do I find out what id's have execute permissions on each procedure in a
database for all procedures using t-sql?
-Thanks! daveHi
I'm sure if you asks questions like this you know you way around SQL,
I did some searching in BOL since this is something that could be usefull
and I would in the future maybe use it.
So I came across this
A. List the permissions for a table
This example lists the permissions for the titles table.
EXEC sp_helprotect 'titles'
B. List the permissions for a user
This example lists all permissions that user Judy has in the current databas
e.
EXEC sp_helprotect NULL, 'Judy'
Now there's a lot of other "users info" tables and processes, so I'm sure if
you search BOL you'll find some usefull info. I'll search and see if I can
come up with something, and add it.
HTH
"dfate" wrote:

> Hi all,
> How do I find out what id's have execute permissions on each procedure in
a
> database for all procedures using t-sql?
> -Thanks! dave
>
>

Programatically disable trigger

Is it possible to disable a trigger for the duration of a stored procedure
? It would become enbled after the sp runs.See "ALTER TABLE" in BOL.
Example:
ALTER TABLE t DISABLE TRIGGER tr_t_ins
GO
AMB
"Rob C" wrote:

> Is it possible to disable a trigger for the duration of a stored procedure
> ? It would become enbled after the sp runs.
>
>|||ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
BUT, if you disable the trigger, it is disabled for everyone connected to
the server that does something against the specific table. Use with great
caution.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:Ze9Pd.4999$a06.2028@.bignews1.bellsouth.net...
> Is it possible to disable a trigger for the duration of a stored procedure
> ? It would become enbled after the sp runs.
>|||alter table disble trigger - check the BOL for more details. but, be very
careful how you use this - it disables trigger (or all triggers for a given
table) for all sessions and for all users. make sure you enable it back
asap.
dean
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:Ze9Pd.4999$a06.2028@.bignews1.bellsouth.net...
> Is it possible to disable a trigger for the duration of a stored procedure
> ? It would become enbled after the sp runs.
>|||You can disable a trigger via the ALTER TABLE command but this will affect
all connections to the database.
One trick is to add a flag column to your table(s) that the triggers work on
which the trigger can examine in order to see if the logic should be
executed.
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:Ze9Pd.4999$a06.2028@.bignews1.bellsouth.net...
> Is it possible to disable a trigger for the duration of a stored procedure
> ? It would become enbled after the sp runs.
>

Program from executing Procedure

I liked of susbistution the QueryAnalyser by other program, somebody know ??:confused:I have just recently downloaded WinSQL (http://www.indus-soft.com/winsql) which lets you connect to any ODBC-datasource and execute sql towards it... the lite-version is free and it works like a bliss