I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
Showing posts with label example. Show all posts
Showing posts with label example. Show all posts
Tuesday, March 27, 2012
HistoryID Problem?
Hi I'm trying to use SOAP in a VAB Access 2003 Project. I can connect to the Reporting Services an Create fr example Folders. But if I want to Render a Report i am Getting a Problem.
Error: -2147221504
For ' snapshotID ' indicated parameter value does not correspond to the type of parameter.
I passed an empty String for the optional Value HistoryID.
I dont know where the problem is.
Can anybody help me ?
CODE:
Dim ReportName As String
Dim RenderFormat As String
Dim HistoryID As Variant
Dim DeviceInfo As String
Dim Parameters(2) As struct_ParameterValue
Dim Credentials() As struct_DataSourceCredential
Dim ShowHideToggle As String
Dim resultFile() As Byte
Dim resultEncoding As String
Dim resultMimeType As String
Dim resultParametersUsed() As struct_ParameterValue
Dim resultWarnings() As struct_Warning
Dim resultStreamIds() As String
Set RS = New clsws_ReportingService
ReportName = "/SampleReports/Employee Sales Summary"
RenderFormat = "mhmtl"
HistoryID = 0
DeviceInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
ShowHideToggle = ""
Set Parameters(0) = New struct_ParameterValue
Parameters(0).Name = "EmpID"
Parameters(0).Value = "38"
Set Parameters(1) = New struct_ParameterValue
Parameters(1).Name = "ReportMonth"
Parameters(1).Value = "6" ' June
Set Parameters(2) = New struct_ParameterValue
Parameters(2).Name = "ReportYear"
Parameters(2).Value = "2004"
resultFile = RS.wsm_Render( _
ReportName, _
RenderFormat, _
nil, _
DeviceInfo, _
Parameters, _
Credentials, _
ShowHideToggle, _
resultEncoding, _
resultMimeType, _
resultParametersUsed, _
resultWarnings, _
resultStreamIds)
thx for your helpSorry for double posting... but there was an error maeesage so i tryed again.sql
Error: -2147221504
For ' snapshotID ' indicated parameter value does not correspond to the type of parameter.
I passed an empty String for the optional Value HistoryID.
I dont know where the problem is.
Can anybody help me ?
CODE:
Dim ReportName As String
Dim RenderFormat As String
Dim HistoryID As Variant
Dim DeviceInfo As String
Dim Parameters(2) As struct_ParameterValue
Dim Credentials() As struct_DataSourceCredential
Dim ShowHideToggle As String
Dim resultFile() As Byte
Dim resultEncoding As String
Dim resultMimeType As String
Dim resultParametersUsed() As struct_ParameterValue
Dim resultWarnings() As struct_Warning
Dim resultStreamIds() As String
Set RS = New clsws_ReportingService
ReportName = "/SampleReports/Employee Sales Summary"
RenderFormat = "mhmtl"
HistoryID = 0
DeviceInfo = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
ShowHideToggle = ""
Set Parameters(0) = New struct_ParameterValue
Parameters(0).Name = "EmpID"
Parameters(0).Value = "38"
Set Parameters(1) = New struct_ParameterValue
Parameters(1).Name = "ReportMonth"
Parameters(1).Value = "6" ' June
Set Parameters(2) = New struct_ParameterValue
Parameters(2).Name = "ReportYear"
Parameters(2).Value = "2004"
resultFile = RS.wsm_Render( _
ReportName, _
RenderFormat, _
nil, _
DeviceInfo, _
Parameters, _
Credentials, _
ShowHideToggle, _
resultEncoding, _
resultMimeType, _
resultParametersUsed, _
resultWarnings, _
resultStreamIds)
thx for your helpSorry for double posting... but there was an error maeesage so i tryed again.sql
History of logins or Userids
Hello All,
Does SQL Sever 2000 keep track of all the logins/userids that were
deleted over the last year? For example, If I deleted a login "Joe"
from a SQL 2000 server, (and of course the corresponding userid "Joe"
got deleted from the database) would that be recorded somewhere in the
system or the production database? i.e. the information such as the
login "joe" deleted on such and such date from such and such database?
Long Live SOX :)
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> Does SQL Sever 2000 keep track of all the logins/userids that were
> deleted over the last year? For example, If I deleted a login "Joe"
> from a SQL 2000 server, (and of course the corresponding userid "Joe"
> got deleted from the database) would that be recorded somewhere in the
> system or the production database? i.e. the information such as the
> login "joe" deleted on such and such date from such and such database?
The only place for this is in the transaction log. So if you have your
master in full recovery - and simple is the default - and you never have
truncated the transaction log, you have the information about the login.
If you then get a log reader - see www.lumigent.com or www.logpi.com -
you can get hold of the information.
Same applies to the production database, except that in case I am quite
sure that you have truncated the log. But of course, if you have all
full backups and log backups since last year saved... :-)
It is possible to set up SQL Server to audit such information with the
C2 setting and a trace. But that has to be thought of in advance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Are there any third party tools that will do this?
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> Are there any third party tools that will do this?
Have a look at Lumigent's Entegra, http://www.lumigent.com. I don't know
if they have what they are asking for, but I would expect it.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Are there any third party tools that will do this?
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***sql
Monday, March 26, 2012
Hilary...Please provide an example
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
Can you contact me offline. I have a bit of a write up on how to do this.
Hilary Cotter
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
"JLS" <judes@.email.uophx.edu> wrote in message news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
|||Yes, absolutely, how do I contact you offline?
If it's easier, you can contact me at your convenience, toll free 1-800-sartomer, extension 4154 or have the operator page Judy Shoop or directly to my office is 610.363.4154.
Thanx!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:eMLs6Zp$FHA.3096@.TK2MSFTNGP14.phx.gbl...
Can you contact me offline. I have a bit of a write up on how to do this.
Hilary Cotter
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
"JLS" <judes@.email.uophx.edu> wrote in message news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
|||Use my email address - but here is the doc I put together.
The requirement is that DATABASE be replicated bi-directionally internally
and externally. There are several options to do this, bi-directional
transactional replication was chosen as it does not modify the schema. Merge
replication, and updateable subscribers all modify the schema with the
addition of a GUID column.
To get around the firewall problem the internal database will replicate to
the external database, and the external database changes will be pulled
internally. The internal server will be configured as an anonymous
subscriber of the external server's publication as a named subscriber will
not work.
Steps required configure replication.
1) connect to the production database, right click on each of the
production tables and set the identity attribute to be not for replication.
Configure the increment to be 2. You will need to modify the following
tables.
TableName1WithIdentityColumn
TableName2WithIdentityColumn
TableName3WithIdentityColumn
TableName4WithIdentityColumn
The best/only way to configure these tables is to right click on them in
Enterprise Manager and set the identity property accordingly. Figure 1 is a
correctly configured table.
2) After all of the tables have been configured, back up the database,
and restore it on the subscriber. Ensure that no users are accessing the
database at this time.
3) Once the database has been restored create the publication on the
Publisher and the Subscriber. Use the attached script for this, editing for
the correct server and database names. - this script is a publication where
the name conflicts section is keep existing table intact, and its a nosync
subscription.
4) Once you have done this you need to run the same script in your
external server. Make sure you have configured your subscriber using its
NetBIOS name in Client Network Utility. A Fully Qualified Domain Name will
not work; it must be a NetBIOS name. Please refer to Figure 2 for an
example.
5) Once you have configured replication, deploy the replication stored
procedures on both sides. Please refer to the attached script to do this.
(this script was generated by running sp_scriptpublicationcustomprocs
6) Once the replication stored procedures are in place you have to
reset the identity seed on both sides. We will assign even numbers to the
internal server, and external numbers to the external server.
7) To do this issue the following commands. Note that TableName5 is the
only table which has to be fixed, and the fix is illustrated in red:
dbcc checkident('TableName5')
--Checking identity information: current identity value '8854', current
column value '8854'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
dbcc checkident('TableName5')
--Checking identity information: current identity value '448', current
column value '448'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
dbcc checkident('TableName6)
--Checking identity information: current identity value '19', current column
value '19'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--not ok as the number is odd
--incrementing it up to the nearest even number
dbcc checkident('TableName7', reseed,20)
--Checking identity information: current identity value '19', current column
value '20'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--now correct
dbcc checkident('TableName8')
--Checking identity information: current identity value '32', current column
value '32'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
8) Repeat for the Subscriber (external server). This time all the
identity values should be reseeded to the next highest odd number.
9) Schedule the distribution agents to start every 5 minutes - this
will guarantee an restart in the event of failure.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eCOXKey$FHA.1408@.TK2MSFTNGP15.phx.gbl...
Yes, absolutely, how do I contact you offline?
If it's easier, you can contact me at your convenience, toll free
1-800-sartomer, extension 4154 or have the operator page Judy Shoop or
directly to my office is 610.363.4154.
Thanx!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eMLs6Zp$FHA.3096@.TK2MSFTNGP14.phx.gbl...
Can you contact me offline. I have a bit of a write up on how to do this.
Hilary Cotter
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
"JLS" <judes@.email.uophx.edu> wrote in message
news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not
following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to
the db on the DMZ, and now I need to pull 2 tables from this same db from
the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I
don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can
restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_
scripts for the pull subscription & pull agent, for NO SYNC?
Jude
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
Can you contact me offline. I have a bit of a write up on how to do this.
Hilary Cotter
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
"JLS" <judes@.email.uophx.edu> wrote in message news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
|||Yes, absolutely, how do I contact you offline?
If it's easier, you can contact me at your convenience, toll free 1-800-sartomer, extension 4154 or have the operator page Judy Shoop or directly to my office is 610.363.4154.
Thanx!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:eMLs6Zp$FHA.3096@.TK2MSFTNGP14.phx.gbl...
Can you contact me offline. I have a bit of a write up on how to do this.
Hilary Cotter
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
"JLS" <judes@.email.uophx.edu> wrote in message news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
|||Use my email address - but here is the doc I put together.
The requirement is that DATABASE be replicated bi-directionally internally
and externally. There are several options to do this, bi-directional
transactional replication was chosen as it does not modify the schema. Merge
replication, and updateable subscribers all modify the schema with the
addition of a GUID column.
To get around the firewall problem the internal database will replicate to
the external database, and the external database changes will be pulled
internally. The internal server will be configured as an anonymous
subscriber of the external server's publication as a named subscriber will
not work.
Steps required configure replication.
1) connect to the production database, right click on each of the
production tables and set the identity attribute to be not for replication.
Configure the increment to be 2. You will need to modify the following
tables.
TableName1WithIdentityColumn
TableName2WithIdentityColumn
TableName3WithIdentityColumn
TableName4WithIdentityColumn
The best/only way to configure these tables is to right click on them in
Enterprise Manager and set the identity property accordingly. Figure 1 is a
correctly configured table.
2) After all of the tables have been configured, back up the database,
and restore it on the subscriber. Ensure that no users are accessing the
database at this time.
3) Once the database has been restored create the publication on the
Publisher and the Subscriber. Use the attached script for this, editing for
the correct server and database names. - this script is a publication where
the name conflicts section is keep existing table intact, and its a nosync
subscription.
4) Once you have done this you need to run the same script in your
external server. Make sure you have configured your subscriber using its
NetBIOS name in Client Network Utility. A Fully Qualified Domain Name will
not work; it must be a NetBIOS name. Please refer to Figure 2 for an
example.
5) Once you have configured replication, deploy the replication stored
procedures on both sides. Please refer to the attached script to do this.
(this script was generated by running sp_scriptpublicationcustomprocs
6) Once the replication stored procedures are in place you have to
reset the identity seed on both sides. We will assign even numbers to the
internal server, and external numbers to the external server.
7) To do this issue the following commands. Note that TableName5 is the
only table which has to be fixed, and the fix is illustrated in red:
dbcc checkident('TableName5')
--Checking identity information: current identity value '8854', current
column value '8854'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
dbcc checkident('TableName5')
--Checking identity information: current identity value '448', current
column value '448'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
dbcc checkident('TableName6)
--Checking identity information: current identity value '19', current column
value '19'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--not ok as the number is odd
--incrementing it up to the nearest even number
dbcc checkident('TableName7', reseed,20)
--Checking identity information: current identity value '19', current column
value '20'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--now correct
dbcc checkident('TableName8')
--Checking identity information: current identity value '32', current column
value '32'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
8) Repeat for the Subscriber (external server). This time all the
identity values should be reseeded to the next highest odd number.
9) Schedule the distribution agents to start every 5 minutes - this
will guarantee an restart in the event of failure.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eCOXKey$FHA.1408@.TK2MSFTNGP15.phx.gbl...
Yes, absolutely, how do I contact you offline?
If it's easier, you can contact me at your convenience, toll free
1-800-sartomer, extension 4154 or have the operator page Judy Shoop or
directly to my office is 610.363.4154.
Thanx!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eMLs6Zp$FHA.3096@.TK2MSFTNGP14.phx.gbl...
Can you contact me offline. I have a bit of a write up on how to do this.
Hilary Cotter
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
"JLS" <judes@.email.uophx.edu> wrote in message
news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not
following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to
the db on the DMZ, and now I need to pull 2 tables from this same db from
the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I
don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can
restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_
scripts for the pull subscription & pull agent, for NO SYNC?
Jude
Friday, March 9, 2012
Hierarchy Equivalent of a Crossjoin
I want to pull back a cellset with two levels of a hierarchy on the same dimension. Using AdventureWorks as an example, let's say I want "Account Level 01" and "Account Level 02" both on the rows dimesion.
When I send MDX like this:
SELECT
{[Account].[Accounts].[Account Level 01].Members * [Account].[Accounts].[Account Level 02].Members}
DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(0) FROM [Adventure Works]
I get this error message:
The Accounts hierarchy is used more than once in the Crossjoin function.
What MDX would I need in this situation? I've tried spying on the MDX generated by BIDs, but it uses so many temporary SETs that it's nearly impossible to follow.
Thanks for any help,
Terry
Terry,
If my understanding of what you are trying to do is correct, one possible solution would be to use the "Generate()" function:
Generate([Account].[Accounts].[Account Level 01].Members,
{[Account].[Accounts].CurrentMember,[Account].[Accounts].CurrentMember.Children})
HTH,
Steve
Wednesday, March 7, 2012
Hierarchichal query in SQL Server 2000
Hi All,
How to implement a hierarchical query in SQL Server 2000.
Example ; I have an Oracle Query as below
SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;
I need to get the equivalent of this query in SQL Server 2000 .
In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .
Is there any way to implement this in SQL Server 2000Is there any way to implement this in SQL Server 2000not easily, no
if there is some maximum number of levels to the hierarchy (e.g. never more than eleven levels deep, from top executive to lowliest peon), then you can write a query with that number of LEFT OUTER JOINs|||You can do this efficiently using a loop. Check out this link, and then I will answer any other questions you have:
http://sqlblindman.googlepages.com/returningchildrecords|||Hi Blindman,
I am unable to open the link u provided .|||http://sqlblindman.googlepages.com/returningchildrecords
Are you getting an error?|||no error, url works just fine|||Here is a little tutorial i wrote some time ago that gives you what you want.
http://vbforums.com/showthread.php?t=366078
How to implement a hierarchical query in SQL Server 2000.
Example ; I have an Oracle Query as below
SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;
I need to get the equivalent of this query in SQL Server 2000 .
In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .
Is there any way to implement this in SQL Server 2000Is there any way to implement this in SQL Server 2000not easily, no
if there is some maximum number of levels to the hierarchy (e.g. never more than eleven levels deep, from top executive to lowliest peon), then you can write a query with that number of LEFT OUTER JOINs|||You can do this efficiently using a loop. Check out this link, and then I will answer any other questions you have:
http://sqlblindman.googlepages.com/returningchildrecords|||Hi Blindman,
I am unable to open the link u provided .|||http://sqlblindman.googlepages.com/returningchildrecords
Are you getting an error?|||no error, url works just fine|||Here is a little tutorial i wrote some time ago that gives you what you want.
http://vbforums.com/showthread.php?t=366078
Sunday, February 26, 2012
hiding Table Rows
Is it possible to determine whether or not another row in a table is visible? The example is in a multiple detail row table, where I want to display a 'header' row (really just another detail row), if any of the other detail rows in the 'section' are visible. Is there some syntax like ReportItems!TableRow7.property("Hidden")=?? that I could use to determine the visibility state of a row?Thanks
AnilHi,in Layout Tab, select the row and in the Property Panel select Visibility -> Hidden -> Expression.
Best Regards
|||
Thanks for your response but it does not solve my problem.
My Problem is I have master detail records.
If detail records are not existing then I have to make the master row invisible
Could anybody help me out fom this problem.
Ofcourse I solved it by modifying the database query but still want the solution of this problem
Thanks
Anil
|||
Hi...Yes u can hide the Rows using If Expressions...do onething...what ever the textbox u want hide...go.
1)..selct Property Window(F4) ...
2) then click Visibility -> hidden -> Select <Expressin..>
then write like this....I am writting One Example Only.....use this...
=iif(ReportItems!textbox2.Value="Y",False,True)
False-- Visible
True- Hide of textbox
U cAN WRITE FOR ANY THING....
Ok..Good Luck...
Friday, February 24, 2012
Hiding group header/footer lines
Is there any way to hide header/footer group lines in a table without displaying the white space? For Example:
Group 1 Label1
Group 2 Label2
Group 3 Label3
Detail
If I put an expression in the visibility property of Group 2 to make Group 2 invisible for certain groups, it also makes Group 3 and the detail invisible as well. If I set the visibility to Label2 to hide the text for that group header, I still have the white space on the report. Is there a good way to hide (or shrink) the header/footer line of a group dynamically on a group by group basis?
Jim
I figured it out, I was setting the visibility on the group instead of the row. Setting the visibility on the row worked great.Sunday, February 19, 2012
Hiding a row in a table based on whether another row is hidden
Is it possible to determine whether or not another row in a table is visible?
The example is in a multiple detail row table, where I want to display a
'header' row (really just another detail row), if any of the other detail
rows in the 'section' are visible. Is there some syntax like
ReportItems!TableRow7.property("Hidden")=' that I could use to determine the
visibility state of a row?
ThanksDid you find a solution on your problem? I have a simular problem.
"PD" wrote:
> Is it possible to determine whether or not another row in a table is visible?
> The example is in a multiple detail row table, where I want to display a
> 'header' row (really just another detail row), if any of the other detail
> rows in the 'section' are visible. Is there some syntax like
> ReportItems!TableRow7.property("Hidden")=' that I could use to determine the
> visibility state of a row?
> Thanks|||I have the same problem. Is there any on
"aCa" wrote:
> Did you find a solution on your problem? I have a simular problem.
> "PD" wrote:
> > Is it possible to determine whether or not another row in a table is visible?
> > The example is in a multiple detail row table, where I want to display a
> > 'header' row (really just another detail row), if any of the other detail
> > rows in the 'section' are visible. Is there some syntax like
> > ReportItems!TableRow7.property("Hidden")=' that I could use to determine the
> > visibility state of a row?
> >
> > Thanks
The example is in a multiple detail row table, where I want to display a
'header' row (really just another detail row), if any of the other detail
rows in the 'section' are visible. Is there some syntax like
ReportItems!TableRow7.property("Hidden")=' that I could use to determine the
visibility state of a row?
ThanksDid you find a solution on your problem? I have a simular problem.
"PD" wrote:
> Is it possible to determine whether or not another row in a table is visible?
> The example is in a multiple detail row table, where I want to display a
> 'header' row (really just another detail row), if any of the other detail
> rows in the 'section' are visible. Is there some syntax like
> ReportItems!TableRow7.property("Hidden")=' that I could use to determine the
> visibility state of a row?
> Thanks|||I have the same problem. Is there any on
"aCa" wrote:
> Did you find a solution on your problem? I have a simular problem.
> "PD" wrote:
> > Is it possible to determine whether or not another row in a table is visible?
> > The example is in a multiple detail row table, where I want to display a
> > 'header' row (really just another detail row), if any of the other detail
> > rows in the 'section' are visible. Is there some syntax like
> > ReportItems!TableRow7.property("Hidden")=' that I could use to determine the
> > visibility state of a row?
> >
> > Thanks
Subscribe to:
Comments (Atom)