Stored Procedure Create & Execute Permissions
Aug 27, 2002
In our development and test environments the developers need to create and execute stored procedures as dbo without having any other dbo permissions. If I place them in db_owner, they have too many permissions. Is there a way to address this situation?
I'm also curious how other companies address the subject of creating stored procedures in development and test environments. If I give developers create and execute permission in a database, all objects would be created as JohnDoe.storedprocedurename instead of dbo.storedprocedurename. Any help in this area is appreciated.
Dave
View 1 Replies
ADVERTISEMENT
Nov 21, 2005
Bit of an emergency!
I do not have direct access to our SQL Server but I have full FTP access to the web server and have the db Username/passwords.
I need to grant execute permissions on a stored procedure, can I do this from an asp/ASP.NET page?
The DB guys take 24 hours to run a script against the database!
Any help would be greatfully recieved.
Rich
View 1 Replies
View Related
Apr 28, 2015
I have stored procedures. I have heard that it is a best practice to use stored procedures to encapsulate some SQL statements and then grant permissions to execute the stored procedure. But when I try this and use EXECUTE AS to test it out, the user in question gets errors about not having access to some of the underlying objects.
How does this best practice work? If I need to grant the user permissions to the underlying objects anyway, I am not sure why a stored procedure is considered best practice in this regard.
View 8 Replies
View Related
Apr 30, 2008
A simple one (not for me)
MS SQL Server 2005
Which permissions do I need to have (as a User) to create a Stored Procedure
Which other entity's properties do I need to change in order to create a Stored Procedure
Thanks
View 2 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Jul 20, 2005
I am trying to create a dynamic SQL statement to create a view.I have a stored procedure, which based on the parameters passed callsdifferent stored procedures. Each of this sub stored procedure createsa string of custom SQL statement and returns this string back to themain stored procedure.This SQL statements work fine on there own. The SQL returned from thesub stored procedure are returned fine. The datatype of the variablethat this sql is stored in Varchar(I have tried using nvarchar alsosame problem).If I have more that 6 SQL statements concated then the main SQL getscut off. It doesnt matter in what sequence I create the main SQL.Here is the Stored procedure/**********************************************//*Main Stored Procedure *//**********************************************/CREATE PROC sp_generate_invoice1 @prev_date NVarchar(1000) ,@prev_month NVarchar(32)ASDECLARE invoice_driver_cur CURSOR FORSelect driversid From Invoice_driversOpen invoice_driver_curDeclare@C VARCHAR(8000),@L_args Varchar(8000),@@sqlstmt Varchar(8000),@L_driverid Int,@L_rowcount IntSET QUOTED_IDENTIFIER ONSET TEXTSIZE 32768Set @L_rowcount = 0-- Drop the previous ViewIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_NAME = 'custom_invoice')DROP VIEW custom_invoiceFetch Next From invoice_driver_curInto @L_driverid-- Create the new ViewSet @L_args = N'Create View custom_invoice As'--Select @L_driveridWHILE( @@FETCH_STATUS = 0)BEGINSet @L_rowcount = @L_rowcount + 1select @L_driveridIf @L_driverid = 2BeginExec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @L_args + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 3BeginExec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 4BeginExec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 5BeginExec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 6BeginExec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 7BeginExec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 8BeginExec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 10BeginExec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,@@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndPrint @CFetch Next From invoice_driver_curInto @L_driveridContinueEndClose invoice_driver_curDeAllocate invoice_driver_curExec (@C)--EXEC sp_executesql @CGO/**********************************************//*Sub Procedure sp_invoice_driver2 *//**********************************************/CREATE PROC sp_invoice_driver2 @args NVarchar(1000), @prev_monthNVarchar(100),@sqlstmt Varchar(8000) OutputASSET QUOTED_IDENTIFIER ONSET @sqlstmt = ' Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''andfee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''GO/**********************************************/This is what the Print Statement give:/**********************************************/Create View custom_invoice As Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September ' Union Select 2,(SELECT Drivers.Description),(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))/ 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September' andDrivers.Driversid = 3 Union Select 3,(Select Drivers.Description From Drivers Where DriversID = 4),Count(*) * Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 4 )As decimal(6,2)) / 12,(Select Drivers.CurrencyFrom Drivers Where DriversID = 4)From Fund Union Select 4,(Select Drivers.Description From Drivers Where DriversID = 5),(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +BillingReport.Man_Reg_Transexch +BillingReport.Man_Allo_Purch +BillingReport.Man_Allo_Red +BillingReport.Man_Allo_Transexch +BillingReport.Man_Allo_Adj_Purch +BillingReport.Man_Allo_Adj_Red +BillingReport.Man_Allo_Adj_Transexch +BillingReport.Man_Adj_Purch +BillingReport.Man_Adj_Red +BillingReport.Man_Adj_Transexch ) ) +(Select Sum(Cast(satuscnt As int ))From Awd_stubWhere CurrentMonth = 'September'))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 5 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 5)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 5,(Select Drivers.Description From Drivers Where DriversID = 6),( Sum( BillingReport.Auto_Reg_Purch +BillingReport.Auto_Reg_Red +BillingReport.Auto_Reg_TRansexch +BillingReport.Auto_Allo_Purch+BillingReport.Auto_Allo_Red +BillingReport.Auto_Allo_Transexch+BillingReport.Auto_Allo_Adj_Purch+BillingReport.Auto_Allo_Adj_Red+BillingReport.Auto_Allo_Adj_transexch+BillingReport.Auto_Adj_Purch+BillingReport.Auto_Adj_Red+BillingReport.Auto_Adj_Transexch )+(Select Sum(Cast(Processed_msg As Int))From XML_messagingWhere CurrentMonth = 'September'))*(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 6 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 6)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 6,(Select Drivers.Description From Drivers Where DriversID = 7),( ( a.Accountholder_Active_Accounts -(select accountholder_active_accounts from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ) )+( a.Accountholder_Zero_Balance -(select accountholder_zero_balance from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 7 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 7)FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,(Select Drivers.Description From Drivers Where DriversID = 8),( Select telephone From cfxbill Where currentmonth = 'September') *(Cast((select feThanks for any helpMD
View 4 Replies
View Related
Jul 22, 2014
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
View 1 Replies
View Related
Apr 24, 2007
HI,
would like to know how to give execute permissions for all the stored procedures in a database at one shot. please advise.
View 6 Replies
View Related
Jul 23, 2005
How can i add Execute permissions on the Stored Procedures under thecreated user permission iusing SQLDMO ?
View 1 Replies
View Related
Sep 20, 2007
Hey guys,I'm pretty new to SQL configuration, and I need to give EXECUTEpersmissions for one of the SQL user roles. I am running SQL 2005Management Studio Express - free version. I found the list of mystored procedures, but I can not locate any permissions screen. Cansomeone help point me in the right direction? Thanks!
View 7 Replies
View Related
Sep 17, 2007
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
Oct 6, 1999
I have revoked an update stored procedure permission to a SQL user. The first time the user tries to execute the stored procedure he gets the error "Execute permission denied." But if he attempts it a second time the stored procedure will execute with success. I want to deny the user EXEC on the stored procedure forever. Does anybody have any advice to make this happen with success? Thanks.
View 1 Replies
View Related
Jun 7, 2002
I have created a Stored Procedure that will not insert into a particular table.
Yet, when I run the same code in Query Analyzer it runs as it should and completes the Insert.
I have tried to both recreate the SP and searched for authorization issues, with no luck.
Any suggestions?
View 1 Replies
View Related
Jun 12, 2008
What role does a user have to be in to execute a stored procedure?
I am trying to run an exec statement from a web app and am getting "The EXECUTE permission was denied".
Thanks.
View 3 Replies
View Related
May 4, 2006
Help!
I usually use SQL 2000 at work but upon deciding to work from home have installed and setup SQL express 2005. I use the management studio to write table and sps but for new sp I cannot find how to allow permission. I have been able to allow permission for all sps on the database I restored that I am now working on and have successfully allocated permissions to a new table but cannot do the same with the sp. The sp is viewable in the database but permission is denied when attempting to execute via my ASP script.
Any ideas??
Error Type:
Microsoft SQL Native Client (0x80040E09)
EXECUTE permission denied on object 'procBannerSlotList', database 'HotLizardWebsite', schema 'dbo'.
View 1 Replies
View Related
Aug 1, 2006
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler.
PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.
View 3 Replies
View Related
Mar 14, 2007
I am using SQL 2000 with the Server Enterprise and the Query Analyzer programs. Almost everytime I create a new Stored Procedure, I forget to go into Server Enterprise and grant Execute permissions to my users.
Is there any way in a Stored Procedure to set the permissions when the Procedure is created?
View 4 Replies
View Related
Jan 16, 2002
Hi,
I am testing a method for users to only have the ability to execute only stored procedures that return data and not be able to execute procs that modify data. For testing purposes I have created a 'select' procedure and an 'insert' procedure. I created a user with db_datareader and execute permissions on the two procs and I was still able to execute the 'insert' proc as this user. I also attempted to deny insert permissions on the referenced table to no avail.
Short of establishing a role and granting execute permission to the appropriate procedures, is there a simpler way to do this?
Thank You
Michael
View 1 Replies
View Related
Nov 29, 2007
Hi All,
For listing login details we have sp_helplogins 'LOGIN NAME'
In the same way i want ti know for particular procedure.
can any one help in this.
Thanks in advance.
malathi
View 4 Replies
View Related
Jul 23, 2005
I have written an stored proc that reads from a text file and executesthe script as dynamic sql.If the text file contains malicious code,I want to be able to detect itand prevent the stored procedure from executing.I've tried revoking delete,insert,update rights all tables in thedatabase to the user .I then granted execute rights to the stored procedure for the sameuser. But the user is still able to delete a record from the table byexecuting the stored procedure.Is there any means to I revoke,insert,delete ,update rights to a storedproc?
View 2 Replies
View Related
Jul 20, 2005
Is there any way to allow a user to use the xp_cmdshell extendedstored procedure without giving that user execute permissions toxp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as thedbo) create a stored procedure called sp_send_err:CREATE PROCEDURE sp_send_err @CompID varchar(20) ASdeclare @strCMD varchar(255)select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + """ERROR!""', no_output"execute (@strCMD)GONow lest say I give "user1" execute permissions on sp_send_err, but nopermissions on xp_cmdshell. When I run sp_send_error I get thefollowing error:"EXECUTE permission denied on object xp_cmdshell, database master,owner dbo".Why doesn't this work? What else can I do?
View 1 Replies
View Related
Jul 20, 2005
We are running SQL Server 2000 Developer Edition. I don't want tomake the developers the sysadmin or even the dbo in the userdatabases. Is there a way to give them access to only view thepermissions for the stored procedures in the user database withoutmaking them dbo?When I take them out of the db_owner role, when they open a storedprocedure they no longer see the permissions tab. I would like forthem to see the permissions tab and be able to view the permissionsbut not change the permissions.Is that doable?
View 1 Replies
View Related
Nov 27, 2006
hello all....can anyone tell me how can i manage the stored procedure permissions in SQL Server Express. Untill now i have developed in SQL Server 2000 and there i used SQL Enterprise Manager for this problem..in SQL Express i can't handle it...Manny thanks.....
View 3 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
May 12, 2008
I'm getting a strange error and I've run out of places to look to fix it. I'm running the following statement when connected as APP_USER in SQL Server Managment Studio (SSMS).
select * from cs.dbo.order
I get the following error.
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'ORDER', database 'CS', schema 'dbo'.
Even after running the following as SA, the result is the same
grant select on cs.dbo.order to APP_USER
Running the query as SA or as a user with datareader permissions works fine. The APP_USER can also select from another table in the same database and schema without error. The APP_USER has SELECT, INSERT, and UDPATE permissions on the table. A second user with only SELECT gets the same error.
I've tried removing and reapplying the permissions with no luck.
Searching for this problem all the examples I find are related to stored procedures (permissions not working the same on dynamic SQL). However, I'm not using a stored procedure. This is plain SQL in a query window in SSMS.
Any help on where else to look for the cause of this would be greatly appreciated.
View 3 Replies
View Related
Apr 24, 2008
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
View 5 Replies
View Related
Aug 21, 2006
I'm having a problem replicating stored procedure permssions accross two sql server 2005 servers, using transactional replication. When I replicate tables, it's easy enough to send the permissions over from the subscriber since there is an option under the articles properties for "Copy permissions." I can't seem to find any such option for SPs, UDFs or views. Am I missing something?
So the other idea I had was to create a script to grant the proper permissions, and run it using sp_addexecscript to my publication. Currently I have two one-way transactional publications. One publication for all my tables, and another for SPs, UDFs and views. I can run sp_addexecscript on the publication containing all my tables with out a problem, but when I run it against the publication containing my SPs, UDFs and views I get the following error:
Msg 21332, Level 16, State 1, Procedure sp_MSrepl_addscriptexec, Line 57
Failed to retrieve information about the publication : SP_UDF_Views_transactional. Check the name again.
I'm not sure why I'm getting this message. If anyone can help me out, I'd greatly appreciate it.
View 11 Replies
View Related
Feb 9, 2007
My Production servers are SQL Server 2005 x64. I would like to allow my developers the ability to look at permissions on production stored procedures but not be able to change those permissions or alter the production code. What has to be set to allow this sort of security.
View 8 Replies
View Related
Dec 12, 2007
I am using SQL 2005, and I am wondering if there is a way to set up a role such that when a new stored procedure is created, the role will automatically be given "execute" permission on that stored procedure.
We have our own dedicated server, but I also administer a database on a shared server (on DiscountASP.net), and they have it set up that way -- when a new stored procedure is created, the user set up by DiscountASP automatically has EXECUTE permission on that procedure.
I keep trying to duplicate this on our dedicated server, but to no avail. I would like to make it so a specific role always has execute permission on every new stored procedure, without having to explicitly add permissions to the role using the properties of the stored procedure.
Is this possible? It seems like it should be, but I can't figure out how. (I'm primarily a programmer, not a DBA, so apologies if this is a dumb question.)
View 3 Replies
View Related
May 16, 2008
Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated.
Thank you,
Dave
View 6 Replies
View Related
Nov 6, 2007
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()
View 3 Replies
View Related
