How to execute a store procesure using a link server to oracle

Is it possible to execute a store procedure via a link server to oracle.
I tried these two options but none of them worked.
select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
Deepak VermaDverma,
Can you execute the sp directly as with SQL Server?
EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
i.e.,
EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
HTH
Jerry
"dverma" <dverma@.discussions.microsoft.com> wrote in message
news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
> Is it possible to execute a store procedure via a link server to oracle.
> I tried these two options but none of them worked.
> select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
> select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
>
> --
> Deepak Verma|||If this linked server were a sql server then there is no problem, I would
like to know specific answer against a ORACLE database.
Deepak Verma
"Jerry Spivey" wrote:

> Dverma,
> Can you execute the sp directly as with SQL Server?
> EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
> i.e.,
> EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
> HTH
> Jerry
> "dverma" <dverma@.discussions.microsoft.com> wrote in message
> news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
>
>

How to execute a dynamic SQL with integer parameter for stored procedure?

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.

Error: Unterminated String Constant.

What is the problem?

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By '+ @.SortExpression+') as Row_Count,Rank() Over (Order By '+ @.SortExpression+') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between '+@.startRowIndex+' and '+ @.maxRowIndex+' ';
Exec(@.strSQL);

Can you please try:

Where Row_Count Between ' +Convert(Varchar,@.startRowIndex)+' and '+Convert(Varchar,@.maxRowIndex)+''

|||

Try

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By ' + @.SortExpression + ') as Row_Count,Rank() Over (Order By ' + @.SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between ' +CONVERT(VARCHAR(10),@.startRowIndex) -- the integer needs to be converted to varchar for the + to be a concatenate!
+ ' and ' +CONVERT(VARCHAR(10),@.maxRowIndex) + ' ';
PRINT @.strSQL -- Comment this out once the TSQL is generated correctly
Exec(@.strSQL);

|||

It works! I was converting it to an integer with the convert function. I didn't realized I had to convert it as a string..

How to execute a DTS Via a stored procedure

Hi I have created a DTS package, it runs well, but i would like to execute it in a storeprocedure

Is this possible and if so where do i start from, I have looked all over for it tho every where i look creates a DTS from a store procedure.

I need desperate help

Thanks in advanvcehere is an excellent solution for you

http://p2p.wrox.com/topic.asp?TOPIC_ID=16647

How to execute

I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.

Can someone please give me a basic DTEXEC example?

Thanks in advance !Moving to the SSIS forum|||

cheers Louis.

To the OP, perhaps this will help:

Online Beginner Resources
http://blogs.conchango.com/jamiethomson/archive/2007/01/30/SSIS_3A00_-Online-Beginner-Resources.aspx

-Jamie

How to exec SQL user defined function?

Hi,
How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write
EXEC nameofstored procedure abc (@.abc is the parameter passed).. But How to run a SQL function ?
Thanks

assume function GetUser takes ID as input parameter , write the following in query analyzer and execute

BEGIN
DECLARE @.ID int
set @.ID = 45
SELECT dbo.GetUser(@.ID) AS 'User Name'
END

|||You need to either use it in a stored procedure and then call thestored procedure, or use it in some other statement, such as a SELECTstatement:
SELECT MyFunc(someField) FROM MyTable
There's no way to call it directly as you want.
Don
sql

how to exec a stored procedure

hi,

how do I exec stored procedure that accept parameter and return a single value?

here is example of report

stu_id = ******

stu_name = ****

subject | marks

aa****** | call sp_mark and return student mark for that particular student id and subject

bb****** | call sp_mark and return student mark for that particular student id and subject

cc****** | call sp_mark and return student mark for that particular student id and subject

thks,

You cannot call a stored procedure per row if you mean that with your mentioned design, you would have to get all the information within one procedure to display it in the bound table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Charles,

Have you tried using a user defined function in place of the stored procedure?

Simone

|||

A potentially better performing alternative to a user defined function would probably be a derived table containing the marks for each student by subject. You would then join on the table.

Something like

select stu_id, stu_name, subject, mark

from students s

left outer join (select stu_id, subject, marks from marks) m on m.stu_id = s.stu_id

Of course you would need to summarize the marks into a table...

cheers,

Andrew

How to exec a SQL in stored procedure?

For example,
I have a variable @.SQL = 'Select ''Hello World'''
How can I execute this @.SQL in stored procedure?
Sorry for newbie question =]
Thank you.EXEC (@.Sql)
However, be aware that "Hello World
"Cylix" <cylix2000@.gmail.com> wrote in message
news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> Sorry for newbie question =]
> Thank you.
>|||The exact same way. (Just a little issue with quotes...)
I suggest that you review this article before embarking into using dynamic S
QL.
http://www.sommarskog.se/dynamic_sql.html
Example:
CREATE PROCEDURE #MyTest
AS
BEGIN
DECLARE @.SQL nvarchar(100)
SET @.SQL = 'SELECT ''Hello, World.'''
EXECUTE sp_executesql @.SQL
END
GO
EXECUTE #MyTest
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Cylix" <cylix2000@.gmail.com> wrote in message news:1155862466.120172.203660@.i3g2000cwc.goog
legroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
>
> Sorry for newbie question =]
> Thank you.
>sql

How to exec a SQL in stored procedure?

For example,
I have a variable @.SQL = 'Select ''Hello World'''
How can I execute this @.SQL in stored procedure?
Sorry for newbie question =]
Thank you.EXEC (@.Sql)
However, be aware that "Hello World
"Cylix" <cylix2000@.gmail.com> wrote in message
news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL = 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> Sorry for newbie question =]
> Thank you.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0BBE_01C6C228.19951980
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The exact same way. (Just a little issue with quotes...)
I suggest that you review this article before embarking into using =dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html=20
Example:
CREATE PROCEDURE #MyTest
AS
BEGIN
DECLARE @.SQL nvarchar(100)
SET @.SQL =3D 'SELECT ''Hello, World.'''
EXECUTE sp_executesql @.SQL
END
GO
EXECUTE #MyTest
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Cylix" <cylix2000@.gmail.com> wrote in message =news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com...
> For example,
> I have a variable @.SQL =3D 'Select ''Hello World'''
> How can I execute this @.SQL in stored procedure?
> > Sorry for newbie question =3D]
> Thank you.
>
--=_NextPart_000_0BBE_01C6C228.19951980
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The exact same way. (Just a little =issue with quotes...)
I suggest that you review this article =before embarking into using dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html
Example:
CREATE PROCEDURE #MyTestAS BEGIN
=DECLARE @.SQL nvarchar(100)
=SET @.SQL =3D 'SELECT ''Hello, World.'''
=EXECUTE sp_executesql @.SQL
=ENDGO
EXECUTE #MyTest
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Cylix" =wrote in message news:1155862466.120172.203660@.i3g2000cwc.googlegroups.com=...> =For example,> I have a variable @.SQL =3D 'Select ''Hello =World'''> How can I execute this @.SQL in stored procedure?> > Sorry for =newbie question =3D]> Thank you.>

---

--=_NextPart_000_0BBE_01C6C228.19951980--

How to eval() a variable to get a column name?

OK.. I've got a stored procedure I'm writing, which accepts an argument called @.statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

select * from table1 where @.statfield = @.value

I want to do basically an eval(@.statfield) so if @.statfield is "key_id", then the select statement comes out:

select * from table1 where key_id = @.value

How can I do this?

Thanks!Originally posted by MDesigner
OK.. I've got a stored procedure I'm writing, which accepts an argument called @.statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

select * from table1 where @.statfield = @.value

I want to do basically an eval(@.statfield) so if @.statfield is "key_id", then the select statement comes out:

select * from table1 where key_id = @.value

How can I do this?

Thanks!

One way would be to build dynamic sql and execute it

I copy/pasted the following from SQL Server help

Building Statements at Run Time

DECLARE @.SQLString NVARCHAR(500)

/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @.SQLString = N'SELECT FirstName, LastName, Title' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
SET @.SQLString = @.SQLString + N'FROM Employees' + CHAR(13)

/* Set WHERE clause. */
SET @.SQLString = @.SQLString + N'WHERE LastName LIKE ''D%'''

EXEC sp_executesql @.SQLString|||One problem:

my sql statement is:

select distinct @.stat = packing_shipping from cp_elements where campaign_id = 10

however, if I use execlsql to execute that, @.stat is in some kind of local scope...and is asking to be declared, even though it already is.

How do I get my @.stat return value?? I can't do

select @.stat = exec sp_executesql @.sql

nor this:

exec @.stat = sp_executesql @.sql

help!|||declare @.stat <data type>
exec sp_executesql @.sql, N'@.stat <data type> out', @.stat out

print @.stat|||Hm, that didn't work for some reason..

declare @.stat int

....

set @.sql = N'select distinct ' + @.statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @.campaign_id)
exec sp_executesql @.sql, N'@.stat int out', @.stat out
set @.rc = @.@.rowcount
select @.stat

@.stat shows up as NULL for some reason. did I do something wrong here?|||nevermind. altered the SQL and it worked:

set @.sql = N'select distinct @.stat = ' + @.statfield + N' from cp_elements where campaign_id = ' + convert(nvarchar, @.campaign_id)

How to Encrypt the Stored Procedures in SQL 2005 Express

Dear All,

I am using SQL 2005 Express, and i need to Encrypt all my Stored Procedure while deploying in my Production Server.

Help me out to do.

Hi,

specify the procedure command with WITH ENCRYPTION and SQL Server will create the procedures encrypted.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to encrypt the password field in SQL table

Hi,

I have a login table with username and password as attributes. I need to encrypt the password using stored procedure and then save it in the database. And also while retrieving the password, decrypt using the same stored procedure and get the original text.

I dont know how to do it in SQL server 2000.

Please help me on this. Its urgent.

Thanks and Regards

Unfortunately, this is not an easy task with SQL 2000.

You will have an easier time if you use the VS.NET's encryption library to encrypt the password, and store the encrypted (hashed) value.

Then retrieve the encrypted value, use the encryption library at the application level to encrypt and match to the stored value.

Otherwise, you will be passing the password as clear text across the 'wires' -which isn't very secure.

|||

ok thanks for the suggestion.

I need to confirm whether the same can be done in SQL server 2005.

If can how to do it?

thanks and regards

|||

SQL Server 2005 has rich encryption capabilities.

However, there are two issues you need to consider about encryption.

Data at rest Data in Transit|||If you cannot rely on the client functionality for en/decrypting the information you will have to buy a third party product for SQL Server 2000 which is in common a extended procedures being able to use cryptographic libraries.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to encrypt all existing stored procedure?

I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.

But how about if I want encrypt existing stored procedures?

Which command should I use ?

how about scripting them all off, and using the following:

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

add your encryption, and you're all set......

|||There is no easy way. You have to script out the SQL modules (Create or Alter) and then add the WITH ENCRYPTION option to the definitions. There is no DDL to just encrypt SQL modules without specifying the definition. Why do you want to encrypt your code? The encryption mechanism is really obfuscation and it is a reversible encryption. So it is possible to get the original code from the encrypted content. If you want to protect your code then you should look at legal binding terms and copyright mechanisms.|||As of now, I mark this thread as no answer.

how to encrypt a single field like a password field

without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.SQL Server 2005 has support for encryption, but you have to manage keys, and write your own stored procedures that use the encrypt and decrypt functions. SQL 2000 does not have native support for encryption, I believe.|||Attached is function that is suitable for encrypting passwords.|||naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.|||Attached is function that is suitable for encrypting passwords.
I have downloaded the code and will take a look at it.|||Theres an inbuilt function in SQL2000:

column type must be:
Declare PWCol varbinary(256)

to insert/update use:

...CONVERT(varbinary(256), PWDENCRYPT('THEPASSWORD'))

and to compare a password...

...where PWDCOMPARE('thepassword', PWCol) = 1

Cheers,
Phil
--
Always remember that you're unique, just like everyone else.|||a quick google search on this undocumented function gives you results on how to hack it. PUBLIC KEY ENCRYPTION is the safest bet. It's been a while since I have done this (4 years?) but I used the RSA cypher.|||naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.
Not true. The encryption algorithm I gave is a "one-way" algorithm. It cannot be unencrypted, and thus is only suitable in limited situations such as password encryption. It is relatively easy to make secure one-way encryption schemes.
The challenge is to make a secure "two-way" encryption algorithm. SQL Server's built-in encryption is "two-way" but is not secure and was hacked years ago, and the decryption method is readily available on the web.|||Duplicate post.|||a quick google search on this undocumented function gives you results on how to hack it.

Blimey you boys do love to p!$$ on someones fire.

It's only hackable if your front end code is crap and you don't parse throu before SQL.
If you're stupid enough to leave your SQL server open to access then the fact you can hack a password in a table is pretty irrelevant when you can get control of the whole box.

