reseed required after solving conflicts

I have set-up several replications, all of them working on the same
way. But on the last one a strange thing occured: It seems that when a
conflict is solved by choosing to use the displayed data from the
looser, I need to reseed the identity column.
The replication is working on the merge model. On the two databases,
the identity is not for replication, with different seed ( 1 for the
main database, 2 for the other one) and an increment of 10.
The databases are SQL Server 2000, and the operating system is Windows
2000 server.
Could you please tell me why this reseed is required.
Regards
Patrick
Why do you say you need to reseed the identity column? What error message is
generated?
Or is what happens is that subsequent inserts pick the last inserted value?
For example you have an identity range of 10 on one side 1000 on another.
The 1001 row is merged to the publisher but is logged as a conflict and
rolled back. Then you select this conflict to win, and the 1001 row gets
inserted in the publisher. Then does the publisher's next value start at
1002?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick" <pmenage@.intnet.mu> wrote in message
news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
>I have set-up several replications, all of them working on the same
> way. But on the last one a strange thing occured: It seems that when a
> conflict is solved by choosing to use the displayed data from the
> looser, I need to reseed the identity column.
> The replication is working on the merge model. On the two databases,
> the identity is not for replication, with different seed ( 1 for the
> main database, 2 for the other one) and an increment of 10.
> The databases are SQL Server 2000, and the operating system is Windows
> 2000 server.
> Could you please tell me why this reseed is required.
> Regards
> Patrick
>
|||After solving a confict on a table with an identity, I need to ressed
the identity of this table else, the identity of next record inserted
by the application takes the value just after the one of the identity
of the record on which there were conflicts. As there are other exiting
records, it is rejected due to primary key problem. It seems that when
solving a conflict, the seed is changed to the value of the identity of
the record on wich the conflict was solved.
To answer your second question, yes, the publishers's next value start
at 1002.
I set up the identity to start at 1 on the main database and at 2 on
the other database. On both side the increment is 10.
Looking forward to reading your thoughts on the above.
Best Regards
Patrick
Hilary Cotter wrote:[vbcol=seagreen]
> Why do you say you need to reseed the identity column? What error message is
> generated?
> Or is what happens is that subsequent inserts pick the last inserted value?
> For example you have an identity range of 10 on one side 1000 on another.
> The 1001 row is merged to the publisher but is logged as a conflict and
> rolled back. Then you select this conflict to win, and the 1001 row gets
> inserted in the publisher. Then does the publisher's next value start at
> 1002?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Patrick" <pmenage@.intnet.mu> wrote in message
> news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
--
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
--
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peter
KB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data file
s
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW.
So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do
I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSD
B
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making th
e
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over t
he
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

Required Suggestion

Hi Friends

I required a suggestion from your side

Actually I am working in MS Access from last 6 months. Now I want to convert my database into SQL Server

I have a knowledge of SQL Server and I don't have any problem with this.

But what I want to know how to maintain a report in SQL Server I don't have any knowledge for creating a report. My freinds suggest me a Visual Studio 2005 for creating a Crystal Report.

So Please suggest me it is helpful or not and onething more is that I have to work on SQL Server 2000 or SQL Server 2005 which is more beneficial.

Please reply me for this

Waiting for your any kind of reply

Please

Thanks

Ashish

Hi Ashish

U can use Reporting services which come default with SQL Server 2005. this helps to access reports thru browser.|||

Thanks for your reply dear

Can you tell me Reporting Service in SQL Server 2005 is work same as a MS Access

because in Access it is very easy to make a reports so it is same as in MS Access or it

work with some other software.

Please reply me

I am waiting for your reply

Thanks

Ashish

required software needed by User to look at SSAS 2005 Cubes in Excel 2003

I am addressing what I consider to be somewhat of a problem. We have built some SSAS Cubes in SQL Server 2005. If I understand this correctly, if our Customer wants to slice and dice one of our Cubes inside his Excel Pivot Table he must:

1. Install XML Services (MSXML) 6.0 è (as a prerequisite to the 9.0 OLE DB Provider install)
2. Install Analysis Services 9.0 OLE DB Provider.

Of course, the above is in addition to the need to have MS Query installed. Also, the above premise assumes that the User/customer is not on the SQL Server box.

Is this true?

The requirement of XML Services is the surprise. Why is this needed for everyone?

Thanks in advance for your help in answering this question.

Mick B

Hello! Regarding point 1 and 2 all clients need this unless the users connect with a third party middleware like ProClarity Analytics. But then these users works with designed reports, Ad hoc conenctions directly to the cube will require 1 and 2.

The user needs to belong to a group or account with permissions on the server running SSAS2005(Analysis Services). It has nothing to do with SQL Server 2005 database permissions.

SSAS2005 uses XMLA for communication and that is why, I guess, that you will need MSXML 6.0 .

HTH

Thomas Ivarsson

|||

Thanks Tom. I was looking for that confirmation. I am envisioning one or more Finance User running our Cost Cubes with Pivot Tables. They have to have all this additional software installed before they can use the Cubes.

As a developer I had everything on my SQL Server box so it did not hit me that Users would not have what they need. The Excel add-in was obvious.

Again, I appreciate your response.

Mick

Required Security for Report Viewer on Windows 2003 SP1 Web Server

When trying to run a local report using the RS Report Viewer for VS
2005 we get the below exception when the User Account for the
Application Pool we are using is not in the Administrators Group of the
web server.
Does anybody have any suggestions as to what specific security is
needed to use the Report Viewer?
Could this be because we built the application as a "Web Project"
instead of a "Web Site"?
Microsoft.Reporting.WebForms.LocalProcessingException: An error
occurred during local report processing. -->
Microsoft.Reporting.DefinitionInvalidException: The definition of the
report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'. -- End of inner exception stack trace -- at
AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
Settings\god\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
C:\Documents and Settings\God\My Documents\Visual Studio
2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
InnerException: Microsoft.Reporting.DefinitionInvalidException: The
definition of the report 'Main Report' is invalid. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
An unexpected error occurred while compiling expressions. Native
compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
could not be found'.This error is also logged in the system event viewer, however granting
access NETMAN in the DCOM manager only keeps the errors from showing
and does not fix the problem. The CLSID refers to tthe Network
Connection Manager Class (under the NETMAN application).
The application-specific permission settings do not grant Local
Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user AMI\srvAcctAppAccessDEV SID
(S-1-5-21-2975352776-793034493-3225607600-10632). This security
permission can be modified using the Component Services administrative
tool.
tfelber@.gmail.com wrote:
> When trying to run a local report using the RS Report Viewer for VS
> 2005 we get the below exception when the User Account for the
> Application Pool we are using is not in the Administrators Group of the
> web server.
> Does anybody have any suggestions as to what specific security is
> needed to use the Report Viewer?
> Could this be because we built the application as a "Web Project"
> instead of a "Web Site"?
> Microsoft.Reporting.WebForms.LocalProcessingException: An error
> occurred during local report processing. -->
> Microsoft.Reporting.DefinitionInvalidException: The definition of the
> report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'. -- End of inner exception stack trace -- at
> AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> Settings\god\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> C:\Documents and Settings\God\My Documents\Visual Studio
> 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> definition of the report 'Main Report' is invalid. -->
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> An unexpected error occurred while compiling expressions. Native
> compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> could not be found'.|||Finally figured it out.
The configurable identity that our application pool was using under
IIS 6.0 needed read/write access to the c:\Windows\Temp directory.
(Explains why we could get this to work when the account was part of
the Administrators or Power Users Group). I suppose this is to give it
temp space when rendering large reports.
Hopefully this helps somebody else out one day.
tfelber@.gmail.com wrote:
> This error is also logged in the system event viewer, however granting
> access NETMAN in the DCOM manager only keeps the errors from showing
> and does not fix the problem. The CLSID refers to tthe Network
> Connection Manager Class (under the NETMAN application).
> The application-specific permission settings do not grant Local
> Activation permission for the COM Server application with CLSID
> {BA126AD1-2166-11D1-B1D0-00805FC1270E}
> to the user AMI\srvAcctAppAccessDEV SID
> (S-1-5-21-2975352776-793034493-3225607600-10632). This security
> permission can be modified using the Component Services administrative
> tool.
>
> tfelber@.gmail.com wrote:
> > When trying to run a local report using the RS Report Viewer for VS
> > 2005 we get the below exception when the User Account for the
> > Application Pool we are using is not in the Administrators Group of the
> > web server.
> >
> > Does anybody have any suggestions as to what specific security is
> > needed to use the Report Viewer?
> >
> > Could this be because we built the application as a "Web Project"
> > instead of a "Web Site"?
> >
> > Microsoft.Reporting.WebForms.LocalProcessingException: An error
> > occurred during local report processing. -->
> > Microsoft.Reporting.DefinitionInvalidException: The definition of the
> > report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'. -- End of inner exception stack trace -- at
> > AMR.AdvertiserClientList.RefreshReport() in C:\Documents and
> > Settings\god\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 192 at
> > AMR.AdvertiserClientList.RUNREPORT_Click(Object sender, EventArgs e) in
> > C:\Documents and Settings\God\My Documents\Visual Studio
> > 2005\Projects\AMR\AdvertiserClientList.aspx.vb:line 50 ::
> > InnerException: Microsoft.Reporting.DefinitionInvalidException: The
> > definition of the report 'Main Report' is invalid. -->
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > An unexpected error occurred while compiling expressions. Native
> > compiler return value: '[BC2001] file 'C:\WINDOWS\TEMP\n_rkco4n.0.vb'
> > could not be found'.

Required Role for SQL 2005 Security operations

We need a user with db_owner permissions plus permission for these
operations:
Create db master key
Create certificate
Create symmetric key
And decrypt / encrypt by key
should it be securityadmin?
YanivHi ,Yaniv
BOL says , if you want the user to perfom CREATE CERTIFICATE for example
,so
'Requires CREATE CERTIFICATE permission on the database.'
Read the BOL for each statement that you need and make a decision. You may
want to consider create a ROLE which will contain all those requierd
permissions
<yaniv.harpaz@.gmail.com> wrote in message
news:1153658166.225912.167740@.p79g2000cwp.googlegroups.com...
> We need a user with db_owner permissions plus permission for these
> operations:
> Create db master key
> Create certificate
> Create symmetric key
> And decrypt / encrypt by key
> should it be securityadmin?
> Yaniv
>|||Thank You Uri,
Yaniv
Uri Dimant wrote:
> Hi ,Yaniv
> BOL says , if you want the user to perfom CREATE CERTIFICATE for example
> ,so
> 'Requires CREATE CERTIFICATE permission on the database.'
> Read the BOL for each statement that you need and make a decision. You may
> want to consider create a ROLE which will contain all those requierd
> permissions
>
>
>
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153658166.225912.167740@.p79g2000cwp.googlegroups.com...
> > We need a user with db_owner permissions plus permission for these
> > operations:
> > Create db master key
> > Create certificate
> > Create symmetric key
> > And decrypt / encrypt by key
> >
> > should it be securityadmin?
> >
> > Yaniv
> >

Required permissions for running TextCopy?

I posted this last week, but unfortunately it was lost. So here 'tis again.

I am having trouble with working out what permissions a user needs to run the Textcopy function.

I have setup a stored procedure on the server which runs Textcopy to insert/export a GIF file to/from a network location to an Image field of a database. The stored procedure is called from an Access97 frontend.

It all works fine when the user on the client PC has SQL Server System Administrator permissions, however when the client PC is logged in as a normal user without these permission, the operation fails. Giving the user System Admin permissions fixes the problem, but obviously I can't do this for all the users!

Does anyone have any idea exactly what individual items I need to set permissions for to enable Textcopy to run??

Any hints at all, (no matter how small) would be much appreciated.

Thanks,

Ian.
:confused:What messages appear in the event viewer ? What are the current permissions on the textcopy.exe file ?|||Thanks for those thoughts.

Permissions on the file and directory are set to Full Control Everyone. (can't be that)

I can't find any messages being writen to the event log when this error occurs.

Ian.|||Please provide the stored procedure code.|||Here is SQL stored procedure:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (60),
@.whereclause varchar (50),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'D:\Temp\textcopy /S ' +@.srvname +
' /U ' + @.login +
' /P ' + @.password+
' /D ' + @.dbname+
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause+
'" /F ' +@.filename+
' /' + @.direction+
' /Z'
EXEC master..xp_cmdshell @.exec_str
GO|||What are the permissions on xp_cmdshell ?|||The permission on xp_cmdshell (in the master DB) are set to enable the cleint user run it.

(They weren't before but they are now. Still won't work)|||Did you create a sql server agent proxy account ?|||Do you mean a Proxy Server account for the same account that SQL Server agent runs under?

How does this effect it?

I would have thought that the permissions are within SQL as setting the account to be a SQL administrator fixes the problem? (I could be wrong)|||SQL server uses this account to execute the xp_cmdshell - it also uses this account for execution of an agent job (both of these apply to non-sysadmin accounts). For non-sysadmin accounts, these commands will be executed under the security context of this account. So you have to create a sql server agent proxy account.|||You were Right!!

I had not created a SQL Server Agent Proxy Account.

When I eventually found this and created an account it works.

Thanks a lot!

Ian.|||Originally posted by rnealejr
SQL server uses this account to execute the xp_cmdshell - it also uses this account for execution of an agent job (both of these apply to non-sysadmin accounts). For non-sysadmin accounts, these commands will be executed under the security context of this account. So you have to create a sql server agent proxy account.

how do you create a sql server agent proxy account?

Required Microsoft Sql Server 2005 Express Server Roles for JDBC Connection

Hi!

I have developed a database in MS SQL Server 2005 Express, to which I would require only bulkadmin server role from an external java application, because I only need to update rows, insert values or use select queries in the database.

The problem is that, using either the Microsoft JDBC Driver 1.1 or the Java JDBC ODBC Driver and the Windows XP Data Base (ODBC) configurations, I need a user with sysadmin server role inside Sql Server, otherwise JDBC won't connect to the database using the selected user. Even if I leave the sql login with setupadmin or any server role lower than sysadmin, the connection is refused.

Is there no way to connect using JDBC to MS Sql Server 2005 other than granting the connected user sysadmin rights? My code looks as follows:

Code Snippet

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://FIREBLADE\\SQLEXPRESS";
String user = "username";
String password = "password$$";
Connection conn;

Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
if (conn != null)
System.out.println("\nSQL Server Connection established ...\n");

I have heard that Java JDBC connections to Microsoft require high-level access.

Any informed answer is more than welcome. Thanks for reading my post!

Your connection string is a bit different than what's documented in the following article - you'd want to start there:

http://blogs.msdn.com/angelsb/archive/2005/08/01/446452.aspx

Connecting through JDBC does not require high level access or sysadmin rights.

-Sue

Required help on sql 2005 Server Management Studio.

Have installed sql 2005 successfully on windows2003 server. But when I
opened
server management studio I found duplicate entries in menu toolbar.
Tried to delete the
duplicate entries from tools > customise > commands > rearrange
commands but in vain.
After restarting the application again it is showing duplicate option
in menu toolbar.
Suggest what needs to be done to fix this permanantely
Regds,
Kiran.Hi Kiran, what happens when you completely un-install and re-install SQL
Server 2005 workstation components? Do you still see duplicate entries?
Thank you,
Saleem Hakani
WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL server Community)
SQLTips, SQL Articles, SQL Forums, SQL Automation Tools/Scripts, SQL Blogs
and much more..
"Mendon" wrote:
> Have installed sql 2005 successfully on windows2003 server. But when I
> opened
> server management studio I found duplicate entries in menu toolbar.
> Tried to delete the
> duplicate entries from tools > customise > commands > rearrange
> commands but in vain.
> After restarting the application again it is showing duplicate option
> in menu toolbar.
> Suggest what needs to be done to fix this permanantely
> Regds,
> Kiran.
>

Required Help

Pl tell me this senario

if we create new database in sql 2000 with size 1024 KB now we insert 500 KB data in that database. so what is actual size of database now?

That depends how your database (files) is (are) extended. The default is defined by the model database which is by default 10Percent / unlimited unless you change the model database or the files in the appropiate database.

The next steps for extending the database would be in your case:

1MB
1,1 MB
1,21 MB
1,33 MB
1,46 MB
1,61 MB

(round about)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Dear Jens,

Pl. tell me in brieaf

Required Help

Hi,

how we use syscolumns in Sql Server 2005 (Its not work in Sql Server 2005)

hi,

what do you mean by that?

sys.syscolumns is still available...

regards

required help

I have written the following stored procedure but I am gettig some errors, can anybody help me.
CREATE PROCEDURE <procedurename>
@.tempstr as varchar(20),
@.ntempstr as varchar(20),
@.cdestr as varchar(20),
@.Indstr as varchar(20),
@.prdstr as varchar(20),
@.notpos as numeric,
@.nnotpos as numeric,
@.orapos as numeric
AS
begin
while (@.tempstr !=' ' )
loop
begin
@.orapos=CHARINDEX("OR", @.tmpstr)
if (@.orapos > 0)
begin
@.ntempstr=LTRIM(LEFT(@.tempstr, 2))
@.tempstr=Ltrim(mid(@.tempstr, (@.orpos+2))
end
else
begin
@.ntempstr=LTRIM(@.tempstr)
@.tempstr= ' '

@.notpos=CHARINDEX("NOT", @.tmpstr)
end
If @. @.notpos > 0 Then
@.nnotpos = LTrim(Mid( @.nnotpos, @.notpos + 3))
end
end

for( loop
GO

Quote:

Originally Posted by samb

I have written the following stored procedure but I am gettig some errors, can anybody help me.
CREATE PROCEDURE <procedurename>
@.tempstr as varchar(20),
@.ntempstr as varchar(20),
@.cdestr as varchar(20),
@.Indstr as varchar(20),
@.prdstr as varchar(20),
@.notpos as numeric,
@.nnotpos as numeric,
@.orapos as numeric
AS
begin
while (@.tempstr !=' ' )
loop
begin
@.orapos=CHARINDEX("OR", @.tmpstr)
if (@.orapos > 0)
begin
@.ntempstr=LTRIM(LEFT(@.tempstr, 2))
@.tempstr=Ltrim(mid(@.tempstr, (@.orpos+2))
end
else
begin
@.ntempstr=LTRIM(@.tempstr)
@.tempstr= ' '

@.notpos=CHARINDEX("NOT", @.tmpstr)
end
If @. @.notpos > 0 Then
@.nnotpos = LTrim(Mid( @.nnotpos, @.notpos + 3))
end
end

for( loop
GO

Unfortunately there really are quite a few errors in it. I suggest you start with a simpler version and then expand it as you get experience. A few corrections:it is not complete code snippet (the 'for' loop at the end is obviously incomplete, plus you have variables which you never use)

Required free eBook on Reporting Services 2005.

Hello Friends,

I am in need of eBook on Reporting Services 2005 for study perpose, Please guide me through any link.

Thanks,

Vishal.

Don't know of any ebooks, however I think the most thorough set of tutorials is here:

http://www.databasejournal.com/article.php/1459531

Cheers
Mark
|||The Books On Line help file is a good place to start.|||

Hello Mark,

Thanks for this link, it is very helpful to me.

Bye.

Required free eBook on Reporting Services 2005.

Hello Friends,

I am in need of eBook on Reporting Services 2005 for study perpose, Please guide me through any link.

Thanks,

Vishal.

Don't know of any ebooks, however I think the most thorough set of tutorials is here:

http://www.databasejournal.com/article.php/1459531

Cheers
Mark
|||The Books On Line help file is a good place to start.|||

Hello Mark,

Thanks for this link, it is very helpful to me.

Bye.

Required free eBook on Reporting Services 2005.

Hello Friends,

I am in need of eBook on Reporting Services 2005 for study perpose, Please guide me through any link.

Thanks,

Vishal.

Don't know of any ebooks, however I think the most thorough set of tutorials is here:

http://www.databasejournal.com/article.php/1459531

Cheers
Mark
|||The Books On Line help file is a good place to start.|||

Hello Mark,

Thanks for this link, it is very helpful to me.

Bye.

required filter on Ad-hoc reports

When using the Report Builder in SQL 2005 some larger reports give me an
error message = filter required.
I assume this is somthing is a setting somwhere but where?
--
Peter
Peter.freeborn@.halliburton.comGo to Filter from the menu bar and select a column for the filter, you can
have prompt as well for the user to select the values.
I think, the Adhoc is designed in such a way that you need to set a filter,
because without filter it will be a long running query and will have great
effects on various things.
Amarnath
"Peter" wrote:
> When using the Report Builder in SQL 2005 some larger reports give me an
> error message = filter required.
> I assume this is somthing is a setting somwhere but where?
> --
> Peter
> Peter.freeborn@.halliburton.com

Required DLLs for SQL7 DTS

I've written a VB6 application that executes a DTS package on SQL7.

The application works perfectly in both Develop and Runtime environments from my system. But when deployed (with default DLLs)to the intended client, the application raises an exception when creating an instance of the DTS object, "Set oPkg = New DTS.Package". The exception says "it can't create and instance of the object" or something to that effect.

While trying to figure out the problem, I installed the application ( without the default DLLs ) on the SQL Server machine. Again, it ran without a problem.

Unfortunately, the SQL machine is not the intended client. All systems here are running W2K(SP2).

Can someone offer some advice?

Thanks,

PaulThere are a couple of rll's you need like dtspkg.rll - the following page should answer your questions:

page (http://www.dbforums.com/archive/43/2002/04/3/356646)|||Originally posted by rnealejr
There are a couple of rll's you need like dtspkg.rll - the following page should answer your questions:

page (http://www.dbforums.com/archive/43/2002/04/3/356646)

Thanks for your response. However I am using a DSN-less connection. So the page above really does not apply.

Any other ideas. My only other option is to incorporate the function of the DTS package into the VB application. I would really like to aviod this option if possible.

Thanks again,

Paul|||Have you included the reference that you are using within your vb project when you install on another machine ? There are probably x number of files that have been left out and/or have not been registered on the deployed machine. I wanted you to see the top half of the article I posted (not the bottom half - about the dsn connection). The top half focused on required files for deployment.

Let me know.

Required disk space to reindex

Hello,
I would like to know the amount of free space I need to reindex a clustered
index. For a clustered index using 100 GB for example, how much data free
space I need ? (I am not talking about the transactional log) ?
Thanks in advance,
Regards,
Vincent
Vincent
100 GB *2= 200 GB
"Vincent D." <VincentD@.discussions.microsoft.com> wrote in message
news:8E183CCA-3E97-4BC7-9A3A-D118682B20FB@.microsoft.com...
> Hello,
> I would like to know the amount of free space I need to reindex a
> clustered
> index. For a clustered index using 100 GB for example, how much data free
> space I need ? (I am not talking about the transactional log) ?
> Thanks in advance,
> Regards,
> Vincent
|||Are you saying that you have a table with a clustered index, and that index tree (which contains the
data) is using 100GB. For that you will need somewhere around 120 GB free space in the database to
rebuild the index. I.e., the size of the index plus some.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Vincent D." <VincentD@.discussions.microsoft.com> wrote in message
news:8E183CCA-3E97-4BC7-9A3A-D118682B20FB@.microsoft.com...
> Hello,
> I would like to know the amount of free space I need to reindex a clustered
> index. For a clustered index using 100 GB for example, how much data free
> space I need ? (I am not talking about the transactional log) ?
> Thanks in advance,
> Regards,
> Vincent
|||> Are you saying that you have a table with a clustered index, and that
> index tree (which contains the data) is using 100GB. For that you will
> need somewhere around 120 GB free space in the database to rebuild the
> index. I.e., the size of the index plus some.
To be safe, I would usually ballpark at 1.5x the index size.