Count Function

I am trying to design a blog and I want to have the typical setup where you see something like this:

Comments(some number), where the some number specifies the number of comments for a particular entry. I am thinking about two different approaches:

#1 is combining the count function into my normal query

objCmd = new OleDbCommand("SELECT TOP 10 Blog.*, BlogCategories.* FROM Blog INNER JOIN BlogCategories ON Blog.categoryID = BlogCategories.categoryID WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07' ORDER BY Blog.EntryDate DESC", objConn);

I would need to add an additional inner join to get to the table, BlogComments, to be able to count the correct number of comments for a specific entry, and obviously incorporate the Count function into my query as well.

Option #2 is creating a new column in my Blog datatable and just keep track of the number of comments by adding or subtracting (++ or --) from a record when a new comment is added or deleted. (I think this is easier but can have obvious problems if non standard changes are made to the blog).

Your best bet is like your 1st option, but instead of a join you use a subquery in your select list.

Add something like the following as your third column in your select list, and wrap parentheses around it

SELECT COUNT(*) FROM BlogComments WHERE BlogID = Blog.BlodID

|||

You can also send two separate statements in your command ("select top 10, etc......order by desc; select count(*)....", objConn)

and then you'll have two tables in your dataset or you can use .NextResult() on the Reader object.

|||

I tried your suggestion and I get an error: The multi-part identifier "Blog.BlogID" could not be bound.

Do I need an alias? Do I need to declare the value?

objCmd = new OleDbCommand("SELECT TOP 10 Blog.*, BlogCategories.* FROM Blog INNER JOIN BlogCategories ON Blog.categoryID = BlogCategories.categoryID WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07' ORDER BY Blog.EntryDate DESC; SELECT COUNT(*) AS NumComments FROM BlogComments WHERE BlogCID = Blog.BlogID", objConn);

|||

It was a silly assumption that there would be something as useful as a unique way to identify each Blog thread.

Please post your table DDL and sample data in the form of INSERT statements.

|||

SELECT TOP 10 Blog.*, BlogCategories.*, (select count(*) from BlogComments where BlogComments.<BlogKey> = Blog.<BlogKey>) as CommentCount

FROM Blog

INNER JOIN BlogCategories

ON Blog.categoryID = BlogCategories.categoryID

WHERE Blog.EntryDate BETWEEN '7/1/06' AND '12/31/07'

ORDER BY Blog.EntryDate DESC

Now, you need to fill in the BlogKey values, whatever they are (and there could be > 1 columns in the key.) If this doesn't make sense then you really do need to post your structures...

|||You are an optimist Louis. <BlogKey> is as likely to be comprehended as [BlogComments] or [BlogID]|||

Each query in the command string needs to be able to execute on its own.

Apparently Blog.BlogID isn't in the table you're referencing.

|||

Here are my tables and the columns within them:

Blog: BlogID(smallint), BlogEntry(varchar),State(varchar),Body(ntext), FirstName(varchar), LastName(varchar), CategoryID(varchar), Email(varchar), EntryDate(datetime)

BlogCategories: CategoryName(nchar), CategoryID(varchar)

BlogComments: BlogCID(smallint), Comment(ntext), FirstName2 (varchar), LastName2(varchar), Email2(varchar), CommentDate(datetime)

FYI: In my original post I had listed BlogID in both the BlogComments and Blog tables. I later changed the BlogComments' BlogID to BlogCID to ensure there was no conflict.

As far as how my pages are setup, I have a page that displays an entry, all comments connected to it, and additional comments can be entered. I have another page where new entries can be added. Then the third page, the main page, where I want to have a listing of entries and the number of comments for each attached to it is the one that is not working.

|||

Thanks, that is very helpful.

Now an additional question.

What links a Blog with its Comments?

(It seems like BlogComments 'should' have the BlogID as a Foriegn Key.)

|||both BlogID and BlogCID are primary keys and this is the information that connects the entry and the comments. On my other page where I display the comments along with the corresponding entry I have two separate functions, the connecting factor is that I do a URL query to collect a variable designating the entry and then both the entry and subsequent comments are each independently called based on that variable. So, joining the tables has been a problem I have been able to avoid until now.|||