Right, I'm off to sulk in the corner.

...

To err is human, to forgive is not our Policy.|||Whoa, Mr. Sensitive! You're gonna need thicker skin than that!

And no sulking, either. If you think we're full-o-crap, then just say so (but without throwing all tact to the wind...).

P!$$!ng on someone's fire: allowed.
Sulking in the corner: frowned upon.
P!$$!ing in the corner: well, when ya gotta go...|||Well at least I now know why my sulking corner is starting to smell so bad.|||We generally do our sulking and grousing in the Yak Corral. You can join us there:
http://www.dbforums.com/showthread.php?t=989246&page=289|||Wy not to use the in-built function encrypt() ?|||It is an undocumented function which may not be supported, or may use a different algorithm in future releases.

The algorithm has changed through releases in the past, rendering whole databases inaccessible for applications that relied upon it.

How to enable Ah Hoc Query in SQL 2000

Hi guys

I have a program which imports data into sql server from csv files. This program uses the something like this

CREATE PROCEDURE MyImport

(

@.FileName AS VARCHAR(200)

)

AS

SET NOCOUNT ON

DECLARE @.SQL VARCHAR(4000)

SET @.SQL = ' SELECT *

FROM OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\;'', ''SELECT * FROM ' + QUOTENAME(@.FileName) + '' + ''') as t

'

EXEC (@.SQL)

when i excute this i get a permission error saying the account i specified in the web config doesn't have rights to run these type of scripts. I tried using the sa account which works. But its not recommended to setup applications to access the database using the sa account.

i did some research and i found out that yon can actually enable that by change a registry key. But i cant find the MSDASQL datasource under ms sql server providers in the registry.

Any other ways i can enable this?

Have a look at http://www.asp101.com/articles/john/connstring/default.asp|||

Excellent article. Will give it a go at it on monday.

Thanks

|||

Hi TATWORTH

The link you provided was good but not what im looking for. If you read my post carefully you will know. I have a stored procedure which will select from a file passed in as a parameter using theMSDASQL provider. NOW THIS IS ALL DONE IN SQL not in my app.

I was saying that the account i specified in the web.config does not have permissions to use the MSDASQL provider inside of SQL. The account i used only have rights to run stored procedures and that is it. But my script is written inside of a stored procedure. The reason for not being able to run the script inside of the procedure is because this procedure produces a dynamic script and executes that inside of the stored procedure.

i heard you can enable a registry key which will enable the use of that provider, but i cant find theMSDASQLprovider in the registry.

i was looking for a way to grant permissions to (web_user account) execute these type of queries.

Hope this cleared things up

Cheers

how to empty a stored procedure in ms sql server management studio express

hi everyone,

I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id′s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?

Thanks in advance, best regards

bg

hi bg,

stored procedures can only be executed and not "filled" like "tables"... so they can not be emptied (is that english? )

so what are you trying to do? if you want to empty a specific table, you can open SQL Server Management Studio Express, select the table, access the "Open table" feature, select all rows and delete them (if not referenced in foreign key constraint) or do the same in a query window executing

TRUNCATE TABLE schema_name.table_name

or

DELETE FROM schema_name.table_name WHERE filter_criterion..

regards

sql

How to edit Stored Procedure from ASP.NET environment?

In visual studio 6.0, there are Database tools, including:
Data View
Database Designer
Query Designer
Source Code Editor for Stored Procedure and Triggers

From .NET environment, click Tools on menu bar --> Connect to Database, I can get the 'Data View', while click the table name in the Data View, I can get 'Qeury Designer'.

It seems the 'Database designer' and 'Source Code Editor' are missing.

Questions:
How can I edit, execute and deburg a stored procedure or trigger from within the .NET environment?

How can I create, modify a database, table and so on from .NET environment?

The most useful and important feature is from the question one. Thanks for any information.Hi,

Assuming you have an enterprise version of VS.NET, you can use the Server Explorer. It should be on the left side of the IDE, alongside the Toolbox. You can also select View | Server Explorer from the main menu.

Don|||Yes, I do have Server Explorer that is what I said the 'Data View' in Visual Studio 6.0. But, if you try to expand the database, and choose stored procedure, double click one of SP, you will see you cannot open it in the .NET environment.

