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?
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
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...
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'''
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)
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...
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.
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.
I 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
Been 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.
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL, N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT', @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.
Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.
I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.
Snippet that is not working:
declare @db_name varchar(80)
declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.
Hi I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using EXEC sp_execute sqlstmt. Here is piece of code.
DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' OPEN c1 FETCH NEXT FROM C1 INTO @v_TableName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @v_SQL= 'DROP TABLE ' + @v_TableName --EXEC @v_SQL PRINT @v_SQL IF @v_Error<>0 BEGIN SELECT @ErrorCount=@ErrorCount+1 PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName --GOTO ErrorHandler END FETCH NEXT FROM C1 INTO @v_TableName END CLOSE c1 DEALLOCATE C1
hi everybody How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server. ie a string can contain insert into a table statement,delete a row from a table statement, update etc. Thanks all of You
Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.
I'm using sp_ExecuteSQL and have hit the 4000 character wall
Hi all,Can sp_executesql used inside a user defined function, itried but it has compiled well, but when i call the functio it showsOnly functions and extended stored procedures can be executed fromwithin a function.What i have went wrongThanks in advancethomson
I have a full sql statement which was generated dynamicly, and need toexecute that string and then take the output and generate aspreadsheet document based on the output. I'm new to sql and the bookI have doesn't really explain much. Anyone with an example of theirwork would be appreaciated.thank you.
(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @callingQueue + ' INTO @msgTable WHERE conversation_group_id = '
+ CAST(@conversationGroup AS char) + '), TIMEOUT 2000'
EXEC sp_executesql @SQL, N'@msgTable TABLE output', @msgTable out
I get the following message:
Msg 137, Level 15, State 2, Procedure CENTRAL_Queue_Processor, Line 92
Must declare the scalar variable "@msgTable".
I have decalred the variable but it is a table variable, this leadds me to believe sp_execute sql only supports scalar varibles not table variables, BOL does not say yes or no in this respect.
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
Hi all, I just wanted to know why this doesn't work: if @1's values is computer---------------------------------------------------------------------------------------------------------- BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ' + '''%@x1%''' + ' OR notes like ' + '''%@x1%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%@x1%' OR notes like '%@x1%'-----------------------------------------------------------------------------------------------------------But this works: BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ''%''+ @x1 + ''%'' OR notes like ''%'' + @x1 + ''%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%'+ @x1 + '%' OR notes like '%' + @x1 + '%'---------------------------------------------------------------------------------------------------------------I just don't get it ?? Doesn't sp_executesql just replaces the @x1 with @1?
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
