TableAdapter to create new SQL stored proc with different owner

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Hi Gang,
I'm curious if this is a bug in Studio or not. Here's the background: Our
system has SQL Server installed and our DBA's all create our databases. We
are not DBOs in any of those databases. We do have rights to create stored
procs, they all show up as our name in the owner field. Such as
dbase.Nick.TableName where Nick is the spot for dbo.

Now if we go into Studio 2005/2008, it doesn't matter which. Drop a dataset
into the system and drag and drop a table on to the canvas. Go ahead and
step through all of the steps to get your table adapter on the canvas.

now, add a new query and tie it to "new stored procedure" where the table
adapter config wizard will generate the stored proc for you. What it does is
picks up our Windows credentials in the owner field and ultimately causes the
wizard to blow up.

So this is what it would try to create: camp.DOMAIN\Nick.tableName instead
of something like camp.[Domain\Nick].TableName, or ulimately we'd prefer,
camp.dbo.table name.

Has anybody seen error occuring?

I'm hoping there is a setting where we could possibly control what database
owner type to use, or possibly come up with a customized table adapter
configuration wizard.

Any help would be appreciated.

Thanks,
Nick
 
Have you created a Profiler trace to get a more accurate understanding
of what's going on under the covers? One of the dba's may need to do
this for you, depending on the permissions you have been granted.

--Mary
 
Are you talking a SQL Profiler Trace? No, I haven't done that. That's one
where the DBAs would need to run.

But, if I have full rights to do a create on a stored proc, and changes it's
owner to dbo, etc. then why can't I do it from inside of Studio 2008's table
adapter? That seems to be where it's not working.

it's almost as if I need the tableadapter config wizard to ask me what owner
to use and then I'd just put in dbo.



Mary Chipman said:
Have you created a Profiler trace to get a more accurate understanding
of what's going on under the covers? One of the dba's may need to do
this for you, depending on the permissions you have been granted.

--Mary

Hi Gang,
I'm curious if this is a bug in Studio or not. Here's the background: Our
system has SQL Server installed and our DBA's all create our databases. We
are not DBOs in any of those databases. We do have rights to create stored
procs, they all show up as our name in the owner field. Such as
dbase.Nick.TableName where Nick is the spot for dbo.

Now if we go into Studio 2005/2008, it doesn't matter which. Drop a dataset
into the system and drag and drop a table on to the canvas. Go ahead and
step through all of the steps to get your table adapter on the canvas.

now, add a new query and tie it to "new stored procedure" where the table
adapter config wizard will generate the stored proc for you. What it does is
picks up our Windows credentials in the owner field and ultimately causes the
wizard to blow up.

So this is what it would try to create: camp.DOMAIN\Nick.tableName instead
of something like camp.[Domain\Nick].TableName, or ulimately we'd prefer,
camp.dbo.table name.

Has anybody seen error occuring?

I'm hoping there is a setting where we could possibly control what database
owner type to use, or possibly come up with a customized table adapter
configuration wizard.

Any help would be appreciated.

Thanks,
Nick
Click to expand...
Click to expand...
 
TableAdapters weren't designed for creating stored procedures, so that
would be the likely reason *why* it doesn't work.

I'd recommend using a SqlCommand to pass the T-SQL DDL statements if
creating sprocs is something that you absolutely must do from your
client code.

--Mary

Are you talking a SQL Profiler Trace? No, I haven't done that. That's one
where the DBAs would need to run.

But, if I have full rights to do a create on a stored proc, and changes it's
owner to dbo, etc. then why can't I do it from inside of Studio 2008's table
adapter? That seems to be where it's not working.

it's almost as if I need the tableadapter config wizard to ask me what owner
to use and then I'd just put in dbo.



Mary Chipman said:
Have you created a Profiler trace to get a more accurate understanding
of what's going on under the covers? One of the dba's may need to do
this for you, depending on the permissions you have been granted.

--Mary

Hi Gang,
I'm curious if this is a bug in Studio or not. Here's the background: Our
system has SQL Server installed and our DBA's all create our databases. We
are not DBOs in any of those databases. We do have rights to create stored
procs, they all show up as our name in the owner field. Such as
dbase.Nick.TableName where Nick is the spot for dbo.

Now if we go into Studio 2005/2008, it doesn't matter which. Drop a dataset
into the system and drag and drop a table on to the canvas. Go ahead and
step through all of the steps to get your table adapter on the canvas.

now, add a new query and tie it to "new stored procedure" where the table
adapter config wizard will generate the stored proc for you. What it does is
picks up our Windows credentials in the owner field and ultimately causes the
wizard to blow up.

So this is what it would try to create: camp.DOMAIN\Nick.tableName instead
of something like camp.[Domain\Nick].TableName, or ulimately we'd prefer,
camp.dbo.table name.

Has anybody seen error occuring?

I'm hoping there is a setting where we could possibly control what database
owner type to use, or possibly come up with a customized table adapter
configuration wizard.

Any help would be appreciated.

Thanks,
Nick
Click to expand...
Click to expand...
Click to expand...
 
I guess I look at it this way, if we weren't suppose to be able to create the
procs, then why is it there. :)

I actually believe it the wizard either needs a way to set a database owner
or go ahead and allow a stored proc to be created with current credentials.

It's not that it's a requirement to create the proc from client code, I can
do it from within SQL Studio, it's more a way to try to keep everything
inside of VS. I'm already there creating everything else for the database,
why not the stored procs as well.