In VS 6.0, if you right click a stored procedure, you will see several choices: open, execute, debug, new stored procedure and refresh.

But, in the server explorer, if you right click a stored procedure, you will only see run stored procedure and refresh.

It seems you have no way to edit a stored procedure. That should be the case, I believe, because this feature is quite useful and has been already integrated into VS 6.0.

By the way, I use pro version for .NET, but enterprise version for VS 6.0. Does it make any difference?|||Yep, that's why. As I mentioned, you need one of the enterprise versions to have these features. That's why you're not seeing them. And it's why youare seeing them in your enterprise edition of VB6.

Don|||That makes sense. Thanks, Don.|||Don, I have VS.Net 2003 Pro and can create and edit stored procs (and other objects) in the NetSDK instance of SQL Server on my PC but can not do this in the default instance, which contains my real databases. Do I need to move my databases to the NetSDK instance or upgrade to the Enterprise Edition to get the full functionality?|||Hi,

Hmm, I'm not sure about how each edition handles named instances. Hopefully someone else can chime in, although it's been a few days since you posted this.

Don|||What do you mean? Can you give an example?|||What do you mean? Can you give an example?

Are you asking that of me or dlgross?

Don|||Sorry, Don, the quesiton must be for dlgross.|||dlgross,

I have VS.Net 2003 Pro and can create and edit stored procs (and other objects) in the NetSDK instance of SQL Server on my PC

I have asked a question to you before, what did you mean? I have installed VS.NET 2003 Pro, but I didn't see the difference between VS.NET 2002 and 2003 for this issue. i.e., No way to edit Stored Procedure in .NET either 2002 or 2003 environment.

If you indeed can do it, could you show some example? I would rather like than "in VS .NET pro environment, Stored Proc cannot be edited" is a wrong statement?

How to edit store proc from Manegement Studio

I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !

Thanks

Click the execute button at the top to run the sql.|||Is it possible that when I try this it does excute the store proc but does not save it?|||You RIGHT click on the stored proc from the explorer on the left and click on "Modify". After you make your changes, you hit F5 key.|||

freedom1029 wrote:

I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !

Thanks

SQL Server files and that includes service packs are.sql files but you can always open them with note pad and save as .txt, I usually save a copy as .txt which I can open and adjust as needed. Hope this helps.

|||

