How to change the Default value of a Column

What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )

How to change table names (Table, Table1, Table2, etc.) returned from SP

Hi all,
How do I customize table names returned from the stored procedure?
For example, I have a stored procedure that is something like this:
SELECT * FROM Employee
SELECT * FROM Employer
SELECT * FROM HealthInsurance
This SP returns multiple tables to the VB.NET application. Now, the names of
the tables retuned in the dataset are: Table, Table1, and Table2. Is there a
way to customize those 3 names so the Table is "Employee", Table1 is
"Employer", Table2 is "HealthInsurance"?
I would then get the Employee data by calling:
ds.Tables("Employee")
instead of:
ds.Tables("Table")
Thanks for your time
Goran Djuranovic
hi Goran,
Goran Djuranovic wrote:
> Hi all,
> How do I customize table names returned from the stored procedure?
> For example, I have a stored procedure that is something like this:
> SELECT * FROM Employee
> SELECT * FROM Employer
> SELECT * FROM HealthInsurance
> This SP returns multiple tables to the VB.NET application. Now, the
> names of the tables retuned in the dataset are: Table, Table1, and
> Table2. Is there a way to customize those 3 names so the Table is
> "Employee", Table1 is "Employer", Table2 is "HealthInsurance"?
> I would then get the Employee data by calling:
> ds.Tables("Employee")
> instead of:
> ds.Tables("Table")
you have to customize the in memory dataset provided by ADO.Net client side,
naming your datatable accordingly to your needs as "TableX" is the default
provided by ADO.Net when no personal provided value is available, so the
TableMapping method come to hand like
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Tabl1e", "Customers")
Dim ds As New DataSet()
da.Fill(ds)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to change owner of stored procedure in MS SQL 2000?

For MS SQL 2000, how to change owner of stored procedure? Many thanksOn Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:

>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT|||Have a look at sp_changeobjectowner
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks

How to change owner of stored procedure in MS SQL 2000?

For MS SQL 2000, how to change owner of stored procedure? Many thanksOn Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT|||Have a look at sp_changeobjectowner
--
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks

How to change owner of stored procedure in MS SQL 2000?

For MS SQL 2000, how to change owner of stored procedure? Many thanks
On Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:

>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT
|||Have a look at sp_changeobjectowner
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks

How to change date formats in stored procedure

I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mikeLook up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?|||I am inserting a date value into a table and I dont want the timestamp portion included.|||Thanks! I figured it out using the convert function|||A very similar question was answered yesterday.|||Heck, cascred, this is one of those questions that gets asked every WEEK.

musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)|||Weekly? Hell sometimes it's hourly|||Weekly? Hell sometimes it's hourly Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.|||Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.

You make things so complicated. Why didn't you just say that today the question will be asked at:

create table Numlist (num int identity(1,1) not null primary key)
go
insert Numlist default values
while scope_identity() < 24 insert numlist default values
go
select dateadd(hh, num, '10/4/2005') from numlist
go
drop table numlist

Bill|||Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.|||I'm actually in favor of the simpler:SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS bBonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).

-PatP|||Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP

I like it. I have never seen this approach before.

You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.

Bill|||You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.Gold star!

Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.

-PatP|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?

Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.|||try dis one.
select convert(varchar,datefield,101) from tablename

How to catch stored procedure execution time?

Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:

Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.

ZYT

The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||Below batch query might help you to determine the performance in secounds

declare @.startproc datetime
declare @.endproc datetime
declare @.time integer

select @.startproc = getdate()

exec <stored procedure>
select @.endproc = getdate()

select @.time = DATEDIFF(second, @.startproc, @.endproc)

print str(@.time)|||Pat:

Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.

ZYT

Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!

Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.

I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!

As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).

The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!

-PatP|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.

Thank

ZYT

I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning

As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...

Logging the sproc call from code would not give you a true length due to other resources

I find that log so useful in so many ways

What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.

Well that's pure bull sheet

Blame it on profiler

How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:

By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.

Thanks

ZYT

No experience but Scenario 1in the paper describes exactly this.|||Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH|||Pootle:

I got it.

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)

can work. or refer http://support.microsoft.com/kb/270599.

Thanks everyone. This is a good post.

ZYT

Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH

How to catch an exception?

Is there something like exception handling in T-SQL?
For example, how to catch an error of convertion at this
sample:

CREATE PROCEDURE SP
@.param VARCHAR(50)
AS BEGIN
DELCARE @.var INT
-- try {
SET @.var = CONVERT( int, @.param)
-- } catch (error#245) {
-- handle an error right here
-- }
END

It must be invisible for a caller of SP if something wrong inside SP.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!>
> CREATE PROCEDURE SP
> @.param VARCHAR(50)
> AS BEGIN
> DELCARE @.var INT
> -- try {
> SET @.var = CONVERT( int, @.param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
In this situation you can use ISNUMERIC function.
In T-SQL there are not try..catch constructions and all errors you will get
on client :(.

ALTER PROCEDURE SP
@.param VARCHAR(50)
AS BEGIN
DECLARE @.var INT
-- try {
if ISNUMERIC(@.param) = 0
begin
RAISERROR('Error converting @.param -> @.var',16,10)
RETURN -1
end
SET @.var = CONVERT( int, @.param)
END
go
exec SP
@.param = '1a'
go|||Hi, Garry!

Thank you for your answer but my question was not about how to suppress
exactly convertion error. I'm looking for something like try-catch. Is
it truth that no way to handle an exception inside the server execution?
It is sad...

Ok, my problem is that: some of my procedures are able to generate both
correct rowset and some error messages at the same time. But when I try
to open the query with EXEC thru OLE DB I receive an error, not rowset
:( The best issue for me: if I would be able to handle all the errors
inside the stored procedure body...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Evgeny Gopengauz (evgop@.ucs.ru) writes:
> Is there something like exception handling in T-SQL?
> For example, how to catch an error of convertion at this
> sample:
> CREATE PROCEDURE SP
> @.param VARCHAR(50)
> AS BEGIN
> DELCARE @.var INT
> -- try {
> SET @.var = CONVERT( int, @.param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
> It must be invisible for a caller of SP if something wrong inside SP.

For SQL2000 the answer is very distinctively: NO. Error handling in
SQL Server 2000 is a mess. There are two articles on my web site about
the topic http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html.

The good news is that in the next version of SQL Server, SQL 2005 which
now is in beta, there are great improvements in this area, and there
is indeed a TRY-CATCH construct.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Garry (vadim@.viii.ntu-kpi.kiev.ua) writes:
> In this situation you can use ISNUMERIC function.

No, you can never use the isnumeric() function, because it is
virtually useless. isnumeric() tells you that a string can be
converted to some numeric data type, but you can find out which. A
string that can be converted to money may not convert to float or
vice versa.

For test of a positive integer number, this is the way to do:

@.x NOT LIKE '%[^0-9]%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to capture the correct identity value

I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.

blumonde

Have you tried this?

SELECT SCOPE_IDENTITY()

|||

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

|||

Thanks for your response, gentlemen.

I tried 'Select @.getKEY = @.@.IDENTITY' right after the first insert and it seems to work pretty good so far. I hope I am doing it the right way. However, I am not sure my method is consistant when several users inserting at the same time.

I think it is better to use 'scope_identity' and follow Motley's method above. I think it can handle multi-tasking better than my method.

Motley, what is the Select below for? I have to use it after the second insert?

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||

Motley:

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

Motley, what is the Select below for? I have to use it after the second insert? Thanks.

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.|||

Motley:

The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.

Thank you.

blumonde

How to capture out param?

I'd like to execute a stored procedure that needs 5 parameters. One of
them is an OUTPUT parameter. What does the
exec sp_name...
code, executed in QA SQL Server 2000, look like for this? Also, if the
OUTPUT parameter is declared last in the spoc, how can I call it by
name as the first parameter in my exec code?
Thanks,
BrettAssuming you have 5 parameters & the 5th one is an OUTPUT paramter, you can
call the procedure like:
EXEC usp @.p1, @.p2, @.p3, @.p4, @.p5 OUT
SELECT @.p5 ;
I know what you posted is just a sample, but in case hadn't noted, avoid
using sp_ prefix for stored procedures sicen they have certain unfavorable
implications.
Anith

How to call stored procedure to table?

Hi....

I have problem and I need your help

I stored a procedure in the Projects Folder in my computer

and I want to return the procedure result in a column inside table

How I can do that???

thank you

Please any one can help me??????????

|||

Hi,

From your description, you have mentioned that you want to return the procedure result in a column.

Well, do you mean you want to select a column from your database with several rows, and then get these rows from your stored procedure, right?

If so, you can execute a procedure that return rows. To execute a stored procedure that returns row, you can run a TableAdapter query that is configured to run a stored procedure (for example, CustomersTableAdapter.Fill(CustomersDataTable)).

If your application does not use TableAdapters, call the ExecuteReader method on a command object, setting its CommandType property to StoredProcedure. ("Command object" refers to the specific command for the .NET Framework Data Provider that your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be SqlCommand.)

For more information, see
http://msdn2.microsoft.com/en-us/library/d7125bke(VS.80).aspx

Thanks.

how to call stored procedure from another stored procedure?

is it possible to call a stored procedure from another stored procedure?Inside your first Stored Procedure:

exec <name of SP to run> <Eventual Parameters this SP requires>

Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.

how to call sql store procedure in asp.net

I create a store procedure in sql, how do I call it?

thankssUse SqlCommand like this:

YourCommandName = New SQLCommand("YourStoredProcedureHere",YourConnection)
YourCommandName.CommandType = CommandType.StoredProcedure

You can also add parameters to the SqlCommand.|||got it.

thankss

How to call remote object using C# Stored procedure

I want to call a windows based service running with remote objects listner from a C# Stored procedure. Any idea how to do that?

Hi-

I'm actually not quite sure what you would like to do, but if you could clarify, I can try to help.

I think your question may be how to do this within C#. 99.9% of the time, if it’s possible to do in C#, then it’s possible to do in SQLCLR (under atleast the UNSAFE permission set).

I'd recommend using MSDN or clarifying what you need to do to make your scenario work within a console application. Once you have your scenario working in a console application, you could port your code to map a stored procedure to an entry point.

Thanks!
Jason

|||

I am running a windows service with one exposed object using remoting.

I want to call that from stored procedure/function created in SQL Server using C#.

How to call remote object using C# Stored procedure

I want to call a windows based service running with remote objects listner from a C# Stored procedure. Any idea how to do that?

Hi-

I'm actually not quite sure what you would like to do, but if you could clarify, I can try to help.

I think your question may be how to do this within C#. 99.9% of the time, if it’s possible to do in C#, then it’s possible to do in SQLCLR (under atleast the UNSAFE permission set).

I'd recommend using MSDN or clarifying what you need to do to make your scenario work within a console application. Once you have your scenario working in a console application, you could port your code to map a stored procedure to an entry point.

Thanks!
Jason

|||

I am running a windows service with one exposed object using remoting.

I want to call that from stored procedure/function created in SQL Server using C#.

How to call remote object using C# Stored procedure

I want to call a windows based service running with remote objects listner from a C# Stored procedure. Any idea how to do that?

Hi-

I'm actually not quite sure what you would like to do, but if you could clarify, I can try to help.

I think your question may be how to do this within C#. 99.9% of the time, if it’s possible to do in C#, then it’s possible to do in SQLCLR (under atleast the UNSAFE permission set).

I'd recommend using MSDN or clarifying what you need to do to make your scenario work within a console application. Once you have your scenario working in a console application, you could port your code to map a stored procedure to an entry point.

Thanks!
Jason

|||

I am running a windows service with one exposed object using remoting.

I want to call that from stored procedure/function created in SQL Server using C#.

How to call Oracle Stored Procedure which has an output parameter from SSIS?

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

PROCEDURE Interface_Begin

(p_from_dttmOUT varchar2,

p_error_codeOUT number,

p_error_textOUTvarchar2,

p_proc_nameOUT varchar2);

please check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1711335&SiteID=1

http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html

-Nikul

|||

Could you please be more specific? I tried the following:

1) The stored proc spec is as follows:

Procedure testing(myDate OUT varchar2);

2) Created a Data Flow Task.

3) Created 2 variables called myStoredProc & myDate at the package level.

4) In the value for myDate variable i set it to myDate.

5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.

6) In the expression for myStoredProc variable i have the following:

"declare myDate varchar2(50); begin sa.testing(" + @.[User::myDate] + "); end;"

7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db

Provider for Oracle.

8) Data Access Mode set to SQL Command from Variable.

9) The value of the Variable Name is User::myStoredProc.

