June 5, 2008 at 5:28 am
Hi,
We have written a stored procedure, wherein we are supposed to create a table in runtime. When I execute the query @qry it works, but unfortunately when we run as a whole program it throws an error.
======
Msg 203, Level 16, State 2, Procedure AssignVehicles, Line 25
The name 'CREATE TABLE bulkdata.[dbo].gpsdata29 ([REGISTRATIONNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[UNIT_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LATITUDE] [float] NULL,[LONGITUDE] [float] NULL,[ALTITUDE] [numeric](15, 9) NULL,[SPEED] [numeric](5, 0) NULL,[LAT_METER] [float] NULL,[LON_METER] [float] NULL,[SATELLITE] [numeric](2, 0) NULL,[GPS_DATETIME] [datetime] NULL,[DIRECTION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[COMMUNICATION_CONTROL] [numeric](2, 0) NULL,[MESSAGE_NUMBER] [numeric](10, 0) NULL,[GPS_COMMUNICATION] [numeric](18, 0) NULL,[UNIT_MODE] [numeric](3, 0) NULL' is not a valid identifier.
======
The stored procedure is as below
ALTER PROCEDURE [dbo].[AssignVehicles]
@strUserID varchar(100),
@vehicleno nvarchar(100)
as
Declare @unitno as bigint
select @unitno=UNIT_NO from VEHICLE_UNIT_HISTORY where REGISTRATIONNO=@vehicleno
Declare @cnt as int
Declare @maxcnttbl1 as nvarchar(20)
Declare @maxcnttbl2 as nvarchar(20)
Declare @maxcnttbl3 as nvarchar(20)
declare @qry as nvarchar(3000)
select @cnt=count(*) from [mapping].[dbo].virtualmapping_com where Userid=@strUserID
if @cnt=0
begin
select @maxcnttbl1='gpsdata'+convert(nvarchar(100),isnull(MAX(CONVERT(int, SUBSTRING(tablename1, 8, LEN(tablename1)))),0)+1),@maxcnttbl2='gpsdata_history'+convert(nvarchar(100),isnull(MAX(CONVERT(int, SUBSTRING(tablename2, 16, LEN(tablename2)))),0)+1),@maxcnttbl3=databasename from [mapping].[dbo].[virtualmapping_com] group by databasename
set @qry= 'CREATE TABLE '+ @maxcnttbl3 + '.[dbo].' + @maxcnttbl1 + ' ([REGISTRATIONNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[UNIT_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LATITUDE] [float] NULL,[LONGITUDE] [float] NULL,[ALTITUDE] [numeric](15, 9) NULL,[SPEED] [numeric](5, 0) NULL,[LAT_METER] [float] NULL,[LON_METER] [float] NULL,[SATELLITE] [numeric](2, 0) NULL,[GPS_DATETIME] [datetime] NULL,[DIRECTION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[COMMUNICATION_CONTROL] [numeric](2, 0) NULL,[MESSAGE_NUMBER] [numeric](10, 0) NULL,[GPS_COMMUNICATION] [numeric](18, 0) NULL,[UNIT_MODE] [numeric](3, 0) NULL,[TXN_REASON] [numeric](3, 0) NULL,[ODOMETER] [numeric](12, 2) NULL,[IGNITION] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DOOR_SENSOR] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[SHOCK_SENSOR] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DISTRESS_ALERT] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OILPRESSURE_SENSOR] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ENGINE_TEMP] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GPS_VOLTAGE] [numeric](5, 2) NULL,[BACKUP_VOLTAGE] [numeric](5, 2) NULL,[SIREN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ALARM_ARMED] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GPSPOWER_STATUS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[HOOD_LOCK] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[INTERNAL_LIGHTS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BLINKER] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[STD_IMMOBILIZER] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[SlNo] [bigint] NOT NULL,[LOCATION] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BaseStationName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Location_Speed] [smallint] NULL,[analog1] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[analog2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[door_lock] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[reed_switch] [int] NULL,[tag_id] [int] NULL,[volume] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[logic_state] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[gps_valid] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[gps_connected] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[software_version] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Geo_Boundary] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[trailer_tag_updatetime] [datetime] NULL,[trailer_id] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[driver_tag_updatetime] [datetime] NULL) ON [PRIMARY]'
print @qry
exec @qry
end
Kindly let me know where we have gone wrong.
Regards
cmrhema
June 5, 2008 at 8:39 am
Your variable @qry as nvarchar(3000) is too small to hold your create table statement.
Do you really need it to be a nvarchar? Your create table statment is over 3000 characters long. Either make it a varchar(4000) or nvarchar(8000).
June 5, 2008 at 8:48 am
Are you the (our) Client....;) ?????
your heading make sence like this...(Kidding....:P:D)
he he he..lolz;):P:D
Cheers!
Sandy.
--
June 5, 2008 at 10:03 am
Sandy (6/5/2008)
Are you the (our) Client....;) ?????your heading make sence like this...(Kidding....:P:D)
he he he..lolz;):P:D
Cheers!
Sandy.
I have no idea what this means.
June 5, 2008 at 12:45 pm
I'm thinking all you need is to put parens around the @SQL in the EXEC.
As in -
...
EXEC (@SQL)
....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 7:46 pm
Applied sp_executesql and got the solution
June 9, 2008 at 7:44 am
but hemu..
your error...show something different error..
how you got the solution..?
i guess your error was in the line..
======
Msg 203, Level 16, State 2, Procedure AssignVehicles, Line 25
The name 'CREATE TABLE bulkdata.[dbo].gpsdata29 ( [REGISTRATIONNO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [UNIT_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [LATITUDE] [float] NULL, [LONGITUDE] [float] NULL, [ALTITUDE] [numeric](15, 9) NULL, [SPEED] [numeric](5, 0) NULL, [LAT_METER] [float] NULL, [LON_METER] [float] NULL, [SATELLITE] [numeric](2, 0) NULL, [GPS_DATETIME] [datetime] NULL, [DIRECTION] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [COMMUNICATION_CONTROL] [numeric](2, 0) NULL, [MESSAGE_NUMBER] [numeric](10, 0) NULL, [GPS_COMMUNICATION] [numeric](18, 0) NULL, [UNIT_MODE] [numeric](3, 0) NULL' is not a valid identifier.
======
And one more thing...:w00t:
declare @qry as nvarchar(3000)
you declare the variable of 3000 size..but your query contains more than 3000 characters..then how your sp_executesql works on a variable which contains more than 3000 character...which is more than its declare size??
Cheers!
Sandy.
--
June 9, 2008 at 10:27 pm
it has been modified as below
declare @qry as nvarchar(3000)
set @qry= 'insert into [bulkdata].[dbo].gpsdata ([REGISTRATIONNO],[UNIT_NO] ,[LATITUDE],[LONGITUDE],[ALTITUDE],[SPEED] ,[LAT_METER],[LON_METER],[SATELLITE],[GPS_DATETIME],[DIRECTION],[COMMUNICATION_CONTROL],[MESSAGE_NUMBER],[GPS_COMMUNICATION],[UNIT_MODE],[TXN_REASON],[ODOMETER],[IGNITION],[DOOR_SENSOR],[SHOCK_SENSOR],[DISTRESS_ALERT],[OILPRESSURE_SENSOR],[ENGINE_TEMP],[GPS_VOLTAGE],[BACKUP_VOLTAGE],[SIREN],[ALARM_ARMED],[GPSPOWER_STATUS],[HOOD_LOCK],[INTERNAL_LIGHTS],[BLINKER],[STD_IMMOBILIZER],[LOCATION],[BaseStationName],[Location_Speed]
,[analog1],[analog2],[door_lock],[reed_switch],[tag_id],[volume],[logic_state],[gps_valid],[gps_connected],[software_version],[Geo_Boundary],[trailer_tag_updatetime],[trailer_id],[driver_tag_updatetime])
select [REGISTRATIONNO],[UNIT_NO] ,[LATITUDE],[LONGITUDE],[ALTITUDE],[SPEED] ,[LAT_METER],[LON_METER],[SATELLITE],[GPS_DATETIME],[DIRECTION],[COMMUNICATION_CONTROL],[MESSAGE_NUMBER],[GPS_COMMUNICATION],[UNIT_MODE],[TXN_REASON],[ODOMETER],[IGNITION],[DOOR_SENSOR],[SHOCK_SENSOR],[DISTRESS_ALERT],[OILPRESSURE_SENSOR],[ENGINE_TEMP],[GPS_VOLTAGE],[BACKUP_VOLTAGE],[SIREN],[ALARM_ARMED],[GPSPOWER_STATUS],[HOOD_LOCK],[INTERNAL_LIGHTS],[BLINKER],[STD_IMMOBILIZER],[LOCATION],[BaseStationName],[Location_Speed]
,[analog1],[analog2],[door_lock],[reed_switch],[tag_id],[volume],[logic_state],[gps_valid],[gps_connected],[software_version],[Geo_Boundary],[trailer_tag_updatetime],[trailer_id],[driver_tag_updatetime] from bulkdata.dbo.' + @maxcnttbl1 + ' where registrationno=' + @vehicleno
exec sp_executesql @qry
AND, it works
June 10, 2008 at 7:43 am
Ok, got it..
Cheers!
Sandy.
--
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply