Create Table In Store Procedure and inserting data with the Kalen user.

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

Create SQL Server Objects from Command Prompts

Hi

Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??

If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts

How Can I do that .. ??

And thanks with my regarding

FraasHave a look at OSQL in SQL Server BOL

Create Script with dependent objects SQL Server 2005

Hi,

I need to script a view but with dependent objects. The view has a lot of user defined functions and manually creating the script is a pain.

There is a way to do it in SQL Server (2000) Enterprise manager where one right clicks the view --> All Tasks --> Generate Script and then Check

"Generate Scripts for all dependent objects" in the Formatting tab.

The db i am using is on SQL Server 2005.

Is there a equivalent way to do this in SQL Server 2005 Management Studio ?

regards,

Rohan Wali

Have you tried the generate script wizard in SSMS?|||You might try using SMO in this case http://www.sqlteam.com/item.asp?ItemID=23185, as I don't see using script wizard.|||

Hi,

Even i dont see a solution using the script wizard. I will try using SMO using the link provided and see how it goes from there.

Thanks for the tip.

regards,

Rohan Wali

sql

CREATE SCHEMA in db A from a stored procedure in db B

Hi All

I have a SP that i create tables and other objects on another database.

Creating table work well.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE TABLE ABC (recid int)
exec (@.s)

But if i try to create a schema it gives error :

'CREATE SCHEMA' must be the first statement in a query batch.

declare @.s nvarchar(2000)
set @.s = 'use db01'
set @.s = @.s + 'CREATE SCHEMA AAA
exec (@.s)

How can i solve it?

Thanks.

declare @.s nvarchar(2000)
set @.s = 'use db01'

exec (@.s)
set @.s = 'CREATE SCHEMA AAA
exec (@.s)

|||

Hi Asvin

If i use "use db01" in exec, when exec completes, it gives up using db01, so if i'm running script in db02, 'CREATE SCHEMA AAA' works on db02.

Best regards.

|||

Standard disclaimer. Generally not a good idea to be creating tables and schemas on the fly.

That out of the way, this method should work:

exec('use tempdb; exec sp_executesql N''create schema test''')

|||

You can do the following in SQL Server 2000/2005 (similar approach can be used in SQL70 also):

declare @.sp nvarchar(500)

set @.sp = quotename(N'db01') + N'sys.sp_executesql'

exec @.sp 'CREATE SCHEMA AAA....'

-- or

exec @.sp @.s

Create Replication in Code

Does anybody have any samples of how to enable replication and create
publication using sql-dmo objects? I am trying to create replication
in a program and all I can find are ways to create the subscription.
Anything that might get me started would be great.
Thanks.
Shane Lim
On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:

>Does anybody have any samples of how to enable replication and create
>publication using sql-dmo objects? I am trying to create replication
>in a program and all I can find are ways to create the subscription.
>Anything that might get me started would be great.
>Thanks.
>Shane Lim
Edit I am tring to do Merge Replication for Pull Subscriptions.
|||On Tue, 01 Feb 2005 11:03:52 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:

>On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
>wrote:
>
>Edit I am tring to do Merge Replication for Pull Subscriptions.
Ok I got it working. Thanks you very much Paul Ibison!!
'Paul Ibison is our Savior!!!! From
http://www.mcse.ms/archive95-2004-7-899774.html
Now all I need is a way to get a list of the current publications. So
I can give my user a list to select from to replicate too.
|||Shane,
my special powers might not be enough here - this is a
script for transactional, while you're after a merge one.
I have some similar scripts on
http://www.replicationanswers.com/Scripts.htm but they
aren't merge either. I think Hilary has one for merge - I
seem to remember him posting one up fairly recently. He's
the one.
Rgds,
Paul
|||On Wed, 2 Feb 2005 02:22:46 -0800, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:

>Shane,
>my special powers might not be enough here - this is a
>script for transactional, while you're after a merge one.
>I have some similar scripts on
>http://www.replicationanswers.com/Scripts.htm but they
>aren't merge either. I think Hilary has one for merge - I
>seem to remember him posting one up fairly recently. He's
>the one.
>Rgds,
>Paul
Paul Actually it was quite simple to create the merge publication from
your transactional example. I pretty much just changed all the objects
to there equivilent in the merge objects. It created a publication for
me just fine. I am doing subscriptions today but in my initial testing
it seems to work just Dandy. Although I am sure I will be purchasing
the not while surfs up book as soon as its available. But since the
beta is due before then I will just have to make due like so. Thanks
again.
Shane Lim

Create Procedure Permission ONLY

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

Thanks

