I Need To Make This Stored Procedure 2005 Compatible
Jun 22, 2006
Hello.
I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.
Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!
--------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure aes.Check_Dependent_Rows_Exist
(@RowID int,
@has_rows int OUTPUT
)
AS
BEGIN
DECLARE @Colname varchar(200), @Tablename varchar(200)
DECLARE @cnt int
DECLARE @temp_row int
DECLARE @owner varchar(25)
DECLARE @ownerid int
DECLARE @lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR
SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'
OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not
SET @has_rows = 0
FETCH NEXT FROM lcur INTO @Colname,@Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_row = 0
SELECT @ownerid = uid from sysobjects where name = @Tablename
SELECT @owner = [name] from sysusers where uid = @ownerid
SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' +
@Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + ''
--print @lstrSql
EXEC (@lstrSql)
SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @has_rows = 1
BREAK
END
FETCH NEXT FROM lcur INTO @Colname,@TableName
END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------
error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.
(1 row(s) affected)
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
View 6 Replies
ADVERTISEMENT
May 1, 2008
Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) )
AS
UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ?
View 2 Replies
View Related
May 30, 2008
suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }
View 2 Replies
View Related
Jan 7, 2007
Hi
In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this
Alvin
View 1 Replies
View Related
Feb 15, 2000
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies
View Related
Mar 29, 2013
I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.
Here is my stored procedure:
USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO
[code]....
View 10 Replies
View Related
Jan 22, 2008
Hi,
I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.
Thanks in advance for any help.
Regards,
Sangeetha
View 6 Replies
View Related
Dec 11, 2006
Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well.
Thanks...
View 1 Replies
View Related
May 8, 2012
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
View 7 Replies
View Related
May 21, 2008
hi,
I need to convert a stored procedure in to a SSIS package, do any body have an idea on this. thanks in advance
View 1 Replies
View Related
Jul 9, 2006
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
View 1 Replies
View Related
Jul 20, 2005
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies
View Related
May 10, 2007
I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies
View Related
Mar 3, 2008
Hi 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.
View 11 Replies
View Related
Jan 24, 2006
Hi ,
do i have to consider any configuration ? Or is a sql 2005 database compatible to sql server 2000 by nature ?
thnx in advance
View 3 Replies
View Related
Oct 9, 2006
Hi, all here,
I use SQL Server 2005 standard edition ,
I choose to use table(table stored in Oracle9i) as source for mining structure, and I use
Oracle Provider for OLE DB(or Microsoft OLE DB Provider for Oracle).I set one column as logic key and this column stored chinese data.Deployment was successful. When I processed the mining structure,
an error happened:
Warning 0x80202066:Data Flow Task :Connot retieve the column code page info from OLE DB provider.If the comopenent supports the "DefaultCodePage" property,the code page from that property will be used .Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
But I created the same mining stucture and processed it successfully in SQL Server 2000. Is it a Compatible bug in SQL Server 2005?
View 3 Replies
View Related
Feb 7, 2006
I'm trying to convert a 6.5 compatible db from SQL Server 2000 to SQL Server 2005. The issue I'm encountering is that:
1. I cannot simply convert the 6.5 compatible db to SQL Server 2005. I tried restoring from a backup - no success.
2. I tried creating the structure in SQL Server 2005 and then importing the data from SQL Server 2000. Problem is - after importing the data, I then tried creating the primary key and indexes - get a time-out error (which I never got with SQL Server 2000.) I also tried creating the primary key and indexes in SQL Server 2005 FIRST before importing - got errors when trying to import and import failed.
I know this sounds like a data problem but I have no problems working with this 3 gig database in SQL Server 2000.
Can anyone please help? This is a pretty serious problem I need to overcome with the SQL Server 2005 conversion.
View 2 Replies
View Related
Apr 19, 2006
Hi everyone,
I'm currently trying to setup a SQL 2k (SP3, build 922) merge replication publisher and a push subscription to a SQL 2005 (RTM release, no hotfixes) subscriber. The distribution database resides on a separate SQL 2k server (SP3, build 1007). I get the error below leading me to believe merge replication is not compatible between versions.
Replication-agentclassname: agent Agent_Name failed. Procedure or function sp_MSupdatesysmergearticles has too many arguments specified..
I say this b/c I've tried SQL 2005 32-bit and x64 subscribers and both give the same error. Anyone have any ideas if this is by design or just a bug that will be fixed later? Thanks.
LSC
View 1 Replies
View Related
Aug 7, 2007
If my client has a SQL Server 2005 Standard Edition license, can they use that license for a SQL Server 2000 Standard Edition installation?
thanks for any help...
View 3 Replies
View Related
Jul 29, 2007
I tried to install SQL SERVER 2005 through SQLEVAL64 (IA64) on Window vista 2007, Intel core 2 duo processor. Like many other software (including my lexmark printer driver), SQL SERVER 2005 does not seem to be working. Anyone has any work around solution or had similar experience ?
View 4 Replies
View Related
Jan 15, 2008
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
View 1 Replies
View Related
Apr 17, 2008
Hi,
I want to get data from Oracle 10g Stored procedure to Reporting Services 2005. I could pass a SQL text and get a record set, but I want to execute a store proc and get the record set.
1. Add New Data Source
2. Choose Type : Oracle and connection tested OK
3. { call Test_Package.Test_Procedure(?) } is it wrong... how to write???
There is an error in the query. ORA-00911: invalid character
Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer.
Question : I tried many methods but unable to solve it...
create or replace
PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;
create or replace PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
)
AS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
END Test_Procedure;
The below site gave some example but i could not solve it... any suggestions greatly appreciated...
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
View 1 Replies
View Related
May 29, 2008
Hi,
Pls let me know if SQL Server 2005 Management Studio (client tools only) is compatible with Windows Server 2000 or not?
Thanks in advance.
Regards,
Bhuvana
View 1 Replies
View Related
Oct 29, 2007
Hi There
Can anyone confirm whether SQL Server 2005 Express edition SP2 is compatible with Windows Server 2003 SP 2? The SQL Server 2005 Express readme only mentions 2003 SP1.
Thanks
Mark.
View 3 Replies
View Related
Oct 29, 2007
Hi There
Can anyone confirm whether SQL Server 2005 Express edition SP2 is compatible with Windows Server 2003 SP 2? The SQL Server 2005 Express readme only mentions 2003 SP1.
Thanks
Mark.
View 4 Replies
View Related
Aug 14, 2007
How to copy only some stored procedure from dev server into prod server in sql server 2005? Should I use import/export wizard or copy database wizard? Thanks in advance.
View 4 Replies
View Related
Oct 15, 2006
Hi,I'd like to know if anyone has had experience upgrading their storedprocedures directly from 7 over to 2005, and what issues they've runinto.Also, if there are any articles available on this subject, couldsomeone please point me in the right direction?Thanks!!
View 2 Replies
View Related
Aug 31, 2006
I create a new stored procedure in SQL SERVER 2005 .It it is stored in " C:Documents and SettingsmmmMy DocumentsSQL Server Management Studio ExpressProjects" as (some_name).sql .it takes this links by default . Then I exeecute it with this command
"exec InsertValueIntoTable ;" BUT when i want to execute with clicking the execute button in the then gives an error "Could not find stored procedure 'InsertValueIntoTable'."
View 3 Replies
View Related
Mar 20, 2007
Dear Sir,
I am from ASP.NET back ground now I am working in SQL Server 2005. I want to debug my stored procedure after coding end. In SQL 2000 there is an option of debug right click on the stored procedure but No such option present in SQL Server 2005 so I am unable to debug my Stored Procedure when any error occur.
Can you please guide me?
Regards
Sandeep Mishra
Bangalore
View 4 Replies
View Related
Aug 7, 2007
I am trying to debug the SQL Server Stored Procedure from .net 2005.
The process is as :
After setting Breakpoints at SP , it cant stop at that break point.
If anybody konws please write in detail.
Shyam.
View 3 Replies
View Related
Apr 20, 2006
Solving one problem leads to another. This post is related to my last one (Can't see stored proc results) so this will look familiar to some of you. This sproc is called by a VB front end. It retrieves a name based on an ID#:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@caseID nvarchar(12)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @caseID)
END
I am also looking for problems on the VB side if it's not here.
It executes fine on the SQL Server side. I took an option to preview the data through the VB dataset that this sproc fills. After entering the required parm, instead of the name I want, I see this:
Type Value
Int32 0
I previewed a view with the same select statement minus the WHERE clause. It worked fine. Any reason why the sproc shows up differently?
Thank you for all of the help.
View 4 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Jun 28, 2006
I am building a Visual Web Developer site with a SQL Server 2005 Express backend. I am trying to create the login page, I have written a stored procedure that takes in the username and password and returns a value which indicates whether the user is valid or not.
My problem now is integrating this stored procedure into the page. In Visual Studio 2003 you could just drag and drop the stored procedure from the Database Explorer window and it would create the neccessary SQLCommand which you can then pass parameters too and recieve return values, i.e:
sqlcommand1.Paramters[ "@username" ].value = TextUsername.Text;
sqlcommand1.Paramters[ "@password" ].value = TextPassword.Text;
sqlConnection1.Open();
sqlCommand1.ExecuteNonQuery();
Int Result = (int)sqlcommand.Parameters["@returnvalue"].value;
sqlConnection1.Close();
I see in VWD there are SqlDataSource's etc, is this what I need to use to do what I'm looking for and how do they exactly work? Ideally what I'm looking for is when a user clicks login, the contents of the 'username' and 'password' text boxes are queried against the database. What controls and configuration is required to achieve this?
Many thanks in advance,
Mervski
View 2 Replies
View Related