Help! I am in a jam and need help suggestions and ideas ASAP!
After performing an in-service operating system upgrade from Win 2000 to Win
2003 both of our SQL Server applications are behaving very badly. The SQL
applications are either giving us errors or unable to run at all. Veritas
Backup Exec is unablel to b
rowse the SQL Database and after working with them for several hours they ar
e stumped.
Has anyone else heard of this issue? Would a reinstall of SQL SP3a fix thi
s mess? Or is the best fix going to be an all night server format and reins
tall of Windows 2003, SQL 2000, and all of the applications?
Thanks! in advance for anything you can give me.
NC Beach BumReapply SP3a.
Uninstall Veritas agent.
Reinstall Veritas agent.
What other application connectivity errors are you getting?
Check your alias settings in Client Network Utility. You may need remove
any alias settings that force named pipes connection to a local instance. I
have seen some problems with SQL Agent on those circumstances
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"NC Beach Bum" <anonymous@.discussions.microsoft.com> wrote in message
news:66808E92-5A64-45AB-91C0-25321EC56C6C@.microsoft.com...
> Help! I am in a jam and need help suggestions and ideas ASAP!
> After performing an in-service operating system upgrade from Win 2000 to
Win 2003 both of our SQL Server applications are behaving very badly. The
SQL applications are either giving us errors or unable to run at all.
Veritas Backup Exec is unablel to browse the SQL Database and after working
with them for several hours they are stumped.
> Has anyone else heard of this issue? Would a reinstall of SQL SP3a fix
this mess? Or is the best fix going to be an all night server format and
reinstall of Windows 2003, SQL 2000, and all of the applications?
> Thanks! in advance for anything you can give me.
> NC Beach Bum
>|||Jeff,
THANKS! for your quick reply.
What I see in the applications are unable to make connections.
The web application is getting this error "Method '~' of object '~' failed."
It looks like the SQL Connections have been trashed.
One Accounting product is getting an error about not being able to log onto
the database.
Another Accounting product on the same SQL Server is not allowing us to post
data to the database.
I can live with the Veritas issues but the Accounting folks are getting a bi
t nasty.
Have you heard of this before and how confident are you that SQL SP3a will p
ut us back to normal?
NC Beach Bum|||"NC Beach Bum" <anonymous@.discussions.microsoft.com> wrote in message
news:DF20331F-C553-48C8-8D31-9A5745A81F24@.microsoft.com...
> Jeff,
> THANKS! for your quick reply.
> What I see in the applications are unable to make connections.
Try to add this in your connection string:
"Network Library=DBMSSOCN"|||Geoff,
I am going to try and update MDAC and the SQL Service Pack
1) Run the MDAC 2.8 install
2) Run the SQL SP3a install
Does this make sense to you and is this the order you would suggest?
Thanks! NC Beach Bum|||That is the correct order. It shouldn't hurt.
You should start checking connectivity an dsee what does and doesn't
connect. Start with IP connectivity (Ping) and then work on SQL
connectivity with query analyzer. Try local and remote from Query Analyzer
and see what happens. Find out what connectivity works and what does not.
If you can connect locally, what does 'SELECT @.@.Version' return? Windows
2003 will disable the IP stack for SQL without SP3a to protect from the
Slammer worm.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"NC Beach Bum" <anonymous@.discussions.microsoft.com> wrote in message
news:59DBF52D-F7AE-4ACD-81A4-639C8E7E4D95@.microsoft.com...
> Geoff,
> I am going to try and update MDAC and the SQL Service Pack
> 1) Run the MDAC 2.8 install
> 2) Run the SQL SP3a install
> Does this make sense to you and is this the order you would suggest?
> Thanks! NC Beach Bum
Showing posts with label ideas. Show all posts
Showing posts with label ideas. Show all posts
Monday, March 26, 2012
Monday, February 27, 2012
Help woth stored procedure!
Hi,
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi
Subscribe to:
Posts (Atom)