BAFFLED! Why won't this code work?!?!

Aaron and Tibor - Thank you for your replys.
Aaron, the tbl-vs-tblname issue and data type were silly mistakes -
thank you.
Tibor, so then making the entire table name a variable (rather than
just part of it) should solve the problem, right? The below code
attempts to make this change. Now getting the following error:
"Syntax error converting the varchar value 'tblIR_LB_' to a column of
data type smallint."
CREATE PROC CreateIRTables ( @.tblname varchar(50))
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
DECLARE @.tblname varchar(50)
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
SET @.tblname = 'tblIR_LB_' + @.i
EXECUTE CreateIRTables @.tblname
SET @.i = @.i + 1
ENDEXACT SAME PROBLEM, you've just moved it to a different place! You're
trying to add an integer to a string. You can't do this!

> SET @.tblname = 'tblIR_LB_' + @.i
Should be
SET @.tblname = 'tblIR_LB_' + CONVERT(VARCHAR(12),@.i)|||i think you are chasing a red herring, but I wil point out this error :)
SET @.tblname = 'tblIR_LB_' + @.i
^ @.I is a small int
SET @.tblname = 'tblIR_LB_' + CAST(@.i As varchar)
My suggestion to see what is happening .... return your @.SQL as an output
paramer and print it each time.
if it *was* giving you the error on tblIR_LB_2 then I assume tblIR_LB_1
worked? So, see what is happening by looking at the @.SQL. PRINT it as an
output variable and run the code directly.
Good luck.
John Scragg
"Butaambala" wrote:

> Aaron and Tibor - Thank you for your replys.
> Aaron, the tbl-vs-tblname issue and data type were silly mistakes -
> thank you.
> Tibor, so then making the entire table name a variable (rather than
> just part of it) should solve the problem, right? The below code
> attempts to make this change. Now getting the following error:
> "Syntax error converting the varchar value 'tblIR_LB_' to a column of
> data type smallint."
> CREATE PROC CreateIRTables ( @.tblname varchar(50))
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> DECLARE @.tblname varchar(50)
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> SET @.tblname = 'tblIR_LB_' + @.i
> EXECUTE CreateIRTables @.tblname
> SET @.i = @.i + 1
> END
>

Backup Transaction Log on TempDb (No items for Databases in EM)

I cant see any items in Enterprise manager under databases so I need to do
this from Query Analyzer. Please help on backing up Transaction log.
"Tibor Karaszi" wrote:

> No can do. Make sure that tempdb is still in simple recovery mode. If it i
s, you can have long
> running transactions, or some humongous modifications requiring lots of lo
g space in tempdb. Short
> term: make sure you have storage and autogrow is possible, Long term, hunt
down what it is using
> this space and fix the code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
> news:C70FC7C3-E8F8-4DE9-A2DA-42D0B44F1AC1@.microsoft.com...
>
>Again, you can't backup log of tempdb. Please read my prior post for recomme
ndations on how to
handle your situation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:B5B42792-D6F0-4620-88D3-3CF822A28CDB@.microsoft.com...
>I cant see any items in Enterprise manager under databases so I need to do
> this from Query Analyzer. Please help on backing up Transaction log.
> "Tibor Karaszi" wrote:
>|||Hi,
As Tibor mentioned see the recovery model for Tempdb. Exeute the below
command:-
Select DATABASEPROPERTYEX('tempdb',Re_covery )
If the recovery model is not SIMPLE change it to Simple
ALTER DATABASE TEMPDB SET RECOVERY SIMPLE
Thanks
Hari
SQL Server MVP
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:B5B42792-D6F0-4620-88D3-3CF822A28CDB@.microsoft.com...
>I cant see any items in Enterprise manager under databases so I need to do
> this from Query Analyzer. Please help on backing up Transaction log.
> "Tibor Karaszi" wrote:
>