How to simplify this slow Stored Procedure

Dear Reader(s),

Is there anyway to write the following stored procedure without the loop so that it goes much faster? :confused:

-----------------------
use MJ_ReportBase
go
if exists(select 1 from sysobjects where type='P' and name='sp_Periode')
begin
drop procedure sp_Periode
end
go
create procedure sp_Periode
@.start int
, @.stop int
as
declare @.x int

set @.x = 0
set @.x=@.start

delete from tbl_periode

while (@.x>=@.stop)
begin

-- --
-- --
-- Create table tbl_inout
if exists(select 1 from sysobjects where type='U' and name='tbl_inout')
begin
drop table tbl_inout
end

select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in
(select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105) group by badgeid)
and badgeid>0
order by personname

-- --
-- --
-- Create table tbl_result

if exists(select 1 from sysobjects where type='U' and name='tbl_result')
begin
drop table tbl_result
end

-- --
-- --

select
convert(varchar,datetimestamp,105) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
into
tbl_result
from
WinXS..x18
where
convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105)
and
accname in ('Kelder -1','Tnk Entree')
and badgeid>0
group by
convert(varchar,WinXS..x18.datetimestamp,105)
, badgeid
, initials
, personname
order by
initials
, personname asc
, convert(varchar,datetimestamp,105) asc

-- --
-- --
-- Rapportage tabel

insert into
tbl_periode
select
tbl_result.datum as DATUM
, ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM
, tbl_result.min as MIN
, tbl_result.max as MAX
, case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT]
, substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS
from
tbl_inout,tbl_result
where
tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas))
= convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid))
order by
tbl_result.naam asc

-- --
-- --
--

set @.x=@.x-1
end
go

print 'Klaar!'
-----------------------

What it does is determining the minimum entry time and the maximum exiting time per day of people going true the main entrance of a building.

Many thanks in advance.
:)First...collapse the queries in to 1.

Second, what's the first select for?

Third

Loose the loop and do

WHERE datetimestamp > GetDate()-@.Start
AND datetimestamp < = GetDate()-@.Stop|||I don't even want to try to figure out your code. Don't use permanent tables for temporary storage (your tbl_inout table). That will get you into trouble in a multi-user environment.

Post the relevent DDL for your tables any somebody here can probably show you a more efficient set-based algorithm.|||Hi to all,

I have found the solution after a long time puzzeling. :)

Please check the sql below:
-----------------------
select
tbl_INOUT.*
, CASE B.inoutreg WHEN 1 THEN 'IN' WHEN 2 THEN 'UIT' ELSE 'Fout' END 'STATUS'
from
(
select
convert(char(10),datetimestamp,120) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
from
WinXS..x18
where
(convert(varchar(10),datetimestamp,120)>=convert(varchar(10),'2005-01-01',120)
and
convert(varchar(10),datetimestamp,120)<=convert(varchar(10),'2005-02-28',120))
and
accname in ('Kelder -1','Tnk Entree')
and
badgeid>0
group by
convert(char(10),datetimestamp,120)
, badgeid
, initials
, personname
) tbl_INOUT, WinXS..x18 B
where
tbl_INOUT.pas=B.badgeid
and
convert(datetime,tbl_INOUT.datum+' '+tbl_INOUT.max,120)=B.datetimestamp
and
badgeid=81
order by
tbl_INOUT.naam asc
, tbl_INOUT.datum DESC

Greetz,
DePrins
:D

how to show binary data in report

I want to display the content stored in image column. Can anyone tell me how?
The content is actually XML text, encoded and stored in a image type.This link should help you.
http://msdn2.microsoft.com/en-us/library/aa179439(SQL.80).aspx
Also if you have the Sample Reports installed the Product Catalog report
shows you how to do this.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Jason Wang" <JasonWang@.discussions.microsoft.com> wrote in message
news:2D1F3430-D9B7-499D-B77E-0CF6DC464F21@.microsoft.com...
>I want to display the content stored in image column. Can anyone tell me
>how?
> The content is actually XML text, encoded and stored in a image type.

How to show a stored PDF