So now I'm confused here.

If BlogCID is a Primary Key for the BlogComments table, and if BlogID is a Primary Key for the Blogs table, AND they are used to link the two tables, that would mean there could only be ONE comment per blog. That doesn't seem quite right.

|||Sorry, you are correct, there is no primary key for the Comments table.|||

Thanks for the clarification.

You would 'best' position the BlogComments table if you were to add a Primary key, perhaps an IDENTITY field.

And name it BlogCommentsID. (A good naming practice is {TableName}ID for IDENTITY fields.)

Without a Primary Key (or other method) to specifically identify each individual row, the row would NOT be editable.

I would rename the existing BlogCID to BlogID -that makes it easier to 'see' that there is a PK-FK relationship with the Blogs table.

Then this revision of your previous query 'should' work as you want. (NOTE: I have assumed renaming the BlogCID to BlogID.)

SELECT TOP
10 b.*,
bc.*,
bcom.ThreadCnt
FROM Blog b
INNER JOIN BlogCategories bc
ON b.categoryID = bc.categoryID
JOIN (SELECT
BlogID,
ThreadCnt = count(1)
FROM blogComments
GROUP BY BlogID
) bcom
ON b.BlogID = bcom.BlogID
WHERE b.EntryDate BETWEEN '7/1/06' AND '12/31/07'
ORDER BY b.EntryDate DESC;

|||I made the update and the page does load (I do not get an error screen), but the area on the page for the data is completely blank.

Could not setup Web Service Identity --> Error Code 80070003

Hello!
I′m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity

ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070003
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)

The default website contains an office sharepoint 2007 beta solution if that could cause the error..

Would be real grateful for some hints...

Regards

Erik B

Have you found a solution to this? I'm getting the same error after moving the Identities from one Website to another.|||

8007003 is ERROR_PATH_NOT_FOUND. Are you on Win2K3? I suspect this has something to do with ASP.NET setup. Either the WMI provider could not open the ASP.NET registry keys, or it could not find the machine.config file. Make sure you have run aspnet_regiis -i -enable.

If it doesn't work, I would start using regmon and filemon to looks for suspicious errors that could lead to 8007003. Unfortunately at this point the error code is too general to provide any clue.

|||

Hi... I reinstalled SQL 2005 and then it worked fine.. However I think it had to do something with the Encryption Keys... Gwired if I were you I′d delete those from the Reporting Services Configuration Manager, create new ones and then setup a Web Service Identity.

Have a nice day

/Erk

|||

I ran into the same problem, and discovered the issue had to do with the fact that I installed SQL Server 2005, SP1, SP2 and THEN installed Reporting Services. Because I had installed SP2 before Reporting Services, SP2 was not applied to Reporting Services.

The only other thing of note that happened was that when I tried to run the User Provisioning Tool after installing SP2 I got a 'object not set to an instance of an object error'. I didn't really think anything about it at the time, but I think it was only because I figured I'd find the problem down the road. I guess you can say that I did.

Could not setup Web Service Identity --> Error Code 80070003

Hello!
I′m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity

ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070003
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)

The default website contains an office sharepoint 2007 beta solution if that could cause the error..

Would be real grateful for some hints...

Regards

Erik B

Have you found a solution to this? I'm getting the same error after moving the Identities from one Website to another.|||

8007003 is ERROR_PATH_NOT_FOUND. Are you on Win2K3? I suspect this has something to do with ASP.NET setup. Either the WMI provider could not open the ASP.NET registry keys, or it could not find the machine.config file. Make sure you have run aspnet_regiis -i -enable.

If it doesn't work, I would start using regmon and filemon to looks for suspicious errors that could lead to 8007003. Unfortunately at this point the error code is too general to provide any clue.

|||

Hi... I reinstalled SQL 2005 and then it worked fine.. However I think it had to do something with the Encryption Keys... Gwired if I were you I′d delete those from the Reporting Services Configuration Manager, create new ones and then setup a Web Service Identity.

Have a nice day

/Erk

|||

I ran into the same problem, and discovered the issue had to do with the fact that I installed SQL Server 2005, SP1, SP2 and THEN installed Reporting Services. Because I had installed SP2 before Reporting Services, SP2 was not applied to Reporting Services.

The only other thing of note that happened was that when I tried to run the User Provisioning Tool after installing SP2 I got a 'object not set to an instance of an object error'. I didn't really think anything about it at the time, but I think it was only because I figured I'd find the problem down the road. I guess you can say that I did.

could not see the replication db under publication

Hello,
I am trying to setup a pull subscription through wizard.
I have a problem when I choose the publicaiton to which I
wnat to subscribe. I can see the publication server,
however, there is nothing under it when I expand the
publisher. Is that anyone knows what it is worng of it.
Any sugessation or help would be really appreciate.
Thanks,
On the Publisher, make sure that the Subscriber is enabled.
"ping" <ping.li@.nokia.com> wrote in message
news:1a9601c5421b$a6bc36b0$a601280a@.phx.gbl...
> Hello,
> I am trying to setup a pull subscription through wizard.
> I have a problem when I choose the publicaiton to which I
> wnat to subscribe. I can see the publication server,
> however, there is nothing under it when I expand the
> publisher. Is that anyone knows what it is worng of it.
> Any sugessation or help would be really appreciate.
> Thanks,
>

Could not locate file 'data01_log' in sysfiles

I need to shrink this log file, and setup some maintenance tasks, but I am
getting the above error. Any advice is greatly appreciated.
sp_helpdb output:
DATA01
1 e:\mssql\data\data01.mdf
PRIMARY 4102976 KB Unlimited 10% data only
DATA01_log
2 c:\logfiles\data01_log.ldf
NULL 327296 KB 460800 KB 10% log only
When I run shrinkfile I get this:
dbcc shrinkfile (data01_log,320)
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'data01_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi
If you do a SELECT * from sysfiles , what is the NAME returned ?
Are you then using the exact - case sensitive spelling?
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Rick Billingsley" <RickBillingsley@.discussions.microsoft.com> wrote in
message news:35983A24-DD51-43EA-A644-BA857A76F29B@.microsoft.com...
> I need to shrink this log file, and setup some maintenance tasks, but I am
> getting the above error. Any advice is greatly appreciated.
> sp_helpdb output:
> DATA01
> 1 e:\mssql\data\data01.mdf
>
> PRIMARY 4102976 KB Unlimited 10% data only
> DATA01_log
> 2
c:\logfiles\data01_log.ldf
>
> NULL 327296 KB 460800 KB 10% log only
> When I run shrinkfile I get this:
> dbcc shrinkfile (data01_log,320)
> Server: Msg 8985, Level 16, State 1, Line 1
> Could not locate file 'data01_log' in sysfiles.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||the select query returns the mastlog. This is not the log I am trying to
shrink/backup.
I believe the problem is that the log I want is on a different logical drive
(c vs. e) than the database.
I have tried running the shrinkfile with the exact case-sensitive spelling
with the same results.|||Change your database context to Data01. The select statement
indicates that you are trying to do this from the master
database. Shrinkfile applies to files in the current
database so you need to be in the appropriate database.
-Sue
On Mon, 23 Jan 2006 08:58:03 -0800, "Rick Billingsley"
<RickBillingsley@.discussions.microsoft.com> wrote:
>the select query returns the mastlog. This is not the log I am trying to
>shrink/backup.
>I believe the problem is that the log I want is on a different logical drive
>(c vs. e) than the database.
>I have tried running the shrinkfile with the exact case-sensitive spelling
>with the same results.

Could not get the data of the row from the OLE DB provider 'SQLOLE

I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...
You might want to post your actual T-SQL statements and some configuration
values.
Sincerely,
Anthony Thomas

"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:1E63096F-95DF-480E-9BFB-6B36BEE44901@.microsoft.com...
I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...

Could not get the data of the row from the OLE DB provider 'SQLOLE

I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE D
B error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...You might want to post your actual T-SQL statements and some configuration
values.
Sincerely,
Anthony Thomas
"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:1E63096F-95DF-480E-9BFB-6B36BEE44901@.microsoft.com...
I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE D
B error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...

Could not get the data of the row from the OLE DB provider 'SQLOLE

