Create an data type with identity(1, 1) property

Hello!

Code Snippet

--I'm trying this command:
create type int8 from tinyint identity(1,1) not null

But, an error occurs: don't possible create an data type with identity property....... Correct?

How to create an data type with identity property? The 'sys.sp_addextendedproperty' SP is the solution? An example, please?

Bye!

The IDENTITY property is linked to a table -as well as a datatype.

CREATE TABLE MyTable

( RowID int IDENTITY,

MyDate varchar(20)

)

You cannot simply CREATE TYPE or DECLARE a variable and also have an IDENTITY property for the type/variable.

|||another example?

execute('
select
identity(int,1,1) as id,
sum(trunk.count) as count,
sum(trunk.duration) as duration,
sum(trunk.charge1) as charge1,
trunk.trunk,
trunk.currency
into
#utilisation
from
(
select
count(local.callid) as count,
sum(local.duration) as duration,
sum(isnull(local.charge1,''0'')) as charge1,
local.trunk,
local.currency
from
tt32.dbo.trunk t,
database.dbo.local01 local
where
local.trunk = t.trunk
group by

local.trunk,
local.currency
)trunk
group by

trunk.trunk,
trunk.currency

--showing the result
select
id,
isnull(count,''0'') as count,
duration,
isnull(charge1,''0'') as charge1,
trunk,
currency
from #utilisation
|||: (

Ok. Thanks!

Bye!

Create an array in a result field

I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.

ReqNo ProcID

7102005 1409

7102005 1796

7139003 1411

7139003 6097

7261030 1409

7261030 1796

7268303 3998

7268303 4000

I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.

So, ideally my result would be:

ReqNo ProcSet

7102005 1409, 1796

7139003 1411, 6097

7261030 1409, 1796

7268303 3998, 4000

Is this possible?

declare

@.startdate smalldatetime,

@.enddate smalldatetime ,

@.month int,

@.year int

select

@.startdate = dateadd (dd, -7, getdate())

SELECT

@.month = datepart (month, @.startdate),

@.year = datepart (year, @.startdate)

SELECT

@.startdate = convert (smalldatetime, convert(varchar(2), @.month) + "/1/" + convert (varchar(4), @.year))

select

@.enddate = dateadd (dd, 1 , @.startdate)

select distinct

pp_req_no as ReqNo,

pp_cproc_id_r as ProcID

from

risdb_rch08_stag..performed_procedure

(index pp_serv_time_r_ndx)

where

pp_service_time_r between @.Startdate and @.Enddate

and pp_status_v = 'CP'

and pp_rep_id > 0

order by

pp_req_no, pp_cproc_id_r

You could create a function that would concatenate the fields together and return them as a string. This is not really a recommended practice, but can be occassionally useful for presentation. One trick is to use a local variable to accumulate the results. Of course, your function would be specific to this table.

I am not up on 2005 features, but it seems like there is a new unpivot operator that might work.

This demonstrates how to do the accumulation into a local variable.

use Northwind

go

Declare @.result varchar(1000)

Select @.result = Case When @.result Is Not Null Then @.result + ', ' Else '' End + Convert(varchar(10),OrderID)

From Orders

Where CustomerID = 'ALFKI'

Select @.result|||

Confused?

So are you saying that this syntax @.result + ', ' will build the results in the field? This looks like it will concatenate one result followed by the OrderID in your example (123456, 99999).

How will that build multiple results in one field? I understand how to declare and select, but this does not seem to make sense to me. I will be the first to admit if I am missing something here...which could very well be the case. Bear with me.

|||

Hi,

SELECT t3.ReqNo, MAX(case t3.seq when 1 then t3.ProcID end)

+ MAX(case t3.seq when 2 then ', ' + t3.ProcID else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.ProcID else '' end) AS ProcID

FROM ( SELECT ReqNo, ProcID, (SELECT COUNT(*) FROM yourTable AS t2 WHERE t2.ReqNo = t1.ReqNo and t2.ProcID <= t1.ProcID) AS seq

FROM yourTable AS t1

) as t3

GROUP BY t3.ReqNo

Or you can use this for SQL Server 2005:

SELECT t3.ReqNo, t3.[1] + coalesce(', ' + t3.[2], '') + coalesce(', ' + t3.[3], '') AS ProcID

FROM (SELECT ProcID, ReqNo, ROW_NUMBER() OVER(PARTITION BY ReqNo ORDER BY ProcID) AS seq FROM yourTable) as t1

PIVOT (MAX(ProcID) for seq in ([1], [2], [3])) AS t3

Or using CTE in SQL Server 2005:

With MyCTE(ReqNo, ProcID, ProcIDs, myNum) AS

(SELECT a.ReqNo, CONVERT(varchar(50), MIN(a.ProcID)) as col1, CONVERT(varchar(50),(a.ProcID)) as ProcIDs, 1 as myNum

FROM yourTable a GROUP BY a.ReqNo, CONVERT(varchar(50),(a.ProcID))

UNION ALL

SELECT b.ReqNo, CONVERT(varchar(50), b.ProcID), CONVERT(varchar(50), (c.ProcIDs + ',' + b.ProcID)), c.myNum+1 as myNum

FROM yourTable b INNER JOIN MyCTE c ON b.ReqNo=c.ReqNo

WHERE b.ProcID>c.ProcID

)

SELECT a.ReqNo, ProcIDs FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.ReqNo FROM MyCTE a1

group by a1.ReqNo) b on a.ReqNo=b.ReqNo AND a.myNum= b.myNumMax ORDER BY a.ReqNo

|||

Sorry that I wasn't very clear.

You would build a User Defined Function that would be specific to the table you are working with and would accept a parameter that would identify the records -- like the customerID in the Northwind case. The function would concatenate the values into the local variable and return that variable. You could then call this function as part of your Select clause.

Performance would likely be a challenge.

Here is a NorthWind example

Drop Function OrderList

go

Create Function OrderList( @.cust varchar(10) )

Returns varchar(1000)

As

Begin

Declare @.result varchar(1000)

Select@.result =

Case When @.result Is Not Null

Then @.result + ', '

Else ''

End + Convert(varchar(10), OrderID )

FromOrders

WhereCustomerId = @.cust

return @.result

End

go

SelectCustomerID,

CompanyName,

dbo.OrderList( CustomerID )

FromCustomers

create a script to display all the rows of the view

I have a view that has a link to 3 tables.
I need to display all the rows of the view.
here is the code for the view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
I like to thank you ahead for the help
Thank you
Cristianselect * from Oils_Cautions_View
"Cristian" <vitanc@.hotmail.com> wrote in message
news:0c9901c392a4$2903c2b0$a001280a@.phx.gbl...
> I have a view that has a link to 3 tables.
> I need to display all the rows of the view.
> here is the code for the view.
> USE Aromatherapy
> GO
> if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
> = 'V')
> DROP VIEW
> Oils_Cautions_View
> go
> CREATE VIEW Oils_Cautions_View AS
> SELECT o.oilID, oilName,
> Description FROM Oils AS o, Cautions as c, OilCautions as
> oc
> Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
> I like to thank you ahead for the help
> Thank you
> Cristian
>|||1. Create DB Links OLE DB SQL Links for all the 3 servers.
say:
DBLINK1 points to Server1.employee
DBLINK2 points to Server2.employee
DBLINK3 points to Server3.employee
create view v_employee as
select * from dblink1...employee
union
select * from dblink2...employee
union
select * from dblink3...employee
go
select * from v_employee
There is no need for 'Hardcoding' the db servername.
If server needs to changed, you change it at the Link creation time.

