Can We Use FOR Statements In Stored Procedures In SQL Server 2005
Aug 9, 2007Hai,
I just waana know whether we can use "FOR statement" in Stored Procedures. If yes, can you describe it with some examples of how they are used in stored procedures ?
ADVERTISEMENT
STORED PROCEDURES - IF THEN STATEMENTS
Feb 21, 2008Hi,
This is driving me up the wall,
I have used SQL for donkey's years, but never really used Stored Procedures, well, I am starting to migrate functions from a front end ASP.Net system, and utilising the stored procedures.
I have a problem with something I thought would be really basic, i am trying to use IF THEN statements within the stored procedure to change the WHERE elements based on the parameters passed to it.
Here is the script (copied from the modification screen)
USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/21/2008 21:50:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@SearchVarChar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
IF (@Search='Dowle')
WHERE (dbo.Members.Username = @Search) OR
(dbo.Members.Forename = @Search) OR
(dbo.Members.Surname = @Search) OR
(dbo.ActiveMember_Mobile.Value = @Search) OR
(dbo.ActiveMember_Email.Value = @Search) OR
(dbo.ActiveMember_Location.Location1 = @Search) OR
(dbo.ActiveMember_Location.Location2 = @Search) OR
(dbo.ActiveMember_Location.Location3 = @Search) OR
(dbo.ActiveMember_Location.Location4 = @Search)
END IF
ORDER BY dbo.Members.Username
The bit I am trying to do above isn't real, but the same error appears every which way I try.
Msg 156, Level 15, State 1, Procedure USERS_LIST, Line 14
Incorrect syntax near the keyword 'WHERE'.
What am i doing wrong?
Thanks
David
DML Statements In Code Vs. Stored Procedures
Aug 4, 2005Hi,We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way". What i mean by using seperate stored procedures is:- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)- The body contains a DML statement that uses the parameters- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)For select statements they think our approach is best...I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...Thanks
View 1 Replies View RelatedEmbedded Sql Statements Vs Stored Procedures
Mar 29, 1999Is it true that replacing embedded sql statements (that insert/update and fetch data
from sql databases) by stored procedures improves overall respones time and SQL performance.
We have tons of embedded SQL statements in our Visual Basic modules and are debating whether replacing them by SQL Stored procedures
will really be worht the effort.
Any insights on this?
Thanks in advance. If possible, please reply to my e-mail.Thanks.
Conditional Statements In Stored Procedures.
Dec 13, 2005i would like some conditional settings in a stored procedure
i have a variable
@Variable
and I want to do a conditional statement like
if @Variable = 1 then @Variable2 = 1
Elseif @Variable = 3 then @Variable2 = 4
Else @Variable = 11 then @Variable2 = 12
not sure about how to implement elseif bit
i know you can have
{if this Else That} in T-Sql
Help With Stored Procedures Using IN / GROUP BY Statements
Jul 20, 2005trying to get to the bottom of this for some time...... eventually tobe used with asp.heres the problemthe following rather complex SQL statement works fine via queryanalyser:SELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId ='W27P'AND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = 2AND tbl_LevelDetail_Report.nvchLevelTypeId = 1AND tbl_Levels.nvchReportingLevelId IN ('a')GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamereturns rows ok no problembut when trying to convert to a stored procedure i dont get anyresults:CREATE PROCEDURE usp_incmpwfilter_rs(@strPeriodID varchar ,@intLevelDetailID varchar,@intLevelReportID varchar,@strFilters varchar)ASset nocount onSELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =@strPeriodIDAND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = @intLevelDetailIDAND tbl_LevelDetail_Report.nvchLevelTypeId = @intLevelReportIDAND tbl_Levels.nvchReportingLevelId IN (@strFilters )GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamethen call it by SQL statement:EXEC usp_incmpwfilter_rs 'W27P',2,1,'a'Returns no rows. This is the initial problem. Also there will beanother issue if i can get the above to work: the @strFilters cancontain multiple data, ie 'a','k'this works fine in the 1st sql statement ie: ANDtbl_Levels.nvchReportingLevelId IN ('a','k') but I dont know how topass as a parameter to the stored procedure. I cannot create temporarytables.i had not created the intial SQL statement, i am just trying toconvert it to a stored procedure which accepts thos parameters. thishas been a real headache for me, any help as always appreciatedgreatly.
View 3 Replies View RelatedUsing Statements With Stored Procedures In SQLJDBC 1.2
Dec 4, 2007Hi,
I am doing some research on sqljdbc 1.2. I have run into a few problems with the jdbc driver. I think it would be so much easier just to ask the expert here.
1. when to use CallableStatment, PreparedStatement, Statement for stored proc and why?
http://msdn2.microsoft.com/en-us/library/ms378046.aspx
talks about using Statements with Stored Procedures.
It says for stored Proc with no param, use Statement; for stored proc with only input param, use PreparedStatement; and for stored proc with output param, return status or update count, use CallableStatement.
Is there any special considerations to select which one to use, is there any performance differences? I assume we can always use CallableStatement for stored proc.
2. mutliple statements on one Connection
The JDBC spec allows multiple Statement on one Connection with no need to close the previous Statment. When Connection.close() is called, all the Statements and ResultSets will be closed too.
I tried with regular stored proc, it is fine of have a CallableStatement, then a Statement, without close the CallableStatment, all the Statements are executed with no error.
However, when I change the stored proc, using explicit transaction, i.e BEGIN TRANSACTION, and COMMIT TRANSACTION, the Statement failed and throw an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3500000001.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
When I add a CallableStatement.close() before the create the second Statment, then it worked. Is this a bug or something special needs to be set for stored proc with Transaction?
3. Statement.getGeneratedKeys
Under the hood, is it making another round trip to get the generatedkeys, or when the Statement created with Statement.RETURN_GENERATED_KEYS is excuted, the identity field is already retured with the Statement?
This method call works with regular insert Statement, but does not work for PreparedStatement calling a stored proc. Is this a bug?
Thanks in advance,
jessica
Select Statements And Nested Stored Procedures
Mar 21, 2008I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Is there any way to do either of those?
How To Execute DBCC Statements Through Remote Stored Procedures
Jan 23, 2004Hello,
Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.
I don’t want to use isql, osql, openquery, openresultset because I have to pass user id or password.
I have linked the required SQL servers using Linked servers.
If so please give some example.
Thanks in Advance,
Sateesh
Processing Results Of SELECT Statements In Stored Procedures
May 7, 2008In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable
(like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns.
(I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
Using EXECUTE Statements Calling An Extended Stored Procedures From Function..
Apr 29, 2004Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)
Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec spTest @from, @to
GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec master..xp_msver
GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I want to know if it's possible or not..
Displaying Print Statements Of Stored Procedures In SSIS Logs
Sep 6, 2007
Hi
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
Transact SQL :: How To Find Stored Procedures Without NOLOCK Statements In Database
Jun 15, 2015I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 Replies View RelatedUnable To Debug SQL Server 2005 Stored Procedures From Another Workstation Running Visual Studio 2005
Sep 18, 2006 I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
Any ideas.
Thanks,
Sql Server 2005 And Stored Procedures - Updated Date?
Jan 30, 2008Hi,
I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem?
Br,
Riika
Execution Time Anomaly In Stored Procedures In SQL Server 2005
Dec 3, 2006Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?
SQL Server 2005 Import/Export Not Copying Stored Procedures
Apr 23, 2006Hi,In SQL 2000 if I wanted to take a complete copy of another running sqldatabase all did was create a new database locally and right-click itand select import and point to another database and click copyeverything (stored procedures as well) and it did it for. I can't seemto find the same functionality in SQL 2005. You can copy tables andviews but not the whole database. Is there another way of doing this?Our SQL database is hosted externaly and they recommend using theimport/export feature to do it. Does anyone know I can copy everything(such stored procedures, data table relations...etc)TanksMA.
View 1 Replies View RelatedHow To Debug Database Engine Stored Procedures On SQL Server 2005?
Mar 19, 20071). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues.
I can't find this functionality in the SQL Server Management studio.
2). By the way I also can't find where went the output of my print statement.
3). Does server cursor functionality is still working in 2k5?
Stored Procedures SQL Server 2005 And JDBC Driver Problems
Aug 22, 2007Hi,
I am using SQL server 2005 stored procedures being called from my java application using the CallableStatement. As long as my stored procedure is a simple and direct Select statement things are moving nicely.
But my stored procedures are a little bit more complicated and this causes problems for me to parse the data in a ResultSet.
a sample stored procedure:
Create procedure sp_Get
@cat int,
@itemId bigint
as
declare @results table (tableId bigint, label varchar(200), typeId int)
if @cat = 1
begin
insert @results (tableId, label, typeId)
select
tableId = personId,
label = fname + lname,
typeId = 1
from Person where catId = @itemId
insert @results (tableId, label, typeId)
select
tableId = prospectId,
label = prospect,
typeId = 2
from Prospects where catId = @itemId
end
else if @cat = 2
begin
insert @results (tableId, label, typeId)
select
tableId = companyId,
label = Company,
typeId = 1
from Company where regionId = @itemId
end
/* result set*/
select
tableId,
label,
typeId
from @results
GO
my java code:
Connection conn = this.getConnection() //opens connection to db
CallableStatement cmd = conn.prepareCall(" { call sp_Get (?,?) }");
cmd.setInteger("cat", 1);
cmd.setLong("itemId", 2);
//this returns false?
boolean hasResults = cmd.execute();
//this also returns false?
boolean moreResults = cmd.getMoreResults();
The strange part of this is that when you execute this query in the SQL Server Managent Studio it returns 1 result set.
Why is my java code not able to see this result?
Thanks for any help,
- Rogier
Accessing SQL Server 2005 Stored Procedures From Visual Studio.NET
Jan 30, 2007Hello,
What permissions do I need to set on our new SQL Server 2005 test server so that I can see the stored procedures, views and tables from Visual Studio development environment.
Example, I can see my older SQL Server 7 tables, edit them, write stored procedures, and so on. But while I can see the SQL Server 2005 and its databases, the folder underneath are empty and I cannot right-click to create New stored procedure or table.
My guess is that there is security involved, if so, what do I set on the new server?
If I'm wrong, and it's something else entirely, please advise.
Thanks!
Executing SQL Server 2005 Stored Procedures On Windows 2003
Jul 18, 2007I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.
I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.
123.sh sp_my_stored_procedure input_parm1 input_parm2
123.sh sp_different_stored_procedure input_parm1
This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.
How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.
Thanks, Jim
How To Invoke Nested Stored Procedures In SQL Server Projects In VS.NET 2005?
Jul 9, 2006Hi,
I would like to use a stroed procedure within another stored procedure ( nested sp )
in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as
connection string, I wont be able to use this connection for another sqlconnection
object. Because its already open. and If i try to use a regular connection string
("server=localhost;...") it will raise a security permission error. Having this
problem, Im not able to use nested stored procedures. Would anyone please give me a
hint how to resolve this issue?
SQL Server 2005 Stored Procedures Running On Server 2003 Machines
Jan 20, 2006Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
SQL Server 2005: Copying Tables And Stored Procedures Between Databases On Same Server
Mar 5, 2008This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
Stored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Migrating SQL Server 2000 Stored Procedures To SQL Server 2005
Dec 20, 2007I have successfully moved my data from a SQL Server 2000 hosting site to a SQL Server 2005 hosting site. I Made a backup of my database using Enterprise Manager (2000) and imported the database tables using SQL Server Management Studio (2005). I do not know how to move the 25 or so stored procedures that I have in SQL Server 2000. I have a very short amount of time to figure this out and am hoping that someone can give me a brief step by step answer on how to get this done. I would appreciate any information you can provide. Thank you!!
View 5 Replies View RelatedControl Access To SQL Server 2005 Database Only Through Stored Procedures Issue
Apr 14, 2008
Hello,
I have a database (SQL 2005) with two schemas (dbo and s1) and with tables defined in dbo and tables defined in s1. The stored procedures are also defined in both schemas, some of them in dbo some of them in s1. Some of the stored procedures query tables from dbo and s1 at the same time.
I want to have a new db role with access to the database only through sps and no other access read/write to the tables. I created a new db role and granted execute permission to it and assigned a user to it.
When I execute stored procedure defined in dbo with query against dbo tables, it works as expected.
However, if I run stored procedure defined in s1 with query to table in dbo, I receive error about missing select permission for the table in dbo. I am not sure why, but I can assume there is an issue with the ownership chain.
I can grand read/write permission for the tables, but this will break our original requirement for limited access to the db only through sp.
The other option is to have another role r2, with read/write, privilege and to use EXECUTE AS r2 in the sp.
I would like to ask first why the error for missing select permission happens and is there another way to have role restricted to only execute permission for all stored procedures.
Thanks,
IT
How To Copy Sql Express Tables && Stored Procedures Into Remote Full Sql Server 2005
Feb 13, 2006Hi all,
I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .
my web site is ready
I didn't have problem to upload my site to my hoster.
Now I want to upload all my tables and my stored procedure create locally with VWD express
How can i do it ?
NB: I know i can't design DB (create/modify tables and stored proc) with express edition
thank's for your help
Oracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedCalling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Stored Procedures In Sql 2005 Vs. 2000
Jun 11, 2007Hi!
I use sql 2005 express on my computer for testing. The server that hosts my site is a sql 2000 server. Therefore I tuned the one database I use down to sql 8.
When I create stored procedures from web developer express, to the local sql 8 database I have in my sql express, it works nice. But when I copy all my files and my stored procedure to the webhost, it doesn't work.
Do you guys have any suggestions?
Thanks in advance.
Stored Procedures In SQL 2005 Express
Feb 26, 2006Is it possible to create and use stored procedures in SQL 2005 Express? As noted by someone else in an earlier post, you can right-click the stored procedures folder, get a template, code a stored procedure, and save it to a project file. However, whereas the earlier thread suggested that one could save the sp to the database by clicking !Execute, this does not work for me and I can find no other obvious way to make the sp accessible to my code-behind procs. Any suggestions will be greatly appreciated!
View 1 Replies View RelatedStored Procedures In Sqlserver 2005
Oct 12, 2006Hi 2 all,
How to create stored procedures using a Wizard in sqlserver 2005 (Like from "Run a Wizard" in sqlserver 2000)
Please Helpme.
Thanx in advance.