New to asp and have no idea what I'm doing wrong here. I just want to open a pdf that I have stored in a table and show it on the page. Here is my code. I keep getting theSystem.NullReferenceException was unhandled by user code.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:smithsdaConnectionStringtest %>"

ProviderName="<%$ ConnectionStrings:smithsdaConnectionStringtest.ProviderName %>"

SelectCommand="SELECT * FROM [correspondence] WHERE ([facilitiesID] = ?)">

<SelectParameters>

<asp:QueryStringParameterName="facilitiesID"QueryStringField="DACorr"Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

<scriptrunat="server">

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim ScanDocAsNew DataViewDim argAsNew DataSourceSelectArguments

ScanDoc = SqlDataSource1.Select(arg)

Dim ScanLet(1)AsByte

ScanLet(0) =CByte(ScanDoc(0)("Letter"))

If ScanDocIsNot""Then

Response.Buffer =True

Response.Clear()

Response.ClearContent()

Response.ClearHeaders()

Response.ContentType ="application/pdf"

Response.BinaryWrite(ScanLet)

Response.End()

EndIf

EndSub

</script

Any Help greatly appreciated.

My suggestion would be to store the PDF files, all in one directory, then, in the database, store the names of the PDF files.

Then, show the relevant information in the database, using a Gridview, including one TemplateField, which would have a Hyperlink to the folder where the pDFs are stored, and the PDF itself. Then, when people clicked on the hyperlink, the PDF would show

|||

David,

Thanks for the reply. I wish I could do it that way, unfortunately they want the pdfs stored in the database so noone can accidentally delete them. The db has probably 200 stored pdfs associated with records and they need to be able to pull them up for viewing.

Thanks,

DeWayne

|||

I've changed the code to this and now it just opens a blank web page.

Dim ScanDocAsNew DataViewDim argAsNew DataSourceSelectArguments

ScanDoc = SqlDataSource1.Select(arg)

Dim ScanLet(1)AsByte

ScanLet(0) =CByte(Request.QueryString("Letter"))

If ScanDocIsNot""Then

Response.Buffer =True

Response.Clear()

Response.ClearContent()

Response.ClearHeaders()

'Response.AddHeader("content-disposition", "inline;filename=" + ScanLet)

Response.ContentType ="application/pdf"

'Response.AppendHeader("content-disposition", "inline;filename=" + "Scan.PDF")

Response.BinaryWrite(ScanLet)

Response.End()

'Else

' Response.Write("Error")

EndIf

I think I am on the right track but no PDF file yet.

|||

I finally got it, thanks to reading other posts in the forum and especially the one from adam v on how to strip the ole header. Now I just have to deal with .doc and .tif headers. This is a great forum and I have found lots of useful tips and tricks in here.

Thanks.

Here is the code I ended up with...

myConnection.Open()Dim myDataReaderAs OleDbDataReader = myCommand.ExecuteReader() myDataReader.Read()Dim intLoopCountAs Integer Dim intPositionAs Integer Dim ScanLetAs Byte() = myDataReader("letter")Do While intLoopCount < ScanLet.LengthIf ScanLet(intLoopCount) = 37Then'%If ScanLet(intLoopCount + 1) = 80Then'PIf ScanLet(intLoopCount + 2) = 68Then'DIf ScanLet(intLoopCount + 3) = 70Then'FIf ScanLet(intLoopCount + 4) = 45Then'- intPosition = intLoopCount intLoopCount = ScanLet.LengthEnd If End If End If End If End If intLoopCount = intLoopCount + 1Loop Dim bytStrippedData(ScanLet.Length - intPosition - 1)As Byte System.Buffer.BlockCopy(src:=ScanLet, srcOffset:=intPosition, dst:=bytStrippedData, dstOffset:=0, count:=ScanLet.Length - intPosition) Response.Clear() Response.ClearContent() Response.ClearHeaders() Response.ContentType ="application/pdf" Response.BinaryWrite(bytStrippedData) Response.Flush() Response.End() myConnection.Close() Response.Write("Person info successfully retrieved!")End UsingEnd Sub

How to setup profile, roles, etc in my hosted sqlserver?

I've been working with godaddy.com, and when I setup there the sqlserver databases it tells me if I want to create the tables and stored procedures that conform the typical profile, roles and users features... the same as if I execute the aspnet_regsql.exe