Create a modified version of an existing rendering extension

Is there any documentation or sample code to point me in the right direction
on how to modify an existing rendering extension?
I need to add some browser checking to the existing JavaScript that is
generated for the toolbar. I was hoping to modify the existing HTML 4.0
rendering to include some more browser checking to replace some of the
specific IE JavaScript properties.
Any help would be greatly appreciated.
Thanks,This is not possible. All of our renderers have link demands and therefore
could not be loaded by your assemblies.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> Is there any documentation or sample code to point me in the right
direction
> on how to modify an existing rendering extension?
> I need to add some browser checking to the existing JavaScript that is
> generated for the toolbar. I was hoping to modify the existing HTML 4.0
> rendering to include some more browser checking to replace some of the
> specific IE JavaScript properties.
> Any help would be greatly appreciated.
> Thanks,
>|||Are you saying what I am trying cannot be done or that I cannot modify an
existing rendering extension. According to the books online I should be
able to modify an existing extension. If I cannot modify an existing
extension, is there any way for me to modify the toolbar JavaScript?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> This is not possible. All of our renderers have link demands and
therefore
> could not be loaded by your assemblies.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > Is there any documentation or sample code to point me in the right
> direction
> > on how to modify an existing rendering extension?
> >
> > I need to add some browser checking to the existing JavaScript that is
> > generated for the toolbar. I was hoping to modify the existing HTML 4.0
> > rendering to include some more browser checking to replace some of the
> > specific IE JavaScript properties.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> >
> >
>|||You can not modify the existing renderers. Can you point me to where in the
docs it says this?
We currently have no mechanism for modifying the JavaScript in the toolbar
so I am not sure how you could accomplish what you are trying to do.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > This is not possible. All of our renderers have link demands and
> therefore
> > could not be loaded by your assemblies.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > Is there any documentation or sample code to point me in the right
> > direction
> > > on how to modify an existing rendering extension?
> > >
> > > I need to add some browser checking to the existing JavaScript that is
> > > generated for the toolbar. I was hoping to modify the existing HTML
4.0
> > > rendering to include some more browser checking to replace some of the
> > > specific IE JavaScript properties.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > >
> >
> >
>|||Could you point me at the section in books online that you're referring to?
You cannot extend our existing rendering extensions. You can extend report
server by writing another rendering extension.
No you cannot change/add script to the report viewer or the report manager.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
>> This is not possible. All of our renderers have link demands and
> therefore
>> could not be loaded by your assemblies.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
>> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
>> > Is there any documentation or sample code to point me in the right
>> direction
>> > on how to modify an existing rendering extension?
>> >
>> > I need to add some browser checking to the existing JavaScript that is
>> > generated for the toolbar. I was hoping to modify the existing HTML
>> > 4.0
>> > rendering to include some more browser checking to replace some of the
>> > specific IE JavaScript properties.
>> >
>> > Any help would be greatly appreciated.
>> >
>> > Thanks,
>> >
>> >
>>
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||I guess I did not read the documentation correctly. It sounded to me that
you can modify the existing extensions but instead it is saying create
another version of an existing extension with your changes. If that is what
the document means than how is that an alternative to writing a new
extension? Its not like MS will give up the source so that I can just
create my own version.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, the documentation seems to be a little misleading. I will forward it
to our doc team to see if they can make it a little more clear.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > You can not modify the existing renderers. Can you point me to where in
> the
> > docs it says this?
> >
> > We currently have no mechanism for modifying the JavaScript in the
toolbar
> > so I am not sure how you could accomplish what you are trying to do.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > This is not possible. All of our renderers have link demands and
> > > therefore
> > > > could not be loaded by your assemblies.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > >
> > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > Is there any documentation or sample code to point me in the right
> > > > direction
> > > > > on how to modify an existing rendering extension?
> > > > >
> > > > > I need to add some browser checking to the existing JavaScript
that
> is
> > > > > generated for the toolbar. I was hoping to modify the existing
HTML
> > 4.0
> > > > > rendering to include some more browser checking to replace some of
> the
> > > > > specific IE JavaScript properties.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Thanks,
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Could you point me at the section in books online that you're referring
to?
> You cannot extend our existing rendering extensions. You can extend
report
> server by writing another rendering extension.
> No you cannot change/add script to the report viewer or the report
manager.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> >> This is not possible. All of our renderers have link demands and
> > therefore
> >> could not be loaded by your assemblies.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> >> > Is there any documentation or sample code to point me in the right
> >> direction
> >> > on how to modify an existing rendering extension?
> >> >
> >> > I need to add some browser checking to the existing JavaScript that
is
> >> > generated for the toolbar. I was hoping to modify the existing HTML
> >> > 4.0
> >> > rendering to include some more browser checking to replace some of
the
> >> > specific IE JavaScript properties.
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >
> >
>|||Is there any documentation on creating your own rendering extension that I
can use now?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eTRNDCzeEHA.1644@.tk2msftngp13.phx.gbl...
> Yes, the documentation seems to be a little misleading. I will forward it
> to our doc team to see if they can make it a little more clear.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Is there any documentation on creating your own rendering extension?
> > Maybe I can create a custom toolbar within my new rendering extension
and
> > then hide the existing toolbar using the command provided by reporting
> > services.
> >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> >
> > Here is a snippet.
> >
> > Writing Custom Rendering Extensions
> > Before you decide to create a custom rendering extension, you should
> > evaluate simpler alternatives. You can:
> >
> > a.. Create a modified version of an existing rendering extension.
> > b.. Customize rendered output by specifying device information
settings
> > for existing extensions.
> > c.. Add custom formatting and presentation features by combining XSL
> > Transformations (XSLT) with the output of the XML rendering format.
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > You can not modify the existing renderers. Can you point me to where
in
> > the
> > > docs it says this?
> > >
> > > We currently have no mechanism for modifying the JavaScript in the
> toolbar
> > > so I am not sure how you could accomplish what you are trying to do.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Are you saying what I am trying cannot be done or that I cannot
modify
> > an
> > > > existing rendering extension. According to the books online I
should
> be
> > > > able to modify an existing extension. If I cannot modify an
existing
> > > > extension, is there any way for me to modify the toolbar JavaScript?
> > > >
> > > >
> > > >
> > > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > > This is not possible. All of our renderers have link demands and
> > > > therefore
> > > > > could not be loaded by your assemblies.
> > > > >
> > > > > --
> > > > > -Daniel
> > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > >
> > > > >
> > > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > > Is there any documentation or sample code to point me in the
right
> > > > > direction
> > > > > > on how to modify an existing rendering extension?
> > > > > >
> > > > > > I need to add some browser checking to the existing JavaScript
> that
> > is
> > > > > > generated for the toolbar. I was hoping to modify the existing
> HTML
> > > 4.0
> > > > > > rendering to include some more browser checking to replace some
of
> > the
> > > > > > specific IE JavaScript properties.
> > > > > >
> > > > > > Any help would be greatly appreciated.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||So, although the documentation alludes to modifying an existing rendering
extension, what it is actually referring to is creating a version of a
rendering extension that is similar to ones that we already have. That is,
writing a new HTML rendering extension. I must admit that it is misleading
and that writing one of our rendering extensions from scratch is not a
"simpler alternative."
Writing a rendering extension is no simple matter, in fact, it is one of the
most challenging development experiences I have seen at Microsoft. Please
see the following blog post for my take:
http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx
The Reporting Services team, in light of other key deliverables, elected not
to document rendering extensions in version 1 of Reporting Services because
it was considered a low usage task (only a few select partners ever
expressed an interest in developing one and only a handful have ever
successfully done it and that is after spending months with our developers
hands-on).
It is not an end-user feature and is not something for the average or even
advanced developer. If you are interested, we have included documentation
for rendering extensions in our SQL Server 2005 Books Online that was
released with SQL Server 2005 Beta 2 recently. We hope to someday create an
SDK that will explain rendering extensions in more detail, but that is
probably a ways off.
Bryan
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:eB9luSzeEHA.1692@.tk2msftngp13.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > Could you point me at the section in books online that you're referring
> to?
> > You cannot extend our existing rendering extensions. You can extend
> report
> > server by writing another rendering extension.
> >
> > No you cannot change/add script to the report viewer or the report
> manager.
> >
> > -Lukasz
> >
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > >> This is not possible. All of our renderers have link demands and
> > > therefore
> > >> could not be loaded by your assemblies.
> > >>
> > >> --
> > >> -Daniel
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >>
> > >>
> > >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > >> > Is there any documentation or sample code to point me in the right
> > >> direction
> > >> > on how to modify an existing rendering extension?
> > >> >
> > >> > I need to add some browser checking to the existing JavaScript that
> is
> > >> > generated for the toolbar. I was hoping to modify the existing
HTML
> > >> > 4.0
> > >> > rendering to include some more browser checking to replace some of
> the
> > >> > specific IE JavaScript properties.
> > >> >
> > >> > Any help would be greatly appreciated.
> > >> >
> > >> > Thanks,
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Create a dataset with .Net code and render report from it. Possible?

Hi

I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.

My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...

I have tried to find a solution but so far, no luck.

Anyone have any ideas?

/F

If you use the reportviewer control in local mode you can do what you say. If you want this sent straight to hard copy that also can be achieved. I'm not sure if that meets your requirement.

create a Custom Delivery Protocol class and Auto-Refresh for SQL Grids displaying SQL Server Dat

How create a custom delivery Protocol class and show data in the Grid?

any advice and is good at having sample code?

I don't have it in front of me at the moment, but I almost positive that Shyam Pather's book show how to create a custom delivery protocol. (Even if it doesn't you'll want to get the book nonetheless; it's a great resource!)

HTH..

Joe

sql

create a Custom Delivery Protocol class and Auto-Refresh for SQL Grids displaying SQL Server Dat

How create a custom delivery Protocol class and show data in the Grid?

any advice and is good at having sample code?

I don't have it in front of me at the moment, but I almost positive that Shyam Pather's book show how to create a custom delivery protocol. (Even if it doesn't you'll want to get the book nonetheless; it's a great resource!)

HTH..

Joe

Crash in Execute() method of _ConnectionPtr

Hi All,
I have a piece of code where object of _ConnectionPtr is used. When I
call the Execute() method for this object I get an debug error stating
that "The Value of ESP could be saved...".
If I ignore the message this leads to crash with "Access violation
error".
Following is the code snippet i use.
void ExecuteSQLCmd( unsigned short *sqlCmd)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I have a piece of code where object of _ConnectionPtr is used. When I
> call the Execute() method for this object I get an debug error stating
> that "The Value of ESP could be saved...".
> If I ignore the message this leads to crash with "Access violation
> error".
> Following is the code snippet i use.
> void ExecuteSQLCmd( unsigned short *sqlCmd)
> {
> _ConnectionPtr ConnPtr ;
> _bstr_t cmd ;
> _RecordsetPtr ptrRS("ADODB.Recordset");
> .
> .
> cmd = sqlCmd ;
> //occassionally crash here
> pRS = ConnPtr->Execute(cmd,NULL,enumtextCmd) ; //enumtextCmd = 1
> }
> The crash at the above mentioned statement occurs sometimes, I am not
> sure why the error is occuring I am using mo.dll version 2.50
Extremely stupid question: should you not assign ConnPtr some value
before using it?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I did forgot to mention in the above code.
I have used CreateInstance() and Open() method of _ConnectionPtr before
actually calling Execute().
The code looks as follows
void ExecuteSQLCmd( unsigned short *sqlCmd, char *ConnStr)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");
CoInitialize(NULL) ;
ConnPtr.CreateInstance(__uuidof( Connection ));
ConnPtr.Open(ConnStr,"","",adoConnectUnSpecified) ; //
adoConnectUnspecified = -1|||
I can't see anything obviously wrong with your code, although
the CoInitialize should be called only once per thread and
before any COM calls, including the line
_RecordsetPtr ptrRS("ADODB.Recordset");
When debugging what does the call stack tell you when you
get the "The Value of ESP could be saved..." message?
Occasional crashes may indicate memory overwrites/array bounds
exceeded etc. Is you app multi-threaded? If so are there thread
safety related issues? There are 3rd party tools available
to help with these kinds of problems (e.g. BoundsChecker).|||Hi,
First, I would like to let you know actually how my code is
implemented.
I have a C++ class "CRdbms". The public member variable "ptrConn" in
class "CRdbms" is an object of class "_ConnectionPtr. The object of
these class "CRdbms" is passed to function "ExecuteSQLCmd()". Before
calling ExecuteSQLCmd(), _ConnectionPtr is initialized. Then the
function ExecuteSQLCmd () is called. ExecuteSQLCmd() looks as follows:
void ExecuteSQLCmd(CRdbms &objRdbms, unsigned short *sqlCmd)
{
bstr_t cmd;
cmd = sqlCmd;
CoInitialize(NULL);
try
{
_RecordsetPtr ptrRs("ADODB.Recordset");
if ( objRdbms.RdbmsCmdTimeOut() != -1 )
{
objRdbms.ptrConn->CommandTimeout = objRdbms.RdbmsCmdTimeOut();
}
//Occassionally crash here
ptrRs = objRdbms.ptrConn->Execute(cmd,
NULL, enumtextCmd) ; //enumtextCmd = 1
}
catch (_com_error &e)
{
status = false;
wsprintf("Code = %08lx\n", e.Error());
wsprintf("Code meaning = %s\n", (char*) e.ErrorMessage());
wsprintf("Source = %s\n", (char*) e.Source());
wsprintf("Description = %s\n", (char*) e.Description());
wsprintf ( objRdbms.errMsg, L"%s", (unsigned short *)
e.Description());
}
CoUninitialize();
}
The application is running in multi-threaded environment. The debug
error is coming will executing Execute() function of "_ConnectionPtr"
and if we ignore the message the crash occurs while results are
returned and assigned to ptrRs. i.e. statement
ptrRs = objRdbms.ptrConn->Execute(cmd, NULL, enumtextCmd) ;
//enumtextCmd = 1
It seems that the memory overwrite is taking place in this situation
but not sure why is it happening so.
Please help me to solve the problem.
Thanks and Regards,
Ashish Choudhari|||
A couple of things to try...
Do you have a single instance of your "CRdbms" class that
is shared between all threads? If so, then you may need
to serialise any access to "objRdbms.ptrConn" (use a
critical section inside your ExecuteSQLCmd function)
or create a new connection per thread.
Try moving the CoInitialize/CoUninitialize so that
they happen once per thread.|||I do not have single instance of "CRdbms" object. It is created one per
thread and not shared between any other thread.
There are multiple functions called by each thread and each function
have CoInitialize and CoUnitilize.
I will try moving them so that it occurs only one per thread.
Thanks for your comments.
Regards,
Ashish Choudhary|||Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I do not have single instance of "CRdbms" object. It is created one per
> thread and not shared between any other thread.
> There are multiple functions called by each thread and each function
> have CoInitialize and CoUnitilize.
> I will try moving them so that it occurs only one per thread.
I would not say that I know this stuff too well, but I've written
a DLL in C++ that implements a general interface for running queries
from Perl. When the DLL is loaded the first time, I do:
CoInitializeEx(NULL, COINIT_MULTITHREADED);
And I, eh, never call CoUnitialize.
But I would like to stress that I'm a not regular programmer of COM
application, so the above may very well be an example of poor practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I modified my code so that CoInitialize is called only once per thread.
Doing this reduced the number of crash locations. But the code still
crash in Open() function of _ConnectionPtr. My Code looks as follows.
bool AdoConnect (CRdbms &objRdbms )
{
bool status = true;
if(FAILED(CoInitialize(NULL)))
{
status = false ;
return status ;
}
try
{
HRESULT hR = objRdbms.Pcn.CreateInstance(__uuidof( Connection ));
if (!SUCCEEDED(hR))
{
_com_issue_error(hR);
status = false;
}
else
{
char *sqlCmd ;
sqlCmd = rdbms.RdbmsConnStr() ;
if (sqlCmd == NULL)
{
printf("Error: No Connection String present") ;
status = false ;
}
else
{
bstr_t cmd(sqlCmd) ;
objRdbms.Pcn->Open ( cmd, "", "", adConnectUnspecified);
status = true;
}
}
}
catch (_com_error &e)
{
if ( status )
{
objRdbms.Pcn.Release();
}
status = false;
printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Code meaning = %s\n", (char*) e.ErrorMessage());
printf("Source = %s\n", (char*) e.Source());
printf("Description = %s\n", (char*) e.Description());
//wcscpy ( rdbms.errMsg, ERR_CONN_FAILED );
wsprintf ( rdbms.errMsg, L"%s", (unsigned short *) e.Description());
}
return status;
}
In the above code SOMETIMES when the statement objRdbms.Pcn->Open() is
execute the application crashes here. Internally, when the function
Open() returns it destroys the object of _bstr_t and it is here it
crashes.
Please do let me know if you have any suggestions.
Thanks and Regards,
Ashish Choudhary

CR from only code(VS 2005)

Hey gurus

I need to create a crystal report entirely from code

i have a form where a customer will add table(s) to a list box, depending on the added tables, a list of fields applicable to the respective table can be added to another list. On selection of each of these fields, values may be entered so as to filter the data displayed on the report.

From each of the availble fields that can be "Parametized", a group can be created, with ascending/descending order

Finally summaries can be added such as the sum of all amounts(total,VAT etc) to the end of the report
Report options like drill-down will need to be able to be set dynamically also.

so far everything above here is basicly how to generate my SQL statement, which won'y be that difficult.

But i have no idea how to make my report document show information the way i have described above.

left list < > right list (Panel with combobox, textbox , checkbox(enable/disable))

tables,field, groups are added/removed as depicted above
fields and groups have a panel next to each rightlistbox where parameters can be set like (< = > between like <> asc desc)

at the bottom of the form there are 3 buttons
Preview report data --> shows datagrid of Data retrieved via generated SQL statement
Preview Report --> shows a itempanel with items for each field/group/summary field
Create report - Creates crystal report and shows in report viewer on a tab created before the report begins creation

At this point, i am not very close to being far from completion
any help will be greatly appreciated
Thanks
Jsee attached file of GUI so you can get a better idea of what i am trying to do, sorry bout bad quality 100kb is not alot but sufficient|||I have made some progress on this

The code below shows the population of a dataset and subsequently binding it to a report, then showing that report in a report viewer control on a new tab created here also.

The report is completely blank. There is however data in the TRDS dataset



Private Sub MakeReport()

Dim TRDS As New DataSet

Dim ConString As String = ""

Dim CON As OleDb.OleDbConnection

Dim DA As OleDb.OleDbDataAdapter

Dim NewTabPanel As New DevComponents.DotNetBar.TabControlPanel

Dim RptUC As New ReportOnTab ' Usercontrol with crystal reportviewer on a panel

'Get Data

Try

TRDS = New Report1DataSet

If My.Settings.User_Claims_ManagementConnection_String = "" Then

ConString = My.Settings.Claims_Management_SystemConnectionString.ToString

Else

ConString = My.Settings.User_Claims_ManagementConnection_String.ToString

End If

CON = New OleDb.OleDbConnection(ConString)

DA = New OleDb.OleDbDataAdapter(FullSQL, CON)

'Fill the dataset

DA.Fill(TRDS, "Client")

'Make report on new tab

NewTabPanel.Name = "NewTab_Panel"

NewTabPanel.Text = "This is the new reports panel"

'Report creation

Report = New Report1

'Settings for the report

'Set datasource to the generated dataset

Report.SetDataSource(TRDS)

'At this point there is a blank report, with a dataset attached to it containing, one table and rows filtered by where in FULLSQL

'Set groups

'Add fields that will be shown to detail section

'Add summaries

'Apply formatting

'End of report creation

'Create tab on tab control

ReportTab.CreateTab("Name of this report", -1)

'Attach usercontrol to tab panel and make dock fill

ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1).AttachedControl.Controls.Add(RptUC)

ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1).AttachedControl.Controls.Item(0).Dock = DockStyle.Fill