Even if I might sound stupid I dont get it :( Or maybe it's me that does not explain my self clearly enoungh. Previously with sql 2000 with Sql Server Entreprise Manager I sed to go in stored procedures, double click on the procedure that I wanted to modify, the the a window open where I was able to modify my procedure and click ok and that was it.

Now in SQL Server Management Studio
Right click and modify ok but F5 execute it and do not save it. I need to modify my procedure permanently.

And File > Save want to save a sql or txt file somewhere on my disc and does not modify my store procedure premennatly either

So what am I missing?

Thanks for your help

|||

I am sorry I was not clear you need to right click on the stored procedure .sql file and you will see open with and choose note pad, then save a copy as .txt and you can modify that copy and save it back as .txt or .sql. It is not automatic but it keeps management studio or query analyzer out of what I put in or take out.

I have used it to open and read SQL Server 2000 service packs and mile long AdventureWorks installation file. I am sorry I was not clear mine uses notepad to open and save the file because notepad can save it as .sql if you need to run it automatically. Hope this helps.

|||

Ok I see where my confusion is coming from

I copied my database from 2000 over to a 2005 Sql Server and all my store proc have 3 lines added at the top of them

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go

Anyway I was just trying to set those to lines of OFF and F5 was not saving my changes

I guess those settings are controled from somewhere else

How To Dynamically Pull Stored Procedure Arguments

I'm sure there is a way using the system tables or maybe on the stored
procedures of the master db to pull the arguments for a given stored
procedure, and their datatypes.
So, for a stored proc like this:
CREATE StoredProc1
OrderNumber AS BIGINT,
OrderName AS VARCHAR(50)
AS ...
I would, using this magical query I am hoping exists, get back:
OrderNumber, BIGINT, 4
OrderName, VARCHAR, 50
or something in that order. Anyone have any ideas?Try,
use northwind
go
exec sp_procedure_params_rowset 'SalesByCategory'
go
AMB
"David Samson" wrote:

> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||From MS SQL Books On Line, you'll read about querying INFORMATION_SCHEMA.PAR
AMETERS:
"Contains one row for each parameter of a user-defined function or stored pr
ocedure accessible to the current user in the current
database. For functions, this view also returns one row with return value in
formation.
The INFORMATION_SCHEMA.PARAMETERS view is based on the sysobjects and syscol
umns system tables.
To retrieve information from these views, specify the fully qualified name o
f INFORMATION_SCHEMA view_name."
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message news:54296B5C-F33F-426A-95A3-7
7366FF14200@.microsoft.com...
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||David Samson (CaptainSlock@.nospam.nospam) writes:
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?
SELECT c.name, t.name, c.length, c.precision, c.scale
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.name = @.name
ORDER BY c.colid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi There,
Why not simply try
sp_help 'procedure name'
OR
Select the name of Procedure and press (Alt+F1) If your QA is
customized for that
With warm regards
Jatinder Singh

How to dynamically create SQL inside a stored procedure?

I am having problem with 'TOP @.pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @.pageSize here so that it dynamically determines the 'n' of 'TOP n' ?

ALTER PROCEDURE dbo.spGetNextPageRecords
(
@.pageSize int,
@.previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @.pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @.previousMaxId) order by ProductId
RETURN

Try aSET @.@.ROWCOUNT instead.

Terri|||So is there a way of dynamically forming a SQL statement inside a stored procedure and then executing it inside the same stored procedure?

I guess, from what you suggested, I should remove the 'TOP @.pageSize' clause from the query, and then use just before the query the following clause - 'SET @.@.ROWCOUNT @.pageSize'.|||The rowcount suggestion works beautifully. I can always get the next 'n' records for the next page.

With this stored procedure, one can always get a fixed number of records for all pages except the last page, while implementing custom paging in a datagrid. Most books will mention an example of custom paging in a datagrid, but will also say that each page might contain a different number of records, if there are missing values of the identity column. But here, even if identity column values are missing ( example: id's = 1,2, 5 , 9, 11 where id's = 3,4,6,7,8,10 are missing ) we can still get a constant number of records per page in custom paging.

I am assuming that the query in this stored procedure is not going to create any inefficiencies on SQL Server side. If you think it will, then please give your feedback.|||SET @.@.ROWCOUNT can have some unexpected side effects if you've got a number of batches in your proc because it restricts the rows for all batches not just the one you want. So just be careful of that.

You could use sp_execute run the dynamic top N (or even wait for Yukon), but unless you get into the problem areas I'd stick with ROWCOUNT.

How to dynamically assign database name in query or store procedure?

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @.MyDatabaseName varchar(30)

set @.MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

declare @.cmd nvarchar (2000)

declare @.MyDatabaseName nvarchar(30)

select @.MyDatabaseName = 'northwind'
select @.cmd = 'SELECT * from '+ @.MyDatabaseName +'.dbo.employees'

exec (@.cmd)

|||

It is much easier and cleaner if you simply deploy your stored procedure in each database. Using dynamic SQL has lot of security implications and you don't really simplify your code.

For a future version of SQL Server, we are looking at features that will enable you to parameterize identifiers without using dynamic SQL. And also the ability to deploy SPs in one module & resolve objects in the execution context database.

|||

Thanks Uma and Joeydj

I can't use dynamic query like Joeydj, simply because my stps are huge; but I am curious about Uma's statement about the security implication in Dynamic query. Could you elaborate a little Uma? thanks

|||

For one, you need to grant more permissions to users on your SPs if you use dynamic SQL. So you increasing the attack surface area of your database by using dynamic SQL. Additionally, if you don't protect against malicious inputs then you are vulnerable to SQL injection attacks which can compromise the database, entire server or network. Lastly, there is also the performance and maintainence aspect of dynamic SQL depending on the usage. So there are many risks in using dynamic SQL. However, for some problems in SQL Server there is no way other than using dynamic SQL (like parameterizing DDL statements, running DDLs against multiple dbs etc). But you can do these carefully by protecting your code against SQL injection attacks, using QUOTENAME for values that can be used as identifiers etc.

Below is a good link on the issues of using dynamic SQL in SQL Server. Also, search the WWW for "SQL Injection" and you will find lot of articles.

http://www.sommarskog.se/dynamic_sql.html

|||

Thank you Uma for your explaination and thanks for the link about dynamic sql. It's a great article!

appreciated.