Need to have 'Use Master' statement in one stored procedure from a Admin database to execute some task only in Master database could be performed.
error: Use database statement is not allowed in a procedure or trigger.
Is there a way to bypass this error?
I know my last result will be to put this stored procedure in Master, which I really don't want to...
thanks for help
-D
I am trying to get file space usage information for all of the databases in my server and store it in a table to collect a history of spce usage. I know that I can get this on a per database basis if I use DBCC(SHOWFILESTATS). So, I would issue a USE statement and then a DBCC statement inserting the rows into a temp table for later processing. Since databases can come and go, I would like to loop through the records in sysdatabases and for each one, issue the USE and then the DBCC via an sp_executesql.
This is all working fine except, the sp_executesql of the USE statement seems to be ignored.
Is there some way to get this statement to take effect?
Is there a better way to get this information - ie is this stored in a table or view that I can't seem to find?
Thanks in advance for your help.
Bob
Is there a way to 'extend' the use of 'Use DatabaseName' sql statement?
I want to build an dynamic sql string such as
'Use '+ @serverName+ '.Master', but it won't accept serverName. just for database within the same server...
thanks
David
Has anyone managed to get this to work?
dbcc rebuildlog (@dbname, 1, 0)
Hi
I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.
Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.
I want to execute the SP withing each db.
Thanks
I have databse called RM-A1 and a log of RM-A1.log
When i run this statement it errors.
select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from RM-A1_log.dbo.sysfiles where fileid = '1'
--This works
select * from dbo.sysfiles
Im using a dynamic statement
set @cmd = N'select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
''MB '' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) '
+ 'else ''PER '' + convert (varchar, growth) '
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles where fileid = ''1'''
exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT
I cannot determine why log name of '-' are erroring all other databases are working.
i have curputed mdf and ldf file for ms sql 2005
i have tried to put the db in standby mode and when i try to run dbcc checkdb or any other command i get this message
Database 'dbname' cannot be opened because its version (611) is later than the current server version (539).
but without standby mode i get this error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Could not find row in sysindexes for database ID 40, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I am in a bind here..
I am getting the message (after do any sort of query) Database context changed to <dbname> I am using PHP as the p language.
Does anyone have any clue on why it is doing that..
Recently my host upgraded there PHP to version 4.3.10 and before the upgrade the queries were working fine.
Im on a windows server that runs PHP and im using a MSSQL database.
I've checked the bug reports on PHP and didn't come up with anything as well as googling the error..
IF you have any insite on this problem please let me know.. It may be a stupid error that I am overlooking on my part.
Thanks in advance
Can anyone help me with this issue? I restored a full backup with NORECOVERY because I need to restore the differentials as well but I have not been able to access the db. No matter what I seem to try i receive the following error:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
Does anyone know what I need to do?
Thank you in advance.
Julio
How can I get
dbname
ip
domain name
Data path
using TSQL.
how can I stop services using TSQL
Emad
Hi!I have a problem. i have created a website with a login page, i have moved all my aspnetdb tables to my remote host. but when iam trying to logon i get this error messages.
EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'dbname', owner 'dbo'.
Someone? I really need help...
Hi,
I'm in need of a few more pointers when it comes to SQL Server 2005 Security setup for accessing a database through IIS web services. There's loads of great advise in these forums which I've followed so pls forgive me if I've missed a post that finally resolves this. I know I'm so close but it feels so very far...
Anyhow, here's the set up:
- Server 2003 / SQL Server 2005 / .NET 2.0 / Visual Studio 2005 / Client XP PC
The story so far:
- Built an ASPX website in VS2005 on a client PC that connects to a SQL2005 db. No probs during development.
- Copied website to WS2005 & configured IIS accordingly. No probs browsing non-db webpages.
The problem:
As soon as I browse to a page that extracts data from the db I get the following error message..
[SqlException (0x80131904): The SELECT permission was denied on the object 'tbl_location', database 'SmartDMA', schema 'dbo'.]
...plus a whole load of code that I've seen plastered over lots of forums.
My configurations:
- The website is in the default SQL Application Pool that has the identity set to 'Predifed: Network Service' (as I'd previously has a similar error; SQLException (blah blah): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection)
- IIS Website ASP.NET is v2.0.50727
- Website Authentication Methods / Enable Anonymous Access (ticked) Username: IUSR_SERVERNAME Password: *********. Intergration Windows Authentication (ticked).
- SQL Server Mngt, Secuirty / Logins / NT AUTHORITYNETWORK SERVICE lists the db in question with dbo as the Default Schema & public ticket as the db role membership.
- connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true" providerName="System.Data.SqlClient"/>
What I've tried already based on forums advise:
- Changing Server Properties to SQL Server & Windows Authentication mode.
- Rather than using Integrated Security I've tried the SQL 'sa' account, but not 100% is I did that right (still got the same error anyway).
The rest of the advise I've read appears to confirm the rest of my settings, but I'm still getting the error.
Can anyone please shed any light on what I'm missing here. Feel free to ask questions on any configuration settings I've missed that may help.
Many thanks in advance..
Hi All,
I have a strange problem with one of my Linked server connections. I am connecting from
SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:
OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".
This probem happens if the table has rows or has no rows (empty table).
I have tried SELECT * and count(*) along with selecting a specific column from the table.
I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.
Any suggestions on what I can try next?
Thanks.
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedI am getting the above error. On server1 I am running a dts package via a sql server job (sql server2005). This has been working perfectly for over a year. This package needs to access a database on server2. The database in question on server2 was recently restored from our produciton environment to refresh the data. Every since thsi time the job on server1 is failing with the above error.
Steps I have taken so far:
droped the user from server2 and recreated it
gave the user sysadmin rights on server 2 and server1
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedBeen looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
View 9 Replies View RelatedIs it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.
If its possible , please provide sample code.
thanks in advance,
Alok.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Hello,i want to use the result set from a stored proc in another stored proc, forexample:create stored procedure proc1 (@x int) asdeclare @tbl (y1 int, y2 int)insert into @tbl values(@ * @x, @x * @x * @x)select * from @tblGO--create stored procedure proc2 (@x int) asdeclare @tbl (y1 int, y2 int)while @x > 0 begininsert into @tbl select (exec proc1 @x) <-- this is my problemset @x = @x - 1endselect * from @tblGO--I know i could use output parameters. But i want to know if something ispossible with SQL-Server?thanks,Helmut
View 4 Replies View RelatedI'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedI am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Hi
I have this stored_proc - which works fine
ALTER PROCEDURE proc_Prod_AdvancedSearch @Title VARCHAR(50) = Null, @Category VARCHAR(8) = null, @ReleaseClass VARCHAR(50) = null, @BuyPrice DECIMAL(8,2) = nullASSET NOCOUNT ONSELECT *FROM ProductWHERE Title = ISNULL(@title, title)AND Category = ISNULL(@Category, Category)AND ReleaseClass = ISNULL(@ReleaseClass, ReleaseClass)AND BuyPrice < ISNULL(@BuyPrice, BuyPrice)Return
except, when i type in something for the @title parameter, unless i type the exact match as title, it returns nothing
i would like it to return results if part of @title is similar to title
ive tried WHERE Title = ISNULL(' %' + @title + '%' , title) but it does the same thing as above
any ideas
cheers
I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd
I also tried
SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.
Im trying to perform an update with a stored procedure thats all working but
an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key
I understand whats going on but how do you update some ones details if its protected this way.
?
I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE) )BEGIN SET @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT @attendanceID = SCOPE_IDENTITY()ENDGO Kindly help with this
View 5 Replies View RelatedHi,
I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT,
I want to create a Geneirc search Proc that can handle a few criteria
I was wondering if it is possible to create a VB like Select case
depending on Information supplied to the stored proc
i.e
@Loc_Thing
@Loc_OtherThing
SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE
Select case @Loc_Thing
Case "Mickey"
LastName = @Loc_OtherThing
Case "Walt"
Company = @Loc_OtherThing
...... etc
is it possible to create a strored proc like this?
I have found a Select case in SQL, but it doesn't work I would like it?
Any Idea's?
This must be a simple one but look all over to find infos on the uses of like % in a store proc and I did'nt find anything. I know it's something wrong with my "%" and where to put the apostrophes.
Please could you check the following code and let me know what wrong with it.
Thanks
ALTER PROCEDURE dbo.GetSearchMultiColumn
(
@strColumnNamenvarchar (50),
@strSearchTermnvarchar (200)
)
as
if (@strColumnName = 'Camera')
begin
SELECT SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4 FROM Customers
WHERE Camera1 LIKE '% @strSearchTerm %' or Camera2 LIKE '% @strSearchTerm %' or Camera3 LIKE '% @strSearchTerm %' or Camera4 LIKE '% @strSearchTerm %'
return 0
end
if (@strColumnName = 'ZMotor')
begin
SELECT SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3 FROM Customers
WHERE ZMotor1 LIKE '% @strSearchTerm %' or ZMotor2 LIKE '% @strSearchTerm %' or ZMotor3 LIKE '% @strSearchTerm %'
return 0
end
if (@strColumnName = 'Stage')
begin
SELECT SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3 FROM Customers
WHERE Stage1 LIKE '% @strSearchTerm %' or Stage2 LIKE '% @strSearchTerm %' or Stage3 LIKE '% @strSearchTerm %'
return 0
end
Hello,
So I created my first SP today which returned data to populate a datagrid. Worked to perfection. Now I'm trying to do a simple login and I'm having a hard time getting it together. I want the user to enter in a username and pass then if user exists, to return their userid and update their last login date. But I can't get it. Any help would be awesome.
Here's my SP...
CREATE PROCEDURE [dbo].[userLogin] @username varchar(50), @pass varchar(50) AS
declare @x int
declare @userid int
if exists (SELECT userid FROM users WHERE username = @username AND password = @pass)
set @x = 1
else
set @x = 0
if @x = 1
UPDATE users SET lastlogin = getdate() WHERE userid = (SELECT userid AS name FROM users WHERE username = @username AND password = @pass)
else
return '0'
if @x = 1
SET @userid = (SELECT userid FROM users WHERE username = @username AND password = @pass)
return @userid
GO
Any constructive criticism on my SP is welcome. Also, should I be using Print or Return if I want to send back a value to my VB code?
And my VB:
Dim user As String = txtUsername.Text
Dim pass As String = txtPassword.Text
Dim objDataSet As DataSet
Dim objAdapter As SqlDataAdapter
Try
Dim cmd As New SqlCommand
'Enter Param's here
Dim myParam1 As SqlParameter = cmd.Parameters.Add("@username", SqlDbType.VarChar)
Dim myParam2 As SqlParameter = cmd.Parameters.Add("@pass", SqlDbType.VarChar)
myParam1.Value = user
myParam2.Value = pass
objAdapter = New SqlDataAdapter
objAdapter.SelectCommand = cmd
objAdapter.SelectCommand.Connection = SqlConn
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
objAdapter.SelectCommand.CommandText = "userLogin"
SqlConn.Open()
Dim str As String = cmd.Parameters("@userid").Value()
cmd.ExecuteNonQuery()
If str = "0" Then
'Error Login Page
Response.Redirect("login_err.aspx")
Else
Session("userid") = str
Response.Redirect("home.aspx")
End If
SqlConn.Close()
Catch ex As SqlException
SqlConn.Close()
End Try