Now I'm working with anotherhoster (aruba) and I've setup the database OK, but i didn't found any way to setup the profile, roles, users, etc features.
Aruba uses the mssql server web admin.

How can I setup this features? Of course, aruba doesn't let me to execute the aspnet_regsql.exe...

thanksNo idea??

I think it's an important problem, isn't it??

ASP.NET 2.0 without the specific tables and sps for users, roles, profiles, etc, aren't powerful!!!

Please, an answer!!!|||I would ask aruba to do it for you. I'm sure they have a way if they support hosting ASP.NET 2.0 apps.|||Yes Motley, I'm doing it... the problem is that aruba isn't well enougth solving things like this

On the other hand, with your post I see that there is no way to make it by myself, isn't it?

thanks!
|||Can you run scripts against the database? All the SQL scripts for ASP.NET application services are in the %windows%\microsoft.net\framework\v2.0.50727 directory.|||Wow, I didn't realize they gave us the .SQL files. I guess I just assumed they were encoded/encrypted in the aspnet_regsql.exe file. Very nice.|||ok, I've found those .sql files andthey can be runned against the database.

They are:
- InstallCommon.sql
- InstalMembership.sql
- InstalPersistSqlState.sql
- InstallPersonalization.sql
- InstallProfile.sql
- InstallRoles.sql
- InstallSqlState.sql
- InstallSqlStateTemplate.sql
- InstallWebEventSqlProvider.sql

Should I run all??

On other hand, I've tried the first one and I've found a lot of problems. The first was the name of the database, taht I've changed, but the next was this error:

"Specified owner name 'dbo' either does not exist or you do not have permission to use it."

What should I do? Remove all references to dbo?

Thanks in advance.|||Yes, remove all references to dbo.

how to setup db replication with objects owned by user other than dbo?

Hi, I was hoping someone could tell me how I am to replicate a database
between servers. The many of the tables, stored procedures and views are
owned by a user (e.g. USER1). The version running is SQL 2000. Lets take for
instance snapshot replication from Server1 to Server2.
Now I know that many people are having problems with replicating a database
and ending up realising that the tables, stored procs and views are now
owned by dbo. I want the replicated database objects owned by USER1. What do
I have to do? Do I:
1) create USER1 on the Server2,
2) and then start snapshot replication from Server1 assigning "Publication
properties > Articles > Article Defaults > Destination Table Owner" = USER1
?
3) then move across USER1's permissions to the replicated objects (not sure
how to proceed with this, I think i have to use ).
or do I:
1) create USER1 on the Server2,
2) use DTS to create empty table structures on Server2
3) and then assign USER1 the permissions
I am somewhat confused because USER1's permissions are dependent on the
tables being defined, and the tables are dependent on USER1 having ownership
on them. I dont know what to set up first.
Simply stated, how do I simply "set up a replication from scratch for a
database with objects owned by a user other than dbo"
Could anyone direct me?
Thanks!
PeterGenerate SQL Script of login,
Execute it on new server (create login + permissions +
add_role logins... )
then DTS the objects of the server (what you want) to the
other server...
>--Original Message--
>Hi, I was hoping someone could tell me how I am to
replicate a database
>between servers. The many of the tables, stored
procedures and views are
>owned by a user (e.g. USER1). The version running is SQL
2000. Lets take for
>instance snapshot replication from Server1 to Server2.
>Now I know that many people are having problems with
replicating a database
>and ending up realising that the tables, stored procs and
views are now
>owned by dbo. I want the replicated database objects
owned by USER1. What do
>I have to do? Do I:
>1) create USER1 on the Server2,
>2) and then start snapshot replication from Server1
assigning "Publication
>properties > Articles > Article Defaults > Destination
Table Owner" = USER1
>?
>3) then move across USER1's permissions to the replicated
objects (not sure
>how to proceed with this, I think i have to use ).
>or do I:
>1) create USER1 on the Server2,
>2) use DTS to create empty table structures on Server2
>3) and then assign USER1 the permissions
>I am somewhat confused because USER1's permissions are
dependent on the
>tables being defined, and the tables are dependent on
USER1 having ownership
>on them. I dont know what to set up first.
>Simply stated, how do I simply "set up a replication from
scratch for a
>database with objects owned by a user other than dbo"
>Could anyone direct me?
>Thanks!
>Peter
>
>
>.
>