William Vaughn said:
Ah, I'm not sure I would go that far. It seems I need to work out the steps
to get this to work--not that it's an ideal approach, but it is something
you should be able to do.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
Mary Chipman said:
TableAdapters weren't designed for creating stored procedures, so that
would be the likely reason *why* it doesn't work.

I'd recommend using a SqlCommand to pass the T-SQL DDL statements if
creating sprocs is something that you absolutely must do from your
client code.

--Mary

Are you talking a SQL Profiler Trace? No, I haven't done that. That's one
where the DBAs would need to run.

But, if I have full rights to do a create on a stored proc, and changes
it's
owner to dbo, etc. then why can't I do it from inside of Studio 2008's
table
adapter? That seems to be where it's not working.

it's almost as if I need the tableadapter config wizard to ask me what
owner
to use and then I'd just put in dbo.



:

Have you created a Profiler trace to get a more accurate understanding
of what's going on under the covers? One of the dba's may need to do
this for you, depending on the permissions you have been granted.

--Mary

On Wed, 19 Mar 2008 19:38:01 -0700, Nick

Hi Gang,
I'm curious if this is a bug in Studio or not. Here's the background:
Our
system has SQL Server installed and our DBA's all create our databases.
We
are not DBOs in any of those databases. We do have rights to create
stored
procs, they all show up as our name in the owner field. Such as
dbase.Nick.TableName where Nick is the spot for dbo.

Now if we go into Studio 2005/2008, it doesn't matter which. Drop a
dataset
into the system and drag and drop a table on to the canvas. Go ahead
and
step through all of the steps to get your table adapter on the canvas.

now, add a new query and tie it to "new stored procedure" where the
table
adapter config wizard will generate the stored proc for you. What it
does is
picks up our Windows credentials in the owner field and ultimately
causes the
wizard to blow up.

So this is what it would try to create: camp.DOMAIN\Nick.tableName
instead
of something like camp.[Domain\Nick].TableName, or ulimately we'd
prefer,
camp.dbo.table name.

Has anybody seen error occuring?

I'm hoping there is a setting where we could possibly control what
database
owner type to use, or possibly come up with a customized table adapter
configuration wizard.

Any help would be appreciated.

Thanks,
Nick
Click to expand...
Click to expand...
Click to expand...
 
The VS wizards and designers are intended to facilitate loading data
into datasets, not for creating server-side objects. There are many
other tasks involving the creation of server-side objects that you
won't be able to perform from inside VS. Unfortunately, there's no
Swiss army knife when it comes to creating data applications; you need
to use multiple tools to get the job done efficiently.

--Mary

I guess I look at it this way, if we weren't suppose to be able to create the
procs, then why is it there. :)

I actually believe it the wizard either needs a way to set a database owner
or go ahead and allow a stored proc to be created with current credentials.

It's not that it's a requirement to create the proc from client code, I can
do it from within SQL Studio, it's more a way to try to keep everything
inside of VS. I'm already there creating everything else for the database,
why not the stored procs as well.

William Vaughn said:
Ah, I'm not sure I would go that far. It seems I need to work out the steps
to get this to work--not that it's an ideal approach, but it is something
you should be able to do.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
Mary Chipman said:
TableAdapters weren't designed for creating stored procedures, so that
would be the likely reason *why* it doesn't work.

I'd recommend using a SqlCommand to pass the T-SQL DDL statements if
creating sprocs is something that you absolutely must do from your
client code.

--Mary

On Mon, 24 Mar 2008 13:41:03 -0700, Nick

Are you talking a SQL Profiler Trace? No, I haven't done that. That's one
where the DBAs would need to run.

But, if I have full rights to do a create on a stored proc, and changes
it's
owner to dbo, etc. then why can't I do it from inside of Studio 2008's
table
adapter? That seems to be where it's not working.

it's almost as if I need the tableadapter config wizard to ask me what
owner
to use and then I'd just put in dbo.



:

Have you created a Profiler trace to get a more accurate understanding
of what's going on under the covers? One of the dba's may need to do
this for you, depending on the permissions you have been granted.

--Mary

On Wed, 19 Mar 2008 19:38:01 -0700, Nick

Hi Gang,
I'm curious if this is a bug in Studio or not. Here's the background:
Our
system has SQL Server installed and our DBA's all create our databases.
We
are not DBOs in any of those databases. We do have rights to create
stored
procs, they all show up as our name in the owner field. Such as
dbase.Nick.TableName where Nick is the spot for dbo.

Now if we go into Studio 2005/2008, it doesn't matter which. Drop a
dataset
into the system and drag and drop a table on to the canvas. Go ahead
and
step through all of the steps to get your table adapter on the canvas.

now, add a new query and tie it to "new stored procedure" where the
table
adapter config wizard will generate the stored proc for you. What it
does is
picks up our Windows credentials in the owner field and ultimately
causes the
wizard to blow up.

So this is what it would try to create: camp.DOMAIN\Nick.tableName
instead
of something like camp.[Domain\Nick].TableName, or ulimately we'd
prefer,
camp.dbo.table name.

Has anybody seen error occuring?

I'm hoping there is a setting where we could possibly control what
database
owner type to use, or possibly come up with a customized table adapter
configuration wizard.

Any help would be appreciated.

Thanks,
Nick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top