'Reset the report creation form

ReportDefinitionsClear()

'Select the newly created tab

ReportTab.SelectedTab = ReportTab.Tabs.Item(ReportTab.Tabs.Count - 1)

'Set source of the usercontrol report viewer to this report

RptUC.ReportViewer.ReportSource = Report

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub



now the FULLSQL variable is rather dynamic and is generated through use of the above picture's gui, thats 2000 lines, shud i post that to?

anyway, it works, all i need to do now is...

1.! create fields from the SELECT statement on the report

2.! create groups via the ORDERBY statement on the report

3.! Finally add any other summaries,special fields, and so on to the report

My question is how do i achieve such a feat,

How do i add groups via code to the detail section?
How do i add fields via code to my blank reports detail section?
Is it even possible?

So far have discovered that most things within the report1 class are readonly.

I have looked everywhere online, searched for 2 days and found nothing like this

Please help
Thanks
J

CR / VB Sort Problem

OS= Windows XP SP2 Professional, Crystal Report XI Developers Edition, Visual Basic 6 Enterprise

Below is the code I am trying to use to print a report. This code except for this "ElseIf" statement is used and works fine to print other reports. The only difference between printing this report and the other reports is 1) the name of the report and 2) this is the only report I have a sortfield.
.
.
.
ElseIf ReportName = "Alternate.rpt" Then
Set craxreport = craxapp.OpenReport(ReportPath & "\" & ReportName)
Set dbTable = craxreport.Database.Tables(1)
craxreport.RecordSortFields(0) = "+{AlternatePart.dbEnterDate}"
End If
craxreport.DiscardSavedData
' ****************************************************************************************
CrystalActiveXReportViewer1.Refresh
Call CrystalActiveXReportViewer1_RefreshButtonClicked(True)
CrystalActiveXReportViewer1.ReportSource = craxreport
CrystalActiveXReportViewer1.ViewReport
Set craxreport = Nothing
When I run this code, if I keep "craxreport.RecordSortFields(0) " I get a "Subscript out of Range" error.

The report is defined as follows:
- It uses a table called "AlternatePart"
- There are 3 fields in the table - (dbOldPn, dbNewPn, and dbEnterDate)
- All fields are described at TEXT
- The report shows all 3 fields.
- I used the Record Sort Expert to set up one sort field and that is "AlternatePart.dbEnterDate"

Can you tell me what is possibly causing the above error?

ThanksI have a little experience with crax.
I think maybe Indexes are not zero based. try index 1|||Yes try using craxreport.RecordSortFields(1) and see if it is working|||Yes try using craxreport.RecordSortFields(1) and see if it is working
That was the problem -- thank you.

Sam

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||I did not manually execute such a step. And if this wasnt done automatically
with the upgrade to SQL2005 then it was not done.
Is this the link for this?
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
or are there more specifc instructions you can point me to?
Thanks
"Andrew J. Kelly" wrote:
> Did you update the stats after you brought over the db?
>
> --
> Andrew J. Kelly SQL MVP
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> > We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> > a
> > 5 hour perfmorance test. The application code is the same and the actions
> > performed during the test are the same. The increase is compared to
> > SQL2000.
> >
> > The db server is the same and was upgraded from SQL2000.
> > Disk Configuration: SCSI RAID 5, 3 Partitions
> > RAM: 3.75 GB of RAM
> > CPU: Quad Processor, 2.7Ghz
> > OS: Windows2003 SP1 Enterprise
> >
> > * Perfmon shows disk read/sec and disk write/sec fairly close between
> > SQL2000 and SQL2005
> >
> > Specifically, perfmon shows increased FullScans/sec and decreased
> > IndexSearches/sec.
> >
> > From perfmon
> > FullScan/sec (avg):
> > 2000: 6.292
> > 2005: 54.338
> >
> > IndexSearches/sec (avg):
> > 2000: 3455.942
> > 2005: 1441.712
> >
> > Any comments or suggestions would be greatly appreciated. Thanks.
>
>|||No this is not done by the upgrade process. The link tells you what stats
do. To update them either run sp_updatestats to update all stats or if you
haven't run DBCC DBREINDEX in a while try running that on all tables.
--
Andrew J. Kelly SQL MVP
"George Koulis" <GeorgeKoulis@.discussions.microsoft.com> wrote in message
news:071092A9-6057-47BB-A0A1-3EC8061D088C@.microsoft.com...
>I did not manually execute such a step. And if this wasnt done
>automatically
> with the upgrade to SQL2005 then it was not done.
> Is this the link for this?
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> or are there more specifc instructions you can point me to?
> Thanks
>
> "Andrew J. Kelly" wrote:
>> Did you update the stats after you brought over the db?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in
>> message
>> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> > We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> > over
>> > a
>> > 5 hour perfmorance test. The application code is the same and the
>> > actions
>> > performed during the test are the same. The increase is compared to
>> > SQL2000.
>> >
>> > The db server is the same and was upgraded from SQL2000.
>> > Disk Configuration: SCSI RAID 5, 3 Partitions
>> > RAM: 3.75 GB of RAM
>> > CPU: Quad Processor, 2.7Ghz
>> > OS: Windows2003 SP1 Enterprise
>> >
>> > * Perfmon shows disk read/sec and disk write/sec fairly close between
>> > SQL2000 and SQL2005
>> >
>> > Specifically, perfmon shows increased FullScans/sec and decreased
>> > IndexSearches/sec.
>> >
>> > From perfmon
>> > FullScan/sec (avg):
>> > 2000: 6.292
>> > 2005: 54.338
>> >
>> > IndexSearches/sec (avg):
>> > 2000: 3455.942
>> > 2005: 1441.712
>> >
>> > Any comments or suggestions would be greatly appreciated. Thanks.
>>|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
--
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.
Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>
|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000
.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

covert this C# to Vb.Net for Code

Hello,

This is my code in C#

DateTime sYear = new DateTime(Now(),1,1);

DateTime q1Start = sYear;

DateTime q2Start = sYear.AddMonths(3);

DateTime q3Start = sYear.AddMonths(6);

DateTime q4Start = sYear.AddMonths(9);

DateTime q1End = q2Start.AddDays(-1);

DateTime q2End = q3Start.AddDays(-1);

DateTime q3End = q4Start.AddDays(-1);

DateTime q4End = sYear.AddYears(1).AddDays(-1);

Can anyone convert this to VB.Net so that I can use it in "Code" section of the Reporting Services. I tried a VB-C# converter but Reporting Services did not like DateTime datatype.

Any help?

Try the following:

Dim sYear As New System.DateTime(Now(),1,1)
Dim q1Start As System.DateTime = sYear
Dim q2Start As System.DateTime = sYear.AddMonths(3)
Dim q3Start As System.DateTime = sYear.AddMonths(6)
Dim q4Start As System.DateTime = sYear.AddMonths(9)
Dim q1End As System.DateTime = q2Start.AddDays(-1)
Dim q2End As System.DateTime = q3Start.AddDays(-1)
Dim q3End As System.DateTime = q4Start.AddDays(-1)
Dim q4End As System.DateTime = sYear.AddYears(1).AddDays(-1)

HTH

|||That conversion was not that hard. Just a pointer, most of the time all you have to do is remove the ; from the c# code but there is quite a bunch of stuff that you still have to convert.|||

Here is a link you might find very hand to convert in either direction:

http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx

Covert Connection from Access to SQL

---

Edited by haidar_bilal - Please place your code within< code > and < /code > tag. Thank you.

---

I've got to convert the following functions so it connects to the SQL server that uses this connection string: ("server=(local);database=BCA;Trusted_Connection=yes")

The code below works fine to an Access db. I know I've got to change all the Ole objects below to SQL objects to connect to SQL server. But try as I might, I'm still not getting them right. Can anyone help by showing me how to change the code? Please just take the code and substitute the SQL connection strings for the Access string. That way even I could understand it. Sigh.

Public Class Functions
Public Function GetCommandObject(ByVal strCommand As String, ByVal strPath As String, ByVal ExecuteIt As Boolean) As Data.OleDb.OleDbCommand
Dim objConnection As Data.OleDb.OleDbConnection = GetConnectionObject(strPath)
objConnection.Open()
Dim objCommand As New Data.OleDb.OleDbCommand(strCommand, objConnection)
If ExecuteIt = False Then
Return objCommand
Else
objCommand.ExecuteNonQuery()
End If
objConnection.Close()
End Function
Public Function GetConnectionObject(ByVal strPath As String) As Data.OleDb.OleDbConnection
Dim objConnection As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath)
Return objConnection
objConnection.Close()
End Function
Public Function GetReaderObject(ByVal strCommand As String, ByVal strPath As String) As Data.OleDb.OleDbDataReader
Dim objCommand As Data.OleDb.OleDbCommand = GetCommandObject(strCommand, strPath, False)
Dim objReader As Data.OleDb.OleDbDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return objReader
objReader.Close()
End Function
End Class