How to set up search on text stored in an image type of data?

I am saving large text document in an image type of column in a SQL Server 2000 table.

How will I set up searching of words/ phrases for data stored in this column?

what type of documents are they? If it is just plaintext you really should store it in an nvarhcar(max) field instead. That way you can use SQL queries to search the text.

|||

But nvarchar has a length problem. If it's a word document that is stored and this doc's length is greater than varchar max size, then we have to use image or ntext datatype. Because of this I have to use image data type.

|||

That's going to be a challenge because the image data type is storing a byte array so i'm not sure if it is possible to search it without first pulling it down and manipulating it in your code behind.

How to set the following report Parameters?

Hi,

I have a report and the parameters Passed to the report are Cusip and PeriodId through a stored Procedure.

i want to populate the cusip Parameters using a dataset thats got by a sproc called usp_getCusips.

and the PerioId using a dataset thats been got by another sproc called usp_Getperiods...

So in my report i have added 3 datasets one for the main report, one to get the values of the PeriodId and other to get the value of the cusip.

So in my Report parameters for the Parameter Cusip and i have checked the Available value option and selected its corresponding Dataset and value .

and i have also done the same thing for the PeriodId.. But when i run my sproc i am getting this Error.

[rsInvalidReportParameterDependency] The report parameter ‘Cusip’ has a DefaultValue or a ValidValue that depends on the report parameter “Cusip”. Forward dependencies are not valid.

[rsInvalidReportParameterDependency] The report parameter ‘PeriodId’ has a DefaultValue or a ValidValue that depends on the report parameter “PeriodId”. Forward dependencies are not valid.

Any Help will be appreciated.

Regards,

Karen

I'm not sure how to directly answer your question, but it sounds like you have created a circular reference.

See the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=184243&SiteID=1

|||

Greg,

Thanks for the help, i got it to work.

Regards

Karen

|||Glad I could help.

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (3
2
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is ther
e
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this
a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help.
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:
> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> >I have a calculation in a stored procedure that returns a percentage like
> >(32
> > * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> > float.
> > I need to set or convert the result to have 2 decimal places like 1.86,
> > rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> > variations (examples from the internet), nothing is working for me. Is
> > there
> > a way to achieve this in SQL? I would really appreciate the help.
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set NT Authority in sql 2000?

We have an application store in a web server using IIS 6.0, however the application is communicating to sql 2000 that is stored in a 2000 server.

In IIS 6.0 the application pool is set to Network Services, therefore sql 2000 is looking for a user called 'NT AUTHORITY\ANONYMOUS LOGON'.

But the login failed, and I don't see how to add NT Authority as a user in a 2000 server like you can in a 2003 server.

In all, we are trying to set permissions to certain users only by using the identity impersonate='true' in the web config file, but even with this setting set to true, and our network information set as a user in sql 2000, we still are denied access to the data.

What are some things we need to look for and what configuration do we need to set?

Thanks,

xyz789

P.S.
We also have all the permissions check to have access to all the tables and store procedures for each user.

Hi,

Are you trying integrated security and impersonating the current asp.net user?

Remove the anonymous access from IIS. Make sure in the authentication, only 'Integrated Windows
authentication' is checked.

You may need kerberos authentication.

http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Thank you!

How to SET multiple variables from one table record?

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.

Currently I'll do something like this:

DECLARE @.var1 int
SET @.var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @.somekey)
DECLARE @.var2 nvarchar(20)
SET @.var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @.somekey)

Of course, I'd rather just have to query "table1" just once to assign my variables.

What obvious bit of T-SQL am I missing?

Thank you in advance.

Select @.var1 = field1, @.var2 = field2, @.var3 = field3 from table1 where recordid = @.recid

|||

Thank you PDraigh!

I knew it was something obvious - I think I need a holiday

How to set is_receive_enabled and is_enqueue_enabled from 1 to 0.

Hi All,

I used service broker activation in my receive queue (SQL 2005 SP1). However, it seems the stored procedure does not work after the receive queue get message. I saw the following error in SQL Error log.

