I have 2 sql boxes setup in a VM environment, with neither of them knowing each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder location (Where I copied the Snapshot files to) c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file 'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch' due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription replication....
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum security I need to give everyone when I try & do this exact same setup when using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but recalling giving Everyone Full Control may be a bad thing (But it's just this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder location (Where I copied the Snapshot files to) c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file 'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch' due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription replication....
|||Its read and list files and folders.
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:ub7HLgO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum
security I need to give everyone when I try & do this exact same setup when
using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt
Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way
from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but
recalling giving Everyone Full Control may be a bad thing (But it's just
this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing
each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as
follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot
location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder
location (Where I copied the Snapshot files to)
c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file
'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch'
due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription
replication....
|||THANX!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uGIyPwO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Its read and list files and folders.
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:ub7HLgO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum
security I need to give everyone when I try & do this exact same setup when
using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt
Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way
from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but
recalling giving Everyone Full Control may be a bad thing (But it's just
this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing
each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as
follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot
location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder
location (Where I copied the Snapshot files to)
c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file
'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch'
due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription
replication....
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
Choosing drives, transfert rate or IO/sec?
Hi,
what is most important to focus on when we choose a drive setup to support a
small datawarehouse?
(10GB and less)
IO/Sec
or
MB/s
?
(same question regarding the tempdb database storage)
except the SQLIO and SQLIOStress, there is any testing tool which simulate
DW loading process & DW query process?
thanks.
Jerome."Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uPa1X68wFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> what is most important to focus on when we choose a drive setup to support
> a small datawarehouse?
> (10GB and less)
> IO/Sec
> or
> MB/s
> ?
> (same question regarding the tempdb database storage)
>
Write throughput.
For a small datawarehouse, your memory cache should be a large percentage of
your database size. So for physical IO, sequential write operations (like
the log file and checkpointing) will predominate.
David|||Hi Jerome,
for 10GB or less of data....design a reasonable model and throw a bit
more hardware at it if it is not going ok......the time and money it
will cost to think about how to tune it is more than the cost of the
HW/SW to run it....
Of course, this kind of advice does NOT apply to large DWs where we do
spend time considering the performance in some great detail..
Best Regards
Peter Nolan
www.peternolan.com
what is most important to focus on when we choose a drive setup to support a
small datawarehouse?
(10GB and less)
IO/Sec
or
MB/s
?
(same question regarding the tempdb database storage)
except the SQLIO and SQLIOStress, there is any testing tool which simulate
DW loading process & DW query process?
thanks.
Jerome."Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uPa1X68wFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> what is most important to focus on when we choose a drive setup to support
> a small datawarehouse?
> (10GB and less)
> IO/Sec
> or
> MB/s
> ?
> (same question regarding the tempdb database storage)
>
Write throughput.
For a small datawarehouse, your memory cache should be a large percentage of
your database size. So for physical IO, sequential write operations (like
the log file and checkpointing) will predominate.
David|||Hi Jerome,
for 10GB or less of data....design a reasonable model and throw a bit
more hardware at it if it is not going ok......the time and money it
will cost to think about how to tune it is more than the cost of the
HW/SW to run it....
Of course, this kind of advice does NOT apply to large DWs where we do
spend time considering the performance in some great detail..
Best Regards
Peter Nolan
www.peternolan.com
Choosing drives, transfert rate or IO/sec?
Hi,
what is most important to focus on when we choose a drive setup to support a
small datawarehouse?
(10GB and less)
IO/Sec
or
MB/s
?
(same question regarding the tempdb database storage)
except the SQLIO and SQLIOStress, there is any testing tool which simulate
DW loading process & DW query process?
thanks.
Jerome.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uPa1X68wFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> what is most important to focus on when we choose a drive setup to support
> a small datawarehouse?
> (10GB and less)
> IO/Sec
> or
> MB/s
> ?
> (same question regarding the tempdb database storage)
>
Write throughput.
For a small datawarehouse, your memory cache should be a large percentage of
your database size. So for physical IO, sequential write operations (like
the log file and checkpointing) will predominate.
David
|||Hi Jerome,
for 10GB or less of data....design a reasonable model and throw a bit
more hardware at it if it is not going ok......the time and money it
will cost to think about how to tune it is more than the cost of the
HW/SW to run it....
Of course, this kind of advice does NOT apply to large DWs where we do
spend time considering the performance in some great detail..
Best Regards
Peter Nolan
www.peternolan.com
what is most important to focus on when we choose a drive setup to support a
small datawarehouse?
(10GB and less)
IO/Sec
or
MB/s
?
(same question regarding the tempdb database storage)
except the SQLIO and SQLIOStress, there is any testing tool which simulate
DW loading process & DW query process?
thanks.
Jerome.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uPa1X68wFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> what is most important to focus on when we choose a drive setup to support
> a small datawarehouse?
> (10GB and less)
> IO/Sec
> or
> MB/s
> ?
> (same question regarding the tempdb database storage)
>
Write throughput.
For a small datawarehouse, your memory cache should be a large percentage of
your database size. So for physical IO, sequential write operations (like
the log file and checkpointing) will predominate.
David
|||Hi Jerome,
for 10GB or less of data....design a reasonable model and throw a bit
more hardware at it if it is not going ok......the time and money it
will cost to think about how to tune it is more than the cost of the
HW/SW to run it....
Of course, this kind of advice does NOT apply to large DWs where we do
spend time considering the performance in some great detail..
Best Regards
Peter Nolan
www.peternolan.com
Choosing decimal symbol (comma, please! :))
Hello!
I'm having a slight problem figuring out how to make a field in an Access ADP accept input using comma as decimal symbol.
The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv). Both machines have regional settings pointing to Denmark and thus decimal symbol is ','.
When entering data through the interface made as forms in the ADP, I'm forced to enter '.' as decimal symbol for fields defined as DECIMAL(12,2) (for instance). ...This is actually a situation I could live with, if it weren't for the problems I face when trying to base calculus on these fields (doing some input checking).
Instead of resorting to making tedious code to handle the situation I know turn to you guys for help!
Regards ...and THANKS in advance! :)
Ult
NB: As an appetizer I can tell that if I multiply 1.00 with 1.00 I get 10000.00! ...So the problem is quite annoying, if you're not into opening a can of coding-with-a-distinct-feeling-of-this-is-not-quite-the-best-solution! :)http://www.dbforums.com/forumdisplay.php?forumid=84 .. post your question here|||Thanx for replying, Enigma!
I admit the problem has a touch of both worlds (Access/MSSQL) but believe/hope the problem has to do with MS SQL - all my 'pure' Access applications have none of the described problems.
I have of course not found any solution in the Access part of this forum.
U
...Still open for bright ideas! :)|||Btw, if anybody has a way to make insert-statements via Analyser accept decimals written with ',' instead of '.' it might just be part of the solution, I'm looking for!
...Or if anybody could 'kill this idea' by confirming it's not possible (at least without using 'format'-functions...)?|||The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv).
Oops .. I missed that (Did not read the entire post :( ) !!!! Let me think about it then !!
I'm having a slight problem figuring out how to make a field in an Access ADP accept input using comma as decimal symbol.
The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv). Both machines have regional settings pointing to Denmark and thus decimal symbol is ','.
When entering data through the interface made as forms in the ADP, I'm forced to enter '.' as decimal symbol for fields defined as DECIMAL(12,2) (for instance). ...This is actually a situation I could live with, if it weren't for the problems I face when trying to base calculus on these fields (doing some input checking).
Instead of resorting to making tedious code to handle the situation I know turn to you guys for help!
Regards ...and THANKS in advance! :)
Ult
NB: As an appetizer I can tell that if I multiply 1.00 with 1.00 I get 10000.00! ...So the problem is quite annoying, if you're not into opening a can of coding-with-a-distinct-feeling-of-this-is-not-quite-the-best-solution! :)http://www.dbforums.com/forumdisplay.php?forumid=84 .. post your question here|||Thanx for replying, Enigma!
I admit the problem has a touch of both worlds (Access/MSSQL) but believe/hope the problem has to do with MS SQL - all my 'pure' Access applications have none of the described problems.
I have of course not found any solution in the Access part of this forum.
U
...Still open for bright ideas! :)|||Btw, if anybody has a way to make insert-statements via Analyser accept decimals written with ',' instead of '.' it might just be part of the solution, I'm looking for!
...Or if anybody could 'kill this idea' by confirming it's not possible (at least without using 'format'-functions...)?|||The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv).
Oops .. I missed that (Did not read the entire post :( ) !!!! Let me think about it then !!
Choosing correct high availability setup
I have a client that wants me to start hosting their SQL and IIS for them.
They want to have the ability to have a "hot spare" in case the primary SQL
server fails. I have read and read until my eyes hurt about clustering and
mirroring and I am getting more confused on how to proceed.
My first question is, should I even be using IIS on the SQL box at all?
They have a web interface that gets it data from SQL.
My second question is, if I host their web site on a separate IIS box is
there any reason that I shouldn't go with database mirroring instead of any
other option?
Thanks for any help you can provide.
Marty
Never do both on the same box, read this -
http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
Server Clustering with make the entire node HA, SQL mirroring will make that
database HA. Which does your customer require?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>I have a client that wants me to start hosting their SQL and IIS for them.
> They want to have the ability to have a "hot spare" in case the primary
> SQL
> server fails. I have read and read until my eyes hurt about clustering
> and
> mirroring and I am getting more confused on how to proceed.
> My first question is, should I even be using IIS on the SQL box at all?
> They have a web interface that gets it data from SQL.
> My second question is, if I host their web site on a separate IIS box is
> there any reason that I shouldn't go with database mirroring instead of
> any
> other option?
> Thanks for any help you can provide.
> Marty
|||Well that is what I had always thought, but you see more and more people
consolidating these functions.
Basically there will be an Access database that arrives via FTP at the web
server, the SQL server will pick it up from a shared drive and import it.
That will happen about every 15 minutes or so. The same web server will host
a site that the client can access to see real time data that it pulls from
the SQL server. The only snag is that if the primary SQL server goes down
for some reason, IIS will not know that it has to get that data from the
backup server if I am using only mirroring.
"Rodney R. Fournier [MVP]" wrote:
> Never do both on the same box, read this -
> http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
> Server Clustering with make the entire node HA, SQL mirroring will make that
> database HA. Which does your customer require?
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>
>
|||Got it, use NLB for the IIS servers, Server Clustering for the backend
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...[vbcol=seagreen]
> Well that is what I had always thought, but you see more and more people
> consolidating these functions.
> Basically there will be an Access database that arrives via FTP at the web
> server, the SQL server will pick it up from a shared drive and import it.
> That will happen about every 15 minutes or so. The same web server will
> host
> a site that the client can access to see real time data that it pulls from
> the SQL server. The only snag is that if the primary SQL server goes down
> for some reason, IIS will not know that it has to get that data from the
> backup server if I am using only mirroring.
>
> "Rodney R. Fournier [MVP]" wrote:
|||NLB?
If I use clustering for the backend, will the web server be able to keep
serving the data from the SQL database to the web interface in the case of a
disaster? The web site will be looking for a specific instance of SQL
correct?
"Rodney R. Fournier [MVP]" wrote:
> Got it, use NLB for the IIS servers, Server Clustering for the backend
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...
>
>
|||Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
won't be supported/allowed with Windows Server 2008 Failover Clustering.
No matter how you do your SQL, IIS will need to know the instance name to
connect.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...[vbcol=seagreen]
> NLB?
> If I use clustering for the backend, will the web server be able to keep
> serving the data from the SQL database to the web interface in the case of
> a
> disaster? The web site will be looking for a specific instance of SQL
> correct?
> "Rodney R. Fournier [MVP]" wrote:
|||Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I am
very new on this subject. If one SQL box in the cluster fails, will the
other SQL box in that cluster pick up where the first left off and assume the
failed box's identity as far as IP address, DNS name, etc...?
Thanks for all of your help Rodney. I am glad there is a forum where people
like me can get real world help from people like yourself. Pat yourself on
the back man!!!
"Rodney R. Fournier [MVP]" wrote:
> Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
> won't be supported/allowed with Windows Server 2008 Failover Clustering.
> No matter how you do your SQL, IIS will need to know the instance name to
> connect.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...
>
>
|||It will failover to another node and continue on, but at a cost. SQL not
running on the node until the failover, so the databases goes through the
normal SQL startup, DB integrity check, roll back non-committed
transactions, roll forward committed ones, etc. Your application(s) have to
be cluster aware to handle the failover. Ping tests will fail during the
process, though maybe only 4-5 depending on the network, hardware, DB sizes,
etc.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...[vbcol=seagreen]
> Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I
> am
> very new on this subject. If one SQL box in the cluster fails, will the
> other SQL box in that cluster pick up where the first left off and assume
> the
> failed box's identity as far as IP address, DNS name, etc...?
> Thanks for all of your help Rodney. I am glad there is a forum where
> people
> like me can get real world help from people like yourself. Pat yourself
> on
> the back man!!!
> "Rodney R. Fournier [MVP]" wrote:
|||The only application that will depend on the SQL server would be IIS. How is
IIS going to react if one of the cluster nodes fail? I guess I would need to
point it to the alternate node's instance.
"Rodney R. Fournier [MVP]" wrote:
> It will failover to another node and continue on, but at a cost. SQL not
> running on the node until the failover, so the databases goes through the
> normal SQL startup, DB integrity check, roll back non-committed
> transactions, roll forward committed ones, etc. Your application(s) have to
> be cluster aware to handle the failover. Ping tests will fail during the
> process, though maybe only 4-5 depending on the network, hardware, DB sizes,
> etc.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...
>
>
|||Correct as Geoff "the SQL God and good buddy of mine" already stated.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:7871ABB2-D689-41A6-B9F7-30C5482A4EE3@.microsoft.com...[vbcol=seagreen]
> So the node that picks up truly is a "clone" of the failed one? Well then
> it
> sounds like I may be better off going with clustering than mirroring in my
> situation wouldn't you say?
> "Rodney R. Fournier [MVP]" wrote:
They want to have the ability to have a "hot spare" in case the primary SQL
server fails. I have read and read until my eyes hurt about clustering and
mirroring and I am getting more confused on how to proceed.
My first question is, should I even be using IIS on the SQL box at all?
They have a web interface that gets it data from SQL.
My second question is, if I host their web site on a separate IIS box is
there any reason that I shouldn't go with database mirroring instead of any
other option?
Thanks for any help you can provide.
Marty
Never do both on the same box, read this -
http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
Server Clustering with make the entire node HA, SQL mirroring will make that
database HA. Which does your customer require?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>I have a client that wants me to start hosting their SQL and IIS for them.
> They want to have the ability to have a "hot spare" in case the primary
> SQL
> server fails. I have read and read until my eyes hurt about clustering
> and
> mirroring and I am getting more confused on how to proceed.
> My first question is, should I even be using IIS on the SQL box at all?
> They have a web interface that gets it data from SQL.
> My second question is, if I host their web site on a separate IIS box is
> there any reason that I shouldn't go with database mirroring instead of
> any
> other option?
> Thanks for any help you can provide.
> Marty
|||Well that is what I had always thought, but you see more and more people
consolidating these functions.
Basically there will be an Access database that arrives via FTP at the web
server, the SQL server will pick it up from a shared drive and import it.
That will happen about every 15 minutes or so. The same web server will host
a site that the client can access to see real time data that it pulls from
the SQL server. The only snag is that if the primary SQL server goes down
for some reason, IIS will not know that it has to get that data from the
backup server if I am using only mirroring.
"Rodney R. Fournier [MVP]" wrote:
> Never do both on the same box, read this -
> http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
> Server Clustering with make the entire node HA, SQL mirroring will make that
> database HA. Which does your customer require?
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>
>
|||Got it, use NLB for the IIS servers, Server Clustering for the backend
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...[vbcol=seagreen]
> Well that is what I had always thought, but you see more and more people
> consolidating these functions.
> Basically there will be an Access database that arrives via FTP at the web
> server, the SQL server will pick it up from a shared drive and import it.
> That will happen about every 15 minutes or so. The same web server will
> host
> a site that the client can access to see real time data that it pulls from
> the SQL server. The only snag is that if the primary SQL server goes down
> for some reason, IIS will not know that it has to get that data from the
> backup server if I am using only mirroring.
>
> "Rodney R. Fournier [MVP]" wrote:
|||NLB?
If I use clustering for the backend, will the web server be able to keep
serving the data from the SQL database to the web interface in the case of a
disaster? The web site will be looking for a specific instance of SQL
correct?
"Rodney R. Fournier [MVP]" wrote:
> Got it, use NLB for the IIS servers, Server Clustering for the backend
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...
>
>
|||Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
won't be supported/allowed with Windows Server 2008 Failover Clustering.
No matter how you do your SQL, IIS will need to know the instance name to
connect.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...[vbcol=seagreen]
> NLB?
> If I use clustering for the backend, will the web server be able to keep
> serving the data from the SQL database to the web interface in the case of
> a
> disaster? The web site will be looking for a specific instance of SQL
> correct?
> "Rodney R. Fournier [MVP]" wrote:
|||Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I am
very new on this subject. If one SQL box in the cluster fails, will the
other SQL box in that cluster pick up where the first left off and assume the
failed box's identity as far as IP address, DNS name, etc...?
Thanks for all of your help Rodney. I am glad there is a forum where people
like me can get real world help from people like yourself. Pat yourself on
the back man!!!
"Rodney R. Fournier [MVP]" wrote:
> Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
> won't be supported/allowed with Windows Server 2008 Failover Clustering.
> No matter how you do your SQL, IIS will need to know the instance name to
> connect.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...
>
>
|||It will failover to another node and continue on, but at a cost. SQL not
running on the node until the failover, so the databases goes through the
normal SQL startup, DB integrity check, roll back non-committed
transactions, roll forward committed ones, etc. Your application(s) have to
be cluster aware to handle the failover. Ping tests will fail during the
process, though maybe only 4-5 depending on the network, hardware, DB sizes,
etc.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...[vbcol=seagreen]
> Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I
> am
> very new on this subject. If one SQL box in the cluster fails, will the
> other SQL box in that cluster pick up where the first left off and assume
> the
> failed box's identity as far as IP address, DNS name, etc...?
> Thanks for all of your help Rodney. I am glad there is a forum where
> people
> like me can get real world help from people like yourself. Pat yourself
> on
> the back man!!!
> "Rodney R. Fournier [MVP]" wrote:
|||The only application that will depend on the SQL server would be IIS. How is
IIS going to react if one of the cluster nodes fail? I guess I would need to
point it to the alternate node's instance.
"Rodney R. Fournier [MVP]" wrote:
> It will failover to another node and continue on, but at a cost. SQL not
> running on the node until the failover, so the databases goes through the
> normal SQL startup, DB integrity check, roll back non-committed
> transactions, roll forward committed ones, etc. Your application(s) have to
> be cluster aware to handle the failover. Ping tests will fail during the
> process, though maybe only 4-5 depending on the network, hardware, DB sizes,
> etc.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...
>
>
|||Correct as Geoff "the SQL God and good buddy of mine" already stated.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:7871ABB2-D689-41A6-B9F7-30C5482A4EE3@.microsoft.com...[vbcol=seagreen]
> So the node that picks up truly is a "clone" of the failed one? Well then
> it
> sounds like I may be better off going with clustering than mirroring in my
> situation wouldn't you say?
> "Rodney R. Fournier [MVP]" wrote:
Thursday, March 8, 2012
Chinese Characters
How do I have to setup my SQL Server in order to be able to introduce (save) Chinese Characters additionally?
If you look at the SQL Server Books On Line it gives you some advice on different collations. Here is a point to one section of the online help.
http://msdn2.microsoft.com/en-us/library/ms143508.aspx
Michelle
Chinese Characters
How do I have to setup my SQL Server in order to be able to introduce (save) Chinese Characters additionally?
If you look at the SQL Server Books On Line it gives you some advice on different collations. Here is a point to one section of the online help.
http://msdn2.microsoft.com/en-us/library/ms143508.aspx
Michelle
Child package ConnectionManager visibility
Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.
If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?
Thanks,
David
David,
I suspect you cannot do this. Connection managers can only be used in the package in which they reside - even if you're using a script task.
-Jamie
|||
Jamie,
Thanks for the response. I must say it is somewhat disappointing, though I think I have a work around for my situation. That said, I would still be interested in hearing a rationale for why this is the case. It seems to me like it breaks the container hierarchy paradigm.
David
|||Well I can see why you think this but remember that connection managers don't follow container scope like variables do so the same rules don't apply.
Having said that, there were plans to scope conenction managers to the container hierarchy but it couldn't be done in time (or something). Reading between the lines its something they (well...kirk Haselden) wanted to do but it was down the priority list.
-Jamie
|||
Thanks again, Jamie. Hopefully this will be implemented at some point in the future.
Related, I found the opposite to be true when dealing with log providers. Interestingly, the connections collection of the parent package DOES appear to be available to child package log providers (I have built a custom log provider in which this appears to be true). It strikes me as bizzare that the functionality I want is there for log providers, but not for the package tasks. That may be due, however, to gaps in my understanding of parent-child package relationships.
Friday, February 24, 2012
Checking the status of replicated transaction
I have an ETL process setup in my OLAP stagging area for a particular
reporting application. The stagging area consists of articles from several
publishers. I have a two step external process that runs, the first step
updates the status of detail items in each publication, the second step
synchronizes my transformation area from the changes in the replicated
transactional tables. My problem is how do I know that the distributor has
for each publication has posted all of the transaction to their respective
subscribers so when I run my synchronize transformation process I get ALL of
the updated data. This sounds like pretty common functionality, I'm just not
sure how to implement... Please help...?
Dan B
Dan,
you could have a 'master' job. This job has several steps - a step to run
each distribution/merge agent and a final step to run the transformation
process.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for your reply... I'm implementing the concept of a master job
already, I don't know how to run the distribution agent remotely in T-SQL, I
was thinking that this agent is already running...? Maybe I'm getting
confused... I thought that you could setup the distribution agent to
immediately update subscribers or queue the updates, I think that ours is
configured to immediately update so I guess I just need to know if there are
any pending transactions on the publisher... Does that make any sense...?
Dan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23wdeDX$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Dan,
> you could have a 'master' job. This job has several steps - a step to run
> each distribution/merge agent and a final step to run the transformation
> process.
> HTH,
> Paul Ibison
>
|||Dan,
to run the transformation task after all synchronizations have finished
you'll need to schedule the synchronizations on specific times rather than
continuously. To run the distribution agents, which is essentially a series
of jobs, you can run sp_start_job.
HTH,
Paul Ibison
reporting application. The stagging area consists of articles from several
publishers. I have a two step external process that runs, the first step
updates the status of detail items in each publication, the second step
synchronizes my transformation area from the changes in the replicated
transactional tables. My problem is how do I know that the distributor has
for each publication has posted all of the transaction to their respective
subscribers so when I run my synchronize transformation process I get ALL of
the updated data. This sounds like pretty common functionality, I'm just not
sure how to implement... Please help...?
Dan B
Dan,
you could have a 'master' job. This job has several steps - a step to run
each distribution/merge agent and a final step to run the transformation
process.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for your reply... I'm implementing the concept of a master job
already, I don't know how to run the distribution agent remotely in T-SQL, I
was thinking that this agent is already running...? Maybe I'm getting
confused... I thought that you could setup the distribution agent to
immediately update subscribers or queue the updates, I think that ours is
configured to immediately update so I guess I just need to know if there are
any pending transactions on the publisher... Does that make any sense...?
Dan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23wdeDX$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Dan,
> you could have a 'master' job. This job has several steps - a step to run
> each distribution/merge agent and a final step to run the transformation
> process.
> HTH,
> Paul Ibison
>
|||Dan,
to run the transformation task after all synchronizations have finished
you'll need to schedule the synchronizations on specific times rather than
continuously. To run the distribution agents, which is essentially a series
of jobs, you can run sp_start_job.
HTH,
Paul Ibison
Thursday, February 16, 2012
Checking for duplicates
Hi,
I have to run through a list of tables and check for duplicates. The
tables have no primary key setup but we expect that there should be only
1 record for a combination of fields.
So for example if I keep it simple and imagine there is a table called
customers with loads of fields and in here the uniqueness of each row is
defined by the fields salesrepid and customerid so there should be only
1 occurrence of eg salesrep xyz123 and customer abc123
What I need to do is somehow look at the table and check to make sure
that the above combination of salesrep xyz123 and customer abc123 only
occurs once and if it does oocur more than once then note it / report on it.
Any ideas on how to approach it? I imagine I could have a static table
containing the tables to be checked plus the fields of each table that
make up the uniquness. So if the tables for checking where in a table
called checkthese then in there I could have a field called tablename,
and then further rows called pkf1 (primary key field 1), pkf2, pkf3 and
so on and for the customers table the pkf1 would have value salesrepid
and pkf2 would have value of customerid.
I've probably not made much sense up there but basically what I need to
do is read a table that contains table names and fields that make up the
uniqueness of the table and then go and check those tables to make sure
that there are no more than one of each record that makes up the uniqueness.
any ideas?
tia,
toeYou could store the table names and column names in a table...or you could
just put them within a script or stored procedure.
To check for uniqueness on one table you could do something like this:
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1
You could also do something like this
IF EXISTS (
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1)
BEGIN
PRINT 'Duplicate found in table xyz'
END
...repeat
--
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:0z0fg.5150$O7.3856@.newsfe5-win.ntli.net...
> Hi,
> I have to run through a list of tables and check for duplicates. The
> tables have no primary key setup but we expect that there should be only 1
> record for a combination of fields.
> So for example if I keep it simple and imagine there is a table called
> customers with loads of fields and in here the uniqueness of each row is
> defined by the fields salesrepid and customerid so there should be only 1
> occurrence of eg salesrep xyz123 and customer abc123
> What I need to do is somehow look at the table and check to make sure that
> the above combination of salesrep xyz123 and customer abc123 only occurs
> once and if it does oocur more than once then note it / report on it.
> Any ideas on how to approach it? I imagine I could have a static table
> containing the tables to be checked plus the fields of each table that
> make up the uniquness. So if the tables for checking where in a table
> called checkthese then in there I could have a field called tablename, and
> then further rows called pkf1 (primary key field 1), pkf2, pkf3 and so on
> and for the customers table the pkf1 would have value salesrepid and pkf2
> would have value of customerid.
> I've probably not made much sense up there but basically what I need to do
> is read a table that contains table names and fields that make up the
> uniqueness of the table and then go and check those tables to make sure
> that there are no more than one of each record that makes up the
> uniqueness.
> any ideas?
> tia,
> toe|||Thanks Keith.
If I where to store the table name and columns to check in a table then
have you any pointers On how I would the go to the table(s) and check
that there is only 1 occurrence for each set of columns?
So if I have the table called 'checkthese' with the fields and data below
Fields Data
tablename: customers
pkf1: salesrepid
pkf2: customerid
How would I actaully look that up and then go to the customers table to
make sure there is only one row of each?
Cheers,
toe
Keith Kratochvil wrote:
> You could store the table names and column names in a table...or you could
> just put them within a script or stored procedure.
> To check for uniqueness on one table you could do something like this:
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1
> You could also do something like this
> IF EXISTS (
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1)
> BEGIN
> PRINT 'Duplicate found in table xyz'
> END
> ...repeat|||One method would be to cursor through the data within checkthese and build
and execute the appropriate sql statement.
You could also write a sql statement that would create the appropriate T-SQL
commands that you would have to execute on your own.
I will let you explore the cursor option a bit. The other option would look
something like this:
--your table
create table #checkthese (TableName varchar(128), PKcols varchar(2000))
insert into #checkthese (TableName, PKcols) VALUES ('customers',
'salesrepid, customerid')
insert into #checkthese (TableName, PKcols) VALUES ('SalesRep',
'salesrepid')
GO
--the select (run the output)
SELECT 'IF EXISTS (SELECT ' + PKcols + ' , COUNT(*) FROM ' + TableName + '
GROUP BY ' + PKcols + ' HAVING COUNT(*) > 1 )
BEGIN
PRINT ''Duplicates found within '' + TableName
END' + char(13) + char(10) + 'GO'
from #checkthese
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:447CBDE8.9030902@.hotmail.com...
> Thanks Keith.
> If I where to store the table name and columns to check in a table then
> have you any pointers On how I would the go to the table(s) and check that
> there is only 1 occurrence for each set of columns?
> So if I have the table called 'checkthese' with the fields and data below
> Fields Data tablename: customers
> pkf1: salesrepid
> pkf2: customerid
> How would I actaully look that up and then go to the customers table to
> make sure there is only one row of each?
> Cheers,
> toe
>
> Keith Kratochvil wrote:
I have to run through a list of tables and check for duplicates. The
tables have no primary key setup but we expect that there should be only
1 record for a combination of fields.
So for example if I keep it simple and imagine there is a table called
customers with loads of fields and in here the uniqueness of each row is
defined by the fields salesrepid and customerid so there should be only
1 occurrence of eg salesrep xyz123 and customer abc123
What I need to do is somehow look at the table and check to make sure
that the above combination of salesrep xyz123 and customer abc123 only
occurs once and if it does oocur more than once then note it / report on it.
Any ideas on how to approach it? I imagine I could have a static table
containing the tables to be checked plus the fields of each table that
make up the uniquness. So if the tables for checking where in a table
called checkthese then in there I could have a field called tablename,
and then further rows called pkf1 (primary key field 1), pkf2, pkf3 and
so on and for the customers table the pkf1 would have value salesrepid
and pkf2 would have value of customerid.
I've probably not made much sense up there but basically what I need to
do is read a table that contains table names and fields that make up the
uniqueness of the table and then go and check those tables to make sure
that there are no more than one of each record that makes up the uniqueness.
any ideas?
tia,
toeYou could store the table names and column names in a table...or you could
just put them within a script or stored procedure.
To check for uniqueness on one table you could do something like this:
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1
You could also do something like this
IF EXISTS (
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1)
BEGIN
PRINT 'Duplicate found in table xyz'
END
...repeat
--
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:0z0fg.5150$O7.3856@.newsfe5-win.ntli.net...
> Hi,
> I have to run through a list of tables and check for duplicates. The
> tables have no primary key setup but we expect that there should be only 1
> record for a combination of fields.
> So for example if I keep it simple and imagine there is a table called
> customers with loads of fields and in here the uniqueness of each row is
> defined by the fields salesrepid and customerid so there should be only 1
> occurrence of eg salesrep xyz123 and customer abc123
> What I need to do is somehow look at the table and check to make sure that
> the above combination of salesrep xyz123 and customer abc123 only occurs
> once and if it does oocur more than once then note it / report on it.
> Any ideas on how to approach it? I imagine I could have a static table
> containing the tables to be checked plus the fields of each table that
> make up the uniquness. So if the tables for checking where in a table
> called checkthese then in there I could have a field called tablename, and
> then further rows called pkf1 (primary key field 1), pkf2, pkf3 and so on
> and for the customers table the pkf1 would have value salesrepid and pkf2
> would have value of customerid.
> I've probably not made much sense up there but basically what I need to do
> is read a table that contains table names and fields that make up the
> uniqueness of the table and then go and check those tables to make sure
> that there are no more than one of each record that makes up the
> uniqueness.
> any ideas?
> tia,
> toe|||Thanks Keith.
If I where to store the table name and columns to check in a table then
have you any pointers On how I would the go to the table(s) and check
that there is only 1 occurrence for each set of columns?
So if I have the table called 'checkthese' with the fields and data below
Fields Data
tablename: customers
pkf1: salesrepid
pkf2: customerid
How would I actaully look that up and then go to the customers table to
make sure there is only one row of each?
Cheers,
toe
Keith Kratochvil wrote:
> You could store the table names and column names in a table...or you could
> just put them within a script or stored procedure.
> To check for uniqueness on one table you could do something like this:
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1
> You could also do something like this
> IF EXISTS (
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1)
> BEGIN
> PRINT 'Duplicate found in table xyz'
> END
> ...repeat|||One method would be to cursor through the data within checkthese and build
and execute the appropriate sql statement.
You could also write a sql statement that would create the appropriate T-SQL
commands that you would have to execute on your own.
I will let you explore the cursor option a bit. The other option would look
something like this:
--your table
create table #checkthese (TableName varchar(128), PKcols varchar(2000))
insert into #checkthese (TableName, PKcols) VALUES ('customers',
'salesrepid, customerid')
insert into #checkthese (TableName, PKcols) VALUES ('SalesRep',
'salesrepid')
GO
--the select (run the output)
SELECT 'IF EXISTS (SELECT ' + PKcols + ' , COUNT(*) FROM ' + TableName + '
GROUP BY ' + PKcols + ' HAVING COUNT(*) > 1 )
BEGIN
PRINT ''Duplicates found within '' + TableName
END' + char(13) + char(10) + 'GO'
from #checkthese
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:447CBDE8.9030902@.hotmail.com...
> Thanks Keith.
> If I where to store the table name and columns to check in a table then
> have you any pointers On how I would the go to the table(s) and check that
> there is only 1 occurrence for each set of columns?
> So if I have the table called 'checkthese' with the fields and data below
> Fields Data tablename: customers
> pkf1: salesrepid
> pkf2: customerid
> How would I actaully look that up and then go to the customers table to
> make sure there is only one row of each?
> Cheers,
> toe
>
> Keith Kratochvil wrote:
Friday, February 10, 2012
Check Sysobjects Error during Snapshot
During the snapshot generation, I get this eror while the snapshot agent is
generating the schema script.
Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
snapshot/transactional replications.
On my sole publisher on its own server, I have 4 publications, two per
published databases (A and B), each publication is slightly different (row
filtering).
One distributor on a different server, One subscriber (1) on this same server.
Another subscriber(2) on a third server.
I can replicate database A publication A1 to to Subscriber1 and publication
A2 to subscriber 2 without a problem.
Next, I can replicate database b publication B2 to Subscriber2 without a
problem.
When I try running the snapshot for database b publication B1 to Subscriber1
it start running along, and then gets the checksysobjects error. There are no
other snapshots running concurrently and all other agents are idle.
Any ideas here?
can you post the entire error message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> During the snapshot generation, I get this eror while the snapshot agent
is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same
server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and
publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to
Subscriber1
> it start running along, and then gets the checksysobjects error. There are
no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
|||Here are the Snapshot Agent Error Details (I substituted the actual
servername with "<My Server Name>":
'. Check sysobjects.
(Source: <My Server Name>(Data source); Error number: 2501)
"Hilary Cotter" wrote:
> can you post the entire error message here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
> news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> is
> server.
> publication
> Subscriber1
> no
>
>
|||does this post help?
http://groups.google.com/groups?hl=e...GP10. phx.gbl
It seems that when you apply the snapshot one of the objects might already
exist on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:E7E74D1F-C27D-4E4E-B767-0F57A7689D98@.microsoft.com...[vbcol=seagreen]
> Here are the Snapshot Agent Error Details (I substituted the actual
> servername with "<My Server Name>":
> '. Check sysobjects.
> (Source: <My Server Name>(Data source); Error number: 2501)
>
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
agent[vbcol=seagreen]
(row[vbcol=seagreen]
a[vbcol=seagreen]
are[vbcol=seagreen]
|||I used the MS Online Assisted Report to help me resolve the problem very
quickly (their initial response within 24hrs pointed me in the right
direction).
Basically, each article's filter name and view name must be DATABASE-UNIQUE
in addition to publication-unique. The names are used to create stored
procedures and views in the publication database. If you create only one
publication there is no problem, but if you create two publications, then
there is name overlap and then you'll probably see the problem during
snapshot generation of the 2nd publication.
So when calling sp_articlefilter and sp_articleview, make sure that
@.filter_name and @.view_name are database-unique!
I hope this saves somebody else from the headache I went through.
- Cynthia
"Need more Zzzz" wrote:
> During the snapshot generation, I get this eror while the snapshot agent is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to Subscriber1
> it start running along, and then gets the checksysobjects error. There are no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
generating the schema script.
Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
snapshot/transactional replications.
On my sole publisher on its own server, I have 4 publications, two per
published databases (A and B), each publication is slightly different (row
filtering).
One distributor on a different server, One subscriber (1) on this same server.
Another subscriber(2) on a third server.
I can replicate database A publication A1 to to Subscriber1 and publication
A2 to subscriber 2 without a problem.
Next, I can replicate database b publication B2 to Subscriber2 without a
problem.
When I try running the snapshot for database b publication B1 to Subscriber1
it start running along, and then gets the checksysobjects error. There are no
other snapshots running concurrently and all other agents are idle.
Any ideas here?
can you post the entire error message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> During the snapshot generation, I get this eror while the snapshot agent
is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same
server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and
publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to
Subscriber1
> it start running along, and then gets the checksysobjects error. There are
no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
|||Here are the Snapshot Agent Error Details (I substituted the actual
servername with "<My Server Name>":
'. Check sysobjects.
(Source: <My Server Name>(Data source); Error number: 2501)
"Hilary Cotter" wrote:
> can you post the entire error message here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
> news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> is
> server.
> publication
> Subscriber1
> no
>
>
|||does this post help?
http://groups.google.com/groups?hl=e...GP10. phx.gbl
It seems that when you apply the snapshot one of the objects might already
exist on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:E7E74D1F-C27D-4E4E-B767-0F57A7689D98@.microsoft.com...[vbcol=seagreen]
> Here are the Snapshot Agent Error Details (I substituted the actual
> servername with "<My Server Name>":
> '. Check sysobjects.
> (Source: <My Server Name>(Data source); Error number: 2501)
>
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
agent[vbcol=seagreen]
(row[vbcol=seagreen]
a[vbcol=seagreen]
are[vbcol=seagreen]
|||I used the MS Online Assisted Report to help me resolve the problem very
quickly (their initial response within 24hrs pointed me in the right
direction).
Basically, each article's filter name and view name must be DATABASE-UNIQUE
in addition to publication-unique. The names are used to create stored
procedures and views in the publication database. If you create only one
publication there is no problem, but if you create two publications, then
there is name overlap and then you'll probably see the problem during
snapshot generation of the 2nd publication.
So when calling sp_articlefilter and sp_articleview, make sure that
@.filter_name and @.view_name are database-unique!
I hope this saves somebody else from the headache I went through.
- Cynthia
"Need more Zzzz" wrote:
> During the snapshot generation, I get this eror while the snapshot agent is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to Subscriber1
> it start running along, and then gets the checksysobjects error. There are no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
check sql licence
Hi, May I know how to check what is my current sql licence installed? I've
learnt that in control panel, there is a SQL Licence setup icon which I can
check. But some of my MSSQL server don't have this icon. Can you help?
Thanks
andrewTry to run this:
select serverproperty('LicenseType')
, serverproperty('NumLicenses')
"andrew" wrote:
> Hi, May I know how to check what is my current sql licence installed? I'v
e
> learnt that in control panel, there is a SQL Licence setup icon which I ca
n
> check. But some of my MSSQL server don't have this icon. Can you help?
> Thanks
> andrew|||Hi,
The result is:
disabled, null
May I know how can I change the values?
"Andre Vigneau" wrote:
[vbcol=seagreen]
> Try to run this:
> select serverproperty('LicenseType')
> , serverproperty('NumLicenses')
>
> "andrew" wrote:
>|||andrew wrote:
> Hi,
> The result is:
> disabled, null
> May I know how can I change the values?
>
LicenceType is not "implemented" in a reliable way. There was post about
this same problem a couple weeks ago. Trying giving the ng a search on
Google.
David Gugick
Imceda Software
www.imceda.com
learnt that in control panel, there is a SQL Licence setup icon which I can
check. But some of my MSSQL server don't have this icon. Can you help?
Thanks
andrewTry to run this:
select serverproperty('LicenseType')
, serverproperty('NumLicenses')
"andrew" wrote:
> Hi, May I know how to check what is my current sql licence installed? I'v
e
> learnt that in control panel, there is a SQL Licence setup icon which I ca
n
> check. But some of my MSSQL server don't have this icon. Can you help?
> Thanks
> andrew|||Hi,
The result is:
disabled, null
May I know how can I change the values?
"Andre Vigneau" wrote:
[vbcol=seagreen]
> Try to run this:
> select serverproperty('LicenseType')
> , serverproperty('NumLicenses')
>
> "andrew" wrote:
>|||andrew wrote:
> Hi,
> The result is:
> disabled, null
> May I know how can I change the values?
>
LicenceType is not "implemented" in a reliable way. There was post about
this same problem a couple weeks ago. Trying giving the ng a search on
Google.
David Gugick
Imceda Software
www.imceda.com
check sql licence
Hi, May I know how to check what is my current sql licence installed? I've
learnt that in control panel, there is a SQL Licence setup icon which I can
check. But some of my MSSQL server don't have this icon. Can you help?
Thanks
andrew
Try to run this:
select serverproperty('LicenseType')
, serverproperty('NumLicenses')
"andrew" wrote:
> Hi, May I know how to check what is my current sql licence installed? I've
> learnt that in control panel, there is a SQL Licence setup icon which I can
> check. But some of my MSSQL server don't have this icon. Can you help?
> Thanks
> andrew
|||Hi,
The result is:
disabled, null
May I know how can I change the values?
"Andre Vigneau" wrote:
[vbcol=seagreen]
> Try to run this:
> select serverproperty('LicenseType')
> , serverproperty('NumLicenses')
>
> "andrew" wrote:
|||andrew wrote:
> Hi,
> The result is:
> disabled, null
> May I know how can I change the values?
>
LicenceType is not "implemented" in a reliable way. There was post about
this same problem a couple weeks ago. Trying giving the ng a search on
Google.
David Gugick
Imceda Software
www.imceda.com
learnt that in control panel, there is a SQL Licence setup icon which I can
check. But some of my MSSQL server don't have this icon. Can you help?
Thanks
andrew
Try to run this:
select serverproperty('LicenseType')
, serverproperty('NumLicenses')
"andrew" wrote:
> Hi, May I know how to check what is my current sql licence installed? I've
> learnt that in control panel, there is a SQL Licence setup icon which I can
> check. But some of my MSSQL server don't have this icon. Can you help?
> Thanks
> andrew
|||Hi,
The result is:
disabled, null
May I know how can I change the values?
"Andre Vigneau" wrote:
[vbcol=seagreen]
> Try to run this:
> select serverproperty('LicenseType')
> , serverproperty('NumLicenses')
>
> "andrew" wrote:
|||andrew wrote:
> Hi,
> The result is:
> disabled, null
> May I know how can I change the values?
>
LicenceType is not "implemented" in a reliable way. There was post about
this same problem a couple weeks ago. Trying giving the ng a search on
Google.
David Gugick
Imceda Software
www.imceda.com
check sql licence
Hi, May I know how to check what is my current sql licence installed? I've
learnt that in control panel, there is a SQL Licence setup icon which I can
check. But some of my MSSQL server don't have this icon. Can you help?
Thanks
andrewTry to run this:
select serverproperty('LicenseType')
, serverproperty('NumLicenses')
"andrew" wrote:
> Hi, May I know how to check what is my current sql licence installed? I've
> learnt that in control panel, there is a SQL Licence setup icon which I can
> check. But some of my MSSQL server don't have this icon. Can you help?
> Thanks
> andrew|||Hi,
The result is:
disabled, null
May I know how can I change the values?
"Andre Vigneau" wrote:
> Try to run this:
> select serverproperty('LicenseType')
> , serverproperty('NumLicenses')
>
> "andrew" wrote:
> > Hi, May I know how to check what is my current sql licence installed? I've
> > learnt that in control panel, there is a SQL Licence setup icon which I can
> > check. But some of my MSSQL server don't have this icon. Can you help?
> >
> > Thanks
> > andrew|||andrew wrote:
> Hi,
> The result is:
> disabled, null
> May I know how can I change the values?
>
LicenceType is not "implemented" in a reliable way. There was post about
this same problem a couple weeks ago. Trying giving the ng a search on
Google.
--
David Gugick
Imceda Software
www.imceda.com
learnt that in control panel, there is a SQL Licence setup icon which I can
check. But some of my MSSQL server don't have this icon. Can you help?
Thanks
andrewTry to run this:
select serverproperty('LicenseType')
, serverproperty('NumLicenses')
"andrew" wrote:
> Hi, May I know how to check what is my current sql licence installed? I've
> learnt that in control panel, there is a SQL Licence setup icon which I can
> check. But some of my MSSQL server don't have this icon. Can you help?
> Thanks
> andrew|||Hi,
The result is:
disabled, null
May I know how can I change the values?
"Andre Vigneau" wrote:
> Try to run this:
> select serverproperty('LicenseType')
> , serverproperty('NumLicenses')
>
> "andrew" wrote:
> > Hi, May I know how to check what is my current sql licence installed? I've
> > learnt that in control panel, there is a SQL Licence setup icon which I can
> > check. But some of my MSSQL server don't have this icon. Can you help?
> >
> > Thanks
> > andrew|||andrew wrote:
> Hi,
> The result is:
> disabled, null
> May I know how can I change the values?
>
LicenceType is not "implemented" in a reliable way. There was post about
this same problem a couple weeks ago. Trying giving the ng a search on
Google.
--
David Gugick
Imceda Software
www.imceda.com
Subscribe to:
Posts (Atom)