M A Srinivas(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I have a requirement in SQL 2005 in Development database
>
1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema
>
How to achieve this using GRANT SCHEMA statement

The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:

CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev

GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev

CREATE TABLE mysig (a int NOT NULL)

EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland

Thanks
Srinivas
Erland Sommarskog wrote:

Quote:

Originally Posted by

(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

>
The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:
>
CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev
>
GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev
>
CREATE TABLE mysig (a int NOT NULL)
>
EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create procedure error on computed column.

I have the following script that was generated using SMO:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_InsertCaseNote]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[proc_InsertCaseNote]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Erin D. Rowley

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[proc_InsertCaseNote]

-- Add the parameters for the stored procedure here

@.ReasonCodeSubCategoryID int,

@.OrderGroupID uniqueidentifier,

@.NoteText text,

@.CustomerEmail varchar(75),

@.EmployeeFirstName varchar(255),

@.EmployeeLastName varchar(255)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)

values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY

END

GO

But when I try to run it (in SQL Management Studio) I get the following error:

Msg 271, Level 16, State 1, Procedure proc_InsertCaseNote, Line 18

The column "DateCreated" cannot be modified because it is either a computed column or is the result of a UNION operator.

Any ideas on how to debug this problem?

Thank you.

Kevin

Please post the table DDL.|||

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

|||

Arnie Rowland wrote:

Please post the table DDL.

Sorry but I am not sure how to do this. The script that I am running is creating a stored procedure not a table that is why the error is so strange.

Kevin

|||

Louis Davidson wrote:

It seems really odd that it DateCreated would be a computed column, but I would also expect that you would know if it was a result of a Union

You can check to see if it is a computed column like this:

create table test
(
notComputed datetime,
computed as getdate()
)
go

select name, is_computed
from sys.columns
where object_id('dbo.test') = object_id
go

Returns:

name is_computed
- --
notComputed 0
computed 1

If you want to see the definition (and other good stuff) use sys.computed_columns:

select name, definition
from sys.computed_columns
where object_id('dbo.test') = object_id
and name = 'computed'

name definition
--
computed (getdate())

Thank you. The stored procedure is "automatically" filling in the data for this column through GetDate(). If you were to create a stored procedure and then try to install it on another computer what would your script look like? I am just relying on the script produced by SMO.

Kevin

|||

Right click the table in SSMS, click "Script table to..."

The error is not really all that strange, it is not letting your procedure do something that won't work.

|||

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

|||

Tom Werz wrote:

Without seeing the DDL for the table, this is hard to anwser. My guess is that this column was added to the table like this:

Alter table CaseNotes add DateCreated as (getdate())

This would make DateCreated be a computed column which is always set to the current date, not the date the row was inserted. This would not be what you want. If you don't have access to see the table structure for some reason, look at the data in the table and verify that the dates are not all the same. If they are all exactly the same, then you know this is the issue.

What you really want is for DateCreated to have a default of Getdate(), not be a computed column using this statement:

Alter table CaseNotes add DateCreated datetime default getdate()

-Tom

The table looks like:

/****** Object: Table [dbo].[CaseNotes] Script Date: 05/07/2007 20:49:37 ******/
CREATE TABLE [dbo].[CaseNotes](
[CaseNotesID] [int] IDENTITY(1,1) NOT NULL,
[ReasonCodeSubCategoryID] [int] NOT NULL,
[OrderGroupId] [uniqueidentifier] NOT NULL,
[NoteText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerEmail] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeFirstName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeLastName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_CaseNotes] PRIMARY KEY CLUSTERED
(
[CaseNotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CaseNotes] WITH CHECK ADD CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory] FOREIGN KEY([ReasonCodeSubCategoryID])
REFERENCES [dbo].[ReasonCodeSubCategory] ([ReasonCodeSubCategoryID])
GO
ALTER TABLE [dbo].[CaseNotes] CHECK CONSTRAINT [FK_CaseNotes_ReasonCodeSubCategory]

The stored procedure is written so that when the row is added the DataCreated is set to the current date when the row is added. I am not sure if I understand what you are suggesting. Does this "create" script help? The stored procedure "works" as is. It seems that I am having a hard time creating a script to create it on another SQL server.

Reproduced here for reference.

