Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I call
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa0400225912014:57:14.380
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa03017225912014:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg Holmes
You might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes
|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do you
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it go
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did was
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it go
> up by 2s. I also added an auto incrementing field to the table and you can
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>
|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attention
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>
|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
|||No, now that I'm using a text query in ASP.NET instead of a stored procedure,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>

Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I cal
l
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg HolmesYou might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do yo
u
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it g
o
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did wa
s
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it
go
> up by 2s. I also added an auto incrementing field to the table and you ca
n
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
>|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attentio
n
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
>|||No, now that I'm using a text query in ASP.NET instead of a stored procedure
,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for
).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column
.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>sql

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/

Altering table structure by comparing it to a MODEL table

Hi,

I have an application which needs to use the MODEL database (This is

created by me and it acts as a template) to synchronize the tables,

stored procedures, triggers, indexes across multiple client database.

Basically, if there is a structural change to the MODEL database the

client databases need to be updated. For example, If I create a new

table or alter a table or modify a stored procedure or modify an index

the client databases need to be updated when I run the sync routine.

So far I have managed to create/drop tables, create/drop columns, create/drop stored procedures and create/drop triggers.

I need to be able to drop all relationships (constraints) and indexes

and recreate them. I need to know how this can be done using SMO. I can

do it using TSQL but I want to know if there is an alternative.

Thanks in advance.I figured it out ...

#Region " Relationships "
Private Sub DropRelationships(ByVal db As Database)
For Each tbl As Table In db.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
key.MarkForDrop(True)
Next
tbl.Alter()
End If
Next
End Sub

Private Sub CreateRelationships(ByVal AlterDB As Database, ByVal ModelDB As Database)
For Each tbl As Table In ModelDB.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
For Each col As ForeignKeyColumn In key.Columns
Dim rc As Column
Dim fk As ForeignKey
fk = New ForeignKey(AlterDB.Tables(tbl.Name), key.Name)
For Each c As Column In AlterDB.Tables(key.ReferencedTable).Columns
If c.InPrimaryKey Then
rc = c
End If
Next
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, col.Name, rc.Name)
fk.Columns.Add(fkc)

fk.ReferencedTable = key.ReferencedTable
fk.ReferencedTableSchema = key.ReferencedTableSchema

fk.Create()
Next
Next
End If
Next
End Sub
#End Regionsql

Altering table structure by comparing it to a MODEL table

Hi,

I have an application which needs to use the MODEL database (This is

created by me and it acts as a template) to synchronize the tables,

stored procedures, triggers, indexes across multiple client database.

Basically, if there is a structural change to the MODEL database the

client databases need to be updated. For example, If I create a new

table or alter a table or modify a stored procedure or modify an index

the client databases need to be updated when I run the sync routine.

So far I have managed to create/drop tables, create/drop columns, create/drop stored procedures and create/drop triggers.

I need to be able to drop all relationships (constraints) and indexes

and recreate them. I need to know how this can be done using SMO. I can

do it using TSQL but I want to know if there is an alternative.

Thanks in advance.I figured it out ...

#Region " Relationships "
Private Sub DropRelationships(ByVal db As Database)
For Each tbl As Table In db.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
key.MarkForDrop(True)
Next
tbl.Alter()
End If
Next
End Sub

Private Sub CreateRelationships(ByVal AlterDB As Database, ByVal ModelDB As Database)
For Each tbl As Table In ModelDB.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
For Each col As ForeignKeyColumn In key.Columns
Dim rc As Column
Dim fk As ForeignKey
fk = New ForeignKey(AlterDB.Tables(tbl.Name), key.Name)
For Each c As Column In AlterDB.Tables(key.ReferencedTable).Columns
If c.InPrimaryKey Then
rc = c
End If
Next
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, col.Name, rc.Name)
fk.Columns.Add(fkc)

fk.ReferencedTable = key.ReferencedTable
fk.ReferencedTableSchema = key.ReferencedTableSchema

fk.Create()
Next
Next
End If
Next
End Sub
#End Region

Altering stored procedure which is part of sys schema

I am trying to alter sys.sp_helpmergeconflictrows which is part fof sys schema and is in System Stored Procedures.

Reason why I need this is because Conglict Viewer in merge replication fails to show data from one of my tables, because aforementioned sp fails during execution. It fails because sql query is declared as nvarchar(4000) and it needs to be longer. So, I tired to change it to nvarchar(max), but I cannot.

I tried few things in order to gain permission to alter that sp, but I fial always.

Can it be done at all, and if can, how?

Thanks

If you have a bug in the conflict viewer (or more specifically this stored proc), you should open a ticket with MS Tech Support to have this resolved.

Bryan

sql

Altering multiple objects schema

Hi,

I need to change the schema of the stored procedures of several databases.

Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

thank you

Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||

You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

declare @.querystring nvarchar(MAX)

set @.querystring=''

select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

exec(@.querystring)

Either way, you will have to use dynamic sql.

Altering column fields with a Stored Procedure

I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String

sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDate, 4, 2)
sDay = Mid(sCurrentDate, 1, 2)
sYear = Mid(sCurrentDate, 7, 2)

If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).

The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it.mwoodward@.quinnpumps.com (Milo Woodward) wrote in message news:<1615a5e3.0308142112.30d6548c@.posting.google.com>...
> I have some columns of data in SQL server that are of NVARCHAR(420)
> format but they are dates. The dates are in DD/MM/YY format. I want to
> be able to convert them to our accounting system format which is
> YYYYMMDD. I know the format is strange but it will make things easier
> in the long run if all of the dates are the same when working between
> the 2 different databases. Basically, I need to take a look at the
> year portion (with a SUBSTRING function maybe) to see if it is greater
> than 50 (there will not be any dates that are less than 1950) and if
> it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
> month and day from the rest to form the date we need in NUMERIC(8).
> So, a date of January 17, 2003 (currently in the format of 17/01/03)
> would become 20030117. In VB, the function I would write is something
> like the following:
> /*
> Dim sCurrentDate as String
> Dim sMon as string
> Dim sDay as String
> Dim sYear as String
> Dim sNewDate as String
> sCurrentDate = "17/01/03"
> sMon = Mid(sCurrentDate, 4, 2)
> sDay = Mid(sCurrentDate, 1, 2)
> sYear = Mid(sCurrentDate, 7, 2)
> If sYear < 50 Then
> sYear = "20" & sYear
> ElseIf sYear > 50 Then
> sYear = "19" & sYear
> End if
> sNewDate = sYear & sMon & sDay
> */
> I was thinking of doing this in a Stored Procedure but am really rusty
> with SQL (it's been since college).
> The datatype would end up being NUMERIC(8). How I would write it if I
> new how to write it would be: grab the column name prior to the
> procedure, create a temp column, format the values, place them into
> the temp column, delete the old column, and then rename the temp
> column to the name of the column that I grabbed in the beginning of
> the procedure. Most likely this is the only way to do it but I have no
> idea how to go about it.

I strongly suggest that you rethink your approach, and change the
column to datetime. You can then do date calculations using the
standard functions (DATEADD etc.), compare the values to datetime
variables without conversion, etc. You can use CONVERT() to extract
dates in a particular format for passing to other systems.

Using numeric will give you serious problems in the long run, although
I appreciate that you may have limited control over the data model.
But if you really have no option but to use numeric, then this should
work (assuming that as you said, all dates are 1950 or later):

update dbo.MyTable
set DateColumn = convert(char(8), convert(datetime, DateColumn, 3),
112)

alter table dbo.MyTable
alter column DateColumn numeric(8)

Simon

Altering a stored procedure

I am modifying a stored procedure to perform a check before updating the
table. I wanted to run tnis if else stmt to see if there is a better way to
perform the task.
The original sp generates a account number for a customer and updates the
table with the new account number.
I want to add a IF stmt to check if the account number already exists before
generating the account number, Print a message that the account # already
exsits and it will keep checking until a new account# appears.
The code:
IF (SELECT @.accountnumber <> @.accountnumberinput) FROM tablekeys
WHERE keyname = 'accountnumber')
BEGIN
UPDATE tablekeys SET @.accountnumberinput = currentvalue = (currentvalue /
2) +
((currentvalue % 2 + ((currentvalue / 8) % 2)) % 2) * POWER(2, 30)
WHERE keyname = 'accountnumber'
SELECT @.accountnumber = db.rcutil_inttobasex(@.accountnumberinput,
'0123456789BCDFGHJKLMNPQRSTVWXYZ')
END
ELSE
PRINT 'Account Number already exists, trying again'Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:

