Excel Import: dbo

Showing posts with label dbo. Show all posts

Thursday, March 29, 2012

exec stored procedure without qualifying dbo

I know it was best practise to call a stored procedure as
"exec dbo.sproc" instead of "exec sproc"
I guess it was in reference to compilations.
But when i monitored compilations/sec and recompilations/sec, I did not see
it happening.
Is there another reason for why I need to use the username.sproc name ?
ThanksHassan
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
"Hassan" <hassan@.test.com> wrote in message
news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>I know it was best practise to call a stored procedure as
> "exec dbo.sproc" instead of "exec sproc"
> I guess it was in reference to compilations.
> But when i monitored compilations/sec and recompilations/sec, I did not
> see it happening.
> Is there another reason for why I need to use the username.sproc name ?
> Thanks
>|||how can i profile for sprocs that are not using dbo. ?
Is there an appropriate filter to search for exec [no dbo].sproc ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
> Hassan
> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>
> "Hassan" <hassan@.test.com> wrote in message
> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>|||Hassan
Try filter on TextData NOT LIKE 'dbo%'
"Hassan" <hassan@.test.com> wrote in message
news:%23dbpD8wMIHA.536@.TK2MSFTNGP06.phx.gbl...
> how can i profile for sprocs that are not using dbo. ?
> Is there an appropriate filter to search for exec [no dbo].sproc ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ujmbZxlMIHA.6108@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx
>> http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:u%23MdDtjMIHA.1204@.TK2MSFTNGP03.phx.gbl...
>>I know it was best practise to call a stored procedure as
>> "exec dbo.sproc" instead of "exec sproc"
>> I guess it was in reference to compilations.
>> But when i monitored compilations/sec and recompilations/sec, I did not
>> see it happening.
>> Is there another reason for why I need to use the username.sproc name ?
>> Thanks
>>
>

exec stored procedure without qualifying dbo

Tuesday, March 27, 2012

EXEC SP w/Function

I have a stored proc called "dbo.SetSettlment" and a used defined
function called "dbo.GetSIFByType". The function works great, and so
does the sp. Problem is, I can't get them to work together.
See below, I have three statement. The first two work fine, the last
doesn't. How can I get around this?
EXECUTE dbo.SetSettlment @.SettlementAmt = 66
go
SELECT dbo.GetSIFByType(1)
go
EXECUTE dbo.SetSettlment @.SettlementAmt = dbo.GetSIFByType(1)
Thanks!
Jason RoozeeYou can't pass a function as a parameter.
Try
DECLARE @.sAmount INT
SELECT @.sAmount = dbo.GetSIFByType(1)
EXEC dbo.SetSettlement @.settlementAmt = @.sAmount
<jroozee@.gmail.com> wrote in message
news:1127408994.622820.135290@.g49g2000cwa.googlegroups.com...
>I have a stored proc called "dbo.SetSettlment" and a used defined
> function called "dbo.GetSIFByType". The function works great, and so
> does the sp. Problem is, I can't get them to work together.
> See below, I have three statement. The first two work fine, the last
> doesn't. How can I get around this?
>
> EXECUTE dbo.SetSettlment @.SettlementAmt = 66
> go
> SELECT dbo.GetSIFByType(1)
> go
> EXECUTE dbo.SetSettlment @.SettlementAmt = dbo.GetSIFByType(1)
> Thanks!
> Jason Roozee
>|||try this
declare @.intType
select @.intType = dbo.GetSIFByType(1)
EXECUTE dbo.SetSettlment @.SettlementAmt = @.intType
or
EXECUTE dbo.SetSettlment @.intType
http://sqlservercode.blogspot.com/
"jroozee@.gmail.com" wrote:

> I have a stored proc called "dbo.SetSettlment" and a used defined
> function called "dbo.GetSIFByType". The function works great, and so
> does the sp. Problem is, I can't get them to work together.
> See below, I have three statement. The first two work fine, the last
> doesn't. How can I get around this?
>
> EXECUTE dbo.SetSettlment @.SettlementAmt = 66
> go
> SELECT dbo.GetSIFByType(1)
> go
> EXECUTE dbo.SetSettlment @.SettlementAmt = dbo.GetSIFByType(1)
> Thanks!
> Jason Roozee
>|||I would but I am using ADO 2.7 - it doesn't like it when I declare a
var like that.
Jason Roozee|||Jason,
Try making 2 seperate calls in ADO.
First, call the function and return the value in a recordset. Then grab the
value from the recordset and cal the stored proc.
Yosh
<jroozee@.gmail.com> wrote in message
news:1127409622.711723.133570@.o13g2000cwo.googlegroups.com...
>I would but I am using ADO 2.7 - it doesn't like it when I declare a
> var like that.
> Jason Roozee
>|||Of course I could do that - but that's exactly what I am trying to
avoid doing.
Jason|||Looks like your gonna have to.
<jroozee@.gmail.com> wrote in message
news:1127410712.970994.235920@.o13g2000cwo.googlegroups.com...
> Of course I could do that - but that's exactly what I am trying to
> avoid doing.
> Jason
>|||> Of course I could do that - but that's exactly what I am trying to
> avoid doing.
Why? The way you've set it up (a stored procedure that uses the result of a
function), you're pretty much guaranteeing that you need to make two calls.
Why don't you consider passing 1 into the stored procedure, and letting IT
call the function locally.
A|||you could change the proc to take the function's parameter instead and
have the proc call the function...
exec dbo.SetSettlement 1
jroozee@.gmail.com wrote:

>I have a stored proc called "dbo.SetSettlment" and a used defined
>function called "dbo.GetSIFByType". The function works great, and so
>does the sp. Problem is, I can't get them to work together.
>See below, I have three statement. The first two work fine, the last
>doesn't. How can I get around this?
>
>EXECUTE dbo.SetSettlment @.SettlementAmt = 66
>go
>SELECT dbo.GetSIFByType(1)
>go
>EXECUTE dbo.SetSettlment @.SettlementAmt = dbo.GetSIFByType(1)
>Thanks!
>Jason Roozee
>
>

Wednesday, March 21, 2012

Excluding rows on a table while importing

I'm using DTSWizard to import a table from my main database to Temp.

This is the SQL statement...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)

What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?

This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.

Thanks,

Bill

Do you have to use the DTSWizard?

If not, BULK INSERT has a FIRSTROW parameter.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley,

I'm really new at this so I'm not sure how to use BULK INSERT, is that a statement that would be used in a Query? Maybe I'm expecting to much from an import/export tool like DTSWizard to get so particular.

Thanks,

Bill

|||

It is indeed a TSQL statement. The Books Online has excellent documentation and examples.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||YOu can also use the DTS wizard with the query option of "skip rows" Normally this is ment for skipping rows that are part of the metadata like column headers etc. But this can be also used in your situation for skipping (non-)relevant data rows.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

In the DTS wizard I don't see anything relating to "skip rows", is this something that would run in the

Create Table Query?

Thanks,

Bill

|||OK, I assumed that you are using a text provider for the insert, which format is the data of or which data source are you querying ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

You mentioned "skip rows" using DTS wizard, and now I'm a bit confused... is there a feature in the DTS wizard where I can skip rows in a table when importing to another table?

Thanks,

Bill

|||

Hi Jens,

What I'm doing in this particular step is... I'm using DTS Wizard to copy a table from my Main Database to Temp. I went thru DTS Wizard and I see that if I was importing a FlatFile into SQL Server, on top of the Preview it allows you to Skip Rows, and this is what you were probably talking about.

Being that there isn't a Skip Row feature when copying a regular table from a database to Temp, but it does have the Create Table statement under Edit Mapping/Edit SQL, is there something that can be added to that statement to Skip Rows in the source table?.

Thanks,

Bill

|||Yes, that depends on the version you are using. In Sql Server 005 you can use the ROW_NUMBER() function to filter out appropiate rows. In SQL Server 2000 you would have to use another approach.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

I'm using SQL Server 2005 Express. This is what I have currently under Edit Mapping/Edit SQL...

I'm really new at this which you can probably tell... do you think you can let me know the code I need to add to the above to exclude certain rows?

Thanks,

Bill

|||

You can use this as the source of the table (instead of using the table direct)

SELECT

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM SomeTable

) SubQuery

WHERE ROWCOUNTER>5000

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, thanks for responding to this. I ran your code in DTS Wizard/Edit Mapping/Edit SQL and I received the error below.

Also, I'm not sure what you meant when you said... "You can use this as the source of the table (instead of using the table direct)." Was I correct in running this in DTS Wizard?

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Incorrect syntax near 'subscriberid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

|||Well just a comma missing

SELECT

FROM

(

Code Snippet

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM xlaANLsubscribers

) SubQuery

WHERE ROWCOUNTER>90091

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for getting back with me Jens.

I ran your code in DTS Wizard.

This is the error I received...

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)

Messages

Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(

SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Invalid object name 'xlaANLsubscribers'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Monday, March 19, 2012

Exchange of login for database user

I have a database with 2 users - 'dbo' and 'user1'.

Currently:
=> 'dbo' is tied to login - 'login1'
=> 'user1' is not tied to any login.

I want to change the login tied to the database users to:
=> 'dbo' tied to no login
=> 'user1' tied to 'login1'

I try to use:
sp_change_users_login 'Update_One', 'user1', 'login1'
But it give me an error said 'login1' is already tied to a user.

Then I try to remove the login tied to 'dbo' with this:
sp_change_users_login 'Update_One', 'dbo', ''
Then I get another error said that 'dbo' is a forbidden value for the
login name parameter.

Previously, all the while 'dbo' is not tied to any login and 'user1' is
tied to 'login1', but yesterday I restore the DB from the backup file
bring back from customer side, then I saw the login changed to the one
I mention above, I'm not sure whether is the DBA from customer side
change it or what, but is there any way for me to change the login back
to the state I want?

Thanks.

Peter CCHPeter CCH (petercch.wodoy@.gmail.com) writes:
> I have a database with 2 users - 'dbo' and 'user1'.
> Currently:
>=> 'dbo' is tied to login - 'login1'
>=> 'user1' is not tied to any login.
> I want to change the login tied to the database users to:
>=> 'dbo' tied to no login
>=> 'user1' tied to 'login1'

dbo is always tied to a login, since dbo is the database owner. But
you can change database owner with sp_changedbowner. Once you've done
that, you can connect user1 to login1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried in this sequence:
1. sp_changedbowner 'user1'
2. sp_change_users_login 'Update_One', 'user1', 'login1'

But "2" still give me the same error:
----------
Server: Msg 15063, Level 16, State 1, Procedure sp_change_users_login,
Line 104
The login already has an account under a different user name.
----------

This is the view in database tree in Enterprise Manager when I click on
the user section (Databases > dataBaseName > Users), there's 2 users in
list.

Name Login Name Database Access
-----------------------
user1 Permit
dbo login1 Permit

Previously, for so many times I restore the backup DB obtained from
customer site to my server, it have this view

Name Login Name Database Access
-----------------------
user1 login1 Permit
dbo Permit

But yesterday once I restore it, the login1 is not tied to user1
anymore, instead, it tied to dbo.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> I tried in this sequence:
> 1. sp_changedbowner 'user1'
> 2. sp_change_users_login 'Update_One', 'user1', 'login1'
> But "2" still give me the same error:
> ----------
> Server: Msg 15063, Level 16, State 1, Procedure sp_change_users_login,
> Line 104
> The login already has an account under a different user name.
> ----------

Of course. Since login1 owns the database, login1 maps to the user
dbo, and cannot map to the user user1. You need to change to a different
owner, for instance sa.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You mean change the DB owner to 'sa', then only maps 'login1' to
'user1'?
I don't really understand what you mean.

Like this?
1. sp_changedbowner 'sa'
2. sp_changeuserslogin 'Update_One', 'user1', 'login1'

Please correct me if I'm wrong.

Thanks a lot.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> You mean change the DB owner to 'sa', then only maps 'login1' to
> 'user1'?
> I don't really understand what you mean.

Yes. If you want login1 to map to user1 in the database, then someone
else must own the database than login1. The database owner always maps
to dbo.

> Like this?
> 1. sp_changedbowner 'sa'
> 2. sp_changeuserslogin 'Update_One', 'user1', 'login1'

Yes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried the code, it still give me the same error.
I just notice there's something different for current state and before
the problem exist state:

I expand the SQL Server tree node, Security > Logins
Then I open the properties page of "login1" and go to "Database Access"
tab.

This is what it looks now:

Permit Database User
---------------
(checked) DB1 user1
(checked) DB2 user1
(checked) DB3 dbo

All that 3 database have "public" and "db_owner" role checked.

Before the problem occured, it looks like this:

Permit Database User
---------------
(checked) DB1 user1
(checked) DB2 user1
(checked) DB3 user1

For "DB 3" ... last time "User" column is "user1", but now it had
changed to "dbo".

QUESTION: Is there any way for me to change the "dbo" at column "User"
back to "user1"?

By the way, I login to the SQL Server with "login1".

Thanks.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> I tried the code, it still give me the same error.
> I just notice there's something different for current state and before
> the problem exist state:
> I expand the SQL Server tree node, Security > Logins
> Then I open the properties page of "login1" and go to "Database Access"
> tab.
> This is what it looks now:
> Permit Database User
> ---------------
> (checked) DB1 user1
> (checked) DB2 user1
> (checked) DB3 dbo

Thus login1 is still the owner of DB3. Did you run sp_changedbowner in
DB3?

What does sp_helpdb say?

What does DB3..sp_helpuser say when run it in DB3?

> QUESTION: Is there any way for me to change the "dbo" at column "User"
> back to "user1"?

Yes, change the database owner of the database to anyone else by user1.
Then use sp_change_users_login, or simply drop and re-add user.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, got you. Tested and working.

Now "user1" is tied to "login1".
But the user "dbo" still tied to a login, is this a must?
Cause last time the user "dbo" is not tied to any login.

Expanding SQL Server tree node, Database > (database name) > Users

Here is what it looks like now:

Name Login Name Database Access
-----------------------
user1 login1 Permit
dbo sa Permit

Last time, it is shown like below:

Name Login Name Database Access
-----------------------
user1 login1 Permit
dbo Permit

I try to use:
sp_change_users_login 'Update_One', 'dbo', ''
thinking of making the "dbo" have no login tie to it, but I got the
following error message:
================================
Server: Msg 15287, Level 16, State 1, Procedure sp_change_users_login,
Line 39
Terminating this procedure. 'dbo' is a forbidden value for the login
name parameter in this procedure.
================================

QUESTION: Is it possible to make the "dbo" have no login tie to it?

Thanks.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> Now "user1" is tied to "login1".
> But the user "dbo" still tied to a login, is this a must?

Yes. A database must be owned by someone.

> Cause last time the user "dbo" is not tied to any login.

You get some funny things going when you move a database from one server
to another, but this is anomaly that should be corrected.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info.
But I'm curious on one thing, since a database must be own by a user,
how come when I try to create a new database, the "dbo" user is not
tied to any login?

I tried create a new database, then expand the tree node and look at
the "Users" section, then I saw this:

Name Login Name Database Access
----------------------
dbo Permit

Above is the "Users" section of a newly created database on my PC, for
the purpose of to confirm whether a user must tied to a login, I just
wondering why the "dbo" is not tied to any login.

Any reason in behind?

Thanks.

Peter CCH|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> But I'm curious on one thing, since a database must be own by a user,
> how come when I try to create a new database, the "dbo" user is not
> tied to any login?
> I tried create a new database, then expand the tree node and look at
> the "Users" section, then I saw this:
> Name Login Name Database Access
> ----------------------
> dbo Permit
> Above is the "Users" section of a newly created database on my PC, for
> the purpose of to confirm whether a user must tied to a login, I just
> wondering why the "dbo" is not tied to any login.

It appears that you only see a login name, if this is an SQL Server
login. So if the database is owned by a Windows login, you don't see
this. The same thing happens if you say "sp_grantdbaccess DOMAIN\user"
to permit access for a windows login, the LoginName column is NULL.

But that is only the trick of the eye. If you would try to add the
Windows user that owns the database, you would be told that is is
already there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 29, 2012

exec stored procedure without qualifying dbo

exec stored procedure without qualifying dbo

exec stored procedure without qualifying dbo

Tuesday, March 27, 2012

EXEC SP w/Function

Wednesday, March 21, 2012

Excluding rows on a table while importing

Monday, March 19, 2012

Exchange of login for database user

Excel Import

Blog Archive

About Me