altering temporary tables

hi all,
i have a simple stored procedure that takes input an integer..then inside it
it creates a temporary table & adds columns which are equal in number to the
integer that was inputed.
but iam encountering variuos error when i execute this procedure
here is the code(a very simple & small stored procedure):
create procedure mySolution @.colCount int as
declare @.i int
declare @.currTable varchar(10)
declare @.query nvarchar(255)
set @.i = 0
while @.i <= @.colCount
begin
set @.i = @.i + 1
if (@.i % 15 = 1)
begin
set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
(empid int)'
set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
exec(@.query)
end
set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
nvarchar(255)'
exec(@.query)
end
here are the errors :
if i execute this line
exec mySolution '15'
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf1' because this table does not exist in database
'MyPracticeDB'.Perhaps you should tell us what are you actually trying to achieve.
Each EXEC statement runs in a different context in which the local temporary
table created in the main procedure does not exist. Using a global table
would work, but I would advise against that - it may lead to other issues.
ML
http://milambda.blogspot.com/|||Hmmm, I think that temp table doesnt exists outside of the execution od
dynamic string and that would cause errors you get.
What are you trying to do with this sp? Perhaps you could explain the
requirement so we can try to find alternative solutions?
MC
"kishore bondada" <kishore bondada@.discussions.microsoft.com> wrote in
message news:361D5746-7137-4361-8BD0-D77150AF7326@.microsoft.com...
> hi all,
> i have a simple stored procedure that takes input an integer..then inside
> it
> it creates a temporary table & adds columns which are equal in number to
> the
> integer that was inputed.
> but iam encountering variuos error when i execute this procedure
> here is the code(a very simple & small stored procedure):
> create procedure mySolution @.colCount int as
> declare @.i int
> declare @.currTable varchar(10)
> declare @.query nvarchar(255)
> set @.i = 0
> while @.i <= @.colCount
> begin
> set @.i = @.i + 1
> if (@.i % 15 = 1)
> begin
> set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
> (empid int)'
> set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
> exec(@.query)
> end
> set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
> nvarchar(255)'
> exec(@.query)
> end
>
> here are the errors :
> if i execute this line
> exec mySolution '15'
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf1' because this table does not exist in
> database
> 'MyPracticeDB'.
>|||I've never tries t his, but perhaps you can switch your context to
tempdb and alter from there...once you find it.|||No, the problem is pre-compile, e.g. the parser can't find #table because
#table hasn't been created yet.
An alternative is to complete the whole chunk in dynamic SQL. Here is one
approach thatuses a permanent table tied to the SPID, this way you don't
have to do everything inside a single EXEC() (but you will still have to use
dynamic SQL to reference the table name):
DECLARE @.colCount INT;
SET @.colCount = 5;
DECLARE @.i INT,
@.TableName VARCHAR(32),
@.Query NVARCHAR(4000);
SET @.TableName = 'TaskSecF_'+RTRIM(@.@.SPID);
SET @.Query = 'IF OBJECT_ID('''+@.TableName+''') IS NOT NULL
DROP TABLE '+@.TableName+';
SELECT col1 = CONVERT(NVARCHAR(255), N'''')';
SET @.i = 1;
WHILE @.i <= @.colCount
BEGIN
SET @.i = @.i + 1
SET @.query = @.query + ',col'+RTRIM(@.i)+' = CONVERT(NVARCHAR(255), N'''')'
END
SET @.query = @.query + ' INTO '+@.TableName+' WHERE 1=2;'
EXEC(@.query)
EXEC('SELECT * FROM '+@.TableName);
...
EXEC('DROP TABLE '+@.TableName);
"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1139324385.781046.242250@.g43g2000cwa.googlegroups.com...
> I've never tries t his, but perhaps you can switch your context to
> tempdb and alter from there...once you find it.
>|||hi all,
first of all thank you all for your valuable replies. As asked by you iam
explaining the requirement a bit more clearly here:
Iam supposed to write a stored Procedure that takes input, an integer ' N '
and inside it create some temp tables, where each temp table contains ' X '
number of columns with datatype NVARCHAR(255).
since the SQL Server 2000 MAXROWSIZE limit is 8060 bytes, each temp table
can atmaximum contain 15 to 16 fields only (with datatype NVARCHAR(255)).
now if the user gives an input as integer 37, then
these temp tables should be created
#taskecf1 with fields col1 .... col15
#taskecf2 with fields col16 .... col30
#taskecf3 with fields col31 .... col37
and iam supposed to create temp tables only (not ordinary tables, i should
follow this coz, told by my project guide), after i complete this task, this
stored procedure is given to another student who will add his code to do
inserts & updates on these temp tables. hope this provides enough explanatio
n.
so i request you all to help me with this .
here is the code :
create procedure mySolution @.colCount int as
declare @.i int
declare @.currTable varchar(10)
declare @.query nvarchar(255)
set @.i = 0
while @.i <= @.colCount
begin
set @.i = @.i + 1
if (@.i % 15 = 1)
begin
set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
(empid int)'
set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
exec(@.query)
end
set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
nvarchar(255)'
exec(@.query)
end
if i execute this
exec mySolution '15'
here are the errors :
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf1' because this table does not exist in database
'MyPracticeDB'|||i got the work done with the help of global temporary tables.
but some of you commented that it would lead to some other issues...can u
please elaborate what they are?
"ML" wrote:

> Perhaps you should tell us what are you actually trying to achieve.
> Each EXEC statement runs in a different context in which the local tempora
ry
> table created in the main procedure does not exist. Using a global table
> would work, but I would advise against that - it may lead to other issues.
>
> ML
> --
> http://milambda.blogspot.com/|||Well, its global. That means that all processes 'see' and affect those
tables. If there are two calls of your procedure at the same time, you'll be
in trouble.
MC
"kishore bondada" <kishorebondada@.discussions.microsoft.com> wrote in
message news:89E8BD16-C428-4C77-9BBB-CC2D4CDEC055@.microsoft.com...
>i got the work done with the help of global temporary tables.
> but some of you commented that it would lead to some other issues...can u
> please elaborate what they are?
> "ML" wrote:
>|||Try executing your script in two separate sessions step-by-step - i.e. creat
e
the object in the first session, then in the second, make the changes in the
first, then in the second, etc.
After each step check the state of the object (perhaps from a third
connection). You'll see what I mean.
Even this situation can be remedied by giving the objects unique names (e.g.
including SPID in the table name), but this only leads to more dynamic SQL.
Is this the purpose of this exercise?
In real-life situations the requirements for a data model are known in
advance and allow for more efficient planning. But that's another story. :)
ML
http://milambda.blogspot.com/|||i thank u all for ur valuable suggestions..
i solved the uniqueness issue of global temporary tables by using your idea
of including the @.@.SPID as part of the table name when creating the
tables...please inform me of any other issues involved
regards,
kishore
"ML" wrote:

> Try executing your script in two separate sessions step-by-step - i.e. cre
ate
> the object in the first session, then in the second, make the changes in t
he
> first, then in the second, etc.
> After each step check the state of the object (perhaps from a third
> connection). You'll see what I mean.
> Even this situation can be remedied by giving the objects unique names (e.
g.
> including SPID in the table name), but this only leads to more dynamic SQL
.
> Is this the purpose of this exercise?
> In real-life situations the requirements for a data model are known in
> advance and allow for more efficient planning. But that's another story. :
)
>
> ML
> --
> http://milambda.blogspot.com/

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.
> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups
.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.
Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

Alter Font of Data

Hello!

I have a matrix. Inside "Data", I have the follow code:

Fields!Name.Value & Chr(13) & Chr(10) & Fields!Group.Value

Is it possible place Font Bold only at Fields!Name.Value? How?

Thanks

Reporting Services does not support rich text or multiple formats inside text boxes. It is on our wishlist for a future release.

However, you could easily put another textbox in the cell with the Name.Value in a bold font and the other fields in another textbox. You could also add another column.

Allowing parameters to be used inside IN cluase

Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
This is currently allowed. What problem are you having?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
|||It's already there:
create table #tempin(anint int)
go
insert #tempin values(1)
insert #tempin values(2)
go
declare @.int1 int
declare @.int2 int
set @.int1 = 1
set @.int2 = 2
select * from #tempin
where anint in (@.int1, @.int2)
go
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>
|||My bad, misunderstood what my sql admin told me.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>
|||He probably meant:
DECLARE @.ids VARCHAR(255)
SET @.ids = '1, 2, 4, 5, 6'
SELECT id FROM table WHERE id IN (@.ids)
See http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uOoo6bFaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> My bad, misunderstood what my sql admin told me.
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
wrote
> in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
>

Allowing parameters to be used inside IN cluase

Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)This is currently allowed. What problem are you having?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)|||It's already there:
create table #tempin(anint int)
go
insert #tempin values(1)
insert #tempin values(2)
go
declare @.int1 int
declare @.int2 int
set @.int1 = 1
set @.int2 = 2
select * from #tempin
where anint in (@.int1, @.int2)
go
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||My bad, misunderstood what my sql admin told me.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||He probably meant:
DECLARE @.ids VARCHAR(255)
SET @.ids = '1, 2, 4, 5, 6'
SELECT id FROM table WHERE id IN (@.ids)
See http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uOoo6bFaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> My bad, misunderstood what my sql admin told me.
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
wrote
> in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
>

Allowing parameters to be used inside IN cluase

Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)This is currently allowed. What problem are you having?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)|||It's already there:
create table #tempin(anint int)
go
insert #tempin values(1)
insert #tempin values(2)
go
declare @.int1 int
declare @.int2 int
set @.int1 = 1
set @.int2 = 2
select * from #tempin
where anint in (@.int1, @.int2)
go
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||My bad, misunderstood what my sql admin told me.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||He probably meant:
DECLARE @.ids VARCHAR(255)
SET @.ids = '1, 2, 4, 5, 6'
SELECT id FROM table WHERE id IN (@.ids)
See http://www.aspfaq.com/2248
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uOoo6bFaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> My bad, misunderstood what my sql admin told me.
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
wrote
> in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> > Are there any plans to allow this in future versions of sql server?
> >
> > e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
> >
> >
>

Aligning text that has been placed inside text boxes

I am new to reporting services. I am having trouble aligning my text. I put
some things in text boxes and even though I have the boxes as close together
as possible, they appear far apart. I tried overlapping the boxes but then
when they deploy it pushes the data in the second box down to the next line.
Anybody know how I can fix this? Problem is part of what I am putting in the
report needs to be bolded and part doesnt, so I cant keep my data together. I
would appreciate any help that I could get.I have fixed this problem before by putting both test boxes in side of a
rectangle. Just make sure the text boxes are not over lapping . Also make the
rectangle the over all size of both text boxes.
"KimB" wrote:
> I am new to reporting services. I am having trouble aligning my text. I put
> some things in text boxes and even though I have the boxes as close together
> as possible, they appear far apart. I tried overlapping the boxes but then
> when they deploy it pushes the data in the second box down to the next line.
> Anybody know how I can fix this? Problem is part of what I am putting in the
> report needs to be bolded and part doesnt, so I cant keep my data together. I
> would appreciate any help that I could get.|||Thank you for your help. I've tried this too, and not overlapping the text
boxes solved the problem of not pushing the data to the next line. But I
still end up with my text far apart. Instead of having something like this:
First Name: Johnny (with Firstname bolded and Johnny not) I end up with
First Name: Johnny Even with the boxes as close together as I can
get them without overlapping them, the data comes back spaced far apart.
"C.M" wrote:
> I have fixed this problem before by putting both test boxes in side of a
> rectangle. Just make sure the text boxes are not over lapping . Also make the
> rectangle the over all size of both text boxes.
>
>
>
> "KimB" wrote:
> > I am new to reporting services. I am having trouble aligning my text. I put
> > some things in text boxes and even though I have the boxes as close together
> > as possible, they appear far apart. I tried overlapping the boxes but then
> > when they deploy it pushes the data in the second box down to the next line.
> > Anybody know how I can fix this? Problem is part of what I am putting in the
> > report needs to be bolded and part doesnt, so I cant keep my data together. I
> > would appreciate any help that I could get.