Reposition Subtotals: February 2012

Saturday, February 25, 2012

REPOST:Cannot find Snapshot 2005

Last week I made the following post...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Running Win 2003 & SQL 2005 on both machines.
I created a publication on serverA. I subscribed to it on serverB. I
received the error...
Command attempted:
E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
FILES\20061206113840\APVENDP_2.sch
(Transaction sequence number: 0x000049A200000088002A00000000, Command
ID: 16)
Error messages:
The device is not ready.
Thinking that replication was attempting to access the snapshot
locally, I changed to the location of the snapshot in the subscription
to \\serverA\E$\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
FILES\20061206113840\APVENDP_2.sch
Note that the location of the snapshot is ....repldata\unc\unc\... in
the error message. I browsed to serverA & there is no
....repldata\unc\unc folder, just a ...repldata\unc folder, then the
folder R99S-SQLA_DBMOTO_AS400 MASTER FILES.
Next I changed the location of the snapshot to reflect where the
subscription was looking ...repldata\unc\unc & now the subscription is
initialized & receiving transactions from the publisher.
With all that said....does anyone know why I had to jump through these
back flips to get replication to work when I did not deviate from the
defaults?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hilary posted the following
It sounds like your default snapshot share location was not specified
correctly. I think it was the double unc which gave you this error
message.
I take it this is a pull, if that is the case you don't need to use a
UNC.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This tells me what the problem is but does not give me any directing in
resolution.
How do I fix this? I do not want to have to do this for every
publication/subscription I create.
AHIA,
Larry...
Larry, you can change the default snapshot location for your publisher from
SSMS by Right-click Replication folder->Distributor Properties->Select the
Publishers page on the left pane->Clink on the ... button for the publisher
you want to change on the right pane->Modify the Default Snapshot Folder
field in the pop-up dialogue box and hit Ok->Hit Ok *again* on the
Distributor Properties dialog box to commit the change. Note that the change
will not be picked up until the next snapshot being generated.
Hope that helps,
-Raymond
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1166644241.462462.124840@.73g2000cwn.googlegro ups.com...
> Last week I made the following post...
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Running Win 2003 & SQL 2005 on both machines.
>
> I created a publication on serverA. I subscribed to it on serverB. I
> received the error...
> Command attempted:
> E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
> FILES\20061206113840\APVENDP_2.sch
> (Transaction sequence number: 0x000049A200000088002A00000000, Command
> ID: 16)
>
> Error messages:
> The device is not ready.
>
> Thinking that replication was attempting to access the snapshot
> locally, I changed to the location of the snapshot in the subscription
> to \\serverA\E$\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\REPLDATA\unc\unc\R99S-SQLA_DBMOTO_AS400 MASTER
> FILES\20061206113840\APVENDP_2.sch
>
> Note that the location of the snapshot is ....repldata\unc\unc\... in
>
> the error message. I browsed to serverA & there is no
> ...repldata\unc\unc folder, just a ...repldata\unc folder, then the
> folder R99S-SQLA_DBMOTO_AS400 MASTER FILES.
>
> Next I changed the location of the snapshot to reflect where the
> subscription was looking ...repldata\unc\unc & now the subscription is
>
> initialized & receiving transactions from the publisher.
>
> With all that said....does anyone know why I had to jump through these
>
> back flips to get replication to work when I did not deviate from the
> defaults?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Hilary posted the following
> It sounds like your default snapshot share location was not specified
> correctly. I think it was the double unc which gave you this error
> message.
> I take it this is a pull, if that is the case you don't need to use a
> UNC.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> This tells me what the problem is but does not give me any directing in
> resolution.
>
> How do I fix this? I do not want to have to do this for every
> publication/subscription I create.
>
> AHIA,
> Larry...
>

Repost: Strange language mix

... Hmmmm. Either my brandnew OE has some problems in showing all
news/threads or.....
This is a repost of an article I wrote here and sent today 21:17. I cannot
find it... This is not the first time this happens to me, so I doubt to
have a news client problem...
Nevermind. Here the initial posting again.
Did anybody of you also encounter this strange language mixing problem? I'm
facing this from time to time, mostly when one component of my webserver had
to be restarted (e.g. IIS). Then RS appears either in plain english ("report
is beeing generated") or in a mixed form (german and english). If IIS is
restarted again everything is fine ...
Additional question: What is responsible for the language in which
subscription page is displayed?
I have uploaded two screenshots from the subscription page. One - ok.jpg -
shows the correct language layout and runs locally on a XP machine. The
latter - not_ok.jpg - runs on my web server and is not correct. This is
independent from the. a.m. restart situation.
http://www.ipviasky.net/Roland/rs/ok.JPG
http://www.ipviasky.net/Roland/rs/not_ok.JPG
The red dots show the problem zones. Both machines have GERMAN as "native"
language.
rolandThe language displayed is based on the accept language specified by the
browser header. We have not seen the problems you described. The
subscription information is a control that is associated with the delivery
extension. I'll see if anyone here can figure out how this could happen.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roland" <nomail@.spamprotect.com> wrote in message
news:ei6OrYVXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> ... Hmmmm. Either my brandnew OE has some problems in showing all
> news/threads or.....
> This is a repost of an article I wrote here and sent today 21:17. I cannot
> find it... This is not the first time this happens to me, so I doubt to
> have a news client problem...
> Nevermind. Here the initial posting again.
> Did anybody of you also encounter this strange language mixing problem?
> I'm
> facing this from time to time, mostly when one component of my webserver
> had
> to be restarted (e.g. IIS). Then RS appears either in plain english
> ("report
> is beeing generated") or in a mixed form (german and english). If IIS is
> restarted again everything is fine ...
> Additional question: What is responsible for the language in which
> subscription page is displayed?
> I have uploaded two screenshots from the subscription page. One - ok.jpg -
> shows the correct language layout and runs locally on a XP machine. The
> latter - not_ok.jpg - runs on my web server and is not correct. This is
> independent from the. a.m. restart situation.
> http://www.ipviasky.net/Roland/rs/ok.JPG
> http://www.ipviasky.net/Roland/rs/not_ok.JPG
> The red dots show the problem zones. Both machines have GERMAN as "native"
> language.
> roland
>|||Brian, thanks for your answer.
> We have not seen the problems you described.
But you have seen this in my shots, right? OK, I'll wait for your results.
roland

repost: SqlServer 2005 => SMS => Object Explorer => Programability Filter questio

SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
Contains _aaa_ shows all stored procs that include the string _aaa_, works
great.
SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
Contains _yyy_ shows all stored procs that include the string _yyy_
How does one structure the "contains" filter to show procs that contain both
_aaa_ AND _yyy_ ?
Still don't have a good feeling for Sql Server tools expressions.
Any help would be greatly appreciated !!!
Barry
in Oregon
I don't think that there is any way to combine filter criteria.
You could execute the following query:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ( ROUTINE_NAME LIKE '%_aaa_%'
OR ROUTINE_NAME LIKE '%_yyy_%'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in message news:%23tuas6o$GHA.4024@.TK2MSFTNGP04.phx.gbl...
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _aaa_ shows all stored procs that include the string _aaa_, works
> great.
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _yyy_ shows all stored procs that include the string _yyy_
> How does one structure the "contains" filter to show procs that contain both
> _aaa_ AND _yyy_ ?
> Still don't have a good feeling for Sql Server tools expressions.
> Any help would be greatly appreciated !!!
> Barry
> in Oregon
>
|||I don't think that there is any way to combine filter criteria.
You could execute the following query:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ( ROUTINE_NAME LIKE '%_aaa_%'
OR ROUTINE_NAME LIKE '%_yyy_%'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in message news:%23tuas6o$GHA.4024@.TK2MSFTNGP04.phx.gbl...
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _aaa_ shows all stored procs that include the string _aaa_, works
> great.
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _yyy_ shows all stored procs that include the string _yyy_
> How does one structure the "contains" filter to show procs that contain both
> _aaa_ AND _yyy_ ?
> Still don't have a good feeling for Sql Server tools expressions.
> Any help would be greatly appreciated !!!
> Barry
> in Oregon
>
|||frostbb (barry.b.frost@.remove-this-spam-filter.state.or.us) writes:
> SqlServer 2005 => SMS => Object Explorer => Programability Filter =>
> Filter: Contains _aaa_ shows all stored procs that include the string
> _aaa_, works great.
> SqlServer 2005 => SMS => Object Explorer => Programability Filter =>
> Filter: Contains _yyy_ shows all stored procs that include the string
> _yyy_
> How does one structure the "contains" filter to show procs that contain
> both _aaa_ AND _yyy_ ?
I thought that maybe something like [xy]{xy][xy] would take you half-way
there, but it was too smart for me and escaped the brackets.
If you feel that this would be a valueable feature, pay a visit to
https://connect.microsoft.com/SQLServer/feedback/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland,
Thanks for the feedback. Its very much appreciated! Also, apologies for
the late acknowledgement.
Good suggestion. I'll ping MS about the issue
https://connect.microsoft.com/SQLServer/feedback/.
I've got a couple hundred stored procs (so far) and the filter is becoming
more and more handy. (we've been migrating our two 'largest' enterprise db's
from UNIX to Sql Server). I name our table management procs to sort by
sub-system / table_name. I also have a number of 'generic tool box' procs
that are accessed by multiple subsystems ... thus the need to display both
_aaa_ and _yyy_ subsystem procs at the same time.
Best Wishes!
Barry
in Oregon
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns986FEE338BC11Yazorman@.127.0.0.1...
> frostbb (barry.b.frost@.remove-this-spam-filter.state.or.us) writes:
> I thought that maybe something like [xy]{xy][xy] would take you half-way
> there, but it was too smart for me and escaped the brackets.
> If you feel that this would be a valueable feature, pay a visit to
> https://connect.microsoft.com/SQLServer/feedback/.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Repost: sql7 maintenance jobs hang

Hi group,
the following has been posted and did not receive any response. In hope to
get some leads I am reposting it here.
we have a sql7 server,
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
For a while now, it has been seeing hanging db maintenance jobs. The jobs
are initiated, but never goes beyond that -- the maintenance task is not
done before the hanging of the job. EM shows that the jobs are running but
loses the ability of managing them. One needs to end the processes with the
windows task manager. When manually initiated, the jobs can run through.
The jobs are not necessarily the same(different days can have different jobs
experiencing the problem, some can be the same), but all came from db
maintenance plan. A job hung yesterday may not hang today. Recreating the
db maint plan does not solve the problem.
Any suggestions are appreciated.
QuentinQuentin
Are you using the repair minor errors option? That tends
to cause problems in maint plans.
Any other problems on the server, I had a similar problem
and it was being caused by another process clashing with
the checkpoint process, but that eventually killed the
whole system.
Regards
John|||John,
thanks for the response.
The repair minor errors option is not being used. We are aware of that
problem.
We are trying to identify other problems on the server but came out empty
handed so far.
Quentin
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:003201c3a86e$ccafad40$a301280a@.phx.gbl...
> Quentin
> Are you using the repair minor errors option? That tends
> to cause problems in maint plans.
> Any other problems on the server, I had a similar problem
> and it was being caused by another process clashing with
> the checkpoint process, but that eventually killed the
> whole system.
> Regards
> John

Repost: Solution didn't work any other ideas??

hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy

>--Original Message--
>Kamy,
>This will do it at least as you specified.
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =
t2.testcol AND t1.Ctr
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
records[vbcol=seagreen]
You need to update using a query and the PK. That identity would be a useful
PK for this
alter table tbl add seq int
update tbl set seq = (select count(*) from tbl t1 where t1.sheet = t.sheet
and t1.pk <= t.pk)
from tbl t
"Kamy" wrote:

> hi Ron,
> It didn't work, as soon as I add an identity column,
> it inserts running nos. into the field.
> Am I missing something?
> Thank you,
> -Kamy
> t2.testcol AND t1.Ctr
> message
> records
>
|||Nigel,
Thanks for taking time to reply! But this also does not
solve my problem, it increments all numbers in order from
1 to ...
I don't want it to do that, please take a look at my
results again, for a given set I would like it to
increment nos, and then start with 1 again.
Thank you again,
-Kamy

>--Original Message--
>You need to update using a query and the PK. That
identity would be a useful
>PK for this
>alter table tbl add seq int
>update tbl set seq = (select count(*) from tbl t1 where
t1.sheet = t.sheet[vbcol=seagreen]
>and t1.pk <= t.pk)
>from tbl t
>"Kamy" wrote:
dbo.test[vbcol=seagreen]
(1,1)
>.
>

Repost: Solution didn't work any other ideas??

hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy
>--Original Message--
>Kamy,
>
>This will do it at least as you specified.
>
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol = t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>>
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>>
>> Thank you in advance
>> -Kamy
>> .
>>
>>You need to update using a query and the PK. That identity would be a useful
PK for this
alter table tbl add seq int
update tbl set seq = (select count(*) from tbl t1 where t1.sheet = t.sheet
and t1.pk <= t.pk)
from tbl t
"Kamy" wrote:
> hi Ron,
> It didn't work, as soon as I add an identity column,
> it inserts running nos. into the field.
> Am I missing something?
> Thank you,
> -Kamy
> >--Original Message--
> >Kamy,
> >
> >This will do it at least as you specified.
> >
> >SET NOCOUNT ON
> >GO
> >IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
> >GO
> >CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
> >GO
> >INSERT test VALUES ('A001',0)
> >INSERT test VALUES ('A001',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A003',0)
> >INSERT test VALUES ('A003',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('C189',0)
> >INSERT test VALUES ('D9090',0)
> >GO
> >ALTER TABLE dbo.test add Ctr int not null identity(1,1)
> >GO
> >SELECT t1.TestCol
> > , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol => t2.testcol AND t1.Ctr
> >>= t2.Ctr)
> >FROM test t1
> >GO
> >ALTER TABLE dto.test DROP COLUMN Ctr
> >
> >Hope this helps,
> >Ron
> >--
> >Ron Talmage
> >SQL Server MVP
> >
> >"Kamy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
> >> I have a need to update data in a table so that
> >> a column contains the generated number for sets of
> records
> >> for instance I have fileName as listed and it should
> >> generate the SheetNum for each of the set
> >>
> >> FileName SheetNum
> >> A001 1
> >> A001 2
> >> A002 1
> >> A002 2
> >> A002 3
> >> A003 1
> >> A003 2
> >> B011 1
> >> B011 2
> >> B011 3
> >> C189 1
> >> D9090 1
> >>
> >> Thank you in advance
> >> -Kamy
> >> .
> >>
> >>
>|||Nigel,
Thanks for taking time to reply! But this also does not
solve my problem, it increments all numbers in order from
1 to ...
I don't want it to do that, please take a look at my
results again, for a given set I would like it to
increment nos, and then start with 1 again.
Thank you again,
-Kamy
>--Original Message--
>You need to update using a query and the PK. That
identity would be a useful
>PK for this
>alter table tbl add seq int
>update tbl set seq = (select count(*) from tbl t1 where
t1.sheet = t.sheet
>and t1.pk <= t.pk)
>from tbl t
>"Kamy" wrote:
>> hi Ron,
>> It didn't work, as soon as I add an identity column,
>> it inserts running nos. into the field.
>> Am I missing something?
>> Thank you,
>> -Kamy
>> >--Original Message--
>> >Kamy,
>> >
>> >This will do it at least as you specified.
>> >
>> >SET NOCOUNT ON
>> >GO
>> >IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE
dbo.test
>> >GO
>> >CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>> >GO
>> >INSERT test VALUES ('A001',0)
>> >INSERT test VALUES ('A001',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A003',0)
>> >INSERT test VALUES ('A003',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('C189',0)
>> >INSERT test VALUES ('D9090',0)
>> >GO
>> >ALTER TABLE dbo.test add Ctr int not null identity
(1,1)
>> >GO
>> >SELECT t1.TestCol
>> > , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =>> t2.testcol AND t1.Ctr
>> >>= t2.Ctr)
>> >FROM test t1
>> >GO
>> >ALTER TABLE dto.test DROP COLUMN Ctr
>> >
>> >Hope this helps,
>> >Ron
>> >--
>> >Ron Talmage
>> >SQL Server MVP
>> >
>> >"Kamy" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> >> I have a need to update data in a table so that
>> >> a column contains the generated number for sets of
>> records
>> >> for instance I have fileName as listed and it should
>> >> generate the SheetNum for each of the set
>> >>
>> >> FileName SheetNum
>> >> A001 1
>> >> A001 2
>> >> A002 1
>> >> A002 2
>> >> A002 3
>> >> A003 1
>> >> A003 2
>> >> B011 1
>> >> B011 2
>> >> B011 3
>> >> C189 1
>> >> D9090 1
>> >>
>> >> Thank you in advance
>> >> -Kamy
>> >> .
>> >>
>> >>
>>
>.
>

Repost: Security level already for "RosettaMgr"

As an MSDN subscriber, I though I was supposed to get a guaranteed response
here. I am reposting this since no one responded to my 1st inquiry.
Out of the blue this morning our Report Server stopped working with the
following error:
Configuration Error
...
Parser Error Message: Security Level already defined for RosettaMgr
Source Error:
Line 22: <securityPolicy>
Line 23: <trustlevel name="RosettaMgr"
policyFile="rsmgrpolicy.config" />
Line 24: </securityPolicy>
line 24: <trust level="RosettaMgr" originUrl="" />
Removing the offending lines from web.config resolved the problem but I'm
unclear why this happened suddenly. I'm also leery of removing these
settings without fully understanding the implications. If anyone has any
insight as to the cause of this error or can intelligently explain the
<securityPolicy> and <trust level> settings I sure would appreciate a little
feedback.
Thanks,
Rich BakosHi Rich,
Sorry that you're encountering trouble. Not sure where your expectation was
set regarding guaranteed response If you could let me know that would be
very helpful. AFAIK, MS doesn't guarantee responses on this newsgroup -
you're actually talking with many folks on the product team directly. Though
we'd love to answer all questions, we're also trying to ship an awesome
product as part of SQL Server 2005. I'm sure you understand our quandry
:-).
Now your question:
Report Manager ships with a default security group called RosettaMgr.
Removing this line is not desired. The relevant section of the file reads:
<securityPolicy>
<trustLevel name="RosettaMgr" policyFile="rsmgrpolicy.config"/>
</securityPolicy>
<trust level="RosettaMgr" originUrl=""/>
What this line does is it ensure that the report manager loads code, that it
assigns the code the correct permission defined in the rsmgrpolicy.config
file.
This is a shot in the dark but the web.config file included with an
application follows a hierarchy based on other web.config files that come
before it. You can read up on this here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod92.asp
Are you running another application that also uses ASP.Net on the same
server? Has any web.config changed on your server shortly before your
application started to fail?
One other questions - did you move/copy the report manager virtual
directory?
-Lukasz
"Richard Bakos" <bakos@.nospam.nospam> wrote in message
news:%23PAol8DGFHA.444@.TK2MSFTNGP15.phx.gbl...
> As an MSDN subscriber, I though I was supposed to get a guaranteed
> response
> here. I am reposting this since no one responded to my 1st inquiry.
> Out of the blue this morning our Report Server stopped working with the
> following error:
> Configuration Error
> ...
> Parser Error Message: Security Level already defined for RosettaMgr
>
> Source Error:
> Line 22: <securityPolicy>
> Line 23: <trustlevel name="RosettaMgr"
> policyFile="rsmgrpolicy.config" />
> Line 24: </securityPolicy>
> line 24: <trust level="RosettaMgr" originUrl="" />
> Removing the offending lines from web.config resolved the problem but I'm
> unclear why this happened suddenly. I'm also leery of removing these
> settings without fully understanding the implications. If anyone has any
> insight as to the cause of this error or can intelligently explain the
> <securityPolicy> and <trust level> settings I sure would appreciate a
> little
> feedback.
> Thanks,
> Rich Bakos
>
>|||Lukasz Pawlowski [MSFT] wrote:
Hi Lukasz.
Thanks for you response.
> Sorry that you're encountering trouble. Not sure where your
> expectation was set regarding guaranteed response If you could let
> me know that would be very helpful.
My mistake, the page says a "commitment" to respond rather than a guarantee.
http://msdn.microsoft.com/newsgroups/managed/
> <securityPolicy>
> <trustLevel name="RosettaMgr" policyFile="rsmgrpolicy.config"/>
> </securityPolicy>
> <trust level="RosettaMgr" originUrl=""/>
Yes. Those are the offending lines and the server will not run with those
elements defined. To add to the problem, we can not move reports to
different folders or update definitions since removing the offending
elements. Everything else appears to work correctly.
Here is the exact text of the error:
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Security level already defined for 'RosettaMgr'.
Source Error:
Line 21: <httpRuntime executionTimeout="9000"/>
Line 22: <securityPolicy>
Line 23: <trustLevel name="RosettaMgr" policyFile="rsmgrpolicy.config"/>
Line 24: </securityPolicy>
Line 25: <trust level="RosettaMgr" originUrl=""/>
Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\web.config Line: 23
----
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573
> What this line does is it ensure that the report manager loads code,
> that it assigns the code the correct permission defined in the
> rsmgrpolicy.config file.
Thanks for the explaination but this doesn't explain why I'm getting a parse
error complaining that a Security level is already defined. My web.config
files had not been changed until the day I removed the offending elements.
> Are you running another application that also uses ASP.Net on the same
> server?
No. However, the web.config for the report server also contains the elements
sin question.
>Has any web.config changed on your server shortly before
> your application started to fail?
No.
> One other questions - did you move/copy the report manager virtual
> directory?
No.
Any other insight you might have certainly would be helpful.
Thanks,
Rich Bakos|||Richard Bakos wrote:
> No. However, the web.config for the report server also contains the
> elements sin question.
My mistake. The element definitions are not identical. Once defines
RosettaMgr while the other defines RosettaSrv.
Rich|||The solution to your problem is to install the ASP.Net hotfix for a
canonicalization issue. You can do this from the following KB article:
http://support.microsoft.com/kb/887787
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Bakos" <bakos@.nospam.nospam> wrote in message
news:uYe6DZKGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Richard Bakos wrote:
>> No. However, the web.config for the report server also contains the
>> elements sin question.
> My mistake. The element definitions are not identical. Once defines
> RosettaMgr while the other defines RosettaSrv.
> Rich
>|||Lukasz Pawlowski [MSFT] wrote:
Hi Lukasz,
> The solution to your problem is to install the ASP.Net hotfix for a
> canonicalization issue. You can do this from the following KB
> article:
That made no difference. The problem still exists.
Rich Bakos

REPOST: Re: internet connection to msde

You probably need to open up UDP on your firewall also. Here a link with
some information about connecting to SQL Server through a firewall:
http://tinyurl.com/m3g9
Jim
"TJS" <nospam@.here.com> wrote in message
news:113fgtcgdki6f7d@.corp.supernews.com...
> yes, it is going thru the firewall during the test.
> there are no firewall logs.
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23W5wvWbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
>
Thank you everyone. After installing another instance of MSDE, following
some advise from this forum, I was finallly able to return a dataset from a
webservice I wrote. But I still think it reminds more of gambling than
science;-) Could even display MSDE data in my PocketPC application, connected
via GPRS! Great!
When I tried create a similar webservice on another computer today, I got
stuck in Visual Studio when I single stepped to conn.Open( ) which gave me
the exception message "Failed to login to myMachine\ASPNET".
My question: Why does VS try to login to an account that does not work?
How do I prevent this to happen? Why does it try to login to a non-existing
account?
I don't understand.
"Jim Young" wrote:

> You probably need to open up UDP on your firewall also. Here a link with
> some information about connecting to SQL Server through a firewall:
> http://tinyurl.com/m3g9
> Jim
> "TJS" <nospam@.here.com> wrote in message
> news:113fgtcgdki6f7d@.corp.supernews.com...
>

Repost: Polling the network for SQL servers, including named instances

Hi group,
I am trying to get a list of SQL Server installations running on the
network. I tried to use sqlcmd -L (or osql -L). However, when I do this
from my box the named instances do not show up, whereas when it is run from
a server box those instances do get listed.
What is required to make the command return the full list? Is there other
(simple) way to do it?
Thanks.
QuentinHi Quentin
Check out SQLPing or SQLRecon at
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
John
"Quentin Ran" wrote:
> Hi group,
> I am trying to get a list of SQL Server installations running on the
> network. I tried to use sqlcmd -L (or osql -L). However, when I do this
> from my box the named instances do not show up, whereas when it is run from
> a server box those instances do get listed.
> What is required to make the command return the full list? Is there other
> (simple) way to do it?
> Thanks.
> Quentin
>
>

REPOST: optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimIf you don't get the SQL Server log entry that is mentioned in the KB
you posted, it may not related at all. By just knowing the warning in
the application event, it is not sufficient to say more.
To find out why the job failed, go to the individual job in EM, right
click and select 'show job history' and check on 'Show Details' box.
It should give you more ideas what went wrong. It could be disk space,
permission, resources conflict issues etc.
Mel|||Hi Mel,
It shows the same message as is in the KB article:
"The job failed. The Job was invoked by User
<computer_system_administrator>. The last step to run was step 1 (Step 1)."
There is only one step:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
It's not clear to me how to troubleshoot further.
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145009319.702671.27150@.v46g2000cwv.googlegroups.com...
> If you don't get the SQL Server log entry that is mentioned in the KB
> you posted, it may not related at all. By just knowing the warning in
> the application event, it is not sufficient to say more.
> To find out why the job failed, go to the individual job in EM, right
> click and select 'show job history' and check on 'Show Details' box.
> It should give you more ideas what went wrong. It could be disk space,
> permission, resources conflict issues etc.
> Mel
>|||The error message isn't very helpful, is it :)
Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it :)
> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

REPOST: optimizations job for db maintenance plan failed

Okay last attempt, change the job owner to 'sa', to see if it is
because of that.
If still not joys, back to the old classic rule - re-create the plan
(delete the existing one and create a new one). Did you create the job
manually? If so, try to use the DB Maint Wizard to create the job and
compare the two.
Mel|||Thanks for the tip, Mel!
I've changed job owner to "sa". This job is part of a maintenance that runs
once a month, on the first of the month. We'll see how it goes in a two and
one-half weeks!
The plan was recently recreated, but it could be re-recreated to see if that
helps.
Good day,
Jim
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145046790.093217.94260@.g10g2000cwb.googlegroups.com...
> The error message isn't very helpful, is it

> Okay last attempt, change the job owner to 'sa', to see if it is
> because of that.
> If still not joys, back to the old classic rule - re-create the plan
> (delete the existing one and create a new one). Did you create the job
> manually? If so, try to use the DB Maint Wizard to create the job and
> compare the two.
> Mel
>

Repost: Merge Replication Error - Failed to enumerate changes in the filtered article

Hi,
I am using Merge Replication and the subscriber is a pull subscriber.
Some tables are filtered, in the publication properties, if I go to FILTER
ROWS, then FILTER CLAUSE column, some tables are filtered like so
depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx' where x makes up the guid.
The replication objects are owned by DBO.
Versions of SQL:
Publisher:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
Subscriber:
MSDE
Everything was working fine, then all of a sudden, I get this error. (See
below)
Does anyone know what would cause this and how I can overcome it?
Thanks in Advance
Warren
************************************************** *
******************ERROR DETAILS******************
************************************************** *
Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
Error Message: Failed to enumerate changes in the filtered articles.
Error Details:
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error number: -2147200925)
Incorrect syntax near the keyword 'where'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
Incorrect syntax near the keyword 'and'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
************************************************** *
*******************END OF ERROR******************
************************************************** *
Someone reported a similar problem and solved it by reapplying sp3a.
http://groups-beta.google.com/group/... e3a5eb8c1ea7a
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
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:eYSANebNFHA.2468@.tk2msftngp13.phx.gbl...
> Hi,
> I am using Merge Replication and the subscriber is a pull subscriber.
> Some tables are filtered, in the publication properties, if I go to FILTER
> ROWS, then FILTER CLAUSE column, some tables are filtered like so
> depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx' where x makes up the guid.
> The replication objects are owned by DBO.
> Versions of SQL:
> Publisher:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> Subscriber:
> --
> MSDE
>
> Everything was working fine, then all of a sudden, I get this error. (See
> below)
> Does anyone know what would cause this and how I can overcome it?
> Thanks in Advance
> Warren
> ************************************************** *
> ******************ERROR DETAILS******************
> ************************************************** *
> Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
> Error Message: Failed to enumerate changes in the filtered articles.
> Error Details:
> Failed to enumerate changes in the filtered articles.
> (Source: Merge Replication Provider (Agent); Error number: -2147200925)
> ----
--
> --
> Incorrect syntax near the keyword 'where'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> Incorrect syntax near the keyword 'and'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> ************************************************** *
> *******************END OF ERROR******************
> ************************************************** *
>
>
|||Hi Hilary,
Installing SP3a doesnt make sense to me. I have 4 other publications on
that same machine, setup the exact same way, except the filters are on other
ID {"depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx' where x makes up the
guid."} and they are working perfectly.
I am running service pack 3 only. I cant remember, but something in the
deep recesses of my mind is telling me that there was a reason I didnt
install SP3a onto the publisher, but I cant remember why.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eYPCVpeNFHA.508@.TK2MSFTNGP12.phx.gbl...
> Someone reported a similar problem and solved it by reapplying sp3a.
>
http://groups-beta.google.com/group/... e3a5eb8c1ea7a[vbcol=seagreen]
> --
> 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
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:eYSANebNFHA.2468@.tk2msftngp13.phx.gbl...
FILTER[vbcol=seagreen]
(See
> ----
> --
> ----
> --
> ----
> --
>

REPOST: Merge Replication Conditional Filter with UDF Problem - Help !

Hi all,
I'm still stuck on this issue. Please find a re-cap below. I'd be really grateful if anyone
could shed some light on this.Thanks a million :o)
I am performing merge replication between SQL CE/Server 2000.
I would like to select the stores specific to each rep
unless the repid = 6, in which case I would like to select
all stores. Is this possible in merge replication?
I have been attempting to set the host_name() to my repID and
passing this Host_Name() into a table-valued UDF. The aim
being that the UDF will return a table of stores. My problem
is that I'm receiving an error and I would like to know if
UDF's accept Host_Name() as a parameter ?
My Dynamic Filter ...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.RepID IN (SELECT * FROM SELECT_Stores_RepID(Host_Name()))
The Error I'm receiving ...
Error 170: Line 1: Incorrect Syntax near '('.
Cube,this is my previous reply:"I have only been able to use UDFs before
when they are scalar, eg SELECT <published_columns> FROM [dbo].[Region]
WHERE region.regionid = dbo.fn_TaxRate(host_name())works fine. In your case
can you restructure your query to return a scalar value and then use this
value combined with another table in a join + where clause perhaps?"You
mentioned that you would give it a try and post back your results - how did
it go?Regards,Paul Ibison
|||Hi Paul,
I gave this a go but I didn't have much joy, maybe I'm just confused as to how to
go about things. As you mentioned I changed my Dynamic Filter so that I returned
the RepID ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] WHERE
CAST(FK_Rep_ID AS char(4)) = Host_Name()
Next I attempted to use this in an Extended Filter (which is where I ran into problems) ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] INNER JOIN
[dbo].[Stores] ON Stores.RepID IN (SELECT * FROM
SELECT_Stores_RepID(tbl_Usr.TKT_RepID))
This product the syntax error ...
Error 170: Line 1: Incorrect Syntax near '.'.
This will work if I pass in a constant instead of tbl_Usr.TKT_RepID, which of
course I can't do. (At this stage you're probably saying to yourself...this guy just
tried the same thing he did in the Dynamic Filter...and is chasing his tail ;o)
I'm very open to trying alternative queries/approaches. At this stage I've looked at this
so many times I need some devine inspiration ;o)
Thanks in advance,
|||OK - I'll give it a try and post up an example later this afternoon (5pm UK
time).
Regards,
Paul
|||OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Hi Paul,
This is what I've been doing to date. But what I'd like is to select ALL records
if Host_Name() = some Rep ID set by the Merge Agent. Pseudocode...
IF (Host_Name() = 6)
SELECT * FROM Stores
ELSE
SELECT * FROM Stores WHERE RepID = Host_Name()
Maybe your filters do this, and I'm just missing the point ?
Thanks for the time you're giving me :o)
-- Paul Ibison wrote: --
OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Cube,
such logic is part of the linking table. If you had 3 stores, then the
linking table looks like this:
LookupTable
StoreID,Host
1,6
2,6
3,6
For the other hostnames there are records representing the corresponding
links:
LookupTable
StoreID,Host
1,6
2,6
3,6
2,999
HTH,
Paul Ibison

Repost: Latches that don't release

First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know

> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/defaul...d=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved

>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Repost: Latches that don't release

First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know

> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/default...=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved

>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Repost: Latches that don't release

I have encountered an intermittent problem where a stored procedure or query
will create a latch and hold onto it indefinitely. I have no explanation
for why the offending process fails to complete. Since the latch is on a
frequently used table, all subsequent insert operations are blocked until
the stubborn process is manually killed.
I've run across it about 4 times in the last month on two instances of a
similar database (SQL2000 latest SP, Simple Recovery Model). The type of
latch and the type of process that created it has varied.
Latch Type Process that caused it
============== ====================================== LATCH_EX -- From a stored proc. with a single INSERT
statement
PAGELATCH_EX -- From a stored proc. with a single INSERT statement
NETWORKIO -- From a SELECT query
Again, I haven't tracked down a legitimate reason for why the offending
process gets stuck. The table where the problems are occurring is
essentially a message log receiving ~400,000 records/day. It has 4 indices
and I've seen some postings regarding timeout problems on heavily indexed
tables with lots of activity. There are no operations that take place in
the database that are suspect for deadlocking. The vast majority of the
operations are simple one record inserts into a single table. All other
operations are occasional SELECT queries coming from a web report. Under
one occasion, the problem was caused by a select query but the others have
been caused by the insert stored procedure.
As a temporary work-around, I'm periodically polling the sysprocesses table
for waiting processes and performing a kill on the process if it is one of
the three latch types above and it has been waiting more than 10 seconds
(for my situation, accidentally killing a innocent process has little
impact).
Since it is not occurring in frequently, I have not been able to catch it
under a SQL Profiler log but I'll try to capture the behavior in the future.Hi Paul,
Thanks for using MSDN Newsgroup!
From your descriptions, I understood that some latches will not release 4
times in two instance. Have I understood you? If there is anything I
misunderstood, please feel free to let me know:)
First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved:)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know:)
> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved:)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Repost: just in case i can get an answer

Hello all I have a situation so far I have been unable to find a solution
I have 3 tables like so
CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NULL ,
[FullName] [varchar] (50) NULL ,
[UserName] [varchar] (50) NULL ,
[Password] [varchar] (50) NULL ,
[Email] [varchar] (50) NULL ,
[CreationDate] [smalldatetime] NULL ,
[LastLogon] [smalldatetime] NULL
)
CREATE TABLE [dbo].[Users2SecurityRights] (
[Users2SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[SecurityRightID] [int] NULL ,
[AssignedDate] [smalldatetime] NULL ,
[AssignedBy] [int] NULL
)
CREATE TABLE [dbo].[SecurityRights] (
[SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
[SecurityRightName] [varchar] (50) NULL ,
[SecurityRightDescription] [varchar] (100) NULL
)
Now I have a stored procedure that will return a string with all the user
Security rights like so (Administrator, Ag, Ed, Cus)
CREATE PROCEDURE ReturnSecurityRights
(
@.UserID INT
)
as
BEGIN
DECLARE @.Count INT
DECLARE @.CountOfRows INT
DECLARE @.SecurityRights VARCHAR(100)
DECLARE @.SecurityRightsHolder VARCHAR(100)
DECLARE MCursor CURSOR
LOCAL
STATIC
FOR (SELECT SecurityRights.SecurityRightName FROM Users2SecurityRights
INNER JOIN SecurityRights ON Users2SecurityRights.SecurityRightID =
SecurityRights.SecurityRightID
WHERE USERID = @.UserID)
OPEN MCursor
SET @.Count = 1
SET @.CountOfRows = @.@.CURSOR_ROWS
SET @.SecurityRights = ''
WHILE @.Count <= @.CountofRows
BEGIN
FETCH MCursor
INTO @.SecurityRightsHolder
SET @.SecurityRights = @.SecurityRightsHolder + ',' + @.SecurityRights
SET @.Count = @.Count + 1
END
SELECT @.SecurityRights
CLOSE MCursor
DEALLOCATE MCursor
END
I know, I know why am i using cursors...
What I want is to query the Users Table and return a result set where the
return of the SP(ReturnSecurityRights) is inline with the fields of the
Users Table
In essence i want to be able to do this
SELECT *, (EXEC ReturnSecurityRights UserID) FROM Users
I understand that I need to use a select within a subquery, just not sure
how i can accomplish what i need.
I can do this programically through Code but I really would like SQL Server
to process this
Thanks All, Hope I was clear in what I want to accomplish, and thank you to
all that will help me find the way
LenLeonard Danao wrote:
> Hello all I have a situation so far I have been unable to find a
> solution I have 3 tables like so
> CREATE TABLE [dbo].[Users] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [int] NULL ,
> [FullName] [varchar] (50) NULL ,
> [UserName] [varchar] (50) NULL ,
> [Password] [varchar] (50) NULL ,
> [Email] [varchar] (50) NULL ,
> [CreationDate] [smalldatetime] NULL ,
> [LastLogon] [smalldatetime] NULL
> )
> CREATE TABLE [dbo].[Users2SecurityRights] (
> [Users2SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NULL ,
> [SecurityRightID] [int] NULL ,
> [AssignedDate] [smalldatetime] NULL ,
> [AssignedBy] [int] NULL
> )
> CREATE TABLE [dbo].[SecurityRights] (
> [SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
> [SecurityRightName] [varchar] (50) NULL ,
> [SecurityRightDescription] [varchar] (100) NULL
> )
> Now I have a stored procedure that will return a string with all the
> user Security rights like so (Administrator, Ag, Ed, Cus)
> CREATE PROCEDURE ReturnSecurityRights
> (
> @.UserID INT
> )
> as
> BEGIN
> DECLARE @.Count INT
> DECLARE @.CountOfRows INT
> DECLARE @.SecurityRights VARCHAR(100)
> DECLARE @.SecurityRightsHolder VARCHAR(100)
> DECLARE MCursor CURSOR
> LOCAL
> STATIC
> FOR (SELECT SecurityRights.SecurityRightName FROM
> Users2SecurityRights INNER JOIN SecurityRights ON
> Users2SecurityRights.SecurityRightID = SecurityRights.SecurityRightID
> WHERE USERID = @.UserID)
> OPEN MCursor
> SET @.Count = 1
> SET @.CountOfRows = @.@.CURSOR_ROWS
> SET @.SecurityRights = ''
> WHILE @.Count <= @.CountofRows
> BEGIN
> FETCH MCursor
> INTO @.SecurityRightsHolder
> SET @.SecurityRights = @.SecurityRightsHolder + ',' + @.SecurityRights
> SET @.Count = @.Count + 1
> END
> SELECT @.SecurityRights
> CLOSE MCursor
> DEALLOCATE MCursor
> END
>
> I know, I know why am i using cursors...
> What I want is to query the Users Table and return a result set where
> the return of the SP(ReturnSecurityRights) is inline with the fields
> of the Users Table
> In essence i want to be able to do this
> SELECT *, (EXEC ReturnSecurityRights UserID) FROM Users
> I understand that I need to use a select within a subquery, just not
> sure how i can accomplish what i need.
> I can do this programically through Code but I really would like SQL
> Server to process this
http://www.sqlteam.com/item.asp?ItemID=2955
http://www.windowsitpro.com/SQLServ...5608/15608.html
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks :)
*** Sent via Developersdex http://www.examnotes.net ***

Repost: Installation trouble

Removed the previous version and tried installing the new trial - was able
to see reports in visual but not in reportmanager. So now I tried to drop it
all including all iis-catalogues and installed it from scratch again.
No luck...
My goal is just to get it running to see reports - not to get it running
with the right security settings...so in IIS i changed reports and
reportserver to anonymous access and then stopped and started iis and
reportserver service.
But when activating reportmanager it keeps on saying:
unable to finde script library
'/aspnet_client/system_web/1_1_4322/webuivalidation.js - try placing this
file manually or reinstall by running 'aspnet_regiis -c'
tried running to above and tried aspnet_regiis -i - no luck.
Have an xp operating system - is there a platform limitation for the trial
version ?
Is it related to my IIS-settings ? It shouldn't try to get the file above
when using anonymous access. I have had the previous version to function.
--MichaelNo hints ?
When using reportmanager I can do nothing in there - tried to make a
datasource but nothing happens when trying to save it. When switching from
one tab to another the error below pops up...
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> skrev i en
meddelelse news:eJGqdb4kEHA.3984@.TK2MSFTNGP14.phx.gbl...
> Removed the previous version and tried installing the new trial - was able
> to see reports in visual but not in reportmanager. So now I tried to drop
it
> all including all iis-catalogues and installed it from scratch again.
> No luck...
> My goal is just to get it running to see reports - not to get it running
> with the right security settings...so in IIS i changed reports and
> reportserver to anonymous access and then stopped and started iis and
> reportserver service.
> But when activating reportmanager it keeps on saying:
> unable to finde script library
> '/aspnet_client/system_web/1_1_4322/webuivalidation.js - try placing this
> file manually or reinstall by running 'aspnet_regiis -c'
> tried running to above and tried aspnet_regiis -i - no luck.
> Have an xp operating system - is there a platform limitation for the trial
> version ?
> Is it related to my IIS-settings ? It shouldn't try to get the file above
> when using anonymous access. I have had the previous version to function.
> --Michael
>|||Not quite sure what did the trick but now it's working ...
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> skrev i en
meddelelse news:%23lDRJ8OlEHA.3760@.TK2MSFTNGP12.phx.gbl...
> No hints ?
> When using reportmanager I can do nothing in there - tried to make a
> datasource but nothing happens when trying to save it. When switching from
> one tab to another the error below pops up...
> "Michael Vardinghus" <michaelvardinghus@.notexisting.com> skrev i en
> meddelelse news:eJGqdb4kEHA.3984@.TK2MSFTNGP14.phx.gbl...
> > Removed the previous version and tried installing the new trial - was
able
> > to see reports in visual but not in reportmanager. So now I tried to
drop
> it
> > all including all iis-catalogues and installed it from scratch again.
> >
> > No luck...
> >
> > My goal is just to get it running to see reports - not to get it running
> > with the right security settings...so in IIS i changed reports and
> > reportserver to anonymous access and then stopped and started iis and
> > reportserver service.
> >
> > But when activating reportmanager it keeps on saying:
> >
> > unable to finde script library
> > '/aspnet_client/system_web/1_1_4322/webuivalidation.js - try placing
this
> > file manually or reinstall by running 'aspnet_regiis -c'
> >
> > tried running to above and tried aspnet_regiis -i - no luck.
> >
> > Have an xp operating system - is there a platform limitation for the
trial
> > version ?
> >
> > Is it related to my IIS-settings ? It shouldn't try to get the file
above
> > when using anonymous access. I have had the previous version to
function.
> >
> > --Michael
> >
> >
>

Repost: Exporting Information

Hello NG
We have a production database that we have moved the tables to SQLServer and
I have been creating stored procedures to replicate some of the processes
done on the access side - the server does processing extremely faster than
access - Here is my problem I am currently pulling info from SQL SERVER to
Access once a w

for w

ly stats I want to create a stored procedure to
process the w

ly stats and push them to an access table upon request - we
have multiple warehouses and I would like to have the process coded once in
sql and dump to the access table based off a warehouse variable passed to
the stored procedure - Can SQLServer from a stored procedure export a
recordset into a varible defined Access Table?
TIAFAH
RandyHi Randy,
Yes this is possible...You can do it by using a linked server. You can
find info about linked servers in SQL Server Books Online. I've given
an outline of the general steps you need to do below.
Hope this helps.
=================
1) Open Query Analyzer and log in using an account with SysAdmin
privileges (e.g. sa). Make sure you are using the "master" database.
2) Type in the following:
exec sp_addlinkedserver '<Linked Server Name>', '<MS Access Version>',
'Microsoft.Jet.OLEDB.4.0', '<Full Path To Access DB>'
<Linked Server Name> can be whatever you want it to be, you will use
this to reference the Access DB.
<MS Access Version> is something like 'Access 97' or 'Access 2003' etc
<Full Path To Access DB> is, well, yeah...
3) Execute what you entered in (2) and then clear the Query Analyzer
window of the command
4) Type in the folliwng into Query Analyzer:
sp_addlinkedsrvlogin '<Linked Server Name>', false, '<SQL Server Login
Name>', 'Admin', NULL
<SQL Server Login Name> is the SQL Server login that will access the
Access DB. Make it something like "sa" if you will be executing the
stored proc under your account.
5) Execute the command entered in (4)
OK, after doing that you can now access you MS Access DB from SQL
Server. To reference a table in Access you have to write:
<Linked Server Name>...<Table Name>
You can do Inserts and other things to this table. In your case you
would probably be wanting to Insert values into the table.
Hope that helps.

Repost: Databases with different collation

Yes, use unicode.
The problem is you can only have one collation per
database table, so combining two will cause a problem. The
answer is to use unicode ie ncar, nvarhar, ntext which is
collation independant.
J

>--Original Message--
>Hi group,
>we have databases now residing on different servers or
instances with
>different collations. We are considering bringing the
databases on the same
>server, keeping their respective collation.
>Some of our applications access databases with different
collations. We
>have been using linked server and specifying "Use remote
collation" (or
>not). But when putting the DBs with different collation
on the same server,
>you loss this feature. To go over all the applications
and specify the
>collation on the query statement level is of course too
big a task. Is
>there a way to do such a blanket declaration on the
database level?
>Or any other way of doing this efficiently?
>Your responses are greatly appreciated.
>Quentin
>
>
>.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2a99d01c46820$c3396fe0$a601280a@.phx
.gbl...
> Yes, use unicode.
> The problem is you can only have one collation per
> database table, so combining two will cause a problem. The
> answer is to use unicode ie ncar, nvarhar, ntext which is
> collation independant.
Julie,
Are you suggesting that unicode columns ignore collation settings? That
is not true:
create table #un1 (a nvarchar(20) collate sql_latin1_general_cp1_ci_as)
create table #un2 (a nvarchar(20) collate sql_latin1_general_cp1_cs_as)
select * from #un1
union
select * from #un2
-- Server: Msg 446, Level 16, State 9, Line 5
-- Cannot resolve collation conflict for UNION operation.
If that wasn't what you meant, please explain in more detail.|||Sorry thats not what I meant. from the description two
different databases are being combined, the point I was
trying (unsuccessfully obviously) was that adding the data
from collation table x to table with collation y will
cause problems, so the best way is to convert them first
to unicode, then combine them.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2a99d01c46820$c3396fe0$a601280a@.phx
.gbl...
The[vbcol=seagreen]
is[vbcol=seagreen]
> Julie,
> Are you suggesting that unicode columns ignore
collation settings? That
>is not true:
>
>create table #un1 (a nvarchar(20) collate
sql_latin1_general_cp1_ci_as)reen">
>create table #un2 (a nvarchar(20) collate
sql_latin1_general_cp1_cs_as)reen">
>select * from #un1
>union
>select * from #un2
>-- Server: Msg 446, Level 16, State 9, Line 5
>-- Cannot resolve collation conflict for UNION operation.
>
> If that wasn't what you meant, please explain in more
detail.
>
>.
>|||Julie/Adam,
Thanks for the responses.
Using unicode (or whatever casting) does not solve the problem. The problem
is that there are existing applications that use the linked server where
with the "use remote collation", you have the whole database covered, but
with any casting, you have only the column covered. You will have to go
over the entire code to do the change. What I am trying to find is rather a
solution that do such a blanket change so you do not need to worry where a
change is missed -- let alone to do all the changes.
Again thanks for the discussion. Any further comments are well come.
Quentin
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:2bdab01c4682d$cf07d2f0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Sorry thats not what I meant. from the description two
> different databases are being combined, the point I was
> trying (unsuccessfully obviously) was that adding the data
> from collation table x to table with collation y will
> cause problems, so the best way is to convert them first
> to unicode, then combine them.
> J
>
> message
> The
> is
> collation settings? That
> sql_latin1_general_cp1_ci_as)
> sql_latin1_general_cp1_cs_as)
> detail.

Repost: Databases with different collation

Let me try this one last time.
Hi group,
we have databases now residing on different servers or instances with
different collations. We are considering bringing the databases on the same
server, keeping their respective collation.
Some of our applications access databases with different collations. We
have been using linked server and specifying "Use remote collation" (or
not). But when putting the DBs with different collation on the same server,
you loss this feature. To go over all the applications and specify the
collation on the query statement level is of course too big a task. Is
there a way to do such a blanket declaration on the database level?
Or any other way of doing this efficiently?
Your responses are greatly appreciated.
QuentinQuentin,
I've been thinking about your problem and the only somewhat-feasible
solution I can think of is to create new databases with your server's
default collation and then use DTS to transfer the old databases in,
ignoring collation (I believe that's an option?)
This would, of course, be slower than just doing a backup/restore, but you'd
only have to do it once and it will definitely fix your issues.
"Quentin Ran" <ab@.who.com> wrote in message
news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Let me try this one last time.
> Hi group,
> we have databases now residing on different servers or instances with
> different collations. We are considering bringing the databases on the
same
> server, keeping their respective collation.
> Some of our applications access databases with different collations. We
> have been using linked server and specifying "Use remote collation" (or
> not). But when putting the DBs with different collation on the same
server,
> you loss this feature. To go over all the applications and specify the
> collation on the query statement level is of course too big a task. Is
> there a way to do such a blanket declaration on the database level?
> Or any other way of doing this efficiently?
> Your responses are greatly appreciated.
> Quentin
>
>
>|||Thanks Adam.
I believe it still does not work. Say I have db1 with case sensetive, db2
case insensitive. The server default is case sensetive. I can put whatever
data into DB2 on the case sensetive server and make DB2 case sensetive.
However, my application dictates that a query run from db1 accessing db2
requires case insensitive -- currently achieved by linked server with use
remote collation -- and the requirement can not be satisfied.
Nevertheless, thanks again for keeping thinking about the problem.
Quentin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#NRk40daEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Quentin,
> I've been thinking about your problem and the only somewhat-feasible
> solution I can think of is to create new databases with your server's
> default collation and then use DTS to transfer the old databases in,
> ignoring collation (I believe that's an option?)
> This would, of course, be slower than just doing a backup/restore, but
you'd
> only have to do it once and it will definitely fix your issues.
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uQvtBxdaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> same
> server,
>|||"Quentin Ran" <ab@.who.com> wrote in message
news:udZ1GXfaEHA.712@.TK2MSFTNGP11.phx.gbl...
> I believe it still does not work. Say I have db1 with case sensetive, db2
> case insensitive. The server default is case sensetive. I can put
whatever
> data into DB2 on the case sensetive server and make DB2 case sensetive.
> However, my application dictates that a query run from db1 accessing db2
> requires case insensitive -- currently achieved by linked server with use
> remote collation -- and the requirement can not be satisfied.
How many databases are already running on the server? Can you re-build
it and make it CI? CS is a hassle to work with anyway

At least, if you can re-collate most of the stuff, you'll only have to
mess with the queries that require CI... So that would be a step in the
right direction!|||
> How many databases are already running on the server? Can you
re-build
> it and make it CI? CS is a hassle to work with anyway

That's exactly the problem -- we are developing a new wide reaching
application that is case sensitive, required by the software vendor --
Peoplesoft. And it has interaction with our existing DBs that are CI.

Saturday, February 25, 2012

REPOST:Cannot find Snapshot 2005

Repost: Strange language mix

repost: SqlServer 2005 => SMS => Object Explorer => Programability Filter questio

Repost: sql7 maintenance jobs hang

Repost: Solution didn't work any other ideas??

Repost: Solution didn't work any other ideas??

Repost: Security level already for "RosettaMgr"

REPOST: Re: internet connection to msde

Repost: Polling the network for SQL servers, including named instances

REPOST: optimizations job for db maintenance plan failed

REPOST: optimizations job for db maintenance plan failed

Repost: Merge Replication Error - Failed to enumerate changes in the filtered article

REPOST: Merge Replication Conditional Filter with UDF Problem - Help !

Repost: Latches that don't release

Repost: Latches that don't release

Repost: Latches that don't release

Repost: just in case i can get an answer

Repost: Installation trouble

Repost: Exporting Information

Repost: Databases with different collation

Repost: Databases with different collation

Reposition Subtotals

Blog Archive

About Me