"The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'"

Then I checked the queue in sys.service_queues and noticed is_receive_enabled and is_enqueue_enabled is 0 but not 1 (is_activation_enabled is 1, which is normal). I believe this is the cause of my service broker activation issue.

According to this link:

http://www.eggheadcafe.com/aspnet_answers/SQLServerservicebroker/May2006/post26788966.asp

I tried to use ALTER QUEUE ... WITH STATUS = ON command to set the above value back to 1, it does not work the value persists 0. Any idea?

Thanks in advance.

Michael

ALTER QUEUE [YourQueue] WITH STATUS = ON will enable the queue and both is_receive_enabled and is_enqueue_enabled will be set to 1. However, if your stored proc rolls back a RECEIVE 5 times consecutively, the poisoned message detector will automatically disable the queue. You should see error messages in the ERRORLOG if your stored proc is throwing an exception or not committing the RECEIVE correctly.

Rushi

|||

Thanks, Rushi. You are correct the receive queue is disabled and I also found 5 errors before the queue is disabled in the SQL ERRORLOG as below:

--

2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.95 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.96 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.98 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:48.01 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:50.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:55.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:00.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:05.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:08.09 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'

Can you let me know:

a. Is the 5 times a built-in value or the value I can set it? Is it the option MAX_QUEUE_READERS = 5 while I set the activation?

b. Since the ReceiveQ is disabled, how can I enable it back? I tried "ALTER QUEUE [YourQueue] WITH STATUS = ON" before, but the ReceiveQ still is disabled. Is it normal? Can I set it back?

Michael

|||

a. The maximum number of times RECEIVE can be consecutively rolled-back without setting of the poisoned message detection cannot be configured. It is hard-coded to '5'. It is also not related to the MAX_QUEUE_READERS which controls max number of concurrent activated tasks.

b. The ATLER QUEUE should re-enable your queue. Of course, if there are pending messages it will also start activation and since you have a bug in your stored proc, the stored proc will rollback 5 times and disable it back.

Rushi

|||

yeah.. that's should my probme. Thanks a lot.

How to set Identity_Insert in dynamic sql?

Hi there,

I need to temporary set identity_insert off on a table in dynamic sql. Here is a sample from my stored procedure:

Create t_Table Table4test
(
ColID Int Not Null Identity(1,1)
, ColContent varchar(30) Not Null
)

Insert t_Table4test Values ( 'Test1' )
Insert t_Table4test Values ( 'Test2' )

Insert t_Table4test Values ( 'Test3' )

Declare
@.cCmd nvarchar(3000)
, @.cTableName nvarchar(15)

-- So far all is fine...
Select @.cTableName = 't_Table4test'
/*
Now I need to insert a new record, BUT this time WITH ColID
so I have to set Identity_Insert to On
*/
Select @.cCmd = 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' On'

Execute( @.cCmd )

-- Now the insert part
Select @.cCmd = 'Insert t_Table4test Values ( 4, ' + Char(39) + 'Test3' + Char(39) + ')'

Execute( @.cCmd )

When it is executed I am getting following error:
Cannot insert explicit value for identity column in table 't_Table4test' when IDENTITY_INSERT is set to OFF.

When I print @.cCmd they are printed in proper order, meaning Set first and then Insert after it, but when it use Exec instead it always checks for Identity status first and fails!

HEEEELP!!!

...and thanks in advance.

Every time you call EXECUTE it runs the command as a separate batch with its own context, so you need to put everything in the @.cCmd and EXECUTE just once. You can simply concatenate the INSERT after the identity_insert command, something like this

Select @.cCmd = 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' On '

Select @.cCmd = @.cCmd + 'Insert t_Table4test (col1, col2) Values ( 4, ' + Char(39) + 'Test3' + Char(39) + ')'

Execute( @.cCmd )

Note that you must specify the column names in the insert statement when you use identity_insert, and don't forget the space between the two commands (I added a space after the word On in the first statement.

|||

Can you explain why you think you need to use dynamic SQL in this way. In your example the table name is hardcoded in the dynamic sql.

If the table name is being passed in you need to be very careful because you can be exposed to a sql injection vulnerability.