www.connectionstrings.com show the connection strings for SQLClient:

Standard Security:

"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
- or -
"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
(booth connection strings produces the same result)

Trusted Connection:

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
- or -
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
(booth connection strings produces the same result)

(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Connect via an IP address:

"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Declare the SqlConnection:

C#:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString="my connectionstring";
oSQLConn.Open();

VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()

Which one you use depends upon how you want to login. You can use a username and password, or you can use a Trusted Connection, in which case, the ASPNET user on the Web Server must be a user in SQL Server (this is awkward if you are running SQL Server on a machine other than the Web Server.

Counting items

Hi,

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

Cheers.Hi!

Would this one help out?

SELECT appt.ref
, (Case When noteCount > 0 Then 1 Else 0 End) AS notes
FROM touchAppointments appt
, (SELECT appointment
, COUNT(*) as noteCount
FROM touchNotes) note
WHERE appt.practitioner=1
AND appt.ref = note.appointment

Greetings,
Carsten|||I would have writen like this
<code>
SELECT appt.ref , count(*)/count(*) AS notes
FROM touchAppointments as appt inner join touchNotes as note
on appt.ref = note.appointment
WHERE appt.practitioner=1 group by appt.ref
</code>|||You would?

count(*)/count(*) is at best going to return only 1s or Nulls, and at worst would return DivZero errors.

There are serveral ways to do this. CarstenK had one, though it is preferable to use a JOIN rather than linking tables in the WHERE clause.

Here are two more methods:

SELECT touchAppointments.ref, cast(count(touchNotes.appointment) as bit) notes
FROM touchAppointments
left outer join touchNotes on touchNotes.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1
GROUP BY touchAppointments.ref

SELECT touchAppointments.ref, isnull(notesSubquery.hasnotes, 0) as notes
FROM touchAppointments
left outer join (select distinct touchNotes.appointment, 1 as hasnotes from touchNotes) notesSubquery
on notesSubquery.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1|||you are right
count(*)/count(*) is at best going to return only 1s
but how come nulls and div by zero error(even at worst case) with inner join on touchAppointments.ref.|||How do you get the "0" paulbrooks wants to get with his CASE (....)?

Carsten|||Blindman,

Your second solution worked the trick. It returns a 1 for true and 0 for false, which is exactly what I needed.

Thanks a lot, guys.

Paul