Executing A Procedure For Every Selected Record
Aug 5, 2007
In a few places in my application I want to execute a procedure for every selected record. The only way that I know how to do this is to use a cursor, as below. This code works perfectly, but everything I read says that one should use set operations rather than cursors wherever possible, as they are much more efficient. So what I really want to do is something like
Exec procedure argument [,argument]... where argument in (Selet value from ....)
or perhaps
Exec procedure (select ...) [,argument]
or
SELECT (procedure....
all of which are invalid. Is there any syntax that avoids the cursor in: ---Copy all facts (and their details, if any) to the new INDI
Declare ccopyIndi cursor for --Step through facts
Select Factid from gdbfact
where factindiid = @IndiidFrom
Open ccopyIndi
FETCH Next from ccopyIndi into @Factid
while @@Fetch_status = 0
Begin
exec dbo.gdbcopyfact @NewIndiid, @Factid
Fetch next from ccopyIndi into @Factid
End
CLOSE ccopyIndi
DEALLOCATE ccopyIndi
(BTW, dbo.gdbcopyfact is NOT a simple INSERT statement)
View 5 Replies
ADVERTISEMENT
May 20, 2015
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
View 2 Replies
View Related
Feb 28, 2008
How can I get an Id of selected record? something like
Code Snippet
DECLARE @Id uniqueidentifier
SELECT @Id = ID FROM DataTable
BUT! I need also retrieve a data in the same query. Is that possible or there is a different way? Thanks.
View 4 Replies
View Related
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
View 1 Replies
View Related
Jan 23, 2008
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies
View Related
May 6, 2015
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
View 3 Replies
View Related
Feb 21, 2014
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
View 0 Replies
View Related
Feb 17, 2006
I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource2"> <Columns> <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False" ReadOnly="True" SortExpression="member_id" /> <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" /> <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" /> <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" /> <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" /> <asp:CommandField ShowEditButton="True" /> <asp:CommandField ShowDeleteButton="True" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>" SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure" DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" > <DeleteParameters> <asp:Parameter Name="member_id" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO
View 1 Replies
View Related
Dec 9, 2005
could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE. I receive my 3 column headers in my datagrid but the parameter isn't doing anything
STORED PROCEDURE
CREATE PROCEDURE USP_Machcusidsearch
@Machcusid nvarchar OUTPUTAS
SELECT dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM dbo.Machine INNER JOIN dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO
VB.NET CODE
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
SqlDataAdapter2.Fill(DsMachcusidsearch1)
SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value
DGstatussearch.DataBind()
End Sub
End Class
View 4 Replies
View Related
Mar 30, 2006
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
View 1 Replies
View Related
Jun 21, 2012
I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?
Which is faster? There will be about 100 rows returned with 10 columns.
View 5 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
May 12, 2015
I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).
I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .
Now i am using dataset with this store procedure to fill my @USER_IDS parameter
Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error
and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..
View 2 Replies
View Related
May 13, 2008
Hi,
I have StoredProcedure with 2 Parameters. I want to execute this procedure in SQLSERVER Query Builder.
I know without using parameters to execute. i.e EXEC pro_demo
But now i have with parameters.
How to pass that parameters in a Query Builder?
View 1 Replies
View Related
Feb 13, 2008
I have the following code. User clicks on a button, then textbox with
calendar icon is displayed, calendar appears when icon is clicked, user
selects date, date is populated in the textbox field. The value in the
textbox field is passed to a stored procedure. How can I check if the
sp call was successful and what can I do to add a message to the user.
Also, is the control flow appropriate or should I change it? Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click TextBox1.Visible = True ImageButton1.Visible = True If Calendar1.Visible = True Then ElseIf TextBox1.Text <> "" Then ' Create connection Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SQLSTRING.ConnectionString) conn.Open() ' Create command Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand() cmd.Connection = conn cmd.CommandType = Data.CommandType.StoredProcedure cmd.CommandText = "sp" cmd.Parameters.Add("@date", Data.SqlDbType.DateTime) cmd.Parameters("@date").Value = Convert.ToDateTime(TextBox1.Text) cmd.ExecuteNonQuery() conn.Close() Else Response.Write("You must select a date.") End If
View 6 Replies
View Related
Oct 11, 2001
Hi,
I have create a DTS package "test"
i see the name is stored in msdb.sysdtspackages
how do i run this "test" package from stored procedure
thx
vik
View 1 Replies
View Related
Nov 1, 2006
Hello,
I'm using Sql Server 2005, and I am receiving an error when I attempt to run a stored procedure and I have no clue why. Can someone please help?
I receive an error when I attempt to execute the following stored procedure:
exec INSERT_OBJ
1234,
'Name',
123,
NULL,
GetDate(),
'system'
The error I receive is: "Incorrect syntax near ')'" All of the values are valid values and valid types.
Is it possible to be an error within the stored procedure itself? This error makes it sound like the syntax of my attempt is incorrect, thus it never gets ran.
Thank you ,
Crystal
View 2 Replies
View Related
Apr 29, 2004
Hi,
I Have the next problem:
I have a store procedure in my db which is called from an aplication (developed in Java). That sp contains a cursor which updates regs from a table. After calling from the java application I notice that some regs of the cursor have not entered in it, i mean if the sp executes 200 iterations in the cursor only 150 have worked. But all the iterations enter when i call the sp from the query analyzer!!! and it takes more time too.
Does anybody know something about it?????
View 1 Replies
View Related
Jun 12, 2008
Hi All,
I am trying to execute a "ServerB" Stored Procedure in "ServerA".This SP is in Multiple DB's in ServerB.
I am trying to use Openquery and Dynamic SQL to do that.But I am having issues.
Intially i am trying to pass just one DBname as parameter..if it returns values then i can use cursor or other options to retrieve for multiple DB's
Please Help!!!
Ex:
DECLARE @TSQL varchar(8000), @DBNAME char(20)
SELECT @DBNAME = 'DB1'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(serverB'+','+''exec '' + @DBNAME + ''.dbo.sp_StoredProcedure''+')'
EXEC (@TSQL)
Thanks in Advance!!!
View 3 Replies
View Related
Apr 17, 2007
when i try to run a procedure i've been working on, i get the following Error:
Server: Msg 8144, Level 16, State 2, Line 0
Procedure or function has too many arguments specified.
execution code:
exec search_orders_1 @dwelltype=1
stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE search_orders_1
@lowageint=null,
@highageint=null,
@highwealthint=null,
@lowwealthint=null,
@highpurpriceint=null,
@lowpurpriceint=null,
@lowmtgamtint=null,
@highmtgamtint=null,
@lowincomenvarchar(4000)=null,
@highincomenvarchar(4000)=null,
@marriedstatnvarchar(4000)=null,
@lowyearbuildnvarchar(4000)=null,
@highyearbuildnvarchar(4000)=null,
@mtgpresstatnvarchar(4000)=null,
@lenderpresstatnvarchar(4000)=null,
@ratetsnvarchar(4000)=null,
@loantsnvarchar(4000)=null,
@childstatnvarchar(4000)=null,
@homeownerstatnvarchar(4000)=null,
@lowpurdatenvarchar(4000)=null,
@highpurdatenvarchar(4000)=null,
@addresstype varchar(3)=null,
@SPSirvarchar(3)=null,
@debttypevarchar(3)=null,
@dwelltypevarchar(3)=null,
@zipselectnvarchar(4000)=null,
@acselectnvarchar(4000)=null,
@stselectnvarchar(4000)=null,
@cityselectnvarchar(4000)=null,
@counselectnvarchar(4000)=null,-- as
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql ='SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o WHERE 1 = 1'
IF @lowage<>0 and @lowage is not null
SELECT @sql = @sql + ' AND o.per1_age >= @xlowage'
IF @highage<>0 and @highage IS NOT null
SELECT @sql = @sql + ' AND o.per1_age <= @xhighage'
IF @lowincome<>'DF' and @lowincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome'
IF @highincome<>'DF' and @highincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome'
IF @highwealth<>0 and @highwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth'
IF @lowwealth<>0 and @lowwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth'
IF @highpurprice<>0 and @highpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice'
IF @lowpurprice<>0 and @lowpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice'
IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild'
IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild'
IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'
IF @highmtgamt<>0 and @highmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt'
IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate'
IF @highpurdate<>'DF' and @highpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate'
IF @marriedstat in ('M','S')
SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat'
IF @ratets in ('V','F')
SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets'
IF @homeownerstat in ('H','R')
SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'
IF @childstat in ('Y','N')
SELECT @sql = @sql + ' AND o.child_pres = @xchildstat'
IF @lenderpresstat ='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>+ '' '''
IF @lenderpresstat ='N'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name=+ '' '''
IF @mtgpresstat='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'
IF @mtgpresstat='N'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'
IF @loants<>'DF' and @loants IS NOT NULL
SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'
if @dwelltype='1'
SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''
if @dwelltype='2'
SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
SELECT @sql = @sql + ' and (1=2'
IF @acselect<>'DF' and @acselect IS NOT NULL
SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )'
IF @stselect<>'DF' and @stselect IS NOT NULL
SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )'
IF @cityselect<>'DF' and @cityselect IS NOT NULL
SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )'
IF @counselect<>'DF' and @counselect IS NOT NULL
SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'
IF @zipselect<>'DF' and @zipselect IS NOT NULL
SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
SELECT @sql = @sql + ')'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xlowageint,
@xhighageint,
@xlowincomenvarchar(4000),
@xhighincomenvarchar(4000),
@xmarriedstatnvarchar(4000),
@xhighwealthint,
@xlowwealthint,
@xhighpurpriceint,
@xlowpurpriceint,
@xlowyearbuildnvarchar(4000),
@xhighyearbuildnvarchar(4000),
@xlowmtgamtint,
@xhighmtgamtint,
@xmtgpresstatnvarchar(4000),
@xlenderpresstatnvarchar(4000),
@xratetsnvarchar(4000),
@xloantsnvarchar(4000),
@xchildstatnvarchar(4000),
@xhomeownerstatnvarchar(4000),
@xlowpurdatenvarchar(4000),
@xhighpurdatenvarchar(4000),
@xzipselectnvarchar(4000),
@xacselectnvarchar(4000),
@xstselectnvarchar(4000),
@xcityselectnvarchar(4000),
@xcounselectnvarchar(4000)'
EXEC sp_executesql @sql, @paramlist,
@lowage,
@highage,
@lowincome,
@highincome,
@marriedstat,
@highwealth,
@lowwealth,
@highpurprice,
@lowpurprice,
@lowyearbuild,
@highyearbuild,
@lowmtgamt,
@highmtgamt,
@mtgpresstat,
@lenderpresstat,
@ratets,
@loants,
@childstat,
@homeownerstat,
@lowpurdate,
@highpurdate,
@addresstype,
@SPSir,
@DebtType,
@DwellType,
@zipselect,
@acselect,
@stselect,
@cityselect,
@counselect
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
could someone help me figure out what's up? someone told me you can have 1,024 parameters for a stored procedure.... what am i missing?
do i have to many IF clauses?
View 2 Replies
View Related
Jul 10, 2007
Hi,
Can anyone tell me how to execute a stored procedure using vb6.0.I am able to connect to sqlserver from my application.Simple select queries are working.
The stored procedure contains a select statement at the end.I want to get that as the resultset in vb6.0.How do I do this.
Keerthi
View 1 Replies
View Related
Oct 2, 2007
Hi
I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000.
But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs.
Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers?
I'm using .NET Framework 1.1ODBC (SQL Native Client)
Here is the Store procedure code:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[CreateProjectDataBase]
@AFModelDatabaseProject varchar(200),
@TemplateDbBackupFileAndName varchar(200),
@DatabaseName varchar(200),
@DataFilePathAndName varchar(200),
@LogFilePathAndName varchar(200)
AS
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName
WITH INIT
RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName
WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName,
MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure;
this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName;
this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName;
this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }";
this.odbcCreateDataBaseCommand.ExecuteNonQuery();
RegardsTomas
View 2 Replies
View Related
Mar 11, 1999
We have a test and production environment. After transfering some tables from
test to prod and all stored procedures using those tables.
We get an error when executing those stored procedures:
" DB-library: Possible network error:
Bad token from SQL Server:
Datastream processing out of sync.
Net-library error 0:
DB-libray Process Dead - Connection Broken. "
When we execute the stored procedure with 1 parameter less we get a parameter
missing error. Then we execute the stored procedure again and everything is allright?
Has anyone experienced this before? If so, please help.
SQlServer 6.50.201
Kees Visser
View 3 Replies
View Related
Jul 31, 2001
How can I execute a package from a store procedure. The package function will export a table to a text file in c:export.
I can run the package manually by clicking on it to execute it. I am looking for another way to run the package from a store procedure. any ideas
Thanks
Ali
View 1 Replies
View Related
Aug 8, 2001
Hi ALl
I need a quick solution for my sql server problem as follows.
Actually Earlier we have SQL Server 7, Every 3 hours we execute one
Procedure it takes only 15 seconds. Now we
Install SQL Server 2000 and Same Procedure Is taking 30 Minutes for
execution what could be the reason
I want to minimize the execution time. how can i ?
Please help me
Thanks in advance
Shan
View 1 Replies
View Related
Jul 22, 2004
Hi Guys..
How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..
I got this results from running (sp_dropallindex) like this:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO
ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO
DROP INDEX table1.index1 GO
DROP INDEX table1.index2 GO
I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...
I tried something like this:
DECLARE @DROP AS VARCHAR(8000)
SET @DROP='exec sp_drop_allindex'
EXECUTE (@DROP)
and i see the same out , but my indexes and PK still there ... i'm confused about it ..
PLEASE HELP ME OUT :D
View 1 Replies
View Related
Oct 4, 2014
how to use opendatasource to execute a stored procedure remotely in MySQL with parameters..I am using sp_configure to enable and disable Ad Hoc Distributed queries as below before and after the open data source statement
sp_configure show,1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
[code]...
View 0 Replies
View Related
Jun 12, 2008
i wanna execute a stored procedure in a select and use its return type
i.e
select name from table1 where id = sp 1,1
i executed it and occurred an error
help me pls
View 2 Replies
View Related
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image attached
Any ideas for this issue?
Thanks a lot
View 2 Replies
View Related
Feb 20, 2007
Hi everyone,first time here, so I'm sorry if this has been covered already agesago. :(I am trying to learn T-SQL and Stored Procedures and bought the bookon these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'malready stuck on my first Stored Procedure and getting error messagesthat I cannot understand. I've already tried Google and Microsoftonline to no avail. I do have the .NET Framework on my system and useit for programming in C# sometimes and by the looks of the error, it'ssomething to do with that? Please note I am only using SQL ServerExpress.Here is the first Stored Procedure:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE ap_HelloASBEGINSET NOCOUNT ON;SELECT 'Hello world'SELECT * from dbo.EqTypeENDGOThe error in its entirety is this:Msg 6522, Level 16, State 1, Procedure ctrd_DDL_PROCEDURE_EVENTS_vb,Line 1A .NET Framework error occurred during execution of user definedroutine or aggregate 'ctrd_DDL_PROCEDURE_EVENTS_vb':System.UnauthorizedAccessException: Access to the path 'c:server_login.log' is denied.System.UnauthorizedAccessException:at System.IO.__Error.WinIOError(Int32 errorCode, StringmaybeFullPath)at System.IO.FileStream.Init(String path, FileMode mode, FileAccessaccess, Int32 rights, Boolean useRights, FileShare share, Int32bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, StringmsgPath, Boolean bFromProxy)at System.IO.FileStream..ctor(String path, FileMode mode,FileAccess access, FileShare share, Int32 bufferSize, FileOptionsoptions)at System.IO.StreamWriter.CreateFile(String path, Boolean append)at System.IO.StreamWriter..ctor(String path, Boolean append,Encoding encoding, Int32 bufferSize)at System.IO.StreamWriter..ctor(String path, Boolean append)at VbTriggers.Triggers.trigger_DDL_PROCEDURE_EVENTS() ..The statement has been terminated.Can anyone assist please? :(Many thanks in advance,Lara
View 3 Replies
View Related
Jul 20, 2005
HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh
View 2 Replies
View Related
Dec 1, 2006
Hi All,
I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.
EXEC [dbo].[ETLloadGROUPS]
@countRun =?,
@startTime =?,
@endTime = ?
This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.
When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.
Am I missing something here ? Please Advice
Thanks in Advance
View 9 Replies
View Related
Mar 27, 2008
I have a maintenance stored procedure that executes a script using xp_cmdshell and sqlcmd. The stored procedure has one argument that is contains the name of the procedure to update.
The stored procedures retrieves the location of the stored procedures (stored in a table) and then builds a variable that contains the sqlcmd command. Note that the file name of the script is <stored procedure name>.sql
Basically stored procedure is ...
select @cmd = 'sqlcmd -E -d ' + @dbname + ' -i "' + @filelocation + @proc + '.sql" '
exec master..xp_cmdshell @cmd
Is there a way to execute sql scripts within a stored procedure without using xp_cmdshell?
Thanks!!
View 2 Replies
View Related