Executing SP_SpaceUsed For Tables In A Other Database With EXEC

Jul 20, 2005

Hi

I'm executing SP_SpaceUsed in a stored procedure like this :

Exec ('SP_SpaceUsed '+ @table)

This works great but when i want to execute it for a table in a other
database i'm running in to troubles. Things i tried is this :

Exec ('USE <DB> ; SP_SpaceUsed '+ @table) -->not working (uncorrect
syntax)

Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @table) -->not working
(uncorrect syntax)

Exec ('SP_SpaceUsed <DB>.dbo.'+ @table) --> not working (uncorrect
syntax)

Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @table) -->not working
(uncorrect syntax)

Could someone give me a clue


Thanx,

Hennie de Nooijer

View 1 Replies


ADVERTISEMENT

How Do I Run Sp_spaceused For All Tables In A Database?

Oct 18, 2007

Hi,

I am struggling to understand how to run the sp_spaceused sproc for all tables in a database.

I know how to use it for one table but how would I replicate it automatically for each table in a given database?

Regards,

Ian.

View 12 Replies View Related

EXEC Sp_msforeachtable 'sp_spaceused ''?'''

Feb 25, 2008

What does the following SQL code mean?
 EXEC sp_msforeachtable 'sp_spaceused ''?'''

View 2 Replies View Related

Error While Executing A A Query String Using EXEC Statement

Sep 18, 2007

Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query



Code Snippet

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a



To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below






Code Snippet

@Category - Will be passed as a parameter to the stored proc


DECLARE @vsBulkSQL VARCHAR(MAX)

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @vsBulkSQL - This prints the folliwing statement


INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a


Exec @vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.






Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

View 4 Replies View Related

Sp_spaceused Database Size

Oct 19, 2004

How is it that the value returned by sp_spaceused is larger than the actual database size and unallocated space be negative? For example sp_spaceused retruns the following for one of our databases.

database_size = 52022.31 MB
unallocated space = -16462.47 MB

reserved = 69559520 KB
data = 68007688 KB
index_size = 1463456 KB
unused = 88376 KB

That does not make much sense to me how the reserved size and even data size can be larger than the database_size.

This database was 85 gigs yesterday but we were running extremely short on disk space so I was forced to do a shrink. I did update stats for the database as well.

Thanks much.

View 9 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

SQL Server 2008 :: Using EXEC Functions And Temp Tables?

Jul 14, 2015

here's an example of what I am trying to do.

--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp

Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.

View 7 Replies View Related

Executing Two Tables And Grabbing It's Data.

May 2, 2008

Hi,

I am passing a parameter and executing two tables and grabbing it's data.. In the future I will put the code into a store-procedure.

--Exec Table 1
declare @id varchar(20), @MEMBER_ID varchar(20)
set @id=null
set @MEMBER_ID ='55555' --ie. 55555

Select id from emp Where MEMBER_ID = @MEMBER_ID

--Okay, Next I need to execute another table and pass in the id
--that was selected from the emp table.

SELECT EMAIL FROM moreInfo WHERE id = @id

Currently, the emp table displays ie. 100 records that matches the member id 55555.. But the second select is empty.. And I need to display email data for the 100 records that were selected from the emp table..

I hope is it not confusing what I am trying to do..


Please tell me how to do it..

Thank you...

View 5 Replies View Related

Executing Dts Package Through Asp.net Creates Tables But No Data!

Sep 25, 2007