10) Now when i hit preview i get the following error Message.

No disconnected record set is available for the specified SQL statement.

I am not sure what's wrong here. Could someone help me?

|||Anyone?|||Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.|||

Hi there,

As Jwelch said use Execute SQL Task and select Single Row in Recordset option.

I think this will iron out your issue.

Please specify if this does not work.

Thanks

|||

Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:

create or replace procedure testing(myDate OUT varchar2)
IS
BEGIN
myDate := 'hey';
return;
END;

This should return 'hey' but i am getting this:


)

Any ideas?

how to call C/C++ DLL in stored Procedure?

How do I call a C/++ DLL in SQL Server stored procedure?
The C function has following prototype.
double function_name(char* x,int i)
ThanksRead about extended stored procedures...

How to call an oracle stored procedure with parameters

I have an oracle stored procedure that will accept 3 parameters from a report
services.... it seems that I can not find the way to call this stored
procedure since the syntax is not the same as this sp was written in SQL
Server.
Could you help?
ThanksAre you running the MSSQL stored proc or the Oracle one? With a MSSQL 2005
sp, just put spMyProc in the query editor in Business Intelligence Studio.
Then configure the parameters using the Edit Dataset window and the
parameters tab.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"greatdane" <greatdane@.discussions.microsoft.com> wrote in message
news:13C68CDD-C7FC-4B18-BCA4-F8F23AD99AA1@.microsoft.com...
>I have an oracle stored procedure that will accept 3 parameters from a
>report
> services.... it seems that I can not find the way to call this stored
> procedure since the syntax is not the same as this sp was written in SQL
> Server.
> Could you help?
> Thanks|||After just typing the spMyProc, I received the following error. The 3
parameters are inside the oracle stored procedure... I had included them also
in the Parameters' tab.
Report item expressions can only refer to fields within the current data
set scope or, if inside an aggregate, the specified data set scope.
Build complete -- 3 errors, 0 warnings
Why is that?
"Alain Quesnel" wrote:
> Are you running the MSSQL stored proc or the Oracle one? With a MSSQL 2005
> sp, just put spMyProc in the query editor in Business Intelligence Studio.
> Then configure the parameters using the Edit Dataset window and the
> parameters tab.
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "greatdane" <greatdane@.discussions.microsoft.com> wrote in message
> news:13C68CDD-C7FC-4B18-BCA4-F8F23AD99AA1@.microsoft.com...
> >I have an oracle stored procedure that will accept 3 parameters from a
> >report
> > services.... it seems that I can not find the way to call this stored
> > procedure since the syntax is not the same as this sp was written in SQL
> > Server.
> >
> > Could you help?
> > Thanks
>

How to call a stored procedure using C#

I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateDate

)

(1) You need to modify your proc according to what I had suggested in my reply to your previous post.

(2) There are plenty of tutorials on the net, including this website where you can get information that you are asking. I just googled for you and here's the first link that came up (and it took me less than a minute):http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

|||

just the same as you do with normalinline queries.

Instead of command type as text, now you include as stored procedure.

Also in the comamnd text just give the stored procedure name and bind the corresponding parameters .

the below query will be much useful

http://www.c-sharpcorner.com/UploadFile/dclark/InsOutsinCS11302005072332AM/InsOutsinCS.aspx

|||

I have made progress but don't know the command to execute the stored procedure. The stored procedure will insert values into a table. So, do I just call the open method of mySqlCommand object, after using "Parameters.Add(" ") to load my values into the stored procedure call? I noticed that another call is "ExecuteNonQuery();" but this is for a call to a stored proc that does a select statement as opposed to one that enters data into the database.

|||

What ever may the stored procedures used for rither insert or select the method is the same.

you can use execute non query for stored procedure

|||

Refer to this thread on how to call stored procedure with c#http://forums.asp.net/t/1152146.aspx