You may be interested to read this article http://www.sommarskog.se/dynamic_sql.html

|||I already tried it and it does NOT work. To make sure all is correct instead of Execute I used Print, executed results and worked. However, when ran in sproc I got a same error message.|||Yes I can, the reason behind it is that this sproc is executed for different tables and table name is passed as one of parameters. The table name in my example is hardcoded just to make it simpler.

This db and the application runs on the intranet, for small number of users, so there is no real SQL injection danger.|||You need to do it all as one EXECUTE command. Every EXECUTE is its own batch, so the setting gets reset as soon as the batch ends.|||

Can you post you stored procedure so we can try and find the problem.

|||

This shows the code works, you should use the sp_executesql as much as possible

if object_id('tableForInsert') is not null

drop table tableForInsert

go

create table tableForInsert (col1 int identity(1,1),col2 int)

go

declare @.sql nvarchar(1000)

set @.sql = 'set identity_insert tableForInsert on

insert into tableForInsert (col1, col2) values (@.col1, @.col2)'

exec sp_executesql @.sql, N'@.col1 int,@.col2 int',10,10

set @.sql = 'set identity_insert tableForInsert on

insert into tableForInsert (col1, col2) values (11, 12)'

execute( @.sql)

|||If you are in SQL2005 you can use a semicolon to demark line ends. Thus you would have something like the following.

USE tempdb
GO
IF OBJECT_ID('tempdb..t_Table4Test') IS NOT NULL
DROP TABLE t_Table4Test

CREATE TABLE t_Table4test
(
ColID int NOT NULL Identity(1,1)
, ColContent varchar(30) NOT NULL
)

INSERT t_Table4test VALUES ( 'Test1' )
INSERT t_Table4test VALUES ( 'Test2' )
INSERT t_Table4test VALUES ( 'Test3' )

DECLARE
@.cCmd nvarchar(3000)
, @.cTableName nvarchar(15)

-- So far all is fine...
SELECT @.cTableName = 't_Table4test'

SELECT * FROM t_Table4Test

SELECT @.cCmd = 'SET IDENTITY_INSERT tempdb.dbo.' + @.cTableName + ' On;'

-- Now the insert part
SELECT @.cCmd = @.cCmd + ' INSERT t_Table4test(ColID,ColContent) VALUES ( 4, ' + CHAR(39) + 'Test3' + CHAR(39) + ');'
SELECT @.cCmd = @.cCmd + ' SET IDENTITY_INSERT tempdb.dbo.' + @.cTableName + ' Off;'

EXEC sp_Executesql @.cCmd
SELECT * FROM t_Table4Test
Select @.cCmd = 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' On;'

Select @.cCmd = @.cCmd + 'Insert t_Table4test Values ( 4, ' + Char(39) + 'Test3' + Char(39) + ');'
Select @.cCmd = @.cCmd + 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' Off;'

EXEC sp_executesql @.Cmd

How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.

How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.

How to set a column to be an identity column in TSQL.

Can someone provide the syntax for setting a column to be an identity column
in a stored proceedure. When I export my database, my identity columns are
no longer identity columns.I don't beleive you can add an Identity property to an existing column.
You may need to create a temp table with the identity column in it, copy the
data to the temp table, drop the existing table and then rename your temp
one.
"Jim Mitchell" <jim_mitchell@.mindspring.com> wrote in message
news:uSn1WWFfDHA.1732@.TK2MSFTNGP12.phx.gbl...
> Can someone provide the syntax for setting a column to be an identity
column
> in a stored proceedure. When I export my database, my identity columns
are
> no longer identity columns.
>

how to send query from SQL Server using post method

Hello Everyone
i am new to SQL Server. i have a problem. i have to send a query string
to a server using post method from a stored procedure.
i got a stored procedure in some group for sending Http Post Request
from Stored Procedure. but i dont know how to pass parameters and Http
Header Information.
i have to send 4 parameters (Control name are)
1. Name (character string)
2. msisdn (Numeric string like Mobile No.)
3. toAddress (Numaric String like Mobile No.)
4. message (character string)
Plz can anyone help me.
the HTTP_post stored procedure which i found at some group is as
follows:
****************************************
**************************
CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
out)
As
Declare
@.obj int
,@.hr int
,@.status int
,@.msg varchar(255)
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
goto eh end
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <>0 begin set @.msg = 'sp_OAMethod read status failed' goto
eh
end
if @.status <> 200 begin set @.msg = 'sp_OAMethod http status ' +
str(@.status) goto eh end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <>0 begin set @.msg = 'sp_OAMethod read response failed' goto
eh end
exec @.hr = sp_OADestroy @.obj
return
eh:
exec @.hr = sp_OADestroy @.obj
Raiserror(@.msg, 16, 1)
return
GO
****************************************
**************************Hi,
I think you need to modify this bit to add the parameters in...
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
change to...
declare @.parms varchar(500)
set @.parms = 'Var1=' + @.yourparm + '&Var2=' + @.yourparm2
exec @.hr = sp_OAMethod @.obj, 'send', NULL, @.parms
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<vineet.jsl@.gmail.com> wrote in message
news:1130749924.513462.309240@.f14g2000cwb.googlegroups.com...
> Hello Everyone
> i am new to SQL Server. i have a problem. i have to send a query string
> to a server using post method from a stored procedure.
> i got a stored procedure in some group for sending Http Post Request
> from Stored Procedure. but i dont know how to pass parameters and Http
> Header Information.
> i have to send 4 parameters (Control name are)
> 1. Name (character string)
> 2. msisdn (Numeric string like Mobile No.)
> 3. toAddress (Numaric String like Mobile No.)
> 4. message (character string)
> Plz can anyone help me.
> the HTTP_post stored procedure which i found at some group is as
> follows:
> ****************************************
**************************
> CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
> out)
> As
>
> Declare
> @.obj int
> ,@.hr int
> ,@.status int
> ,@.msg varchar(255)
>
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
> if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
> failed', 16,1) return end
>
> exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
> 'Content-Type', 'application/x-www-form-urlencoded'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
> goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
>
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <>0 begin set @.msg = 'sp_OAMethod read status failed' goto
> eh
> end
>
> if @.status <> 200 begin set @.msg = 'sp_OAMethod http status ' +
> str(@.status) goto eh end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <>0 begin set @.msg = 'sp_OAMethod read response failed' goto
> eh end
>
> exec @.hr = sp_OADestroy @.obj
> return
>
> eh:
> exec @.hr = sp_OADestroy @.obj
> Raiserror(@.msg, 16, 1)
> return
> GO
> ****************************************
**************************
>|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL but this time i have to
send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL from stored procedure
but this time i have to send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?XP_sendmail lives in the master database, so you should use the command
master..XP_sendmail
HTH, Jens Suessmeyer.

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?Did you qualify with the master database?
EXEC master..xp_sendmail ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23apBGJDQGHA.516@.TK2MSFTNGP15.ph
x.gbl...
>I tried to use the stored procedure xp_sendmail to send mail
> programmatically from MSSQL2000.
> But it seems to be inexistent.
> Is there any other means to do that?
>|||I wrote now the following code:
exec [master].[dbo].[xp_sendmail]
@.recipients = 'bishoy@.bishoy.com',
@.message = 'Test',
@.subject = 'Test SQL Mail'
and received the following error:
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
Although when I added @.user parameter, I received the following error:
Server: Msg 17981, Level 16, State 1, Line 0
xp_sendmail: Invalid parameter '@.user'|||There are plenty of issues with xp_sendmail. I have seen this in newsgroups
reported before. You'll
probably find a resolution by Google and searching the newsgroup archives.Al
so consider using
xp_smtp_sendmail instead (www.sqldev.net).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23ErGM4FQGHA.3896@.TK2MSFTNGP15.p
hx.gbl...
>I wrote now the following code:
> exec [master].[dbo].[xp_sendmail]
> @.recipients = 'bishoy@.bishoy.com',
> @.message = 'Test',
> @.subject = 'Test SQL Mail'
>
> and received the following error:
> Server: Msg 17985, Level 16, State 1, Line 0
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> Although when I added @.user parameter, I received the following error:
> Server: Msg 17981, Level 16, State 1, Line 0
> xp_sendmail: Invalid parameter '@.user'
>