hey out there,
just started playing with this so bare with me...
i've used the data export wizard to create a dts package in sql server 2000. the package takes selected tables, creates an access database and then dumps all the data into the tables.
it works fine when i run the package in enterprise manager, but when i call it from asp.net (vb.net) the access database gets created, the tables are created but it fails to dump the data!
this is my code:1 Public Sub executeDts()
2
3 Dim oPkg As DTS.Package2
4 oPkg = New DTS.Package2
5 Dim oStep As DTS.Step
6 Dim sMessage As New StringBuilder
7
8
9 oPkg.LoadFromSQLServer("serverNameHere", "userNameHere", "passwordHere", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , "packageNameHere")
10
11 For Each oStep In oPkg.Steps
12
13 sMessage.Append("<p> Step [" & oStep.Name & "] ")
14
15 If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
16
17 sMessage.Append(" failed<br>")
18
19 Else
20
21 sMessage.Append(" succeeded<br>")
22
23 End If
24
25 sMessage.Append("Task """ & oPkg.Tasks.Item(oStep.TaskName).Description & """</p>")
26
27 Next
28
29 Response.Write("sMessage = " & sMessage.ToString & "<br/>")
30
31 oPkg.Execute()
32
33 oPkg.UnInitialize()
34
35 oPkg = Nothing
36
37 End Sub
38

am i missing a step?! it seems very odd that the tables are created but the insert fails...
any advice anyone can offer would be great!
cheers,
jake

View 2 Replies View Related

Integration Services :: Get Data From Source By Executing Set Of Queries That Have Temp Tables

Jul 29, 2015

I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).

So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )

View 2 Replies View Related

Exec Stored Proc In Different Database

Apr 17, 2001

I have a set of stored procedures copied into several databases (similar table structures but different data). I would like to avoid maintaining so many versions of the same procedures, so want to put the stored procs in a common database and execute it from there, e.g.
exec common_db..the_procedure
However, when you do that, the procedure executes in the context of common_db, not in the context of the calling proc's database. Is there a way of calling a procedure so that the calling proc's context is used?

View 1 Replies View Related

Problem With Backup Exec 11d Database

Oct 30, 2007

Having a problem with my backup database.

Tonight I got this error:

Msg 8921, Level 16, State 1, Line 3

Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Msg 824, Level 24, State 2, Line 3

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55555545). It occurred during a read of page (1:934) in database ID 12 at offset 0x0000000074c000 in file 'E:SomefolderSomedatabase.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Tried to restore from my local .bak file and it was giving me the same error and since I cant get my database back online I cant run the restore from tape. I did some online research and found this command:

EXEC sp_resetstatus 'BEDB';
ALTER DATABASE BEDB SET EMERGENCY
DBCC checkdb('BEDB')
ALTER DATABASE BEDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('BEDB', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE BEDB SET MULTI_USER

Didnt correct the problem and produced the same error above.

This has happened once before and I rebuit the entire backup server. That seems to not "fix" the problem because it has happened again. Does anyone have a suggestion. Keep in mind that the storage for the database is on iSCSI if that means anything but I have 11 other databases (including a BES Database) that never have any issues.

OS: Windows 2003 Server Standard Edition R2 SP2
SQL: SQL 2005 Enterprise Edition SP2

View 1 Replies View Related

Seagate Backup Exec Will Not Copy DAT Database Files

Feb 10, 1999

We are having a problem with trying to backup the database device and log DAT files located in the MSSQLData directory.
The Seagate Backup Exec. states that the files are busy and skips them during its backup cycle. It skips all the devices in the
directory.

Any suggestions?

View 3 Replies View Related

Sp_spaceused

May 28, 2004

When I run sp_spaceused I will get something like:

database_name . database_size . unallocated space
------------------ ----------------- ------------------
. . . DBA . . . . . . . 2.50 MB . . . . . 0.07 MB


reserved . . . . . . data . . . . . . . index_size . . . . unused
------------------ ------------------ ------------------ ------------------
1720 KB . . . . . . . 888 KB . . . . . . 648 KB . . . . . .184 KB

Is there any way to load this info into a Table?
I'm having trouble because there are (2) result sets returned.

View 1 Replies View Related

Sp_spaceused

Oct 18, 2000

I am using the sp_spaceused system stored procedure to identify tables in my database that are large. I have two questions. Can anyone help me sort this list. I assume this is returned in clustered order, but I want to order the list by rows. Also, when I run this sp_spaceused stored proc, on some of my tables I get a negative value returned under the 'index_size' and 'unused' columns in my result set. How can this be.

View 1 Replies View Related

Sp_spaceused

Jan 12, 2000

I am looking for an accurate way to calculate database size and
unused space.

I do NOT want the log size calculated into the database size or
the space unused.

Thank you

View 2 Replies View Related

Sp_spaceused

Sep 16, 2002

on some of our sql 6.5 databases the space available is shown as 0MB. I realise that I must run sp_spaceused to get an accurate result, but the results I then get are slightly confusing. All I want to know is the space left in both the database and the log - can I get a reading for both?. Sometimes the unused space is shown as a negative number. Can anyone advise since books online is fairly unspecific.

(the total script that i run incidentaly is:

exec sp_spaceused
go
DBCC checktable (sysindexes)
go
DBCC checktable (syslogs)
go
exec sp_spaceused @updateusage=true
go)

Thanks

View 3 Replies View Related

Sp_spaceused

Jul 27, 1999

Can someone please explain the outputs below of the sp_spaceused stored procedure.

database_name database_size unallocated space
------------------------------ ------------------ ------------------
Employees 5054.00 MB 1441.93 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3698758 KB 2770552 KB 917274 KB 10932 KB

View 1 Replies View Related

Sp_spaceused

Jun 29, 2001

hi,
I used sp_spaceused 'owner.tablename' and it shows index size as 24kb whereas there is no index on this table.
I thought may be it's not showing the right statistics so I update the statistics but it is still showing the same .
what is happening?
I am using SQl 2k/Win2k.
TIA.

View 2 Replies View Related

Sp_spaceused

Sep 7, 2004

can someone please explain what reserved, data and unused represent?

View 4 Replies View Related

Sp_spaceused

Jul 20, 2005

Hi,In our environment sp-spaceused returns:Allocated: 500Unallocated: -100Enterprise Database Taskpad shows that our total database size = 400 MB(320 used; 80 unused)Windows Explorer shows also that MDF file is more or less 400 MB.What does sp_spaceused exactly ???How do I SELECT the 320;80 of the taskpad with a query ???Greetings,Arno de Jong, The Netherlands.

View 1 Replies View Related

Executing On A Different Database.

Nov 8, 2006

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?



View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Question On Sp_spaceused

Nov 2, 1999

What does sp_spaceused show nonzero sizes for data, reserved and index size when the table is empty?
It also does this on 6.5.

View 1 Replies View Related

Sp_spaceused Riddle....

Mar 15, 1999

I have ran sp_spaceused (SQL 6.5 SP4) on my database (4000MB Data, 500 Log) and received the following output for "Reserved" : 4412390 KB. Note that this is more than 4000MB. I checked and made sure my data and log were truly separated...

Just to make sure, I added 2000MB more data space. Sp_spaceused now returned the following for reserved: 3493220 KB.

I am wondering why the allocation dropped, while no user activity was taking place during the process of growing the database....sp_spaceused reported data size first at 2725532, then at 1806314. Any ideas? A bug? Did the database fill up and begin using log pages...?

Any help is GREATLY appreciated...

Thanks!

Dean

View 2 Replies View Related

Sp_spaceused Result

Oct 26, 2006

Hi,
I ran sp_spaceused against a DB table, and got -160K in the field "unused". If anybody can explain what this negative number means?
We have a problem with performance. It's extremly slow. The table contains 600,000 records. Even simple select * from <table> (table scan) takes 6 minutes. This is SQL Server2000.
Any help is appreciated.

View 5 Replies View Related

Enhanced Sp_spaceused

Aug 16, 2005

I can't remember if I posted this already or not:

CREATE PROCEDURE sp_space @sortbyrows bit=0 AS
SET NOCOUNT ON
select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from sysindexes with (nolock) where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY CASE WHEN @sortbyrows=1 THEN sum(CASE WHEN indid<2 THEN rows END) ELSE sum(reserved)*8 END desc

Usage:

EXEC sp_space --show stats sorted by reserved space size
EXEC sp_space 1 --show stats sorted by row count

It basically displays the results of sp_spaceused, but broken out by each table, and it rolls it up to a grand total. Naturally, for the most accurate results you should run DBCC UPDATEUSAGE before running this sproc.

View 10 Replies View Related

Sp_spaceused Equivalent In CE?

Oct 3, 2007

I am looking for a way to programatically determine the size of the data contained in a table, much like the sp_spaceused proc in SQL Server.


Thanks.

View 3 Replies View Related

Sp_spaceused Question !

May 10, 2007

name
rows
reserved
data
index_size
unused

Table1
2553136
294424 KB
293176 KB
1072 KB
176 KB





When I run sp_spaceused on a table called Table1, I get numbers as shown above.



I have a clustered Index on a datetime column AND that is the only index on the table.



So according to my calculations Index Size should be 2553136 (No. Of Rows) X (8 (DataType of DateTime) + 4 (Uniquifier) )



So the number I should be seeing in the Index Size column = 2553136 * 12 = 30637 KB, why am I only seeing 1072 KB ?



Where am I going wrong in my calculations.



To be more specific -- how do I calculate the Size Occupied by Indexes on a Particular Table ??



Thanks

View 5 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

Executing SQL Statements In Another Database

Apr 9, 2008

I have a stored procedure named usp_CreateSomeAdminObjects that resides in say a database named AdminStuff (ok, fictional but it gets the point across :) ). This proc generates a series of dynamic SQL statements (DROP TRIGGER, CREATE TRIGGER and EXECUTE sp_settriggerorder) against tables that reside in different databases. However I am unable to change the context of the database from AdminStuff to the database I am cycling. Any suggestions on how I can have execute this dynamic SQL in the context of a specific database (other than the one the proc resides in)?

I can not put the USE statement in a stored procedure. Attempts to use sp_executesql, EXECUTE and even run the script through sqlcmd using either (a) the -d option or (b) a USE <dbname>; statement as part of the -q parameter have all been unsuccessful.

Heck, even if I execute this code through SSMS it does not work as I need it to:

USE OtherDatabaseName;
EXEC DBA.dbo.usp_CreateSomeAdminObjects

I confirmed this by adding these statements to the proc:

/* Gets list of tables from the current database */
SELECT name, create_date FROM sys.objects WHERE type = 'U' ORDER BY name

SELECT DB_NAME();

These statements return a list of user tables that reside in AdminStuff and not OtherDatabaseName (the same goes for the DB_NAME() statement.

Any suggestions on how I can accomplish what I'm looking to do - have this proc execute dynamically generated DDL scripts for tables that reside in a different database. I'd rather not name this procedure sp_CreateSomeAdminObjects and compile it in the master database. If that is my only option I will but figured I'd check out other options first.

View 10 Replies View Related

Quick Question On Sp_spaceused

Jun 10, 2004

I executed sp_spaceused on the db ang got following results
database_name:myDB
database_size: 1017.75 MB
unallocated space :104.13 MB

reserved : 309752 KB
data : 306832 KB
index_size : 1936 KB
unused : 984 KB

If we add reserved,data,index_size and unused up , we will get around 600MB.But the database has size of 1G. Could anyone tell me why?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved