Connect to a DSN for data

I have an AS400 system and I created a DSN to see the data on my workstation. The connection works fine. I created the samne connection on the Server that has SQL 2005 installed. What I need to do is to populate the SQL database with tables from the AS400 table but I am having trouble connecting a DSN to the SQL database.

Can anyone offer some advice or a website that could walk me through this?

Hello,

I think you should be using Integration Services for this, Reporting Services does not do any type of data transfer. IS will be able to handle this easily, one connection will be your SQL Server, the other will be a ODBC or OLE DB connection using your DSN.

Hope this helps.

Jarret

Connect Problems with Sql Server 05 Express

Hallo,
Maybe some hints are avialable.
System: Vista Business
SQL-Server: 2005 Express SP2
UserStatus: Admin
Connect via Management Studio 05
Error: 5120 bzw. 5123 - access declined.
Datenfile is local with full access
any ideas?
regards
akko
What does the connectstring look like? Is the instance running? Are you
accessing a local instance or one on the network?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"akko" <info@.akko.at> wrote in message
news:B44FF7D8-BFA1-4556-965F-AB9E81DC7452@.microsoft.com...
> Hallo,
> Maybe some hints are avialable.
> System: Vista Business
> SQL-Server: 2005 Express SP2
> UserStatus: Admin
> Connect via Management Studio 05
> Error: 5120 bzw. 5123 - access declined.
> Datenfile is local with full access
> any ideas?
> regards akko
|||problem fixed, caused by sqlserver-service had not enaugh rights on folder.
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schrieb im Newsbeitrag
news:uMHrs5EjHHA.392@.TK2MSFTNGP06.phx.gbl...
> What does the connectstring look like? Is the instance running? Are you
> accessing a local instance or one on the network?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "akko" <info@.akko.at> wrote in message
> news:B44FF7D8-BFA1-4556-965F-AB9E81DC7452@.microsoft.com...
>

Connect Problems with Sql Server 05 Express

Hallo,
Maybe some hints are avialable.
System: Vista Business
SQL-Server: 2005 Express SP2
UserStatus: Admin
Connect via Management Studio 05
Error: 5120 bzw. 5123 - access declined.
Datenfile is local with full access
any ideas?
regards
akkoWhat does the connectstring look like? Is the instance running? Are you
accessing a local instance or one on the network?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"akko" <info@.akko.at> wrote in message
news:B44FF7D8-BFA1-4556-965F-AB9E81DC7452@.microsoft.com...
> Hallo,
> Maybe some hints are avialable.
> System: Vista Business
> SQL-Server: 2005 Express SP2
> UserStatus: Admin
> Connect via Management Studio 05
> Error: 5120 bzw. 5123 - access declined.
> Datenfile is local with full access
> any ideas?
> regards akko|||problem fixed, caused by sqlserver-service had not enaugh rights on folder.
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schrieb im Newsbeitrag
news:uMHrs5EjHHA.392@.TK2MSFTNGP06.phx.gbl...
> What does the connectstring look like? Is the instance running? Are you
> accessing a local instance or one on the network?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "akko" <info@.akko.at> wrote in message
> news:B44FF7D8-BFA1-4556-965F-AB9E81DC7452@.microsoft.com...
>

connect by DSN

Hi,

how can i connect MSSQL through System DSN. i know how to create DSN. but i don't know how to code the connection string.

regards,

SeeConnectionStrings.com.

Ryan

Conncurrent access to the same stored procedure

Hi all DB guys, I am curious of the performance of the database system on
concurrent access to a single stored procedure. What I mean is, when I have
a
stored procedure that is access by multiple connections/clients, what is the
impact on the system?
Apart from that, if I am not mistaken, we can set the isolation level to
prevent concurrent access to the same stored procedure, is it? So, this is
actually to prevent concurrent read/write to the same data
(locking/blocking), or is it because of any other problem?
Would really appreciate if you people can share your experience or thought.
Thanks
EugeneHi Eugene
Multiple users can execute the same proc at the same time. Any performance
impact would be because of the data being accessed, not because the commands
were in a procedure. If multiple processes are trying to access the same
data, there are always possible blocking issues to be aware of, whether or
not you are going through a stored procedure.
Your isolation level doesn't control anything about stored procedures. It
only controls how long shared locks are held, no matter how those locks are
acquired. Have you read everything about isolation levels, locking and
blocking in the Books Online?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:C08DF3A2-52F7-4471-9284-7C9089E1292A@.microsoft.com...
> Hi all DB guys, I am curious of the performance of the database system on
> concurrent access to a single stored procedure. What I mean is, when I
> have a
> stored procedure that is access by multiple connections/clients, what is
> the
> impact on the system?
> Apart from that, if I am not mistaken, we can set the isolation level to
> prevent concurrent access to the same stored procedure, is it? So, this is
> actually to prevent concurrent read/write to the same data
> (locking/blocking), or is it because of any other problem?
> Would really appreciate if you people can share your experience or
> thought.
> Thanks
> Eugene
>|||Your isolation level will manage how data is read or written to across
connections and clients. You need to worry/plan for this.
There are other things that can affect the performance of your stored
procedure, some of these include your stored procedure recompilations,
indices, table design, etc.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:C08DF3A2-52F7-4471-9284-7C9089E1292A@.microsoft.com...
> Hi all DB guys, I am curious of the performance of the database system on
> concurrent access to a single stored procedure. What I mean is, when I
> have a
> stored procedure that is access by multiple connections/clients, what is
> the
> impact on the system?
> Apart from that, if I am not mistaken, we can set the isolation level to
> prevent concurrent access to the same stored procedure, is it? So, this is
> actually to prevent concurrent read/write to the same data
> (locking/blocking), or is it because of any other problem?
> Would really appreciate if you people can share your experience or
> thought.
> Thanks
> Eugene|||Hi Kalen, Kingsley
thanks for the explanation. Kalen, I didn't read up clearly on the Isolation
level, I asked does Isolation prevent concurrent access to the stored
procedure because I am told so by my friend. To get myself clear, since
isolation level does not prevent concurrent access to the same sp, so does
Isolation prevent concurrent access to the same Transaction block code in th
e
same sp?
As I said with Isolation level, I am told concurrent access to the same sp
would be terrible to the performance - which you two have explained that it
is not a concern, the main concern is the underlying data, thanks a lot :)
I guess I can better explain to my friends next time such discussion pops
up. Thanks.
"Kalen Delaney" wrote:

> Hi Eugene
> Multiple users can execute the same proc at the same time. Any performance
> impact would be because of the data being accessed, not because the comman
ds
> were in a procedure. If multiple processes are trying to access the same
> data, there are always possible blocking issues to be aware of, whether or
> not you are going through a stored procedure.
> Your isolation level doesn't control anything about stored procedures. It
> only controls how long shared locks are held, no matter how those locks ar
e
> acquired. Have you read everything about isolation levels, locking and
> blocking in the Books Online?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Eugene" <Eugene@.discussions.microsoft.com> wrote in message
> news:C08DF3A2-52F7-4471-9284-7C9089E1292A@.microsoft.com...
>
>

Confysed by the roles

DaI have an application that uses the Extended Stored Procedures sp_OA...
I thought that I read that members of the System Administrators Role could
access the Extended Stored Procedures in the Master Databasewithout
specifically granting execute permissions on each procedure. When the sorce
code executes an error stating that "EXECUTE permission denied on
'sp_OACreate', database 'master', owner 'dbo'
I then granted the user the Execute permissions and had the same error
message
I then Granted the Execute Permissions to Public and it worked
I then removed the permissions from the user and it still worked.
So in the end just adding the Public made this work BUT to reduce my
security risks I would prefer not to give Public the rights to execute the
Extended Stored Procedures.
Can this be done? if so how?Yes, it's possible. It sounds like an issue somewhere with
the login - maybe it's executing under a different login
than the one being granted permissions. You can run a
profiler trace and include login, user info to check this.
-Sue
On Tue, 16 Aug 2005 09:31:09 -0700, "Jim Abel"
<JimAbel@.discussions.microsoft.com> wrote:

>DaI have an application that uses the Extended Stored Procedures sp_OA...
>I thought that I read that members of the System Administrators Role could
>access the Extended Stored Procedures in the Master Databasewithout
>specifically granting execute permissions on each procedure. When the sorc
e
>code executes an error stating that "EXECUTE permission denied on
>'sp_OACreate', database 'master', owner 'dbo'
>I then granted the user the Execute permissions and had the same error
>message
>I then Granted the Execute Permissions to Public and it worked
>I then removed the permissions from the user and it still worked.
>So in the end just adding the Public made this work BUT to reduce my
>security risks I would prefer not to give Public the rights to execute the
>Extended Stored Procedures.
>Can this be done? if so how?

Conflicts in Updateable Subscriptions

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.

There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.

Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,

-chiraj.

Hi,

Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.

Peng

|||

Hi Peng,

Thank you for responding.

I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.

Any ideas?

Thanks.

|||

I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :

if (@.is_debug = 0)

begin

select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'

,@.dbname = db_name()

exec @.retcode = sys.xp_execresultset @.cmd, @.dbname

if (@.@.error != 0 or @.retcode != 0)

begin

raiserror(21465, 16, 1, 'xp_execresultset')

return (1)

end

end

else

I wonder why the message for #21465 is not installed.

I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.

I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?

-chiraj.

Conflicts in Updateable Subscriptions

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.

There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.

Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,

-chiraj.

Hi,

Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.

Peng

|||

Hi Peng,

Thank you for responding.

I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.

Any ideas?

Thanks.

|||

I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :

if (@.is_debug = 0)

begin

select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'

,@.dbname = db_name()

exec @.retcode = sys.xp_execresultset @.cmd, @.dbname

if (@.@.error != 0 or @.retcode != 0)

begin

raiserror(21465, 16, 1, 'xp_execresultset')

return (1)

end

end

else

I wonder why the message for #21465 is not installed.

I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.

I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?

-chiraj.

Conflicts in Updateable Subscriptions

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.

There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.

Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,

-chiraj.

Hi,

Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.

Peng

|||

Hi Peng,

Thank you for responding.

I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.

Any ideas?

Thanks.

|||

I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :

if (@.is_debug = 0)

begin

select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'

,@.dbname = db_name()

exec @.retcode = sys.xp_execresultset @.cmd, @.dbname

if (@.@.error != 0 or @.retcode != 0)

begin

raiserror(21465, 16, 1, 'xp_execresultset')

return (1)

end

end

else

I wonder why the message for #21465 is not installed.

I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.

I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?

-chiraj.

Conflict between BOL and README - Which one is correct?!

BOL:
Microsoft recommends that you use the built-in account (NT AUTHORITY/SYSTEM)
when installing Reporting Services on a Windows 2000 server. If you use a
domain user account (even one that has local administrator privileges), it
will limit your choices on how to configure a data source connection for a
report as well as introduce new configuration steps that are not otherwise
required.
README-EN:
1.10 Domain user account requirement for subscriptions and ReportServer
Windows service running under Windows 2000 or Windows XP
If the ReportServer Windows service is running under Windows 2000 or Windows
XP, you must configure the service to run under a domain account. If you use
an account that does not have sufficient domain permissions, subscription
delivery will fail when the report server attempts to deliver reports to
domain user accounts. The following error occurs when report delivery fails
due to authorization and domain access issues: Failure sending mail: The
Report Server has encountered a configuration error.
I tried configuring the server using the SYSTEM account, however when
subscription SQL agent jobs are run I get the error listed in README 1.10.
When I reinstall the server and select a Domain Account for the ReportServer
service, I have to use a SQL account to connect. When I try this
configuration, the agent job runs and the Event table is populated, but
nothing else happens. The status shown in the Subscription is that the job
has not run. I'm at a loss. How are the accounts supposed to be configured?
Server is 2000 Advanced server with SQL 2000 Ent Ed.Hi,
I am having exactly the same problem as you report here. Did you get a
resolution to teh problem? If so please let me know!
Thanks
Laurence Neville
"Lifelongstudent" <dveit@.comcast.net> wrote in message
news:uRC9KxVZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> BOL:
> Microsoft recommends that you use the built-in account (NT
AUTHORITY/SYSTEM)
> when installing Reporting Services on a Windows 2000 server. If you use a
> domain user account (even one that has local administrator privileges), it
> will limit your choices on how to configure a data source connection for a
> report as well as introduce new configuration steps that are not otherwise
> required.
> README-EN:
> 1.10 Domain user account requirement for subscriptions and ReportServer
> Windows service running under Windows 2000 or Windows XP
> If the ReportServer Windows service is running under Windows 2000 or
Windows
> XP, you must configure the service to run under a domain account. If you
use
> an account that does not have sufficient domain permissions, subscription
> delivery will fail when the report server attempts to deliver reports to
> domain user accounts. The following error occurs when report delivery
fails
> due to authorization and domain access issues: Failure sending mail: The
> Report Server has encountered a configuration error.
> I tried configuring the server using the SYSTEM account, however when
> subscription SQL agent jobs are run I get the error listed in README 1.10.
> When I reinstall the server and select a Domain Account for the
ReportServer
> service, I have to use a SQL account to connect. When I try this
> configuration, the agent job runs and the Event table is populated, but
> nothing else happens. The status shown in the Subscription is that the job
> has not run. I'm at a loss. How are the accounts supposed to be
configured?
> Server is 2000 Advanced server with SQL 2000 Ent Ed.
>

configuring reporting services (to work for SCOM2007)

Hello-
I am having a bit of an issue configuring reporting services so that I can
install the System Center Operations Manager 2007 Reporting feature.
On our SQL server we have SQL2005 SP2 installed with the operations manager
database installed and working. I installed SQL reporting services on the
server that runs Operations Manager (OM) which is required to install OM
Reporting. I am however having an issue configuring reporting services.
I go into the reporting services configuration and there are 3 ares which
have red X's, Report Manager Virtual Directory, Web Service Identity and
Database Setup.
First thing I do is set the virtual directory, I click new and it creates
the new virtual directory in the default website however the red X never
turns to a green checkmark.
Next I go to web service identity and create a new app pool called reportsvc
and click apply and get an error saying "There was an error setting the
identity for the web service" If i set it to DefaultAppPool I get the same
thing.
then create the reporting database in the sql2005 server instance which
seems to go fine untill I go to click apply then I get an error saying that
it could not apply it because a virtual directory must be created first.
If i go into inetmgr the virtual directory was created under the default
website but it does not register in reporting servcies for some reason.
Can anyone help!? Much thanks in advance!!!
/EhrenMake sure you have asp.net installed,
run %WINNT%\Microsoft.NET\Framework\%framework version%\aspnet_regiis.exe -i.
"Ehren" wrote:
> Hello-
> I am having a bit of an issue configuring reporting services so that I can
> install the System Center Operations Manager 2007 Reporting feature.
> On our SQL server we have SQL2005 SP2 installed with the operations manager
> database installed and working. I installed SQL reporting services on the
> server that runs Operations Manager (OM) which is required to install OM
> Reporting. I am however having an issue configuring reporting services.
> I go into the reporting services configuration and there are 3 ares which
> have red X's, Report Manager Virtual Directory, Web Service Identity and
> Database Setup.
> First thing I do is set the virtual directory, I click new and it creates
> the new virtual directory in the default website however the red X never
> turns to a green checkmark.
> Next I go to web service identity and create a new app pool called reportsvc
> and click apply and get an error saying "There was an error setting the
> identity for the web service" If i set it to DefaultAppPool I get the same
> thing.
> then create the reporting database in the sql2005 server instance which
> seems to go fine untill I go to click apply then I get an error saying that
> it could not apply it because a virtual directory must be created first.
> If i go into inetmgr the virtual directory was created under the default
> website but it does not register in reporting servcies for some reason.
> Can anyone help!? Much thanks in advance!!!
> /Ehren|||Hi, thanks for your suggestion. I verified that ASP.NET is installed for the
default website and is using version 2.0.50727. I tried switching to v1.xxxx
to see if that would make a difference and it did not.
I also created a new website called "reports" on port 1024 to see if that
would make a difference and then clicked the new button again and told it to
create the reports virtual directory in the reports website. It did add the
virtual directory (as it did in the default site) however it never registers
as "OK" (with the green checkmark)
When i click the "report manager virtual directory" item on the left nav bar
it shows Name: Reports / and Website: Reports (or Default Website) in greyed
out text boxes.
Any other ideas as to why this might not be working?
Thanks
"Alien2_51" wrote:
> Make sure you have asp.net installed,
> run %WINNT%\Microsoft.NET\Framework\%framework version%\aspnet_regiis.exe -i.
> "Ehren" wrote:
> > Hello-
> >
> > I am having a bit of an issue configuring reporting services so that I can
> > install the System Center Operations Manager 2007 Reporting feature.
> >
> > On our SQL server we have SQL2005 SP2 installed with the operations manager
> > database installed and working. I installed SQL reporting services on the
> > server that runs Operations Manager (OM) which is required to install OM
> > Reporting. I am however having an issue configuring reporting services.
> >
> > I go into the reporting services configuration and there are 3 ares which
> > have red X's, Report Manager Virtual Directory, Web Service Identity and
> > Database Setup.
> >
> > First thing I do is set the virtual directory, I click new and it creates
> > the new virtual directory in the default website however the red X never
> > turns to a green checkmark.
> >
> > Next I go to web service identity and create a new app pool called reportsvc
> > and click apply and get an error saying "There was an error setting the
> > identity for the web service" If i set it to DefaultAppPool I get the same
> > thing.
> >
> > then create the reporting database in the sql2005 server instance which
> > seems to go fine untill I go to click apply then I get an error saying that
> > it could not apply it because a virtual directory must be created first.
> >
> > If i go into inetmgr the virtual directory was created under the default
> > website but it does not register in reporting servcies for some reason.
> >
> > Can anyone help!? Much thanks in advance!!!
> > /Ehren

Configuring merge replication for side-by-side SQL Server 2000 and 2005

I am trying to migrate from my current system, where I do merge replication from Windows Mobile devices running SQL Server CE 2.0 to a central database running SQL Server 2000 sp3a. I want eventually to move to a system running SQL Server 2005 CE replicating to a SQL Server 2005 back-end. But the transition will need to be gradual, and I may have to support both systems for a while until I can convert all clients from the old system to the new. I also need to do thorough testing.

So ... I'm trying to set up a test environment giving me the maximum possible flexibility to do my testing. Ideally, I'd like to set up SQL Server 2000 and SQL Server 2005 on a side-by-side basis, in a manner that would potentially allow mobile devices running both SQL Server CE 2.0 and SQL Server 2005 CE to sync with either back-end server.

Can someone provide me with guidance as what is possible to set up here? I know that SQL Server 2000 and 2005 can be installed side-by-side on the same server. It also appears that you can set up SQL Server 2000 so that EITHER SQL Server CE 2.0 OR SQL Server 2005 CE can sync with SQL Server 2000 (see www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx), but I don't know if it's possible for BOTH SQL Server CE 2.0 AND SQL Server 2005 CE to sync to the same SQL Server 2000. As for SQL Server 2005 ... it appears to be possible to set up SQL Server 2005 so that BOTH SQL Server CE 2.0 devices AND SQL Server 2005 CE devices can sync to the same SQL Server 2005 (see web page cited above). However, I don't know if it's possible to set up a SQL Server 2005 server installation in this manner while at the same time having a side-by-side SQL Server 2000 installation supporting any level of mobile merge replication.

HELP!!!!

The following Technet document describes side-by-side solutions for SQL CE 2.0 and SQL Mobile 2005:

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ceupgrade.mspx

Hope this will assist.

|||

Erik -

That's a very interesting article, and I appreciate the reference, but it doesn't answer my questions, as it doesn't discuss side-by-side server installations of SQL Server 2000 and 2005. There is a brief discussion of the need to set up separate virtual directories on the server for SQL Server CE 2.0 and SQL Server 2005 CE, but that's about it for the server discussion.

The article at http://www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx contains some good information, but still does not address the questions I detailed in my original post.

Larry

|||

Larry -

Installing SQL Server 2005 as a named instance on the same server will allow you to do what you are trying to acheive.

Virtual PC would also be able to assist in solving your problem.

Hope this assists.

|||

Erik -

No, sorry, but your reply does not address my questions. Let me try to be more specific here.

Yes, I know that it's possible to set up SQL Server 2000 and 2005 side-by-side -- I've already done this. What I'm trying to figure out is: with SQL Server 2000 and 2005 set up side-by-side on the same server computer, how do you do the setup to maximize the number of compact-mobile editions of SQL Server that can be synched to each server version of SQL Server?

This is not an academic question. I'm trying to plan for a conversion of my existing client base, currently using SQL Server 2000 and SQL Server CE 2.0, to a configuration that eventually would use SQL Server 2005 and SQL Server CE 2005. The conversion needs to be tested, and may need to occur in stages. So, for example, let's say that (1) I have clients using a database on SQL Server 2000, and that these clients have a version of my Pocket PC software based on the SQL Server CE 2.0 platform, and (2) I create a version of the same server database and install it on SQL Server 2005. Would it be possible for these clients to switch over and sync to the SQL Server 2005 database (assuming, of course, that we make certain changes to the settings on the Pocket PCs -- for example, so that they point to a different IIS virtual directory and refer to the named instance of the SQL Server 2005 installation), without having to tear down my SQL Server 2000 installation and without affecting the ability of OTHER clients to continue to sync to SQL Server 2000? Then, once I know this is working, can I then implement a version of my Pocket PC software based on SQL Server CE 2005, and (while this new version is being deployed) have some of my clients sync to SQL Server 2005 using the old (SQL Server CE 2.0) mobile software, while others sync using the new (SQL Server CE 2005) software? In other words, can I support three different sync scenarios on the same server at the same time (SQL Server CE 2.0 to SQL Server 2000, SQL Server CE 2.0 to SQL Server 2005 and SQL Server CE 2005 to SQL Server 2005)?

Alternatively, the better way to go might be to first switch the database platform on the Pocket PCs, and later switch the back-end SQL Server from 2000 to 2005. I won't specify the details here -- presumably you have a good idea of the steps I would take to test this out and then implement the change. The only important point is that this introduces a fourth possible sync scenario -- synching from SQL Server CE 2005 to SQL Server 2000.