> I am modifying a stored procedure to perform a check before updating the
> table. I wanted to run tnis if else stmt to see if there is a better way t
o
> perform the task.
> The original sp generates a account number for a customer and updates the
> table with the new account number.
> I want to add a IF stmt to check if the account number already exists befo
re
> generating the account number, Print a message that the account # already
> exsits and it will keep checking until a new account# appears.
>
This is not possible with a stored procedure because a stored proc is not
made for interactive communication. The stored proc can only send back
something (a value, a result set, an error message) to the calling
application, the rest must be done by the application and not by the stored
procedure.
So you can do the check and if it fails you can send back the message to
the calling application, then the calling application shows the message to
the user, the user enters a new account number and the application calls
the stored proc again with the new account number and so on...
By the way, your IF looks wrong for me, is this working? I think not.
For example, if i want to check if the @.newaccountnumber exists in the
accounttable, i would write the statement this way:
IF exists(select * from accounttable where accountnumber =
@.newaccountnumber) begin
raiserror('account number exists',16,1)
return -1
end
...
But if i am wrong, forget this sample :-))
bye, Helmut|||The user doesn't enter the account number. The stored procedure generates th
e
account number and assigns it the customer or user by updating the table.
I was just displaying a message but it is not necessary. I just wanted to
perform a check within the procedure to check the account prior to generatin
g
the new account number.
Therefore, there is nothing entered by the user or application for
interaction with the sp.
Would I still use your sample?
"Helmut Woess" wrote:

> Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:
>
> This is not possible with a stored procedure because a stored proc is not
> made for interactive communication. The stored proc can only send back
> something (a value, a result set, an error message) to the calling
> application, the rest must be done by the application and not by the store
d
> procedure.
> So you can do the check and if it fails you can send back the message to
> the calling application, then the calling application shows the message to
> the user, the user enters a new account number and the application calls
> the stored proc again with the new account number and so on...
> By the way, your IF looks wrong for me, is this working? I think not.
> For example, if i want to check if the @.newaccountnumber exists in the
> accounttable, i would write the statement this way:
> IF exists(select * from accounttable where accountnumber =
> @.newaccountnumber) begin
> raiserror('account number exists',16,1)
> return -1
> end
> ...
> But if i am wrong, forget this sample :-))
> bye, Helmut
>|||Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:

> The user doesn't enter the account number. The stored procedure generates
the
> account number and assigns it the customer or user by updating the table.
> I was just displaying a message but it is not necessary. I just wanted to
> perform a check within the procedure to check the account prior to generat
ing
> the new account number.
> Therefore, there is nothing entered by the user or application for
> interaction with the sp.
> Would I still use your sample?
Hm, okay, sorry, my english is not the best, propably i missunderstand you.
And i cannot find out, why there is @.accountnumberinput, if nothing is
entered by user or application. So i don't know how you will generate a
unique accountnumber if the first generated number is not unique..?
I would need more input because i don't understand your question :-(
bye, Helmut|||It is grapping that value from another table.
When a new user is added via the Web UI, a new account number generated and
added to the table along with the customer information that was entered by
the user. The account number is not entered by the user, the system assigns
this number via the store procedure.
Currently, when a new user is added under an exisitng account, that user
shares the same account number. We do not want this to happen. We want each
user, rather with the same company or under the same account name to have
their own account number.
Therefore, I wanted to alter the existing stored procedure to add a check
clause. If the new user that is being added and the system tries to assigned
an existing account number to the new user, I want a flag or check clause to
not assigned the user the same acct # but generated a new one and assigned i
t
to the new user.
I hope that makes more sense.
Actually, I think I need to perform this check in another stored procedure
that is creating the account information. I will post that code in a few
minutes. Thanks
"Helmut Woess" wrote:

> Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:
>
> Hm, okay, sorry, my english is not the best, propably i missunderstand you
.
> And i cannot find out, why there is @.accountnumberinput, if nothing is
> entered by user or application. So i don't know how you will generate a
> unique accountnumber if the first generated number is not unique..?
> I would need more input because i don't understand your question :-(
> bye, Helmut
>sql

Altering (or recreating) a Stored Procedure "header"

We are using SQL Server 2005 to develop a simple SP. We started by including an output parameter which would report back the identity of the record being inserted or updated. We have since been trying to drop and recreate the SP without the output parameter, or alter the SP with the same outcome in mind. Neither has been succeeding, as confirmed by inspection of the sys.objects and sys.parameters tables. What might we be missing? We are using the Developer Edition, which may or may not be adequate to the task. Or maybe earlier versions of SQL Server are more robust and would be more successful to help us succeed? Please advise. Thank you.Could you please explain how you are recreating the SP? If you are doing it from the UI or something then you may want to post this question in the Tools forum. Otherwise, please post the DDL statement(s) and the reprot steps.|||I believe I see what we were (or in this case weren't) doing... The USE statement is necessary to point the scripts to the correct database. We were seeing the outcome of confusing the master database with our application database. Thanks much for anyone stopping to consider our "dilemma".|||In essence, we are checking for existence of the stored procedure in the system table first, I believe sys.objects. If we find it there first, we drop it. Then we follow up by recreating it. But, as I mentioned in a follow up to our original post, the issue turned out to be a case of not using the USE statement. So what I thought was showing up in our application database was actually showing up in the master database. Not quite what we were shooting for. So hence the confusion.

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
>
>sql

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> > Is there a system table that has the date when the strored procedure was
> > altered?
> > thanks
>
>

Alteration date

THere's a create date stored in Sysobjects, but is it true that there's no track of alteration dates?I also have been unable to find one.
In my case I'm planning to use sp_table_validation to generate a checksum to determine whether a table has been altered.

- Andy Abel|||About sp_table_validation - is there anything similar for other objects than tables?

I thought about searching for a certain string in an SP's code, a string where the user that made the last change wrote his/her signature as a comment. Our developers are fairly disciplined...
But, the 'text' attribute of syscomments was hard to understand, since selecting it using left() and substr() functions gave very different results compared to doing just a select on the attribute. I need to use a string function to truncate the 'text' attribute because it's so wide.|||Rather than using substring() you could pattern matching if you know the form of the comment you're looking for. i.e. you could use:

where text like '%Version%'

or use patindex() with substr() to locate the beginning of your comment section and do a substring from that point.

substring(text,PATINDEX('%Version%', text),255)

- Andy Abel

Alter table script

Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
this.
> which
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got.
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> into
> this.
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
persist
>into
VB.NET)
do
>this.
constraints etc
>
>

Alter table script

Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I want to insert a new column in a data table using code (from VB.NET)
> > Is there a stored procedure or does any example script exists to do
this.
> >
> > I need to take in account any existing keys, indexes, constraints etc
> which
> > exist on the table.
> >
> > Thanks
> > Tim
> >
> >
> >
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got. :)
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> > Do you want to:
> >
> > 1) Add a column to an SQL table through .NET? or
> > 2) Add a column to a DataTable in .NET? or
> > 3) Add a column through a DataTable in .NET and have the change persist
> into
> > the source table in SQL?
> >
> > Christian Smith
> >
> > "Tim Marsden" <TM@.UK.COM> wrote in message
> > news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I want to insert a new column in a data table using code (from VB.NET)
> > > Is there a stored procedure or does any example script exists to do
> this.
> > >
> > > I need to take in account any existing keys, indexes, constraints etc
> > which
> > > exist on the table.
> > >
> > > Thanks
> > > Tim
> > >
> > >
> > >
> >
> >
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Do you want to:
>> 1) Add a column to an SQL table through .NET? or
>> 2) Add a column to a DataTable in .NET? or
>> 3) Add a column through a DataTable in .NET and have the change
persist
>into
>> the source table in SQL?
>> Christian Smith
>> "Tim Marsden" <TM@.UK.COM> wrote in message
>> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > I want to insert a new column in a data table using code (from
VB.NET)
>> > Is there a stored procedure or does any example script exists to
do
>this.
>> >
>> > I need to take in account any existing keys, indexes,
constraints etc
>> which
>> > exist on the table.
>> >
>> > Thanks
>> > Tim
>> >
>> >
>> >
>>
>
>