I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...You might want to post your actual T-SQL statements and some configuration
values.
Sincerely,
Anthony Thomas
"ChrisD" <ChrisD@.discussions.microsoft.com> wrote in message
news:1E63096F-95DF-480E-9BFB-6B36BEE44901@.microsoft.com...
I am running SQL2000 on two different machines. One is 2003 server, the
other is 2000 server. DTC is setup on the 2003 server. Both servers are
linked to one another. I am running several small jobs that simply execute
a
sp. I am consistantly getting the following error message:
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'.
[SQLSTATE 42000] (Error 7346) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e23].
[SQLSTATE 01000] (Error 7300). The step failed.
The sp that is running to cause this is error is one of two that consist of
Several varialbes that get their set values from select statements with
views back into Server B and some of them are setting values from the local
server A.
The error occurs after these are set, and when I try to do one of the two
following updates.
One is an update From Server A via a view into Server B. There are no date
values being passed or updated, but the view does include date fields.
The second update is all on the local server. Updating a source table flag.
Any help would be greatly appreciated.
Thanks...

Could not find stored procedure 'master..xp_jdbc_open'

Hi All,

I am using WebSphere 5.1 and try to setup Datasoruce connection in WebSphere

I have selected "Microsoft JDBC driver for MSSQLServer 2000" from the picklist in WebSphere Admin Console.

The 3 jar files below are referenced correctly as well:

${MSSQLSERVER_JDBC_DRIVER_PATH}/msbase.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/mssqlserver.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/msutil.jar

However when I run a testconnection, it fails with this error:

Test Connection failed for datasource celio on server server1 at node workstation_50 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'master..xp_jdbc_open'.. View JVM logs for further details. Null

My Datbase is MS SQL 2000 !

Is it a bug?

Any help is appreciated.

Thank you

Ramin

Hi there,

Perhaps this article could help with your problem:

http://www-1.ibm.com/support/docview.wss?uid=swg21168355

Hope that helps a bit, but sorry if it doesn't

Could not find stored procedure 'master..xp_jdbc_open'

Hi All,

I am using WebSphere 5.1 and try to setup Datasoruce connection in WebSphere

I have selected "Microsoft JDBC driver for MSSQLServer 2000" from the picklist in WebSphere Admin Console.

The 3 jar files below are referenced correctly as well:

${MSSQLSERVER_JDBC_DRIVER_PATH}/msbase.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/mssqlserver.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/msutil.jar

However when I run a testconnection, it fails with this error:

Test Connection failed for datasource celio on server server1 at node workstation_50 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'master..xp_jdbc_open'.. View JVM logs for further details. Null

My Datbase is MS SQL 2000 !

Is it a bug?

Any help is appreciated.

Thank you

Ramin

Hi there,

Perhaps this article could help with your problem:

http://www-1.ibm.com/support/docview.wss?uid=swg21168355

Hope that helps a bit, but sorry if it doesn't|||

Thank you

I have followed that reference, after applying the sqljdbc.dll and running th script, I have got this error when run the testconnection: I restarted WS and MSSQL Server both

Test Connection failed for datasource celio on server server1 at node RBONAKDA03 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the DLLs it references. Reason: 193(error not found).. View JVM logs for further details. null

|||Hi there,

Apologies for my late response.

Is the DLL registered on the database server? From things I have read, the DLL needs to be placed and registered on the server on which SQL Server runs.

Other than that, I can't think of anything. Sorry.
|||

There seems to be a slight confusion here.

It sounds like you are trying to configure Sql Server to use the Microsoft 2000 JDBC driver but then you are referencing the 2005 JDBC driver.

For a new application I would recommend that you use the 2005 Jdbc driver:

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

To set up distributed transaction support follow the instructions on the xa_install.sql file found ont the Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\xa directory (or sqljdbc_1.0\ directory for the RTW driver). To set up Websphere to work with the 2005 driver you can:

1) Add a new provider from the Providers menu under JDBC Providers
2) For path specify the path to your sqljdbc.jar
example : C:\Microsoft SQL Server 2005 JDBC
Driver\sqljdbc_1.1\enu\sqljdbc.jar
3) For implementation class name use the following.
com.microsoft.sqlserver.jdbc.SQLServerXADataSource

4) One you saved the provider choose the datasources menu and create a new
data source
5) You can use the generic data store helper class provided by Websphere AS
(com.ibm.websphere.rsadapter.GenericDataStoreHelper)

Could not find stored procedure 'master..xp_jdbc_open'

Hi All,

I am using WebSphere 5.1 and try to setup Datasoruce connection in WebSphere

I have selected "Microsoft JDBC driver for MSSQLServer 2000" from the picklist in WebSphere Admin Console.

The 3 jar files below are referenced correctly as well:

${MSSQLSERVER_JDBC_DRIVER_PATH}/msbase.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/mssqlserver.jar
${MSSQLSERVER_JDBC_DRIVER_PATH}/msutil.jar

However when I run a testconnection, it fails with this error:

Test Connection failed for datasource celio on server server1 at node workstation_50 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'master..xp_jdbc_open'.. View JVM logs for further details. Null

My Datbase is MS SQL 2000 !

Is it a bug?

Any help is appreciated.

Thank you

Ramin

Hi there,

Perhaps this article could help with your problem:

http://www-1.ibm.com/support/docview.wss?uid=swg21168355

Hope that helps a bit, but sorry if it doesn't|||

Thank you

I have followed that reference, after applying the sqljdbc.dll and running th script, I have got this error when run the testconnection: I restarted WS and MSSQL Server both

Test Connection failed for datasource celio on server server1 at node RBONAKDA03 with the following exception: java.lang.Exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the DLLs it references. Reason: 193(error not found).. View JVM logs for further details. null

|||Hi there,

Apologies for my late response.

Is the DLL registered on the database server? From things I have read, the DLL needs to be placed and registered on the server on which SQL Server runs.

Other than that, I can't think of anything. Sorry.
|||

There seems to be a slight confusion here.

It sounds like you are trying to configure Sql Server to use the Microsoft 2000 JDBC driver but then you are referencing the 2005 JDBC driver.

For a new application I would recommend that you use the 2005 Jdbc driver:

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

To set up distributed transaction support follow the instructions on the xa_install.sql file found ont the Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\xa directory (or sqljdbc_1.0\ directory for the RTW driver). To set up Websphere to work with the 2005 driver you can:

1) Add a new provider from the Providers menu under JDBC Providers
2) For path specify the path to your sqljdbc.jar
example : C:\Microsoft SQL Server 2005 JDBC
Driver\sqljdbc_1.1\enu\sqljdbc.jar
3) For implementation class name use the following.
com.microsoft.sqlserver.jdbc.SQLServerXADataSource

4) One you saved the provider choose the datasources menu and create a new
data source
5) You can use the generic data store helper class provided by Websphere AS
(com.ibm.websphere.rsadapter.GenericDataStoreHelper)

Could not establish trust relationship with remote server.

I am trying to setup SQL Server 2000 Reporting services with an SSL
connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting
Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I
have tried setting up Certificates from both and Stand-Alone and Enterprise
CA. I have tried several combinations of the Issue To name (server |
server.company | server.company.local) I have modified the
rsWebApplication.config and rsReportServer.config to match the certificateâ's
Issue to exactly. I have tried installs with both Domain Accounts and NT
AUTHORITY\SYSTEM logins. I have managed to get https://server/ReportServer
to work but I have had no luck with the report manager
https://server/reports. How do I fix this error?
ThankyouThe most likely reason this is failing is that your Report Server does not
trust the Root Certificate Authority that created the certificate you are
using.
If you go to https://machine/ReportServer and IE says "this certificate is
not valid are you sure you want to accept it?" then you will know that the
certificate is not 100% trusted and that is why you are receiving the error.
You will need to export the Trusted Root Authority certificate from the
issuing server and import it on the Report Server.
--
SQL Server Developer Support Engineer
"vbchewie" wrote:
> I am trying to setup SQL Server 2000 Reporting services with an SSL
> connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting
> Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I
> have tried setting up Certificates from both and Stand-Alone and Enterprise
> CA. I have tried several combinations of the Issue To name (server |
> server.company | server.company.local) I have modified the
> rsWebApplication.config and rsReportServer.config to match the certificateâ's
> Issue to exactly. I have tried installs with both Domain Accounts and NT
> AUTHORITY\SYSTEM logins. I have managed to get https://server/ReportServer
> to work but I have had no luck with the report manager
> https://server/reports. How do I fix this error?
> Thankyou
>|||I went to the stand alone root certificate authority and exported the
certificate for that server. I then imported it into both the SQL Server and
Reporting Server. Now when I type the FQDN
(https://machine.domain.local/Reports ) I no longer get a certificate error,
but I do still get â'The underlying connection was closed: Could not establish
trust relationship with remote server.â'
Is there something else I can do? Does it have to be an Enterprise Root
Certificate Authority in order to work?
Thank You.
"Chris Alton [MS]" wrote:
> The most likely reason this is failing is that your Report Server does not
> trust the Root Certificate Authority that created the certificate you are
> using.
> If you go to https://machine/ReportServer and IE says "this certificate is
> not valid are you sure you want to accept it?" then you will know that the
> certificate is not 100% trusted and that is why you are receiving the error.
> You will need to export the Trusted Root Authority certificate from the
> issuing server and import it on the Report Server.
> --
> SQL Server Developer Support Engineer
>
> "vbchewie" wrote:
> > I am trying to setup SQL Server 2000 Reporting services with an SSL
> > connection. The SQL Server (Windows Server 2003 SP2 Standard) and Reporting
> > Server (Windows Server 2003 R2 SP2 Standard) are on separate machines. I
> > have tried setting up Certificates from both and Stand-Alone and Enterprise
> > CA. I have tried several combinations of the Issue To name (server |
> > server.company | server.company.local) I have modified the
> > rsWebApplication.config and rsReportServer.config to match the certificateâ's
> > Issue to exactly. I have tried installs with both Domain Accounts and NT
> > AUTHORITY\SYSTEM logins. I have managed to get https://server/ReportServer
> > to work but I have had no luck with the report manager
> > https://server/reports. How do I fix this error?
> >
> > Thankyou
> >|||You also need to make sure that the SSL certificate you are using matches
the machine name you are accessing it by EXACTLY. You will also need to
make sure that in the Reporting Services configuration tool that you have
checked the "Require Secure Socket Layer (SSL) Connections" and put the
same name you are accessing the server by in the "Certificate Name" field.
So in your example you would put "machine.domain.local" in the Certificate
Field and the certificate should be issued to "machine.domain.local".
The easiest way to tell if the certificate is valid is to open up
https://machine.domain.local/ReportServer from the web server and if IE
complains about the certificate at all then it will not work.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.|||In RSReportServer.config I have my "SecureConnectionLevel" Value="3" and my
<UrlRoot>https://machine.domain.local/ReportServer</UrlRoot>
in RSWebApplication.config
I have
<ReportServerUrl>https://machine.domain.local/ReportServer</ReportServerUrl>
When I go to IIS on the Reporting Server and right click on Default Web Site
> Properties > Directory Security.
And click on 'View Certificate...' it says Issued to: machine.domain.local I
also checked the friendly name it also has machine.domain.local.
When I click 'Edit...' under 'Secure communications' both Require secure
channel(SSL) and Require 128-bit encryption are checked.
When I click 'Edit...' Under 'Authentication and access control' Enable
anonymous access is unchecked.
The same is true for my Virtual Directories 'Reports' and ReportServer'
When I go to https://machine/domain.local/Reports there are no certificate
errors. It goes straight though to a page that says
"The underlying connection was closed: Could not establish trust
relationship with remote server."
You mentioned Reporting Services Configuration Tool. This is Reporting
Services for SQL Server 2000. Is there a Reporting Services Configuration
Tool for this version? I thought that was for 2005.
Iâ'm not sure what I am missing. Any other ideas?
Thank you
"Chris Alton [MSFT]" wrote:
> You also need to make sure that the SSL certificate you are using matches
> the machine name you are accessing it by EXACTLY. You will also need to
> make sure that in the Reporting Services configuration tool that you have
> checked the "Require Secure Socket Layer (SSL) Connections" and put the
> same name you are accessing the server by in the "Certificate Name" field.
> So in your example you would put "machine.domain.local" in the Certificate
> Field and the certificate should be issued to "machine.domain.local".
> The easiest way to tell if the certificate is valid is to open up
> https://machine.domain.local/ReportServer from the web server and if IE
> complains about the certificate at all then it will not work.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I didn't know you were using SQL 2000. The only thing I can think of is
that you need to install the Root CA Certificate in the Certificate Store
of the Machine account. What account do you have the SRS Windows Service
and the IIS Application Pool running under?
If it is LocalSystem or Network Service you can install the certificate by
following these steps:
a. Click Start->Run
b. Type mmc and hit enter.
c. Click File->Add/Remove Snap-in
d. Click the "Add" button.
e. Select "Certificates" from the list
f. Click the "Add" button.
g. Click the "Computer Account" radio button.
h. Click "Next"
i. Make sure "Local Computer" is selected and click "Finish"
j. Click "Close"
k. Click "Ok"
l. Branch down on Certificates.
m. Right click Trusted Root Certification Authorities->All
Tasks->Import...
n. Click "Next" and browse to the certificate you are importing.
o. Click "Next" and make sure the "Place all certificates in the
following store" is selected and "Trusted Root Certification Authorities"
is listed in the "Certificate Store" box. If not browse to it by clicking
the "Browse" button.
p. Click "Next" and then click "Finish"
q. The Trusted Root Authority Certificate should now be imported and
available to web application/service.
Hopefully that should get it now.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.|||It works!!!
SRS is running under a domain\account. In order to get Kerberos to function
properly I used setspn to allow the domain\account to use the http service.
So I am not running under LocalSystem or Network Service.
I logged into the computer with the domain\account that is running both the
SRS Service and the ReportingServices Application Pool. I followed your
directions exactly with only one variation. Instead of picking "Computer
Account" at g. I chose "My Account". It looks like it is working now.
To recap for anyone else that runs into this issue:
SQL Server 2000 is on machine1
SharePoint and Reporting Services are on machine2 and are running under a
domain\account (very limited rights).
SSL Certificate was issued from a Stand-Alone Root Certificate Authority.
Thank you very much for your help,
"Chris Alton [MSFT]" wrote:
> I didn't know you were using SQL 2000. The only thing I can think of is
> that you need to install the Root CA Certificate in the Certificate Store
> of the Machine account. What account do you have the SRS Windows Service
> and the IIS Application Pool running under?
> If it is LocalSystem or Network Service you can install the certificate by
> following these steps:
> a. Click Start->Run
> b. Type mmc and hit enter.
> c. Click File->Add/Remove Snap-in
> d. Click the "Add" button.
> e. Select "Certificates" from the list
> f. Click the "Add" button.
> g. Click the "Computer Account" radio button.
> h. Click "Next"
> i. Make sure "Local Computer" is selected and click "Finish"
> j. Click "Close"
> k. Click "Ok"
> l. Branch down on Certificates.
> m. Right click Trusted Root Certification Authorities->All
> Tasks->Import...
> n. Click "Next" and browse to the certificate you are importing.
> o. Click "Next" and make sure the "Place all certificates in the
> following store" is selected and "Trusted Root Certification Authorities"
> is listed in the "Certificate Store" box. If not browse to it by clicking
> the "Browse" button.
> p. Click "Next" and then click "Finish"
> q. The Trusted Root Authority Certificate should now be imported and
> available to web application/service.
> Hopefully that should get it now.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||If the Windows Service and Application Pool all run under a domain account
then you really don't have to go through all those convoluted steps to
import the cert. That is only if you are using LocalSystem/Network Service
since those are considered the "Machine" account.
If you can log on to the account in an interactive session all you really
have to do to import the certificate is logon and then double click the
cer file and follow the prompts :)
Glad its working for you now though, those SSL issues with SRS can be quite
complicated sometimes.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Could not establish trust relationship with remote server.
> From: <vbchewie@.discussions.microsoft.com>
> Subject: RE: Could not establish trust relationship with remote server.
> Date: Tue, 2 Oct 2007 14:18:01 -0700
> It works!!!
> SRS is running under a domain\account. In order to get Kerberos to
function
> properly I used setspn to allow the domain\account to use the http
service.
> So I am not running under LocalSystem or Network Service.
> I logged into the computer with the domain\account that is running both
the
> SRS Service and the ReportingServices Application Pool. I followed your
> directions exactly with only one variation. Instead of picking "Computer
> Account" at g. I chose "My Account". It looks like it is working now.
> To recap for anyone else that runs into this issue:
> SQL Server 2000 is on machine1
> SharePoint and Reporting Services are on machine2 and are running under a
> domain\account (very limited rights).
> SSL Certificate was issued from a Stand-Alone Root Certificate Authority.
> Thank you very much for your help,
>
> "Chris Alton [MSFT]" wrote:
> > I didn't know you were using SQL 2000. The only thing I can think of is
> > that you need to install the Root CA Certificate in the Certificate
Store
> > of the Machine account. What account do you have the SRS Windows
Service
> > and the IIS Application Pool running under?
> >
> > If it is LocalSystem or Network Service you can install the certificate
by
> > following these steps:
> >
> > a. Click Start->Run
> > b. Type mmc and hit enter.
> > c. Click File->Add/Remove Snap-in
> > d. Click the "Add" button.
> > e. Select "Certificates" from the list
> > f. Click the "Add" button.
> > g. Click the "Computer Account" radio button.
> > h. Click "Next"
> > i. Make sure "Local Computer" is selected and click "Finish"
> > j. Click "Close"
> > k. Click "Ok"
> > l. Branch down on Certificates.
> > m. Right click Trusted Root Certification Authorities->All
> > Tasks->Import...
> > n. Click "Next" and browse to the certificate you are importing.
> > o. Click "Next" and make sure the "Place all certificates in the
> > following store" is selected and "Trusted Root Certification
Authorities"
> > is listed in the "Certificate Store" box. If not browse to it by
clicking
> > the "Browse" button.
> > p. Click "Next" and then click "Finish"
> > q. The Trusted Root Authority Certificate should now be imported
and
> > available to web application/service.
> >
> > Hopefully that should get it now.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
>

