As Mike said, don't create objects in tempdb. However, your stored
procedure is failing because the first thing you are doing is dropping
a table that doesn't exist.
What are you trying to accomplish?
StuThank stu and mike,
I' know: the tempdb is a system database but i use this database like
workspace.
I explain this again, for this
1) Create a user Kalen with the create table permission on tempdb database
2) Connect with this user and execute
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TabTest]
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
This code is simple and the more important work
3) Connect with a user sa and create this SP
Create procedure DBO.ProcTest
as
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to Kalen
4) Connect with the Kalen user this not work
I receive this message
--Invalid object name 'Tempdb..TabTest'
The question is:
I need create this one SP and not use SQL Dynamic.
Easy'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1148603889.848594.234520@.j73g2000cwa.googlegroups.com...
> As Mike said, don't create objects in tempdb. However, your stored
> procedure is failing because the first thing you are doing is dropping
> a table that doesn't exist.
> What are you trying to accomplish?
> Stu
>|||"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:%23s7zNOMgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Thank stu and mike,
> I' know: the tempdb is a system database but i use this database like
> workspace.
<snip> SQL Server uses it as a workspace as well. You're asking for trouble
here. Have fun.</snip>
> I need create this one SP and not use SQL Dynamic.
1) I HIGHLY recommend against forcing your own DDL inside TempDB. At best
you're creating contention within SQL Server for TempDB resources. It's a
System Database for a reason.
2) I don't see the point of what you're doing, but if you're building the
SP in a separate Database from the one you're executing it in (assuming
that's the reason you feel the need to prefix the table with Tempdb..
everywhere), then you need to look at the effect of not using that prefix in
your IF EXISTS statement. I added the database name prefix to your select *
from dbo.sysobjects and object_id statements and dropped the OBJECTPROPERTY
function in favor of checking the XTYPE column. Works great.
> Easy'
Too Easy.sql
Showing posts with label storedprocedure. Show all posts
Showing posts with label storedprocedure. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
create storedproc with smo
hi,
i try to create storeproc with smo but i 've an exception
"Create failed for StoredProcedure 'dbo.TEST'"
{"Cannot create StoredProcedure '[dbo].[TEST]' if parent is not yet created."}
but sp.parent is created
please help me
attache my work
StoredProcedure sp = new StoredProcedure();
sp.Schema = "dbo";
sp.Name = "TEST";
sp.Parent = new Database(new Server("serveur"), "db_TEST");
sp.IgnoreForScripting = false;
sp.TextMode = false;
sp.ImplementationType = ImplementationType.TransactSql;
sp.Parameters.Add(new StoredProcedureParameter(sp,"@.toto, DataType.DateTime));
sp.TextBody = "Select 1";
sp.Create();
Database db = new Database(new Server(), "db_TEST");
db.Create();
StoredProcedure sp = new StoredProcedure();
sp.Schema = "dbo";
sp.Name = "TEST";
sp.Parent = db;
sp.TextMode = false;
sp.ImplementationType = ImplementationType.TransactSql;
sp.Parameters.Add(new StoredProcedureParameter(sp, "@.toto", DataType.DateTime));
sp.TextBody = "Select 1";
sp.Create();
Create stored procs in new DB from within a stored proc?
In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:
> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:
> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:
> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:
> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql
Monday, March 19, 2012
Create Procedure Output ntext
Hello,
I need to produce with T-SQL a user defined function or stored
procedure that make one SLQ-Statement and prepare as string from the
result set.
The request muss be able to return a very long unicode string. The
return value nvarchar is being truncated so I'm trying to create a
stored procedure, that returns a ntext string.
I can't manage it (I am no T-SQL specialist). Maybe someone can help
me?
Thanks for your help.
--
Here is my sp:
alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
@.return ntext output)
AS
BEGIN
DECLARE @.onelevel nvarchar(4000)
DECLARE @.pos varchar(1000)
DECLARE @.leveldone varchar(100)
DECLARE @.levelplaned varchar(100)
DECLARE @.planeddate nvarchar(4000)
DECLARE @.elementid varchar(10)
DECLARE @.levelid varchar(10)
DECLARE @.levelstatus varchar(10)
DECLARE @.levelupd nvarchar(4000)
DECLARE @.levelauthor varchar(10)
DECLARE @.prevelementid varchar(10)
BEGIN
declare level_cursor CURSOR FOR
SELECT
B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
convert(varchar,A.Upd,126) as Upd,A.Author
FROM T_TABLE1 as B left outer join T_TABLE2 as A on
(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
where B.ParentID=@.parentid
ORDER BY B.ElementID,B.ElementPos
END
set @.onelevel=''
OPEN level_cursor
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
@.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.prevelementid=@.elementid
if (@.pos IS NULL)
set @.onelevel=''
else
set @.onelevel=@.pos
if (@.elementid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.elementid
if (@.levelid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelid
if (@.leveldone IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.leveldone
if (@.levelplaned IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelplaned
if (@.planeddate IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.planeddate
if (@.levelstatus IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelstatus
if (@.levelupd IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelupd
if (@.levelauthor IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelauthor
-- Part Output
print @.onelevel
if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor
if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor
END
CLOSE level_cursor
DEALLOCATE level_cursor
RETURN
END
--
Call of the function with:
exec dbo.F_FUNCTION 550,1632, 0, ''
Here the beginning of the query analyser output:
1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.
--I think you cannot return that using an output parameter - you'll have to
return it as a field in a SELECT.
On 24 Oct 2005 08:17:01 -0700, rey@.infoman.de wrote:
>Hello,
>I need to produce with T-SQL a user defined function or stored
>procedure that make one SLQ-Statement and prepare as string from the
>result set.
>The request muss be able to return a very long unicode string. The
>return value nvarchar is being truncated so I'm trying to create a
>stored procedure, that returns a ntext string.
>I can't manage it (I am no T-SQL specialist). Maybe someone can help
>me?
>Thanks for your help.
>--
>Here is my sp:
>alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
>@.return ntext output)
>AS
>BEGIN
> DECLARE @.onelevel nvarchar(4000)
> DECLARE @.pos varchar(1000)
> DECLARE @.leveldone varchar(100)
> DECLARE @.levelplaned varchar(100)
> DECLARE @.planeddate nvarchar(4000)
> DECLARE @.elementid varchar(10)
> DECLARE @.levelid varchar(10)
> DECLARE @.levelstatus varchar(10)
> DECLARE @.levelupd nvarchar(4000)
> DECLARE @.levelauthor varchar(10)
> DECLARE @.prevelementid varchar(10)
> BEGIN
> declare level_cursor CURSOR FOR
> SELECT
>B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
>convert(varchar,A.Upd,126) as Upd,A.Author
> FROM T_TABLE1 as B left outer join T_TABLE2 as A on
>(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
> where B.ParentID=@.parentid
> ORDER BY B.ElementID,B.ElementPos
> END
> set @.onelevel=''
> OPEN level_cursor
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
> @.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>set @.prevelementid=@.elementid
> if (@.pos IS NULL)
> set @.onelevel=''
> else
> set @.onelevel=@.pos
> if (@.elementid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.elementid
> if (@.levelid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelid
> if (@.leveldone IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.leveldone
> if (@.levelplaned IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelplaned
> if (@.planeddate IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.planeddate
> if (@.levelstatus IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelstatus
> if (@.levelupd IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelupd
> if (@.levelauthor IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelauthor
> -- Part Output
> print @.onelevel
> if (@.return is NULL)
> exec(@.return+@.onelevel)
> else
> exec(@.return+'*;*'+@.onelevel)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> END
> CLOSE level_cursor
> DEALLOCATE level_cursor
> RETURN
>END
>--
>Call of the function with:
>exec dbo.F_FUNCTION 550,1632, 0, ''
>Here the beginning of the query analyser output:
>1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '*'.
>--|||Am 24 Oct 2005 08:17:01 -0700 schrieb rey@.infoman.de:
...
> --
> Call of the function with:
> exec dbo.F_FUNCTION 550,1632, 0, ''
> Here the beginning of the query analyser output:
> 1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '*'.
> --
ntext is a valid datatype for the OUTPUT variable of a stored procedure.
This is not the problem. Your error appears here:
...
-- Part Output
print @.onelevel
if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)
...
because if @.return is Null then you do a
exec('1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277')
and what should this be?
EXEC starts another stored proc, from there you get your error message. And
so it is an error in line 1.
bye
Helmut|||Hello Helmut,
thanks for your answer.
With exec(@.return+@.onelevel) I try to fill my @.return variable with
the content of @.onelevel. I can't do it with set @.return=@.onelevel
because @.return is of type ntext.
How can I do it else?
thanks and bye,
Agns.|||Not sure if you can do this with ntext, but try this
select @.return = @.return + '*;*' + @.onelevel|||(rey@.infoman.de) writes:
> thanks for your answer.
> With exec(@.return+@.onelevel) I try to fill my @.return variable with
> the content of @.onelevel. I can't do it with set @.return=@.onelevel
> because @.return is of type ntext.
> How can I do it else?
You can't. Since you are in a dead end, I suggest that you explain
your underlying business problem that you are trying to solve. What
does the calling side of this look like?
I can offer one workaround: move to SQL 2005, which offers the
new datatype nvarchar(MAX), which in difference to ntext is a first
class citizen.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 25 Oct 2005 00:35:55 -0700, rey@.infoman.de wrote:
>Hello Helmut,
>thanks for your answer.
>With exec(@.return+@.onelevel) I try to fill my @.return variable with
>the content of @.onelevel. I can't do it with set @.return=@.onelevel
>because @.return is of type ntext.
>How can I do it else?
>thanks and bye,
>Agns.
What's wrong with returning it via SELECT rather than via a parameter?
Subscribe to:
Posts (Atom)