alter table problem?

in a stored procedure , i've the following coding
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>

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 Table in Stored Procedure

Hi,
The script in the stored procedure below works. But, when creating the
stored procedure, I only see the first 'if'. There is nothing in there.
I tried through Enterprise Manager as well. Anybody knows what could be
causing this?
Thanks,
CREATE PROCEDURE sp_ImportKEYBANKAccountsFeed
AS
-- Drop Constraints
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblAccounts_tblAccountTypes]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccounts] DROP CONSTRAINT
[FK_tblAccounts_tblAccountTypes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblAccTransactions_tblTransactionTyp
es]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccTransactions] DROP CONSTRAINT
[FK_tblAccTransactions_tblTransactionTyp
es]
GO
-- Clean tables
TRUNCATE TABLE [dbo].[tblAccounts]
GO
TRUNCATE TABLE [dbo].[tblAccountTypes]
GO
TRUNCATE TABLE [dbo].[tblAccTransactions]
GO
TRUNCATE TABLE [dbo].[tblTransactionTypes]
GO
-- Populate tables
INSERT INTO tblAccountTypes
(
AccountTypeID,
TypeDesc
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractAccountTypes
GO
INSERT INTO tblTransactionTypes
(
TransactionTypeID,
TransactionDesc
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractTranCodes
GO
INSERT INTO tblAccounts
(
KeyBankAccID,
KeyBankCustomerID,
CACNO,
AccountTypeID,
Balance,
InterestRate,
DateOpened,
MaturityDate,
MonthlyDepositAmt,
DateDeposit,
Settlement,
FinalPaymentDate,
DateMonthlyPmtDue,
DirectDebitDetails,
ArrearsAmt,
ProductDescription,
LedgerCd
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractESBAccounts
GO
INSERT INTO tblAccounts
(
KeyBankAccID,
KeyBankCustomerID,
CACNO,
AccountTypeID,
Balance,
InterestRate,
DateOpened,
MaturityDate,
MonthlyDepositAmt,
DateDeposit,
Settlement,
FinalPaymentDate,
DateMonthlyPmtDue,
DirectDebitDetails,
ArrearsAmt,
ProductDescription,
LedgerCd
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractSavingsAccounts
GO
INSERT INTO tblAccounts
(
KeyBankAccID,
KeyBankCustomerID,
CACNO,
AccountTypeID,
Balance,
InterestRate,
DateOpened,
MaturityDate,
MonthlyDepositAmt,
DateDeposit,
Settlement,
FinalPaymentDate,
DateMonthlyPmtDue,
DirectDebitDetails,
ArrearsAmt,
ProductDescription,
LedgerCd
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractPLAccounts
GO
INSERT INTO tblAccTransactions
(
KeyBankTransID,
KeyBankCustomerID,
AccountID,
TransactionTypeID,
Reference,
Debit,
Credit,
Balance,
Arrears,
BookingDate,
Amount,
Narrative
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractTrans
GO
--Add Constraints back to tables
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblAccounts_tblAccountTypes]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccounts] ADD CONSTRAINT
[FK_tblAccounts_tblAccountTypes]
FOREIGN KEY ([AccountTypeID]) REFERENCES [tblAccountTypes]
([AccountTypeID])
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblAccTransactions_tblTransactionTyp
es]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccTransactions] ADD CONSTRAINT
[FK_tblAccTransactions_tblTransactionTyp
es]
FOREIGN KEY([TransactionTypeID] ) REFERENCES [tblTransactionTypes]
([TransactionTypeID])
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*** Sent via Developersdex http://www.examnotes.net ***That's because GO is a batch terminator
If you do sp_helptext 'ImportKEYBANKAccountsFeed ' you will see that
the procedure stops after the first GO
Take out the GO's
Denis the SQL Menace
http://sqlservercode.blogspot.com/