could not deliver the snapshot to the subscriber

We have a customer w/ merge replication setup and has been functioning.
Their server's were hacked. We have spent 3 days on the phone w/ MS and
everything is working except for 1 client. When it is trying to deliver
the snapshot it is getting an error:
The process could not bulk copy into table {table name}
Violation of PRIMARY KEY constraint. Cannot insert duplicate key.
I understand what that means, so on that table (it is not a necessary
table, just a log), I deleted all records from that table and the
delivery got past that table to the next table in the list and displayed
the same message.
What can I do on the client to get this to work? I can't delete the info
in the second table because it is much more required and I don't want to
have it delete the same data on the main server. I know it is trying to
insert a new record that has the same primary key, but is it trying to
do that because the server record has one guid and the client has a
different guid (I don't really know how the guid's work in replication -
if the record has the same guid on all servers).
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I would use a tool like RedGate's datacompare to reconcile the data to the
Publisher. You'll need to modify the scripts somewhat if you have PK issues
and then I'd reinitialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I know they are duplicate keys. The client has a record w/ the key as 2
(identity column), so does the distributor. The snapshot is trying to
send that record down - why, and how can I make so the snapshot doesn't
try to send it down?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi.
In the publisher, mark that identity column which is acting as primary key
to NOT FOR REPLICATION in the design mode of that table.
in this case, the identity will be inserted independently at the subscriber
avoiding primary key errors.
"Darin" wrote:

> I know they are duplicate keys. The client has a record w/ the key as 2
> (identity column), so does the distributor. The snapshot is trying to
> send that record down - why, and how can I make so the snapshot doesn't
> try to send it down?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||The table on both the subscriber and the distrubtor are setup that way.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Can you outline your situation a little more. Usually a snapshot of the
publisher's data is sent from the publisher to the subscriber. Initially
there is a drop table executed on the subscriber. There are variations - one
of which is a nosync initialization, where all the required info already
exists on the subscriber and the publisher just sends the metadata and
system stored procedures/triggers required to run replication. There are
other possibilities where the table and its data remain and the new data is
appended. Of these, which is your requirement, or is your requirement
something quite different?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com