USE [BuySeasons]
GO
/****** Object: StoredProcedure [dbo].[proc_InsertCaseNote] Script Date: 05/07/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Erin D. Rowley
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[proc_InsertCaseNote]
-- Add the parameters for the stored procedure here
@.ReasonCodeSubCategoryID int,
@.OrderGroupID uniqueidentifier,
@.NoteText text,
@.CustomerEmail varchar(75),
@.EmployeeFirstName varchar(255),
@.EmployeeLastName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
insert into CaseNotes (ReasonCodeSubCategoryID, OrderGroupID, NoteText, CustomerEmail, EmployeeFirstName, EmployeeLastName, DateCreated)
values (@.ReasonCodeSubCategoryID, @.OrderGroupID, @.NoteText, @.CustomerEmail, @.EmployeeFirstName, @.EmployeeLastName, GetDate())

return @.@.IDENTITY
END

Thank you for your suggestions.

Kevin

Create objects from a configuration file

Anyone think this is a good idea?
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=42c20106-de27-4fb8-88e4-1bf13598c4e1

-Jamie

Hello Jamie,

How far would you go?

If I choose to export a whole package worth of configurations are you saying

that I should be able to take this to a blank package, point it at the config

file and it would at "Just Before Runtime" create everything for me?

You can imagine the hit associated with this, validation would be costly.

What about Connection Managers? sensitive data?

How would you design workflow in a configuration file?

Allan

> Anyone think this is a good idea?

> http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedba

> ck id=42c20106-de27-4fb8-88e4-1bf13598c4e1

>

> -Jamie

>

|||Nope, I'm not saying that at all. This isn't a runtime thing - just an aid to developer productivity.

e.g. All my packages will be inserting into the same database. I have a configuration set up for a connection manager for that database. Whenever I start to build a new package I can just point at an existing configuration and say "create the object that this configuration references in my current package". Then the developer continues as normal building his/her package with whatever functionality it requires.

-Jamie|||Would this approach be extended to putting standard code for error handling, logging, event handling, etc...?|||

Not really. This is talking about something alot more specific than that.

Create objects calling other scripts.

Hello,
Can anybody tell me, how can i create one script that calls other scripts.
For example, i need to create object1, object2, object3 but for control
created releases i cant create my objects in the same script, so all that i
want its to create one 'run_all.sql' script that calls
'object1.sql','object2.sql', 'object3.sql'.
Can you give me any ideas, i've done this with isql or osql but i always
need to authenticate my self.
Thanks and best regards
A workaround would be to use a batch file to call osql for each script you
have.
Cristian Lefter, SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CC7ABEB1-AA65-468B-904A-88E42D5D2EDB@.microsoft.com...
> Hello,
> Can anybody tell me, how can i create one script that calls other scripts.
> For example, i need to create object1, object2, object3 but for control
> created releases i cant create my objects in the same script, so all that
> i
> want its to create one 'run_all.sql' script that calls
> 'object1.sql','object2.sql', 'object3.sql'.
> Can you give me any ideas, i've done this with isql or osql but i always
> need to authenticate my self.
> Thanks and best regards

Create objects calling other scripts.

Hello,
Can anybody tell me, how can i create one script that calls other scripts.
For example, i need to create object1, object2, object3 but for control
created releases i cant create my objects in the same script, so all that i
want its to create one 'run_all.sql' script that calls
'object1.sql','object2.sql', 'object3.sql'.
Can you give me any ideas, i've done this with isql or osql but i always
need to authenticate my self.
Thanks and best regardsA workaround would be to use a batch file to call osql for each script you
have.
Cristian Lefter, SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CC7ABEB1-AA65-468B-904A-88E42D5D2EDB@.microsoft.com...
> Hello,
> Can anybody tell me, how can i create one script that calls other scripts.
> For example, i need to create object1, object2, object3 but for control
> created releases i cant create my objects in the same script, so all that
> i
> want its to create one 'run_all.sql' script that calls
> 'object1.sql','object2.sql', 'object3.sql'.
> Can you give me any ideas, i've done this with isql or osql but i always
> need to authenticate my self.
> Thanks and best regards

Create objects calling other scripts.

Hello,
Can anybody tell me, how can i create one script that calls other scripts.
For example, i need to create object1, object2, object3 but for control
created releases i cant create my objects in the same script, so all that i
want its to create one 'run_all.sql' script that calls
'object1.sql','object2.sql', 'object3.sql'.
Can you give me any ideas, i've done this with isql or osql but i always
need to authenticate my self.
Thanks and best regardsA workaround would be to use a batch file to call osql for each script you
have.
Cristian Lefter, SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CC7ABEB1-AA65-468B-904A-88E42D5D2EDB@.microsoft.com...
> Hello,
> Can anybody tell me, how can i create one script that calls other scripts.
> For example, i need to create object1, object2, object3 but for control
> created releases i cant create my objects in the same script, so all that
> i
> want its to create one 'run_all.sql' script that calls
> 'object1.sql','object2.sql', 'object3.sql'.
> Can you give me any ideas, i've done this with isql or osql but i always
> need to authenticate my self.
> Thanks and best regards