August 29, 2008 at 11:45 am
It's kind of wired error. when I create stored procedure on one of the database I get following error.
String or binary data would be truncated.
but when I create same stored procedure in master database, I don't get an error. Don't know whats going wrong.
appreciated any help.
August 29, 2008 at 12:11 pm
I would check the tables involved for inserts if there is a column not large enough to hold the default value you set in create table statement
August 29, 2008 at 3:20 pm
I don't think that is the case because if that is the case then it should not get created in master database. when I create this store procedure on master or other database it is working fine except one database. It think it is something related to database configuration.
August 29, 2008 at 3:24 pm
It would be easier to help if you posted some code. I can't think of any reason why creating a stored procedure would fail because of truncation.
You may want to check the compatibility level of your database.
Again it would be much easier to provide an answer with some code to look at.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2008 at 4:16 pm
I checked the compatability level and it's SQL Server 2005(90). I don't think code have some issue because it's working on other database. Only one database it is giving error when I try to create it.
August 29, 2008 at 5:05 pm
You may not think code would help, but you haven't gotten much help without it either. Seeing the code may trigger someone's memory.
Have you compared all the database options?
Have you tried creating the procedure with only a small piece and then add more as you have success to isolate the problem?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2008 at 5:13 pm
Sure, here is the procedure. if tried creating this procedure in small segment but it is failing at following area but I can create this table without any problem.
CREATE TABLE #TabelSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
create PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name
-- If omitted, the current database is reported.
, @SortBy char(1) = 'S' -- N for name, S for Size
-- T for table name
/* Returns a table with the space used in all tables of the
* database. It's reported with the schema information unlike
* the system procedure sp_spaceuse.
*
* sp_spaceused is used to perform the calculations to ensure
* that the numbers match what SQL Server would report.
*
* Compatible with sQL Server 2000 and 2005
*
* Example:
exec dbo.dba_SpaceUsed null, 'T'
*/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN
SET @SourceDB = DB_NAME () -- The current DB
END
--------------------------------------------------------
-- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname
, TabName sysname )
SELECT @sql = 'insert #tables ([schema], [TabName])
select TABLE_SCHEMA, TABLE_NAME
from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)
---------------------------------------------------------------
-- #TabelSpaceTxt Holds the results of sp_spaceused.
-- It Doesn't have Schema Info!
CREATE TABLE #TabelSpaceTxt (
TabName sysname
, [Rows] varchar (11)
, Reserved varchar (18)
, Data varchar (18)
, Index_Size varchar ( 18 )
, Unused varchar ( 18 )
)
---------------------------------------------------------------
-- The result table, with numeric results and Schema name.
CREATE TABLE #TabelSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
DECLARE @tab sysname -- table name
, @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR
SELECT [SCHEMA], TabNAME
FROM #tables
OPEN TableCursor;
FETCH TableCursor into @Sch, @tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB
+ ']..sp_executesql N''insert #TabelSpaceTxt exec sp_spaceused '
+ '''''[' + @Sch + '].[' + @tab + ']' + '''''''';
Delete from #TabelSpaceTxt; -- Stores 1 result at a time
EXEC (@sql);
INSERT INTO #TabelSpace
SELECT @Sch
, [TabName]
, convert(bigint, rows)
, convert(numeric(18,3), convert(numeric(18,3),
left(reserved, len(reserved)-3)) / 1024.0)
ReservedMB
, convert(numeric(18,3), convert(numeric(18,3),
left(data, len(data)-3)) / 1024.0) DataMB
, convert(numeric(18,3), convert(numeric(18,3),
left(index_size, len(index_size)-3)) / 1024.0)
Index_SizeMB
, convert(numeric(18,3), convert(numeric(18,3),
left(unused, len([Unused])-3)) / 1024.0)
[UnusedMB]
FROM #TabelSpaceTxt;
FETCH TableCursor into @Sch, @tab;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
-----------------------------------------------------
-- Caller specifies sort, Default is size
IF @SortBy = 'N' -- Use Schema then Table Name
SELECT * FROM #TabelSpace
ORDER BY [Schema] asc, [TabName] asc
ELSE IF @SortBy = 'T' -- Table name, then schema
SELECT * FROM #TabelSpace
ORDER BY [TabName] asc, [Schema] asc
ELSE -- S, NULL, or whatever get's the default
SELECT * FROM #TabelSpace
ORDER BY ReservedMB desc
DROP TABLE #Tables
DROP TABLE #TabelSpaceTxt
DROP TABLE #TabelSpace
August 30, 2008 at 8:56 am
balbirsinghsodhi (8/29/2008)
Sure, here is the procedure. if tried creating this procedure in small segment but it is failing at following area but I can create this table without any problem.
VERY nice try... but, you don't need any temp tables and you sure don't need any cursors for this. Try this instead (I already made the minor tweek to make it run on SQL Server 2k5)...
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
-- SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1,
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
--Jeff Moden
August 30, 2008 at 9:04 am
Damned smiley faces... 🙂
Ok... here it is again without the smiley faces... it's not as "pretty", but it'll do...
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
-- SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN (255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN (255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN (1) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E') pkb --Identifies row for system type
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1,
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
--Jeff Moden
August 30, 2008 at 12:28 pm
balbirsinghsodhi (8/29/2008)
It's kind of wired error. when I create stored procedure on one of the database I get following error.String or binary data would be truncated.
but when I create same stored procedure in master database, I don't get an error. Don't know whats going wrong.
Just to clarify: Are you getting this error when you try to Create it or when you try to Run it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 30, 2008 at 1:57 pm
rbarryyoung (8/30/2008)
balbirsinghsodhi (8/29/2008)
It's kind of wired error. when I create stored procedure on one of the database I get following error.String or binary data would be truncated.
but when I create same stored procedure in master database, I don't get an error. Don't know whats going wrong.
Just to clarify: Are you getting this error when you try to Create it or when you try to Run it?
Not sure why we're trying to correct an error in Cursor code when a set based solution is the way to go.
--Jeff Moden
August 30, 2008 at 4:20 pm
Jeff Moden (8/30/2008)
rbarryyoung (8/30/2008)
Just to clarify: Are you getting this error when you try to Create it or when you try to Run it?Not sure why we're trying to correct an error in Cursor code when a set based solution is the way to go.
If the error is coming from trying to run it, then I agree with you Jeff: Rip the cursor out and do it right.
But if the error is coming just from trying to Create the procedure (which is what the OP said), then that is a different kettle of fish. Whether or not taking the cursor out would fix that problem, I still would like to understand what causes it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 30, 2008 at 4:48 pm
If execute just the create proc code, you get no errors on creation.
If you execute the proc in AdventureWorks, you get no run errors.
What's the problem? 😉
--Jeff Moden
August 31, 2008 at 12:07 am
I get this error when I try to create it.
August 31, 2008 at 12:16 am
Thanks a lot,
I can run the code you have provided but the point is why I am getting this error. The reason I have to fix it because I have some other stored procedure which is also not getting created on particular database. I really don't understand why only particular database giving an error. If I create this stored procedure on other database it's working fine.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply