Error when running temp table proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error when running temp table proc

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 22:23:51

To add to below I changed types to varchar and re-run and the message still refers to nvarchar? The actual datatypes for all the fields in the DB is nvarchar except userid is int.

Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to int.

If I run TestMultipleDB it works fine however if I run TempTable I get the following error. I don't know where is it referring too? As far as I can see my datatypes match?

USE [VC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[TempTable]

SELECT 'Return Value' = @return_value

GO

Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to int.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TempTable] Script Date: 08/20/2008 10:49:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[TempTable]
AS

CREATE TABLE #Temp
([USERID] int, [FirstName] nvarchar(50), [LastName] nvarchar(50), [CombinedName] nvarchar(100), [CombPriv] nvarchar(100), [MiddleName] nvarchar(50), [Initials] nvarchar(50), [LoginName] nvarchar(50), [GroupName] nvarchar(50), [GroupDescription] nvarchar(50), [PermissionName] nvarchar(50), [PermissionDescription] nvarchar(50), [Description] nvarchar(50))

INSERT INTO #Temp ([USERID], [FirstName], [LastName], [CombinedName], [CombPriv], [MiddleName], [Initials], [LoginName], [GroupName], [GroupDescription], [PermissionName], [PermissionDescription], [Description])
EXEC TestMultipleDB @CombinedName = 'Not Archive Not Archive', @CombPriv = 'Super - Permission1'

---INSERT INTO #Temp ([USERID], [Description], Qty)
---EXEC Sproc2

SELECT DISTINCT [USERID], [FirstName], [LastName], [CombinedName], [CombPriv], [MiddleName], [Initials], [LoginName], [GroupName], [GroupDescription], [PermissionName], [PermissionDescription], [Description]
FROM #Temp

DROP TABLE #Temp

RETURN

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TestMultipleDB] Script Date: 08/20/2008 11:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TestMultipleDB]
@CombinedName nvarchar (100),
@CombPriv nvarchar (100)
AS

declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT UserDetails.LastName + '' '' + UserDetails.FirstName AS CombinedName,
GroupDetails.GroupName + '' - '' + PermissionDetails.PermissionName AS CombPriv, UserDetails.FirstName, UserDetails.MiddleName,
UserDetails.LastName, UserDetails.Initials, UserDetails.LoginName, GroupDetails.GroupName, GroupDetails.GroupDescription,
FeatureDetails.Description, PermissionDetails.PermissionName, PermissionDetails.PermissionDescription, UserDetails.UserID
FROM '+name+'.dbo.UserDetails INNER JOIN
'+name+'.dbo.UserGroup ON '+name+'.dbo.UserDetails.UserID = '+name+'.dbo.UserGroup.UserID INNER JOIN
'+name+'.dbo.GroupDetails ON '+name+'.dbo.UserGroup.GroupID = '+name+'.dbo.GroupDetails.GroupID INNER JOIN
'+name+'.dbo.GroupFeatures ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupFeatures.GroupID INNER JOIN
'+name+'.dbo.FeatureDetails ON '+name+'.dbo.GroupFeatures.FeatureID = '+name+'.dbo.FeatureDetails.FeatureID INNER JOIN
'+name+'.dbo.GroupPermissions ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupPermissions.GroupID INNER JOIN
'+name+'.dbo.PermissionDetails ON '+name+'.dbo.GroupPermissions.PermissionID = '+name+'.dbo.PermissionDetails.PermissionID
WHERE ((UserDetails.LastName + '' '' + UserDetails.FirstName) IN ('''+@CombinedName+''')) AND ((GroupDetails.GroupName + '' - '' + PermissionDetails.PermissionName) IN ('''+@CombPriv+''')) AND (UserDetails.IsDeleted = ''False'')
ORDER BY CombinedName ' from sys.databases where name='VC' or name like 'VCA%'
exec(@sql)

Return

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 00:09:24

Are you trying to return the entire result to variable? If yes, then this is not possible also. the syntax is wrong. it should be
DECLARE @return_value int

EXEC [dbo].[TempTable] @return_value OUTPUT

SELECT @return_value

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-20 : 02:54:30

I am not sure if I understand what you mean, I am a beginner. I have the TestMultipleDB code and I can run this on its own and it works, however it returns one result set for each DB. So.. I am trying to exec this proc from the temptable code to return one result set for all DB's rather than 3 (because I have 3 DB's currently) seperate result sets (this is for reporting services). I had it working with a simpler example with variables so I dont understand why this one is giving me the conversion error, what and why is it trying to convert anything? Where is the error coming from? How do you debug? So I think the answer to your question is no, I am not trying to return the result to a variable rather obtain one result set from multiple db's??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 04:18:43

to obtain reslut from multiple dbs you can store the result onto temporary table each time and call sp with dbname each time by looping through entries in sysdatabases. I'm assuming your result set from sp will have same structure through out.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-20 : 06:23:18

That seems to be what the code is doing looping through the sql for each database and storing into a temp table. I can see this as with the simpler proc that worked each time the loop occured the sql code is repeated and the database name changes '''+name+'''.dbo.whatever.whatever for example. I can see this as I added Print @sql. So I am still lost as to why with this code I am getting the conversion error?

Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to int.

...and the databases do have the same structure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 07:00:38

Did you try like i suggested? also what are you returning from sp?

DECLARE @return_value int

EXEC [dbo].[TempTable] @return_value OUTPUT

SELECT @return_value

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-20 : 10:16:51

I found the reason why I was getting an error however I do not understand why? The position of [UserID] in the two sp's must be in the same position. Originally in the TestMultipleDB sp it was at the end of the select statement and in the temptable sp it was at the beginning of the create, insert and select statements. If I move [UserID] to the beginning or end in both sp's it works fine. What surprises me is that the other fields are in differing orders between the two sp's but this does not seem to matter. I noticed that all other fields are nvarchar and userid was the only one that is int?????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 10:21:34

yup thats explain it. when order changes, its as if you're trying to insert varchar to int field and hence the error.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-20 : 10:25:16

OK, so the order of all fields between the two sp's should always be the same to be safe? Learnt something new today, along with everything else new :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 10:26:39

quote:
Originally posted by harlingtonthewizard

OK, so the order of all fields between the two sp's should always be the same to be safe? Learnt something new today, along with everything else new :)

yup. provided you're using the resultset to populate a table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources