Excel Import: sproc

Showing posts with label sproc. 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 sproc in a update function

Folks

Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity ....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.

update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID

FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.

ThanxUse (create) function instead of sp in this case.|||Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx|||Originally posted by kir441
Snail

Y do I need to make it a function?

create procedure ALSHeadr2Persons
as
/*declaration goes here*/
/* Update existing Persons*/
set @.Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

update
Persons set
Persons.strNameSuffix=ALSHeadr.strNameSuffix,
Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
from ALsHeadr
where Persons.lngSSN=ALSHeadr.lngFedTaxID

end

How do I pass the values of ALSHeadr table the GetAddress sproc??
based on the condition Persons.lngssn=alsheadr.lngfedtaxid

Any other syntax solution?

Thx

What about this draft?

drop table test
drop table test2
create table test(id int)
create table test2(id int, code varchar(10))
go
insert test values(1)
insert test values(2)
insert test values(3)
insert test2 values(1,'a')
insert test2 values(2,'b')
insert test2 values(3,'c')
go
CREATE FUNCTION getit(@.id int)
RETURNS varchar
AS
BEGIN
declare @.ret varchar(10)
select @.ret=code from test2 where id=@.id
RETURN @.ret
END
GO
select *,dbo.getit(id)
from testsql

Monday, March 26, 2012

EXEC Permission

Hi,
I got this 'EXECUTE permission denied on object <mySproc>' error message
everytime I try executing my SQL server Sproc.
What's this and how to fix this err?
many thnaks in advance,
mikeMike
You are not probably an owner of the stored procedure.
So, DBA should grant you 'EXECUTE' permissions on the object
Also ,If you use dynamic sql within the stored procedure it will be forced
you to grant permissions on underlying tables
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got this 'EXECUTE permission denied on object <mySproc>' error message
> everytime I try executing my SQL server Sproc.
> What's this and how to fix this err?
> many thnaks in advance,
> mike
>
>|||Thanks Uri,
Actually, I got this prob after I moved my ASP.NET web appl to a new
hosting.
And I never such prob with my old hosting.
How can I grant any permissions in such hosted environment?
rgds,
mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8wyEHrBFHA.4004@.tk2msftngp13.phx.gbl...
> Mike
> You are not probably an owner of the stored procedure.
> So, DBA should grant you 'EXECUTE' permissions on the object
> Also ,If you use dynamic sql within the stored procedure it will be forced
> you to grant permissions on underlying tables
>
> "Mike L." <globalwebservices2005@.yahoo.com> wrote in message
> news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
>|||One more thing,
Everytime I log in to my SQL server using my login name/pwd, i've never get
my default DB as the first db in QA db list. It's always 'master' db.
I found out also, that my default db for my login name is not assigend to my
default db, it's to 'master' db instead.
Could this be a 'sign' that I really don't have enough permissions on my own
db?
rgds,
mike|||Hello Mike L.,
It's starting to sound like you should contact your hosting provider about
the way that your database is set up. From the sound of it it looks like
your login doesn't have permission to your database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've
> never get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend
> to my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on
> my own
> db?
> rgds,
> mike|||You can change your default database using sp_defaultdb:
EXEC sp_defaultdb 'MyLogin', 'MyDatabase'

> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
The default database is used when you haven't specified an initial database
context in your connection string. Since you got far enough to get a
permission error, this indicates that your login has access to your database
but insufficient permissions to execute the proc.
Your hosting provider needs to either:
1) Provide a separate administrative login that you can use to manage your
database and permissions
2) Add your login to a privileged role like db_owner
3) Perform database administration on your behalf. Was this how the
database was created?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
> get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
> my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
> rgds,
> mike
>|||Thanks Aaron, Dan,
I'll try to contact my hosting provider to fix this up.
rgds,
mike
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
own
> db?
> rgds,
> mike
>sql

EXEC of a sproc within another sproc

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy

Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@.EntryLogID int,
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2)
)
AS
DECLARE@.OldCategoryID int

EXEC @.OldCategoryID = BP_GetLedgerCategory @.EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @.ProjectID,
CategoryID = @.NewCategoryID,
Expended = @.Expended

WHERE
EntryLogID = @.EntryLogID

EXEC BP_UpdateCategories @.ProjectID, @.NewCategoryID, @.Expended, @.OldCategoryID

Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@.EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @.EntryLogID

RETURN

*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@.ProjectID int,
@.NewCategoryID int,
@.Expended decimal(10,2),
@.OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.NewCategoryID

UPDATE
BP_Categories
SET CatExpended = CatExpended - @.Expended
WHERE
ProjectID = @.ProjectID
AND
CategoryID = @.OldCategoryIDyou need to use OUTPUT parameters to return a value..check BOL (Books On Line ) its a free download from microsoft...check for correct synax and documentation...

hth|||Thank you...though...I found more info later last night after I came across the phrase 'OUTPUT' parameters.

This thread in particular ...
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=399995

My sproc is finished and I've moved on...

Friday, February 24, 2012

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:
> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > Hi,
> >
> > I'm trying to run a sproc that was created on SQL server and has two date
> > parameters. I can run the sproc easily enough with fixed values but it
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
> >
> > I have seen that the suggested solution is to create a view but as this
> > query uses group by, using the where clause in a view to specify the
> > parameteres will not give the correct result set.
> >
> > This is part of the SQL from the sproc.....
> >
> > SELECT SG.SchemeName as SchemeName,
> > 'Fail' as Type,
> > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> END),0)
> > as Value
> > FROM Quote Q
> > INNER JOIN SchemeGroup SG
> > ON Q.SchemeGroupID = SG.ID
> > INNER JOIN SubScheme SS
> > ON Q.ID = SS.QuoteID
> > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > GROUP BY SG.SchemeName
> > ORDER BY SG.SchemeName,Type desc
> >
> > Any help or ideas would be appreciated.
> >
> > Thanks,
> >
> > Ruz
> >
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"
>
> Cheers,
> "Uri Dimant" wrote:
> > Ruz
> > CREATE PROC myProc
> > @.SDate DATETIME,
> > @.FDate DATETIME
> > AS
> > --Your code here
> >
> >
> > If not sure I understand you
> > What does it mean?
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> >
> >
> >
> > "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> > news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > > Hi,
> > >
> > > I'm trying to run a sproc that was created on SQL server and has two
date
> > > parameters. I can run the sproc easily enough with fixed values but it
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> > >
> > > I have seen that the suggested solution is to create a view but as
this
> > > query uses group by, using the where clause in a view to specify the
> > > parameteres will not give the correct result set.
> > >
> > > This is part of the SQL from the sproc.....
> > >
> > > SELECT SG.SchemeName as SchemeName,
> > > 'Fail' as Type,
> > > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> > END),0)
> > > as Value
> > > FROM Quote Q
> > > INNER JOIN SchemeGroup SG
> > > ON Q.SchemeGroupID = SG.ID
> > > INNER JOIN SubScheme SS
> > > ON Q.ID = SS.QuoteID
> > > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > > GROUP BY SG.SchemeName
> > > ORDER BY SG.SchemeName,Type desc
> > >
> > > Any help or ideas would be appreciated.
> > >
> > > Thanks,
> > >
> > > Ruz
> > >
> >
> >
> >

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 sproc in a update function

Monday, March 26, 2012

EXEC Permission

EXEC of a sproc within another sproc

Friday, February 24, 2012

EXCEL: parameters and stored procedures

EXCEL: parameters and stored procedures

EXCEL: parameters and stored procedures

Excel Import

Blog Archive

About Me