To clarify one thing: I'm not trying to figure out how to set up a single DATABASE so that it allows syncs from both SQL Server CE 2.0 and SQL Server 2005 CE. I'm perfectly willing to set up multiple databases and to test a single sync scenario on each. This will mimic what I hope to do in production, which is to migrate a client database from SQL Server 2000 to SQL Server 2005.

The main point here is, I need to be able to set up the most flexible possible test environment to figure out how to make this transition. Ideally, I'd like to be able to support all four sync scenarios on the same server. While I know that's it is possible to set up ANY of these syncs, I don't if it's possible to set up ALL of them on the same server. Also, from the little bit I know, it appears to be important to complete the set-up steps in the right order, in order to permit multiple possible sync scenarios.

Does this make my question any clearer? I hope so! And thanks for your help.

Larry

|||

OK, Larry, thank for your clarification.

Yes, it is possible to do what you want, it would probably be a good idea to set up 4 virtual folders, 2 with sqlcesa20.dll and 2 with sqlcesa30.dll, each pointing to either the SQL Server 2000 or SQL Server 2005 repldata share.

I would, however recommend against the SQL CE 2.0 to SQL Server 2005 scenario, as this will prevent you from taking advantage of the advanced features of merge replication in 2005, like download only tables and pre-generated snapshots, as you 2005 publication then will have to be backwards compatible.

I would upgrade the device app to SQL 2005 and at the same time point it to a 2005 compatible publication. Remember to install SQL Server 2005 SP2 (or as close as you can get, ie the latest hotfix rollup)

|||

Erik -

Thanks for that last post. Good guidance and advice.

Upgrading my existing client apps is not easy to do. It's difficult enough just to collect the devices to do the upgrade. If the upgrades take place over several days, then I have to figure out how to simultaneously support the old app and the new app until all upgrades are complete.

From my research, it may be important what order certain components are installed on a single server so that all components work properly. For example, it may be advisable to install the SQL Server 2000 replication components (for example, sqlce20sql2ksp4.exe), then the .NET 2.0 Framework, then the SQL Server 2005 Mobile/Compact Edition Server Tools (for example, sqlce30setupxx.msi). Not sure. I plan to spend the weekend trying to get this set up.

Before receiving your last post, I hadn't even considered trying to set up a single database with a backwards-compatible merge publication! I was planning to set up multiple databases in SQL Server 2000 and SQL Server 2005, with each database supporting a single type of mobile client replication. That's still my plan.

It would have been nice if Microsoft had provided us with more guidance on how to make this transition. There are bits and pieces of information out there, but nothing like a complete transiiton plan.

Larry

Configuring merge replication for side-by-side SQL Server 2000 and 2005

I am trying to migrate from my current system, where I do merge replication from Windows Mobile devices running SQL Server CE 2.0 to a central database running SQL Server 2000 sp3a. I want eventually to move to a system running SQL Server 2005 CE replicating to a SQL Server 2005 back-end. But the transition will need to be gradual, and I may have to support both systems for a while until I can convert all clients from the old system to the new. I also need to do thorough testing.

So ... I'm trying to set up a test environment giving me the maximum possible flexibility to do my testing. Ideally, I'd like to set up SQL Server 2000 and SQL Server 2005 on a side-by-side basis, in a manner that would potentially allow mobile devices running both SQL Server CE 2.0 and SQL Server 2005 CE to sync with either back-end server.

Can someone provide me with guidance as what is possible to set up here? I know that SQL Server 2000 and 2005 can be installed side-by-side on the same server. It also appears that you can set up SQL Server 2000 so that EITHER SQL Server CE 2.0 OR SQL Server 2005 CE can sync with SQL Server 2000 (see www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx), but I don't know if it's possible for BOTH SQL Server CE 2.0 AND SQL Server 2005 CE to sync to the same SQL Server 2000. As for SQL Server 2005 ... it appears to be possible to set up SQL Server 2005 so that BOTH SQL Server CE 2.0 devices AND SQL Server 2005 CE devices can sync to the same SQL Server 2005 (see web page cited above). However, I don't know if it's possible to set up a SQL Server 2005 server installation in this manner while at the same time having a side-by-side SQL Server 2000 installation supporting any level of mobile merge replication.

HELP!!!!

The following Technet document describes side-by-side solutions for SQL CE 2.0 and SQL Mobile 2005:

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ceupgrade.mspx

Hope this will assist.

|||

Erik -

That's a very interesting article, and I appreciate the reference, but it doesn't answer my questions, as it doesn't discuss side-by-side server installations of SQL Server 2000 and 2005. There is a brief discussion of the need to set up separate virtual directories on the server for SQL Server CE 2.0 and SQL Server 2005 CE, but that's about it for the server discussion.

The article at http://www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx contains some good information, but still does not address the questions I detailed in my original post.

Larry

|||

Larry -

Installing SQL Server 2005 as a named instance on the same server will allow you to do what you are trying to acheive.

Virtual PC would also be able to assist in solving your problem.

Hope this assists.

|||

Erik -

No, sorry, but your reply does not address my questions. Let me try to be more specific here.

Yes, I know that it's possible to set up SQL Server 2000 and 2005 side-by-side -- I've already done this. What I'm trying to figure out is: with SQL Server 2000 and 2005 set up side-by-side on the same server computer, how do you do the setup to maximize the number of compact-mobile editions of SQL Server that can be synched to each server version of SQL Server?

This is not an academic question. I'm trying to plan for a conversion of my existing client base, currently using SQL Server 2000 and SQL Server CE 2.0, to a configuration that eventually would use SQL Server 2005 and SQL Server CE 2005. The conversion needs to be tested, and may need to occur in stages. So, for example, let's say that (1) I have clients using a database on SQL Server 2000, and that these clients have a version of my Pocket PC software based on the SQL Server CE 2.0 platform, and (2) I create a version of the same server database and install it on SQL Server 2005. Would it be possible for these clients to switch over and sync to the SQL Server 2005 database (assuming, of course, that we make certain changes to the settings on the Pocket PCs -- for example, so that they point to a different IIS virtual directory and refer to the named instance of the SQL Server 2005 installation), without having to tear down my SQL Server 2000 installation and without affecting the ability of OTHER clients to continue to sync to SQL Server 2000? Then, once I know this is working, can I then implement a version of my Pocket PC software based on SQL Server CE 2005, and (while this new version is being deployed) have some of my clients sync to SQL Server 2005 using the old (SQL Server CE 2.0) mobile software, while others sync using the new (SQL Server CE 2005) software? In other words, can I support three different sync scenarios on the same server at the same time (SQL Server CE 2.0 to SQL Server 2000, SQL Server CE 2.0 to SQL Server 2005 and SQL Server CE 2005 to SQL Server 2005)?

Alternatively, the better way to go might be to first switch the database platform on the Pocket PCs, and later switch the back-end SQL Server from 2000 to 2005. I won't specify the details here -- presumably you have a good idea of the steps I would take to test this out and then implement the change. The only important point is that this introduces a fourth possible sync scenario -- synching from SQL Server CE 2005 to SQL Server 2000.

To clarify one thing: I'm not trying to figure out how to set up a single DATABASE so that it allows syncs from both SQL Server CE 2.0 and SQL Server 2005 CE. I'm perfectly willing to set up multiple databases and to test a single sync scenario on each. This will mimic what I hope to do in production, which is to migrate a client database from SQL Server 2000 to SQL Server 2005.

The main point here is, I need to be able to set up the most flexible possible test environment to figure out how to make this transition. Ideally, I'd like to be able to support all four sync scenarios on the same server. While I know that's it is possible to set up ANY of these syncs, I don't if it's possible to set up ALL of them on the same server. Also, from the little bit I know, it appears to be important to complete the set-up steps in the right order, in order to permit multiple possible sync scenarios.

Does this make my question any clearer? I hope so! And thanks for your help.

Larry

|||

OK, Larry, thank for your clarification.

Yes, it is possible to do what you want, it would probably be a good idea to set up 4 virtual folders, 2 with sqlcesa20.dll and 2 with sqlcesa30.dll, each pointing to either the SQL Server 2000 or SQL Server 2005 repldata share.

I would, however recommend against the SQL CE 2.0 to SQL Server 2005 scenario, as this will prevent you from taking advantage of the advanced features of merge replication in 2005, like download only tables and pre-generated snapshots, as you 2005 publication then will have to be backwards compatible.

I would upgrade the device app to SQL 2005 and at the same time point it to a 2005 compatible publication. Remember to install SQL Server 2005 SP2 (or as close as you can get, ie the latest hotfix rollup)

|||

Erik -

Thanks for that last post. Good guidance and advice.

Upgrading my existing client apps is not easy to do. It's difficult enough just to collect the devices to do the upgrade. If the upgrades take place over several days, then I have to figure out how to simultaneously support the old app and the new app until all upgrades are complete.

From my research, it may be important what order certain components are installed on a single server so that all components work properly. For example, it may be advisable to install the SQL Server 2000 replication components (for example, sqlce20sql2ksp4.exe), then the .NET 2.0 Framework, then the SQL Server 2005 Mobile/Compact Edition Server Tools (for example, sqlce30setupxx.msi). Not sure. I plan to spend the weekend trying to get this set up.

Before receiving your last post, I hadn't even considered trying to set up a single database with a backwards-compatible merge publication! I was planning to set up multiple databases in SQL Server 2000 and SQL Server 2005, with each database supporting a single type of mobile client replication. That's still my plan.

It would have been nice if Microsoft had provided us with more guidance on how to make this transition. There are bits and pieces of information out there, but nothing like a complete transiiton plan.

Larry

configuring locks in awe system

I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition
on Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPU
and 8GB of RAM. I have AWE enabled, /pae /3gb switch is on in
boot.ini, In my errorlog I have noticed few times error messages as
"The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system
administrator to check the SQL Server lock and memory configuration..
" I haven't modified anything in sp_configure 'locks', so i am
assuming SQL server is dynamically managing the lock memory. Running
perfmon for few days I observed that Lock Requests/sec are usually few
100,000 most of the times and sometimes goes to 2+ million. However I
haven't seen Lock Memory (KB) going more than 40MB. So i wonder why am
I seeing the Error: 1204 in my sql log ? And any advise on how to
prevent it.
Thanks
Aswineeaswinee (aswinee@.yahoo.com) writes:
> I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Edition
> on Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPU
> and 8GB of RAM. I have AWE enabled, /pae /3gb switch is on in
> boot.ini, In my errorlog I have noticed few times error messages as
> "The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration..
> " I haven't modified anything in sp_configure 'locks', so i am
> assuming SQL server is dynamically managing the lock memory. Running
> perfmon for few days I observed that Lock Requests/sec are usually few
> 100,000 most of the times and sometimes goes to 2+ million. However I
> haven't seen Lock Memory (KB) going more than 40MB. So i wonder why am
> I seeing the Error: 1204 in my sql log ? And any advise on how to
> prevent it.

I guess there are two questions here:
1) is that locking frequency natural, or is there is a need to investigate
this?
2) are you really hitting hard limits of number of available locks.

I don't know what the maximum number of locks, but I expect it to be more
than two million. Then again, two million lock requests in one second,
does not tell us how many locks thar actually are in use. A lock can be
held for a short moment, or several hours (which would be due to poor
design).

But two million lock requests, or even 100000 sounds a lot to me. But if
that is a busy system it could be normal.

Since this is not really my field of expertise, so I asked in our private
MVP forum, and I got these suggestion:

few interesting points to check are:
- make sure your statistics are up to date
- you have sufficient indexes to run your query efficiently

which is pretty standard. But this is more interesting:

I'd also ask if trace flag 1211 has been set, see
http://support.microsoft.com/?kbid=323630

With 1211 set, you turn off lock escalation, and this can lead to a lot of
locks being requested.

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

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

Configure SQL server 2000.

Hi all,
Please, tell me how to configure database SQL server 2000 for a large system, and make it uninterrupted run.
ThanksYou might want to give some more specifics...what is large?|||Originally posted by Brett Kaiser
You might want to give some more specifics...what is large?

Hi,
I mean the data in database so large and there are so many table in it.|||that's still not very specific... how large? how many tables? what type of database is it? how many users? etc, etc, etc...|||Originally posted by drew
that's still not very specific... how large? how many tables? what type of database is it? how many users? etc, etc, etc...
Hi,
For example, there are N table and M user and L user concurrent...
please, show me.
Thanks|||Well .. it would be more useful, if you gave us numbers to crunch ... what may be very large for you ...(Lets say 30 GB database .. 500 Users etc ...) might be normal for the DBA's around here ... We will be able to help you much more if you provide more specifications.|||Originally posted by Enigma
Well .. it would be more useful, if you gave us numbers to crunch ... what may be very large for you ...(Lets say 30 GB database .. 500 Users etc ...) might be normal for the DBA's around here ... We will be able to help you much more if you provide more specifications.

amen to that! we can't help you if you don't post specifics

Configure SQL Server 2000 for more than 2GB RAM on Windows 2000 server

Hi,

I've a SQL Server 2000 (SP4) installed on Windows 2000 Server.
The system has 4GB RAM.

SQL Server not using more than 2GB RAM even I set the following.

AWE enabled.
Max server Memory (MB) : 3GB
SQLService account is enabled for "Lock Pages in Memory"

Can you please help what I'm missing here?
Thanks in advance.

Regards,
Mkarumuru

have a link from Microsoft that explain this subject:

http://support.microsoft.com/kb/274750/en-us

if you use SQL Server 2000 Standart, then only use 2 GB

|||Thanks for the quick reply.
Its very useful.

I've SQL Server 2000 Standard Edition.
So I cannot use more than 2 GB RAM.

Regards,
MKarumuru