Wednesday, March 28, 2012
Help getting linked server to work using multiprotocol!
Help gathering SQL info from all servers - noobie alert :)
finding all SQL servers in the company and coming up with a migration plan
to a single SQL cluster.
The issue at hand is that I'm hoping not to try to reinvent the wheel... is
there any script (vb, js, osql, something), that will query a list of known
SQL servers and output the database names and sizes to a text/csv file?
If it could go one step further and give me other info like usernames and
such that would be great too!
Thanks for any help you can give!
Aaron
Do a scan for anything listening on port 1433 and you should find all of
your SQL Servers. You will have to factor out anything that is nothing more
than local MSDE due to some application install.
Once you have the server names, you can connect to them with a trusted
connection (proxies your domain admin authority). The list of databases can
be found by using "select * from master.dbo.sysdatabases". The list of
users can be found by using "select * from master.dbo.sysxlogins".
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"livin" <livin@.@.cox.net> wrote in message
news:rDxuf.676$B93.22@.fed1read07...
> I'm a domain admin, not a SQL guy. Unfortunately I have been tasked with
> finding all SQL servers in the company and coming up with a migration plan
> to a single SQL cluster.
> The issue at hand is that I'm hoping not to try to reinvent the wheel...
> is there any script (vb, js, osql, something), that will query a list of
> known SQL servers and output the database names and sizes to a text/csv
> file?
> If it could go one step further and give me other info like usernames and
> such that would be great too!
> Thanks for any help you can give!
> Aaron
>
>
|||Hi
Look at SQLRecon on http://www.sqlsecurity.com/DesktopDe...aspx?tabid=26,
you will need the rights to access these servers to audit the database
details you may want to look at
http://www.sqlservercentral.com/scri...?scriptid=1589
John
"livin" <livin@.@.cox.net> wrote in message
news:rDxuf.676$B93.22@.fed1read07...
> I'm a domain admin, not a SQL guy. Unfortunately I have been tasked with
> finding all SQL servers in the company and coming up with a migration plan
> to a single SQL cluster.
> The issue at hand is that I'm hoping not to try to reinvent the wheel...
> is there any script (vb, js, osql, something), that will query a list of
> known SQL servers and output the database names and sizes to a text/csv
> file?
> If it could go one step further and give me other info like usernames and
> such that would be great too!
> Thanks for any help you can give!
> Aaron
>
>
|||Hi living,
just for fun I wrote a Application for you to gather this information
for all servers / all databases which put this information into an XML
file which can be easily used in Excel or some other program. If you
(or any other guy) is Interested in just drop me a mail, i=B4ll send it
to you, later on after finishing the building of my new portal
www.sqlserver2005.de I will put it up there. It uses the new SQL.SMO
and the .net 2.0 to collect this information.
HTH, Jens Suessmeyer.
|||You guys rock!
Thanks for helping out a noobie... I DL'd all the stuff and will have fun
with it this week!
Jens... sending you an email now.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1136325248.839414.309600@.g49g2000cwa.googlegr oups.com...
Hi living,
just for fun I wrote a Application for you to gather this information
for all servers / all databases which put this information into an XML
file which can be easily used in Excel or some other program. If you
(or any other guy) is Interested in just drop me a mail, ill send it
to you, later on after finishing the building of my new portal
www.sqlserver2005.de I will put it up there. It uses the new SQL.SMO
and the .net 2.0 to collect this information.
HTH, Jens Suessmeyer.
Help for Performing Updates Across Multiple Servers
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
Jothi
Don't fire triggers over a network. The latency added by doing this tends to
make such a solution unworkable. Plus if one of the servers go down the
trigger and sometimes the entire server will hang for up to 20 seconds
before failing.
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
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:7F1F3EA8-2719-4A0B-AC57-1F6AB44183CF@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
>
|||BTW - your best bet is using distributed transactions.
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
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:7F1F3EA8-2719-4A0B-AC57-1F6AB44183CF@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
>
sql
Friday, March 9, 2012
Help - Sql Server 2005 at a crawl on Win 2000 Server
I know nothing about running servers, however, I am in a position where I
not only programmed the app, programmed Sql Server 2005 for the app and but
now had to install Sql Server 2005 on a box that was given to me - Pentium I
V
with 40 gigs of storage and 512 MB RAM. I thought the machine would be
sufficient, however, it has been running (with no users using the app yet)
for three days and it's at a crawl. The OS is Windows 2000 Server.
Any suggestions as to what may a likely problem? (Looks like some kind of
memory leak to me.) I know more specifics are probably needed, but I don't
know what specifics to provide.
Any help will be greatly appreciated! These people expect this app to be up
and running by Thursday morning!!!!
--
SandySandy
http://www.microsoft.com/sql/prodin...qs/default.mspx
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:AA02BFDD-1790-4A30-BA64-DE8937B5172A@.microsoft.com...
> Hello -
> I know nothing about running servers, however, I am in a position where I
> not only programmed the app, programmed Sql Server 2005 for the app and
> but
> now had to install Sql Server 2005 on a box that was given to me - Pentium
> IV
> with 40 gigs of storage and 512 MB RAM. I thought the machine would be
> sufficient, however, it has been running (with no users using the app yet)
> for three days and it's at a crawl. The OS is Windows 2000 Server.
> Any suggestions as to what may a likely problem? (Looks like some kind of
> memory leak to me.) I know more specifics are probably needed, but I
> don't
> know what specifics to provide.
> Any help will be greatly appreciated! These people expect this app to be
> up
> and running by Thursday morning!!!!
> --
> Sandy|||Apart from what Uri has recommended - particuarly the bit about 1GB memory
recommended, there are a number of measurements you could take focusing
around CPU,RAM,Hard Disk . I haven't included Network as it sounds like
there are no users, which is the main cause for network bottlenecks.
Quick tests are: Pages/Sec memory, percentage disk time, average disk queue
length , buffer cache hit ratio .
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:AA02BFDD-1790-4A30-BA64-DE8937B5172A@.microsoft.com...
> Hello -
> I know nothing about running servers, however, I am in a position where I
> not only programmed the app, programmed Sql Server 2005 for the app and
but
> now had to install Sql Server 2005 on a box that was given to me - Pentium
IV
> with 40 gigs of storage and 512 MB RAM. I thought the machine would be
> sufficient, however, it has been running (with no users using the app yet)
> for three days and it's at a crawl. The OS is Windows 2000 Server.
> Any suggestions as to what may a likely problem? (Looks like some kind of
> memory leak to me.) I know more specifics are probably needed, but I
don't
> know what specifics to provide.
> Any help will be greatly appreciated! These people expect this app to be
up
> and running by Thursday morning!!!!
> --
> Sandy|||Hi Jack and Uri -
Thank you very much for your responses.
One further question: I looked through the System properties and noticed
that under Performance Options (System Properties -> Advanced -> Performance
Options - Optimize Performance), "Background" is checked. Should
"Applications" be checked there instead?
Again, any help will be greatly appreciated!
--
Sandy
"Sandy" wrote:
> Hello -
> I know nothing about running servers, however, I am in a position where I
> not only programmed the app, programmed Sql Server 2005 for the app and bu
t
> now had to install Sql Server 2005 on a box that was given to me - Pentium
IV
> with 40 gigs of storage and 512 MB RAM. I thought the machine would be
> sufficient, however, it has been running (with no users using the app yet)
> for three days and it's at a crawl. The OS is Windows 2000 Server.
> Any suggestions as to what may a likely problem? (Looks like some kind of
> memory leak to me.) I know more specifics are probably needed, but I don'
t
> know what specifics to provide.
> Any help will be greatly appreciated! These people expect this app to be
up
> and running by Thursday morning!!!!
> --
> Sandy
Wednesday, March 7, 2012
HELP - Problem with Replication
Hello,
I have transactional replication running between two SQL Servers. It's a push subscription and the distributor is on the local publisher.
Recently after running for a year the replication stopped working and we get the following two errors.
1 - The process could not bulk copy into the table "History_Rep"
String Data, Right Truncation. Error Number 22001.
-
2 - The process could not bulk copy into the table "History_Rep"
Unexpected EOF encountered in BCP Data-File. Error Number S1000
The data types between the source and target tables are identical. I've tried emptying my source tables so that BCP doesn't have to send anything to the subscriber but it still fails. Do I maybe need to purge my distribution database and BCP log files? How do I go about doing that.
Thanks in advance Guys,
Hugo
Hi Hugo,
Once you have emptied the source table, you would need to reinitialize your subscription and re-generate the snapshot in order to 'remove' the bad bcp data from the system. Having said that, I can't help but suspect that there is a schema (column size) mismatch between the publisher and the subscriber based on the error message that you posted. Is this on SQL2000 (my guess) or SQL 2005?
-Raymond
|||Raymond.
Yes, SQL Server 2000.
I've checked the column names, data types, sizes, three or four times - they're identical. How do I reinitialize the subscription - do I have to remove it and re-add it? Can it be done easily with Enterprise manager?
Thanks,
Hugo
|||Hi Hugo,
You should be able to just right-click on your subscription node in Enterprise Manager and choose reinitialize subscription on the context manual. Does the problematic table have multiple text\image columns at the end by any chance?
-Raymond
|||Hey Raymond,
When I right click on the subscription node, there's no option for re-initializing the subscription. I can't find that anywhere.
The table is all varchars and ints. No Text fields. There is a varchar 4000 second from the end where we put some XML data but that's it.
Thanks,
Hugo
|||Hi Hugo,
My thinking was probably mixed up by how the SQL2005 UI is laid out, here is what worked for me using the SQL2000 Enterprise Manager:
For a well-known push or pull subscription:
1) Publisher Server->Expand Replication Folder->Expand Publications Folder->Select publication whose subscription(s) you want to reinitialize, all your subscriptions should show up on the right plane->now right-click on the subscription you want to reinitialize on the right-plane and you should be able to choose "Reinitialize" from the context menu.
For an anonymous pull subscription:
2) Subscriber Server->Expand Replication Folder->Select Subscriptions Folder, all your pull subscriptions should show up on the right-plane-> Right-click on the subscription you want to reinitialize on the right-plane and you should be able to choose "Reinitialize" from the context menu.
Hope that helps,
-Raymond
|||Raymond,
Awesome - that worked. Not it says it's pending - why? How can I find out why it's pending - there's nothing in any logs that I can see.
Thanks for all your help,
Hugo
|||Hey Raymond,
I also started the snapshot agent and it ran okay. But, my subscription still says pending and when I try to start the distribution agent I get the same error.
Hugo
|||Have you restarted the snapshot agent?
-Raymond
|||Sorry for the hasty reply, were there any rows bulk copied out by the snapshot agent for the problematic table?
-Raymond
|||No need for apologies 
Well, it said four articles were generated - I'll investigate more.
Hugo
|||Hugo, something is not adding up correctly in your case (assuming that you have indeed deleted all data of the problematic table at the publisher), you can drop and re-add the subscription to make sure that it is not picking up a stale snapshot (I am out of ideas and my mind if that doesn't work). I am also wondering if you have different code pages between the publisher and the subscriber (or if you are using character mode snapshot) which would have caused the original bcp problem to happen. In any case, it would appear that the problem becomes extremely difficult to debug through forum postings, and so I would really appreciate if you can:
1) Open a support case with product support so they can gather sufficient information for us to investigation the issue further, or
2) Log a bug @. http://connect.microsoft.com/registration/registration.aspx with enough information (schema & data of problematic article) so we can investigate the issue properly.
Thanks much!
-Raymond
|||Raymond,
We solved the problem last night - thanks. There was a program pumping data into the problematic table and once we shut that program down and followed your advice it worked. I think that maybe I have character mode of the snapshot going on because that has never worked. Can I change that - how?
Thanks again,
Hugo