problem with SQLSTATE

create function
"DBO".sf_GetStateID( @.Abbr char(2))
returns integer
begin
declare @.StateID integer;
set @.Abbr=UPPER(@.Abbr);
if @.Abbr is null
set @.Abbr=''
set @.StateID=53;

select MIN(lngStateID) into StateID from "DBA".States where strAbbr=@.Abbr;

if @.StateID is null
insert into States(strAbbr,strName) values(@.Abbr,@.Abbr)

if SQLSTATE = '00000'

set @.StateID=@.@.IDENTITY
return(@.StateID)
end

In this function, I am getting error at SQLSTATE = '00000'. I saw in books online and it says that SQLSTATE is a keyword. It is not recognizing SQLSTATE as a keyword in the function.
Can you help in that?

2nd Error: It is not accepting the statement
select MIN(lngStateID) into StateID from "DBO".States where strAbbr=@.Abbr;

I don't see any problem in the above statement. Do help in solving these two problems.

Tks
K.The 2 problems is that sqlstate is used for odbc/embedded sql - not for udfs. Also, you are returning a scalar in the udf and not a table (for the 2nd error).sql

Problem with sp_change_users_login

Hello,
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
Ragnar
Use "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>
|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnar

Problem with sp_change_users_login

Hello,
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
RagnarUse "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnarsql

Problem with sp_change_users_login

Hello,
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
RagnarUse "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnar

Problem with SP

Hello All!
CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
@.Wager Nvarchar(50))
AS
IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
begin
Update BankRoll
Set Wager = Wager + CAST(@.Wager as Nvarchar)
where userID = @.userID
return -1
end
ELSE
begin
Insert Bankroll (Wager) Values (@.Wager)
RETURN @.@.IDENTITY
end
GO
Having a little problem with the SP above. I'm trying to take a value from
the a "Wage" textbox, and update the value in the table. The userID number
is a value in the userID texbox, and already created in the SQL table. I
need to make sure the the wage value will go in the right row, where the use
r
ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
Procedure PlbetII, Line 13
Cannot insert the value NULL into column 'UserID', table
'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The 'PlbetII' procedure attempted to return a status of NULL, which is not
allowed. A status of 0 will be returned instead."
I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager +
CAST(@.Wager as Nvarchar)
where userID = @.userID" might be my problem, but I thought it's just
returning the value for user id and selecting it. I'm so .
Any help would be great!!!
TIA!!
RudyThe identity value (or autoincrement) for the column userId is not set,
hence the impossibility of inserting a new record at line 13 without
specifying the value for UserId. When creating a new record, you have two
choice: specify the value for UserId or ask SQL-Server to automatically
create a new value each time by setting the identity property.
From you piece of code, it's impossible to tell which one of these two
methods you should use.
Also, I'm not sure if the line Set Wager = Wager + CAST(@.Wager as
Nvarchar) will do what you are expecting it to do.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:B0D0D405-F131-4106-8602-E5F77D684B4D@.microsoft.com...
> Hello All!
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from
> the a "Wage" textbox, and update the value in the table. The userID
> number
> is a value in the userID texbox, and already created in the SQL table. I
> need to make sure the the wage value will go in the right row, where the
> user
> ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
> Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager
> +
> CAST(@.Wager as Nvarchar)
> where userID = @.userID" might be my problem, but I thought it's just
> returning the value for user id and selecting it. I'm so .
> Any help would be great!!!
> TIA!!
> Rudy
>
>|||Rudy (Rudy@.discussions.microsoft.com) writes:
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from the a "Wage" textbox, and update the value in the table. The
> userID number is a value in the userID texbox, and already created in
> the SQL table. I need to make sure the the wage value will go in the
> right row, where the user ID matches. I get an error in SQL,
> "Server: Msg 515, Level 16, State 2, Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID.
Obviously the table does not have the IDENTITY property. If I am to
believe your procedure, the UserID column is nvarchar(50), why IDENTITY
is completely off-track.
Just say:
Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
By the way, the RETURN statement is mainly used to indicate
success/failure, with success being 0 and anything else means an
error. The regular way to return a value is to use an OUTPUT
parameter.
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 and Sylvain!
I did change things up a little bit per you sugestions. Still having
problems, but I suspect something with the table itself. I deleted the table
,
and built it again, and now it works fine.
Thanks again!
Rudy
"Erland Sommarskog" wrote:

> Rudy (Rudy@.discussions.microsoft.com) writes:
> Obviously the table does not have the IDENTITY property. If I am to
> believe your procedure, the UserID column is nvarchar(50), why IDENTITY
> is completely off-track.
> Just say:
> Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
> By the way, the RETURN statement is mainly used to indicate
> success/failure, with success being 0 and anything else means an
> error. The regular way to return a value is to use an OUTPUT
> parameter.
> --
> 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
>|||Can run the following query and post the output.
select COLUMNPROPERTY( object_id('Bankroll') , 'pub_id' , 'AllowsNull' ) as
nullable,
COLUMNPROPERTY( object_id('Bankroll') , 'userID' , 'IsIdentity' ) as
[autogenID]
And a few observations:
Why is UserID nvarchar?
and why do you do this step?
Wager + CAST(@.Wager as Nvarchar)
when @.wager is already nvarchar.. and are you trying to append the string
wager or add the wager to the existing value? Is Wager a numeric value or ha
s
characters like the dollar and pounds.
It will be better to answer your question if you give table definition and
test data
so that we can reproduce the error here.

Problem with simple Where Clause

Please Help me. I have a Stored Proc as follows:

USE feesched
GO

CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]
AS

-- copy records with alpha in pos 1 that's not J
SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode
INTO t_AveragedMedicare
FROM AveragedMedicare
WHERE NOT ISNUMERIC(LEFT([cpt code],1))
AND LEFT([cpt code],1) <> 'J'

I'm getting the following error message:
Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare, Line 10
Incorrect syntax near the keyword 'AND'.

Can anyone help me out?
Thanks!
Tony"Tony" <topoulos@.mchsi.com> wrote in message
news:6b5416f0.0403041238.31850940@.posting.google.c om...
> Please Help me. I have a Stored Proc as follows:
> USE feesched
> GO
> CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]
> AS
> -- copy records with alpha in pos 1 that's not J
> SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode
> INTO t_AveragedMedicare
> FROM AveragedMedicare
> WHERE NOT ISNUMERIC(LEFT([cpt code],1))
> AND LEFT([cpt code],1) <> 'J'
> I'm getting the following error message:
> Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare,
Line 10
> Incorrect syntax near the keyword 'AND'.
> Can anyone help me out?
> Thanks!
> Tony

ISNUMERIC() returns an integer, so I guess you want this:

WHERE ISNUMERIC(LEFT([cpt code],1)) = 0

However, be aware that ISNUMERIC() returns 1 for many things which you may
not consider to be a number, as it will evaluate conversion to any numeric
data type, not just integers. Perhaps this would be better:

WHERE [cpt code] NOT LIKE '[0-9]%'
AND [cpt code] NOT LIKE 'J%'

Also, note that using procedure names beginning with sp_ is usually reserved
for system stored procedures, and isn't recommended for application code.

Simon

problem with select stored procedure

i have this stored procedure:

ALTER PROCEDURE dbo.SearchContact
@.searchCriteria nvarchar(128)
AS
Select FstNam1,FstNam02 from Contacts where FstNam1 like '%'+@.searchCriteria+'%'
RETURN

In my .aspx i have a SqlDataSource named SqlDataSource1 which have asocciated this stored procedure to select operation and parameter source of 'searchCriteria' is Control and ControlID is "TextBox1".

Also i have a gridview with source this sqlDataSource1 and a button . When i click this button i want to take value entered in textbox and send it to above stored procedure and show returned infos in my gridview.

i put in

Button1_Click(object sender, EventArgs e)

{

SqlDataSource1.SelectParameters["searchCriteria"].DefaultValue = Session["searchContact"].ToString();

xxxxxx
}

what i need to have to xxxxx to work fine ?

I searched for a solution and i find a lot of posibilities, but all are fragmented.......pls give a solution ...

thx for help.

P.S: Sorry for my english.

try use valid parameter name:

SqlDataSource1.SelectParameters["@.searchCriteria"].DefaultValue = Session["searchContact"].ToString();

|||

Get rid of the Button1_Click event handler, and in the source view of your aspx, make sure that the SqlDataSource is bound to the GridView (DataSourceID="SqlDataSource1"), and add a SelectParameter to the SqlDataSource:

<SelectParameters>
<asp:ControlParameter ControlID="TextBox1"
Name="searchCriteria"
PropertyName="Text"
Type="String" /> />
</SelectParameters>

Also, I don't know why you are storing the search phrase in a Session variable. This doesn't seem necessary, unless you are using it for something else.

|||

nice. I understand do not use session variable for this , but after i do that , what`s next to see a result?

thx.

|||

that`s the code from aspx.cs

protected void Button1_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.SelectParameters["@.searchCriteria"].DefaultValue = TextBox1.Text;
GridView1.DataBind();
}
catch (Exception ex)
{
}
finally
{

}

that`s the code from aspx.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
SelectCommand="SearchContact" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="searchCriteria" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

when i debug that function, i receive a exception : "System.NullReferenceException: Object reference not set to an instance of an object."

how fix this problem???

|||

Like I said earlier, get rid of the code:

protected void Button1_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.SelectParameters["@.searchCriteria"].DefaultValue = TextBox1.Text;
GridView1.DataBind();
}
catch (Exception ex)
{
}
finally
{

}

You don't need this. There are two ways to perform data access. One is to use a SqlDataSource and let that do all the work for you, and the other is to write code. You are mixing the two. All you need in your aspx is a textbox, button, gridview and data source control. No code in the code behind at all.

|||

Mikesdotnetting, i remove the "protected void Button1_Click(object sender, EventArgs e)" and in .apx.cs is clear ( no line of code), and .aspx is the same. I don`t see any change.

Pls help me to fix it.

|||when i making test to gridview - testquery - it works fine. To have nothing in Button1_Click , how gridview know to make databind ?? Pls make some light in my mind.|||

Ah - magic.

The SqlDataSource control takes care of creating a connection object, command object, parameter objects and databinding, behind the scenes. All you have to do is tell the datasource (declare) what the connection string is, or where to find it, where any parameter values come from, and their datatype, and what command to execute. Oh, and you have to tell the GridView what datasource to use. It's called the Declarative DataBinding Model. All the internal workings of how it does its thing are abstracted away from the user. Perfect OOP.

|||

And after all you said, i didn`t make it to work :( .

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Find" Width="65px" /*not have on click, as you said*/ /><br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString1 %>"
SelectCommand="SearchContact" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="searchCriteria" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

when i select gridview "configure data source" an make a test query i works fine, no problem. ...but when i run the page in browser and i click on button ......nothing ...

What are my mistakes?? pls write for me some code...how to do...thx a lot

|||Change AutogenerateColumns to true in the GridView, and in the configuration for the GridView, click Refresh Schema. If you set it to false, you have to create your own ItemTemplate.|||

thx man ! it works.

Thx a lot !

|||

hi

if that work then mark as answer

thanks

|||

there i have another problem:

my gridview is populated ok , but works very slowly. I need to have about 20 - 40 textboxes and dropdownlists on my page. i put on page a scriptManager and all control are putted in an UpdatePanel . when i search a item in database , my gridview is populated very slowly..

what is wrong?..how can i fix this problem?

|||If you have a different problem, you should start a new thread in an appropriate forum for the problem. This one has been marked as resolved, so I'm probably the only person reading it now. Your new problem is related to AJAX. The subject of this thread is stored procedures. No one who knows about AJAX will see your question. I don't use ASP.NET AJAX, so I am afraid I can't help you with this one.

problem with procedure 2

Hi, Please with procedure not funcion:

--PROCEDIMIENTO CARGAR DM_CURSOS

create procedure dbo.sp_dm_cursos @.db varchar(50) as

INSERT INTO [DW_MMQ].[dbo].[dm_cursos]
([cu_codigo], [cu_descripcion], [cu_cod_nivel],
[cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo],
[cu_ao_lectivo], [cu_cod_unidad])

select
convert(varchar,a.courseid) + 'Sec' as codigo,
case b.name
when 'Basica' then 'Bsica'
else b.name end as nombre ,
d.levelid as cod_nivel,
d.name as nom_nivel,
c.parallelid as cod_paralelo,
c.name as nom_paralelo,
convert(varchar,startrange)+ '-'+ convert(varchar,endrange) as cod_ao_lectivo,
1 as cod_unidad
from
[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

where a.parallelid = c.parallelid
and a.levelid = d.levelid
and b.SpecializationID = d.SpecializationID
and e.schoolyearid = c.schoolyearid
and b.schoolyearid = e.schoolyearid
and convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

RESULT
-- execute sp_dm_cursos2 'Quitumbe'
--this is the problem?, please

Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Course'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Parallel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.mLevel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Specialization'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.SchoolYear'.change

[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

to

[db].[dbo].[Course] a,
[db].[dbo].[Parallel] c,
[db].[dbo].[mLevel] d,
[db].[dbo].[Specialization] b,
[db].[dbo].[SchoolYear] e|||create procedure dbo.sp_dm_cursos @.db varchar(50) as

DECLARE @.sql varchar(8000)

SELECT @.sql = 'INSERT INTO [DW_MMQ].[dbo].[dm_cursos]'
+'([cu_codigo], [cu_descripcion], [cu_cod_nivel], '
+'[cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo], '
+'[cu_ao_lectivo], [cu_cod_unidad]) '
+''
+'select '
+'convert(varchar,a.courseid) + ''Sec'' as codigo,'
+'case b.name '
+'when ''Basica'' then ''Bsica'' '
+'else b.name end as nombre ,'
+'d.levelid as cod_nivel,'
+'d.name as nom_nivel,'
+'c.parallelid as cod_paralelo, '
+'c.name as nom_paralelo, '
+'convert(varchar,startrange)+ ''-''+ convert(varchar,endrange) as cod_ao_lectivo, '
+'1 as cod_unidad '
+'from '
+'['+@.db+'].[dbo].[Course] a,'
+'['+@.db+'].[dbo].[Parallel] c,'
+'['+@.db+'].[dbo].[mLevel] d,'
+'['+@.db+'].[dbo].[Specialization] b,'
+'['+@.db+'].[dbo].[SchoolYear] e'
+''
+'where a.parallelid = c.parallelid '
+'and a.levelid = d.levelid '
+'and b.SpecializationID = d.SpecializationID '
+'and e.schoolyearid = c.schoolyearid '
+'and b.schoolyearid = e.schoolyearid '
+'and convert(varchar,a.courseid) + ''Sec'' not in (select cu_codigo from dm_cursos)'

SELECT @.sql

EXEC(@.sql)|||Nope..the db is dynamic

change

[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

to

[db].[dbo].[Course] a,
[db].[dbo].[Parallel] c,
[db].[dbo].[mLevel] d,
[db].[dbo].[Specialization] b,
[db].[dbo].[SchoolYear] e|||thank brett|||No worries...but why do you have to do it that way?

How many databases do you have?

problem with procedure

Hi, Please with procedure:

go

--PROCEDIMIENTO CARGAR DM_CURSOS

create procedure dbo.sp_dm_cursos @.db varchar(50) as

INSERT INTO [DW_MMQ].[dbo].[dm_cursos]
([cu_codigo], [cu_descripcion], [cu_cod_nivel],
[cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo],
[cu_ao_lectivo], [cu_cod_unidad])

select
convert(varchar,a.courseid) + 'Sec' as codigo,
case b.name
when 'Basica' then 'Bsica'
else b.name end as nombre ,
d.levelid as cod_nivel,
d.name as nom_nivel,
c.parallelid as cod_paralelo,
c.name as nom_paralelo,
convert(varchar,startrange)+ '-'+ convert(varchar,endrange) as cod_ao_lectivo,
1 as cod_unidad
from
[@.db].[dbo].[Course] a,
[@.db].[dbo].[Parallel] c,
[@.db].[dbo].[mLevel] d,
[@.db].[dbo].[Specialization] b,
[@.db].[dbo].[SchoolYear] e

where a.parallelid = c.parallelid
and a.levelid = d.levelid
and b.SpecializationID = d.SpecializationID
and e.schoolyearid = c.schoolyearid
and b.schoolyearid = e.schoolyearid
and convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

-- execute sp_dm_cursos2 'Quitumbe'
--this is the problem?, please
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Course'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Parallel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.mLevel'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.Specialization'.
Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
Invalid object name '@.db.dbo.SchoolYear'.mi espanol is muy mal. que es el problemo? necessito mas informacion. Inglis por favor?|||Start by converting your WHERE syntax into the more acceptable JOIN syntax:

from
[Quitumbe_Secundaria].[dbo].[Course] a
inner join [Quitumbe_Secundaria].[dbo].[Parallel] c on a.parallelid = c.parallelid
inner join [Quitumbe_Secundaria].[dbo].[mLevel] d on a.levelid = d.levelid
inner join [Quitumbe_Secundaria].[dbo].[Specialization] b on b.SpecializationID = d.SpecializationID
inner join [Quitumbe_Secundaria].[dbo].[SchoolYear] e
on e.schoolyearid = c.schoolyearid
and e.schoolyearid = b.schoolyearid
where convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

Now, if you map out your table relationships, you can see that you have exclusive inner joins for five tables the form a relational loop:

A - C \
| E
D - B /

For a record to appear in your dataset, all five of these joins must be satisfied. It is very possible that you have no records that pass this test, plus the criteria left in the WHERE clause above.

You may be able to drop [SchoolYear] from your query entirely, as it does not seem to appear in the SELECT clause, and tables [Parallel] and [Specialization] can be joined directly on the shared schoolyearid key.

problem with orphaned "dbo" user of an attached database

Hi everybody,
I'm in a situation where I allow my users of SQL Server the creation of her
own databases. This is been done in order to allow the "creator" (the owner)
full acces to the created DB. Everything works fine, as long as the DB is
created from scratch, like:
CREATE DATABASE universe
But additionally, my users are also exchanging databases, so they will also
use
CREATE DATABASE universe ... FOR ATTACH
When attaching a database, the owner of the so created database is the login
that
performed the operation, which is fine. But, the "dbo" user in the attached
database is still associated with the original login (before detaching the
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know that
this can be fixed with
ALTER AUTHORIZATION ON DATABASE::universe to [login]
So, after attaching a database, the user can simply all ALTER AUTHORIZATION.
My problem is that ALTER AUTORIZATION requires "CONTROL SERVER" permission,
which is simply a synonym for "sysadmin" role membership and therefore not
what I want. All I want my users grant is "CREATE ANY DATABASE" permission.
Does anybody know a solution besides doing the CREATE DATABASE ... FOR
ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
regarding signature?
Thanks,
HolgerHolger (Holger@.discussions.microsoft.com) writes:
> I know that this can be fixed with ALTER AUTHORIZATION ON
> DATABASE::universe to [login] So, after attaching a database, the user
> can simply all ALTER > AUTHORIZATION. My problem is that ALTER
> AUTORIZATION requires "CONTROL SERVER" permission, which is simply a
> synonym for "sysadmin" role membership and therefore not what I want.
> All I want my users grant is "CREATE ANY DATABASE" permission. Does
> anybody know a solution besides doing the CREATE DATABASE ... FOR ATTACH
> with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
> regarding signature?
You could put the ALTER AUTHORSIZATION statement in a stored procedure
that you sign with a certificate, and then you grant a login associated
with that cert CONTOL SERVER. Note that the login is not a real login,
that is, it cannot connect.
For a lot more detail on this, see this article on my web site:
http://www.sommarskog.se/grantperm.html
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|||Holger
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
What is authentication are you using?
See if this helps
http://dimantdatabasesolutions.blog...on.
html
"Holger" <Holger@.discussions.microsoft.com> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
> Hi everybody,
> I'm in a situation where I allow my users of SQL Server the creation of
> her
> own databases. This is been done in order to allow the "creator" (the
> owner)
> full acces to the created DB. Everything works fine, as long as the DB is
> created from scratch, like:
> CREATE DATABASE universe
> But additionally, my users are also exchanging databases, so they will
> also
> use
> CREATE DATABASE universe ... FOR ATTACH
> When attaching a database, the owner of the so created database is the
> login
> that
> performed the operation, which is fine. But, the "dbo" user in the
> attached
> database is still associated with the original login (before detaching the
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
> this can be fixed with
> ALTER AUTHORIZATION ON DATABASE::universe to [login]
> So, after attaching a database, the user can simply all ALTER
> AUTHORIZATION.
> My problem is that ALTER AUTORIZATION requires "CONTROL SERVER"
> permission,
> which is simply a synonym for "sysadmin" role membership and therefore not
> what I want. All I want my users grant is "CREATE ANY DATABASE"
> permission.
> Does anybody know a solution besides doing the CREATE DATABASE ... FOR
> ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with
> a
> regarding signature?
> Thanks,
> Holger|||Hi Erland,
thanks for your answer. I know that this is a solution but the problem is
that, for ALTER AUTHORIZATION I need a login and a certificate inside the
master database and also a master key.
I'd like to fairly avoid storing information of any kind inside master -
regardless of what information this is. That's the reason why I was asking
for a solution without a signed stored procdure.
"Erland Sommarskog" wrote:

> Holger (Holger@.discussions.microsoft.com) writes:
> You could put the ALTER AUTHORSIZATION statement in a stored procedure
> that you sign with a certificate, and then you grant a login associated
> with that cert CONTOL SERVER. Note that the login is not a real login,
> that is, it cannot connect.
> For a lot more detail on this, see this article on my web site:
> http://www.sommarskog.se/grantperm.html
> --
> 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
>|||Hi Uri,
thanks for your answer. As for our SQL Server - we use Windows
authentication. But I do know nothing about the source Server,so it should
work with all authentication modes. Also I guess, in order work with Windows
authentication, the prerequisite will certainly be that all servers reside i
n
one domain, which is not the case. I get databases from unkown sources from
all over the world and simply have to use those.
regards,
Holger
"Uri Dimant" wrote:

> Holger
> What is authentication are you using?
> See if this helps
> http://dimantdatabasesolutions.blog...o
n.html
>
>
> "Holger" <Holger@.discussions.microsoft.com> wrote in message
> news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
>
>|||Holger (Holger@.discussions.microsoft.com) writes:
> thanks for your answer. I know that this is a solution but the problem is
> that, for ALTER AUTHORIZATION I need a login and a certificate inside the
> master database and also a master key.
> I'd like to fairly avoid storing information of any kind inside master -
> regardless of what information this is. That's the reason why I was asking
> for a solution without a signed stored procdure.
If you prefer you can use impersonation in the procedure, that is EXECUTE
AS.
I'm not sure that I understand your reluctance against storing information
in master. After all, it is here you have information about logins,
databases, server-level permissions, the service master key in master.
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