Boot.ini file: following

Showing posts with label following. Show all posts

Tuesday, March 20, 2012

bug? DeviceInfo ColorDepth ignored for TIFF image

Hello!
I have a problem generating report (format = IMAGE) using following
DeviceInfo settings:
<DeviceInfo>
<ColorDepth>1</ColorDepth>
<OutputFormat>TIFF</OutputFormat>
</DeviceInfo>
Using this settings, generated report (TIFF) always has 24 bit color
depth which is default value, and I wanted to generate black & white
report (1 bit)
Documentation states that ColorDepth could be changed only for TIFF,
but it is not true, it always applies the default value and ignores
user settings ?!
Is this a bug in reporting server?
Any ideas on how to generate TIFF 1-bit with Reporting Server are
welcome...
Thanks,
JohnYour observation is correct and yes, this is a known bug. I have passed your
comments along to the appropriate parties.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Smith" <ng950@.yahoo.com> wrote in message
news:de84550e.0408200420.287ad689@.posting.google.com...
> Hello!
> I have a problem generating report (format = IMAGE) using following
> DeviceInfo settings:
> <DeviceInfo>
> <ColorDepth>1</ColorDepth>
> <OutputFormat>TIFF</OutputFormat>
> </DeviceInfo>
> Using this settings, generated report (TIFF) always has 24 bit color
> depth which is default value, and I wanted to generate black & white
> report (1 bit)
> Documentation states that ColorDepth could be changed only for TIFF,
> but it is not true, it always applies the default value and ignores
> user settings ?!
> Is this a bug in reporting server?
> Any ideas on how to generate TIFF 1-bit with Reporting Server are
> welcome...
> Thanks,
> John

BUG: SECURITY Problem 1355 error

Hello
I am receiving the following error for subscriptions we create:
ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
IMPORTANT: These subscriptions were working last week but after applying
ALL the outsanding Windows 2003 patches the problem showed up. Also, it
doesn't matter which delivery extension (including custom) we choose.. we get
the same 1355 error. We can run the reports interactively through report
manager so the issue is definetly subscription related.
We are running the following software:
- Windows 2003 including ALL patches released to date (latest monthly
patches appear to be causing this issue)
- SQL Server 2000 SP3a
- SQL Reporting Services Standard Addition SP1
- Exchange 2000. I only mention this as other posts have suggested this is
Lotus notes issue. In our case it doesn't matter which delivery extension we
choose.
I can get the subscriptions to work using a god like account (ie has domain
admin rights) so the issue appears to center around which user account
actually owns the subscription. If I modify the subscription with any other
account the subscription continues to function properly.
My question is how is the OwnerID found in Subscription table actualy used
becuase this appear to be the key to resolving this issue?
Regards
Kevin WeirA follow-up to my own post:
Here is sample eror log messages containing error:
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:03:: EventPolling
processing item cb787fc0-b06b-4bc0-aa65-386991109995
ReportingServicesService!dbpolling!7dc!10/22/2004-21:27:03:: EventPolling
processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!c24!10/22/2004-21:27:03:: Schedule
90015ab7-ec96-4891-b51f-c3ff202da46b executed at 10/22/2004 9:27:03 PM.
ReportingServicesService!schedule!c24!10/22/2004-21:27:03:: Creating Time
based subscription notification for subscription:
bb790a12-151c-4074-a79b-61c158088965
ReportingServicesService!library!c24!10/22/2004-21:27:04:: Schedule
90015ab7-ec96-4891-b51f-c3ff202da46b execution completed at 10/22/2004
9:27:04 PM.
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04:: EventPolling
finished processing item cb787fc0-b06b-4bc0-aa65-386991109995
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04::
NotificationPolling processing item a7712007-71d4-44cc-81c0-5a68b9bbdf6a
ReportingServicesService!dbpolling!7dc!10/22/2004-21:27:04::
NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!c24!10/22/2004-21:27:04:: i INFO: Call to
RenderFirst( '/Test' )
ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
ReportingServicesService!library!c24!10/22/2004-21:27:04:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server system
properties.
ReportingServicesService!emailextension!c24!10/22/2004-21:27:04:: Error
sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
The Report Server has encountered a configuration error; more details in the
log files -->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
at
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
userName)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String
userName, IntPtr userToken)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String
userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
catItemType, Byte[] secDesc, ReportOperation rptOper)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext
reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32&
executionOption, String& savedParametersXml, ReportSnapshot&
compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime&
historyDate)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum
type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters,
String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String
renderFormat, String deviceInfo)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!notification!c24!10/22/2004-21:27:04:: Notification
a7712007-71d4-44cc-81c0-5a68b9bbdf6a completed. Success: False, Status:
Failure sending mail: The Report Server has encountered a configuration
error; more details in the log files, DeliveryExtension: Report Server Email,
Report: Test, Attempt 0
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04::
NotificationPolling finished processing item
a7712007-71d4-44cc-81c0-5a68b9bbdf6a
ReportingServicesService!library!c24!10/22/2004-21:27:47:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs|||If anyone else reads this and has same problem I applied Windows 2003 hotfix
834859 which resolved the problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;834859
"Kevin Weir" wrote:
> Hello
> I am receiving the following error for subscriptions we create:
> ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files, AuthzInitializeContextFromSid: Win32 error: 1355;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files
> IMPORTANT: These subscriptions were working last week but after applying
> ALL the outsanding Windows 2003 patches the problem showed up. Also, it
> doesn't matter which delivery extension (including custom) we choose.. we get
> the same 1355 error. We can run the reports interactively through report
> manager so the issue is definetly subscription related.
> We are running the following software:
> - Windows 2003 including ALL patches released to date (latest monthly
> patches appear to be causing this issue)
> - SQL Server 2000 SP3a
> - SQL Reporting Services Standard Addition SP1
> - Exchange 2000. I only mention this as other posts have suggested this is
> Lotus notes issue. In our case it doesn't matter which delivery extension we
> choose.
> I can get the subscriptions to work using a god like account (ie has domain
> admin rights) so the issue appears to center around which user account
> actually owns the subscription. If I modify the subscription with any other
> account the subscription continues to function properly.
> My question is how is the OwnerID found in Subscription table actualy used
> becuase this appear to be the key to resolving this issue?
> Regards
> Kevin Weir
>

Monday, March 19, 2012

BUG(?): Distinct + variables TSQL

Hi All,
the following TSQL statment doesn't return expected value:
declare @.tmp varchar(500)
set @.tmp = ''
select distinct @.tmp = @.tmp + ',' + Col
from ( select 'A' as Col union all select 'B' union all select 'A' ) x
select @.tmp
expected: ',A,B', returns: ',B'
This bug (?) relates to SQL2000 and SQL2005
Regards
Marcin Zacharzewskideclare @.tmp varchar(500)
set @.tmp = ''
select @.tmp = @.tmp + ',' + Col
from
( select 'A' as Col
union
select 'B'
union
select 'A' ) x
select @.tmp
Hope this helps.
mzacharzewski@.linksoft.pl wrote:
> Hi All,
> the following TSQL statment doesn't return expected value:
> declare @.tmp varchar(500)
> set @.tmp = ''
> select distinct @.tmp = @.tmp + ',' + Col
> from ( select 'A' as Col union all select 'B' union all select 'A' ) x
> select @.tmp
> expected: ',A,B', returns: ',B'
> This bug (?) relates to SQL2000 and SQL2005
> Regards
> Marcin Zacharzewski|||Thank you, for your help,
but I just wanted to warn everybody, that such a bug exists in SQL200x
- In my particular situation I found a different walkaround.
select @.tmp = @.tmp + Col from
( select distinct Col from some_table) x
But this unexpected behaviour caused me some problems with my dynamic
SQL.
Hope MS will patch it in following SPs.
Regards:
Marcin Zacharzewski
gandhimanisha@.gmail.com napisal(a):
> declare @.tmp varchar(500)
> set @.tmp = ''
> select @.tmp = @.tmp + ',' + Col
> from
> ( select 'A' as Col
> union
> select 'B'
> union
> select 'A' ) x
> select @.tmp
> Hope this helps.
>
> mzacharzewski@.linksoft.pl wrote:|||mzacharzewski@.linksoft.pl wrote:
> Thank you, for your help,
> but I just wanted to warn everybody, that such a bug exists in SQL200x
> - In my particular situation I found a different walkaround.
> select @.tmp = @.tmp + Col from
> ( select distinct Col from some_table) x
> But this unexpected behaviour caused me some problems with my dynamic
> SQL.
> Hope MS will patch it in following SPs.
> Regards:
> Marcin Zacharzewski
>
Officially, it's not a bug. The correct result of an assignment in a
SELECT statement that returns multiple rows is undefined, so it's
dangerous to rely on it in any case. In fact Books Online says only
that the "last" value returned should be assigned to the variable.
Arguably therefore the result of the query you posted is correct.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> the following TSQL statment doesn't return expected value:
That is not valid syntax and hence we shouldn't have any expectation.
It is unfortunate that such methods are widely used and even promoted by
some as valid shortcuts to generate concatenated lists of column values from
multiple rows.
Anith|||Thanks David,
I my opinion common sense/programming practices suggest this should
work and shame on MS, it doesn't...
There shouldn't be such an unexpected behaviour - without DISTINCT
everything works great, using DISTINCT SQL Server returns one value -
definitely an error should be raised instead. I still consider this as
a bug.
I used:
SELECT @.var = @.var + col FROM table
syntax very often before - instead of other more complicated/longer
statements and that always worked as expected.
TSQL programmer shouldn't waste his time on checking (books online)
whether sth is possible and will work as expected - he should rely on
his programming practice and errors reported instead.
In my particular case this bug made me lots of problems with a
complicated report - consisting of a few views definitions which are
dynamically constucted based on rules defined in a table..
Regards:
Marcin Zacharzewski ( MCT, MCDBA, MCSD, MCSE)
David Portas napisal(a):
> mzacharzewski@.linksoft.pl wrote:
> Officially, it's not a bug. The correct result of an assignment in a
> SELECT statement that returns multiple rows is undefined, so it's
> dangerous to rely on it in any case. In fact Books Online says only
> that the "last" value returned should be assigned to the variable.
> Arguably therefore the result of the query you posted is correct.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||mzacharzewski@.linksoft.pl wrote:
> Thanks David,
> I my opinion common sense/programming practices suggest this should
> work and shame on MS, it doesn't...
> There shouldn't be such an unexpected behaviour - without DISTINCT
> everything works great, using DISTINCT SQL Server returns one value -
> definitely an error should be raised instead. I still consider this as
> a bug.
I'll agree that MS should in future disallow these assignments
altogether. BOTH the queries you posted ought to return a syntax error.
It's always seemed obvious to me that there was something faulty about
the @.tmp = @.tmp + ',' + Col syntax in a query with multiple rows. The
result is a string that depends on the evaluation order. But in SQL
there is no way to control the evaluation order so the result is always
going to be unpredictable. Unfortunately, it has turned out that many
people don't find this so obvious. The lesson is that idiosyncratic
"features", however convenient, shouldn't be viewed as a substitute for
good logical design on the part of the developer.
There are some reliable alternatives in SQL Server 2000 and you can
Google for them. In 2005 we have some other options. The following are
proper aggregations (they can be grouped) and you can control the order
of concatenation so as to ensure a deterministic result.
CREATE TABLE tbl (col1 INT NOT NULL, col2 VARCHAR(10) NOT NULL, PRIMARY
KEY (col1,col2));
INSERT INTO tbl (col1,col2) VALUES (1,'ABC');
INSERT INTO tbl (col1,col2) VALUES (1,'DEF');
INSERT INTO tbl (col1,col2) VALUES (2,'GHI');
INSERT INTO tbl (col1,col2) VALUES (2,'JKL');
WITH t AS (
SELECT col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS row_no
FROM tbl)
SELECT col1,
MAX(CASE WHEN row_no = 1 THEN col2 END)+
MAX(CASE WHEN row_no = 2 THEN ','+col2 ELSE '' END)+
MAX(CASE WHEN row_no = 3 THEN ','+col2 ELSE '' END)+
MAX(CASE WHEN row_no = 4 THEN ','+col2 ELSE '' END)
FROM t
GROUP BY col1 ;
SELECT DISTINCT col1,
SUBSTRING(
(SELECT ','+col2 AS [text()]
FROM tbl
WHERE col1 = T.col1
ORDER BY col2
FOR XML PATH( '' )
), 2,100) AS concat
FROM tbl AS T
ORDER BY col1 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||(mzacharzewski@.linksoft.pl) writes:
> I my opinion common sense/programming practices suggest this should
> work and shame on MS, it doesn't...
> There shouldn't be such an unexpected behaviour - without DISTINCT
> everything works great, using DISTINCT SQL Server returns one value -
> definitely an error should be raised instead. I still consider this as
> a bug.
No matter what your opinion is, it is not a bug, just undefined behaviour.

> I used:
> SELECT @.var = @.var + col FROM table
> syntax very often before - instead of other more complicated/longer
> statements and that always worked as expected.
> TSQL programmer shouldn't waste his time on checking (books online)
Here I must take strong exception. If you think that reading Books
Online is a waste of time, then you have a serious problem.

> whether sth is possible and will work as expected - he should rely on
> his programming practice and errors reported instead.
Relying on programming practice can lead you seriously astray. Consider
this statment:
SELECT ...
FROM tbl
WHERE b <> 0
AND a/b > 1
A programer who is new to SQL but have done a lot of C/C++ following
his programming practice only would gladly assume this would shortcut
and be safe. But he would be very wrong on that point, because SQL does
not shortcut.
Different language has different practices, and a good prorgammer must
check the documentation for the tool he is currently using.
But I can agree that it would be a good thing if SELECT @.x = @.x + col FROM
produced a warning that you are on dangerous grounds. Or even produced an
error (depending on compatibility level). Or simply the behaviour would be
the one that everyone expects.
(The correct way of coding the above is:
SELECT ...
FROM tbl
WHERE CASE WHEN b <> 0 THEN a/b END > 1
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I can agree only with your last sentence:
>Erland Sommarskog napisal(a):
>...
> But I can agree that it would be a good thing if SELECT @.x = @.x + col FROM
> produced a warning that you are on dangerous grounds. Or even produced an
> error (depending on compatibility level). Or simply the behaviour would be
> the one that everyone expects.
thats what I meant - the problem is that SQL allows such a
sensible-looking statements and doesn't raise an error/warning.
You misunderstood me - reading SQL Books online is not a waste of time
- I use this great help really often, but I think a programmer
shouldn't waste his time bothering (and digging BOL) whether a
sensible-looking statement is valid - especially considering that:
select @.tmp = @.tmp + col from table
works well in any scenario, and I've been using this for years.
My programming rule tells me:
everythnig that is not an error/warning, and looks-sensible - is
allowed and should give definend/predicitble result.
BTW I don't understand your examples, WHERE clause is evaluated first,
so both your queries are allowed:
select * from table where b<>0 and a/b > 1
and
select * from table where CASE WHEN b <> 0 THEN a/b END > 1
in this particular example, the first one is (IMHO) even better:)
Of course you have to use CASE in order to get a/b value in SELECT
clause:
select CASE WHEN b <> 0 THEN a/b END from table
but I am sure you know it.
Regards:
Marcin Zacharzewski ( MCT, MCDBA, MCSD, MCSE)
Erland Sommarskog napisal(a):
> (mzacharzewski@.linksoft.pl) writes:
> No matter what your opinion is, it is not a bug, just undefined behaviour.|||mzacharzewski@.linksoft.pl wrote:
> I think a programmer
> shouldn't waste his time bothering (and digging BOL) whether a
> sensible-looking statement is valid - especially considering that:
> select @.tmp = @.tmp + col from table
> works well in any scenario, and I've been using this for years.
> My programming rule tells me:
> everythnig that is not an error/warning, and looks-sensible - is
> allowed and should give definend/predicitble result.
How can a string concatentation possibly give a defined and predictable
result if the concatenation order is not specified? Maybe you think
that if you include ORDER BY it will affect the result. But ORDER BY
only applies to multiple row result sets, not to the execution order of
a query. So while it may *seem* to work for you (most of the time),
this undocumented curiosity is just that. It isn't sensible at all in
my book and I would generally recommend you avoid it.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
HenriYou have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
--
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
--
http://www.aspfaq.com/
(Reverse address to reply.)
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a écrit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
Henri
You have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
http://www.aspfaq.com/
(Reverse address to reply.)

> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a crit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

Bug with GetFloat?

I have field defined as float (price).

when I do the following command I get invalid cast.

reader.GetFloat(reader.GetOrdinal("price"));

I have to pull it with GetDouble.

reader.GetDouble (reader.getOrdinal("price"));

which works.

since it's defined as a float would that not make this an error.. or am I missing something?

GetFloat and GetDouble don't do any conversions. GetFloat expects a single precision and GetDouble a double precision value. Since the SQL Float is double precision you'll get an casting error with GetFloat. You could use GetFloat with a real field.

See "SQL Server Data Types and Their .NET Framework Equivalents" in books online for more info.

|||
When dealing with currency, why not create price as a sql type 'money'?

reader.GetDecimal (reader.getOrdinal("price")) would then work for you without throwing any casting exceptions.

Bug with GetFloat?

I have field defined as float (price).

when I do the following command I get invalid cast.

reader.GetFloat(reader.GetOrdinal("price"));

I have to pull it with GetDouble.

reader.GetDouble (reader.getOrdinal("price"));

which works.

since it's defined as a float would that not make this an error.. or am I missing something?

See "SQL Server Data Types and Their .NET Framework Equivalents" in books online for more info.

|||
When dealing with currency, why not create price as a sql type 'money'?

reader.GetDecimal (reader.getOrdinal("price")) would then work for you without throwing any casting exceptions.

Bug report RS with SP1

Microsoft gang,
I have the following query. if I use @.Shipping instead of @.CustomerShipTo as
a parameter name, at the preview stage, I receive @.StartDate not defined
error.
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
To)
GROUP BY ProductType,Product,UnitofMeasure,DDate
Regards,
CemSome more information about the parameter bug.
When I add the parameter manually (Report > Report Parameters), the query
below blows at run time, works perfect at query designer.
The way I get around this problem was to define the parameter in the result
query first. The moment I add it to the function below, it shows up in
report parameters. After configuration works perfect.
/*Main query*/
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
,@.BrandName,@.CanSize)
GROUP BY ProductType,Product,UnitofMeasure,DDate
/*Parameter query*/
SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM fn_CreateCanList()
UNION
SELECT Null , '--Select All--'
Regards,
Cem
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>
> Microsoft gang,
> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
as
> a parameter name, at the preview stage, I receive @.StartDate not defined
> error.
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
>
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> To)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> Regards,
> Cem
>|||Thanks. We'll take a look and see if we can repro here.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Could you send me definitions of your functions fn_CreateBlankSalesCases and
fn_CreateCanList?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Lev,
Below you may find the functions you requested. Thanks.
Regards,
Cem
CREATE FUNCTION fn_CreateBlankSalesCases (
@.StartDate as DateTime,
@.EndDate as DateTime,
@.Broker as VarChar(50),
@.Customer as VarChar(50),
@.CustomerShip as VarChar(50),
@.BrandName as VarChar(50),
@.CanSize as VarChar(50))
RETURNS @.DateTable TABLE (
ProductType VarChar(150),
Product VarChar(150),
TotalCases Int,
UnitofMeasure VarChar(10),
DDate DateTime)
/*Table created in memory*/
AS
BEGIN
DECLARE @.StartTempDate as DateTime
DECLARE @.ProductType as VarChar(150)
DECLARE @.Product as VarChar(150)
DECLARE @.TotalCases as Int
DECLARE @.UnitofMeasure as VarChar(10)
DECLARE @.TDate as DateTime
SET @.StartTempDate = @.StartDate
/* String manupilation for the brandname */
If (@.CanSize IS NULL)
SET @.CanSize = '%'
ELSE
SET @.CanSize = '%' + @.CanSize + '%'
If (@.BrandName IS NULL)
SET @.BrandName = '%'
ELSE
SET @.BrandName = @.BrandName + '%'
DECLARE SalesActualProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesActualProducts
FETCH NEXT FROM
SalesActualProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,@.TotalCases,@.UnitofMeasure,@.TDate)
FETCH NEXT FROM SalesActualProducts INTO @.ProductType, @.Product,
@.TotalCases, @.UnitofMeasure, @.TDate
END
CLOSE SalesActualProducts
DECLARE SalesProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesProducts
FETCH NEXT FROM
SalesProducts INTO @.ProductType, @.Product, @.TotalCases, @.UnitofMeasure,
@.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
WHILE (@.StartDate < @.EndDate)
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,0,@.UnitofMeasure,@.StartDate)
SET @.StartDate = DATEADD(month, 1, @.StartDate)
END
SET @.StartDate = @.StartTempDate
FETCH NEXT FROM SalesProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
END
CLOSE SalesProducts
/*Return Table*/
RETURN
END
CREATE FUNCTION fn_CreateCanList()
RETURNS
@.CanTable TABLE (
CanSize VarChar(150),
CanSizeOld VarChar(150)
)
/*Table created in memory*/
AS
BEGIN
DECLARE @.CanSizeTemp as VarChar(50)
DECLARE CanSizeList CURSOR FOR
SELECT DISTINCT can_size
FROM can_typ1
WHERE can_size IS NOT NULL
/*Start to insert data into the table */
OPEN CanSizeList
FETCH NEXT FROM
CanSizeList INTO @.CanSizeTemp
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF CHARINDEX('/',@.CanSizeTemp) > 0
INSERT INTO @.CanTable
VALUES(RIGHT(RTRIM(@.CanSizeTemp),LEN(@.CanSizeTemp)-CHARINDEX('/',@.CanSizeTem
p)) ,@.CanSizeTemp)
ELSE
INSERT INTO @.CanTable VALUES(@.CanSizeTemp, @.CanSizeTemp)
FETCH NEXT FROM CanSizeList INTO @.CanSizeTemp
END
CLOSE CanSizeList
/*Return Table*/
RETURN
END
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:uQ8Q1hnfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Could you send me definitions of your functions fn_CreateBlankSalesCases
and
> fn_CreateCanList?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> >
> >
> > Some more information about the parameter bug.
> >
> > When I add the parameter manually (Report > Report Parameters), the
query
> > below blows at run time, works perfect at query designer.
> >
> > The way I get around this problem was to define the parameter in the
> > result
> > query first. The moment I add it to the function below, it shows up in
> > report parameters. After configuration works perfect.
> >
> > /*Main query*/
> > SELECT
> > ProductType,Product,
> > SUM(TotalCases) as TotalCases,
> > UnitofMeasure,
> > DatePart(yyyy, DDate) as YDate,
> > DateName(mm,DDate) as MDate
> >
> > FROM
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> > ,@.BrandName,@.CanSize)
> > GROUP BY ProductType,Product,UnitofMeasure,DDate
> >
> > /*Parameter query*/
> > SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> > fn_CreateCanList()
> > UNION
> > SELECT Null , '--Select All--'
> >
> > Regards,
> > Cem
> >
> > "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> > news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
> >>
> >>
> >> Microsoft gang,
> >>
> >> I have the following query. if I use @.Shipping instead of
@.CustomerShipTo
> > as
> >> a parameter name, at the preview stage, I receive @.StartDate not
defined
> >> error.
> >>
> >> SELECT
> >> ProductType,Product,
> >> SUM(TotalCases) as TotalCases,
> >> UnitofMeasure,
> >> DatePart(yyyy, DDate) as YDate,
> >> DateName(mm,DDate) as MDate
> >>
> >> FROM
> >>
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> >> To)
> >> GROUP BY ProductType,Product,UnitofMeasure,DDate
> >>
> >> Regards,
> >> Cem
> >>
> >>
> >
> >
>

Sunday, March 11, 2012

Bug is com.microsoft.sqlserver.jdbc.SQLServerDataSource (2005 Beta

The following JUnit code fails:
// elided ...
public void testSelectMethod()
{
SQLServerDataSource source = new SQLServerDataSource();
source.setSelectMethod("cursor");
assertEquals("cursor", source.getSelectMethod());
}
// elided ...
with:
junit.framework.ComparisonFailure: Select method not set to cursor
expected:<cursor> but was:<null>
at junit.framework.Assert.assertEquals(Assert.java:81 )
at test.foo.assumptions.JTASQLTest.testSelectMethod(J TASQLTest.java:49)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154 )
at junit.framework.TestCase.runBare(TestCase.java:127 )
at junit.framework.TestResult$1.protect(TestResult.ja va:106)
at junit.framework.TestResult.runProtected(TestResult .java:124)
at junit.framework.TestResult.run(TestResult.java:109 )
at junit.framework.TestCase.run(TestCase.java:118)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)
jef - integralpath.blogs.com
Yes we changed the name of the SelectMethod property to
forwardReadOnlyMethod.
So you would say:
forwardReadOnlyMethod=direct
or
forwardReadOnlyMethod=serverCursor
Unfortunately we have not yet updated the SQLServerDataSource class to work
with this new setting yet.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:evctk3ozFHA.3312@.TK2MSFTNGP09.phx.gbl...
>
> jef wrote:
>
>
> Check the docs. I believe SelectMethod=cursor is now functionally
> meaningless with this version of the driver, and maybe they allow
> you to make the set call, just so it doesn't break existing code
> that was written for the previous driver, that shares zero code
> with this new one. They might also document that getSelectMethod()
> will do what it does...
> I think the best course is to verify my first contention, and
> then just not call either method for this one...
> Joe Weinstein at BEA
>
|||Note we are planning on changing this back to allowing selectMethod due to
huge customer demand.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"jef" <jef@.discussions.microsoft.com> wrote in message
news:5FB92280-7932-4722-84AC-72A3102980E5@.microsoft.com...
> The following JUnit code fails:
> // elided ...
> public void testSelectMethod()
> {
> SQLServerDataSource source = new SQLServerDataSource();
> source.setSelectMethod("cursor");
> assertEquals("cursor", source.getSelectMethod());
> }
> // elided ...
> with:
> junit.framework.ComparisonFailure: Select method not set to cursor
> expected:<cursor> but was:<null>
> at junit.framework.Assert.assertEquals(Assert.java:81 )
> at test.foo.assumptions.JTASQLTest.testSelectMethod(J TASQLTest.java:49)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at junit.framework.TestCase.runTest(TestCase.java:154 )
> at junit.framework.TestCase.runBare(TestCase.java:127 )
> at junit.framework.TestResult$1.protect(TestResult.ja va:106)
> at junit.framework.TestResult.runProtected(TestResult .java:124)
> at junit.framework.TestResult.run(TestResult.java:109 )
> at junit.framework.TestCase.run(TestCase.java:118)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)
>
> --
> jef - integralpath.blogs.com

Bug in SELECT TOP (100) PERCENT with ORDER BY in SQLExpress?

I have a problem with the following query in SQLExpress:

SELECT TOP (100) PERCENT ClientSurname, ClientName
FROM dbo.Client
ORDER BY ClientSurname, ClientName

The query returns always assorted data ignoring the ORDER BY keyword, no matter if the query is invoked directly from Management Studio Express CTP as a View, Table-valued function or called from an Access ADP project. The result is always assorted.

Now an interesting thing is that the syntax below returns always an expected order:

SELECT TOP (99) PERCENT
SELECT TOP 10000

Am I missing something or is it a bug in SQLExpress?I never use the brackets (parenthesis) around the percentage. Maybe your second example is just using the top 10000 (not the 99%) and sorting your recs as you expect.

Keeping in mind that I know nothing of this SQLExpress :)|||TOP is a function, not a predicate as it used to be in 2K.

Thursday, March 8, 2012

Bug in MS Management Studio for SQL-Server 2005 while creating a publication!

Hello,

I found a bug in Management Studio, which can be reproduced with following steps:

1. Create a new publication with the 'New Publication Wizard'
2. Select a database which contains at least 2 tables.
3. Select 'transactional publicatioon'
4. Select at least 2 tables as articles
5. Set article properties for all table articles
6. Change 'Statement Delivery' to 'Insert statement', 'Update statement' and 'Delete statement' and save the article properties.
7. If you now check the properties for a single article you find that the 'Statement Delivery' is set to 'Do not replicate...'. If you generate this publication and create a subscritpion you will see, that actual no replication take place.

It took me a while to find out why my replication doesn't work! :-((

Wolfgang Kunk

PS : Management Studio version is 9.00.1399.00

Unfortunately that part of the wizard is kind of broke, and it won't get fixed for SP2 either. A similar issue was raised in an earlier post as well - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=984751&SiteID=1.

The workaround is to either use TSQL scripts to set everything up, or set everything up with UI and then immediately issue a sp_changearticle to set it.

As a side note, may I ask why you want to use "Insert/Update/Delete statement" instead of the default stored proc? This can have bad performance impact against a SQL subscriber.

|||Normally we use T-SQL scripts to set up replication. So it is not a real big problem.
I wanted to see if it is a known bug!

Can you please explain what the bad performance impact can be?
As far as I know the default stored procs execute nothing else then a simple insert, update or delete statement. So I cannot see a bad impact. The only thing is that the optimizer optimzes a stored proc once while it has to optimize insert, update ans delete-statements every time.

Wolfgang Kunk|||

When using stored procs, each proc is fired off as an individual RPC, and the proc itself generates a plan that can be reused over and over for optimal performance.

When using SQL, we fit as many commands as we can (I think up to 500) into a single parameterized sp_executesql and execute it all at once. Meaning one single sp_executesql call can have anywhere from one to 500 different ins/upd/del statements - this generates one single plan. The next sp_executesql call will most likely be different, and generate a totally different plan. So what happens is every call will most likely have to be compiled because you'll never get plan reuse. In an OLTP scenario where you're replicating thousands of commands a second, you'lll definitely see an impact in performance.

For proof, apply a mixed/random workload to the publisher and then trace the distribution agent calls at the subscriber using SQL and stored procs, you'll see what I mean.

SQL is used mostly with heterogeneous subscriber cases and the very rare case when customer has a purpose for SQL subscriber cases.

|||Thanks for the explanation.

At the moment we only use heterogenous subscriber, where we work with SQL. So I used SQL for my first MS-MS Replication as well. I will take your recommendation in account for my further tests!

Wolfgang Kunk

Saturday, February 25, 2012

Buffer Overrun?

Over the past couple of days we have seen the following message in the SQL
Server Logs:
"SqlSecurityHandler is invoked. Potential buffer overrun detected - server
is terminating."
At this point, the SQL Server and SQL Server Agent will terminate and have
to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
Has anyone else seen this?We've just seen the error. Our production environment went down with that
message in the log. It took about 8 hours before we were up and running
again. (The Sql Server automatically started a restore operation)
We are also running SQL2000(SP4) on Windows Server 2003.
Veritas NetBackup had just finished a backup when the error occurred.
I really hope that Microsoft can help out with this, cause this is a
_serious_ problem.
SQL server just terminates!
Do you have any new information?
"JKKUS" wrote:
> Over the past couple of days we have seen the following message in the SQL
> Server Logs:
> "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> is terminating."
> At this point, the SQL Server and SQL Server Agent will terminate and have
> to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> Has anyone else seen this?
>|||Nothing yet. If you find anything out, please post. This problem is annoying.
"Bjorn L" wrote:
> We've just seen the error. Our production environment went down with that
> message in the log. It took about 8 hours before we were up and running
> again. (The Sql Server automatically started a restore operation)
> We are also running SQL2000(SP4) on Windows Server 2003.
> Veritas NetBackup had just finished a backup when the error occurred.
> I really hope that Microsoft can help out with this, cause this is a
> _serious_ problem.
> SQL server just terminates!
> Do you have any new information?
> "JKKUS" wrote:
> > Over the past couple of days we have seen the following message in the SQL
> > Server Logs:
> >
> > "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> > is terminating."
> >
> > At this point, the SQL Server and SQL Server Agent will terminate and have
> > to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> > Has anyone else seen this?
> >
> >|||Its more than annoying to us, since our production database shuts down.
Anyway, from what we've seen, it definitely seems to be related to backups.
We disabled all backup activities this weekend, and from that on we haven't
seen the problem. (fingers crossed though)
We'll keep investigating.
Would be interesting to hear if its backups thats causing your problems as
well?
"JKKUS" wrote:
> Nothing yet. If you find anything out, please post. This problem is annoying.
> "Bjorn L" wrote:
> > We've just seen the error. Our production environment went down with that
> > message in the log. It took about 8 hours before we were up and running
> > again. (The Sql Server automatically started a restore operation)
> >
> > We are also running SQL2000(SP4) on Windows Server 2003.
> > Veritas NetBackup had just finished a backup when the error occurred.
> >
> > I really hope that Microsoft can help out with this, cause this is a
> > _serious_ problem.
> > SQL server just terminates!
> >
> > Do you have any new information?
> >
> > "JKKUS" wrote:
> >
> > > Over the past couple of days we have seen the following message in the SQL
> > > Server Logs:
> > >
> > > "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> > > is terminating."
> > >
> > > At this point, the SQL Server and SQL Server Agent will terminate and have
> > > to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> > > Has anyone else seen this?
> > >
> > >

buffer latch?

The following error got posted in the error log:
Could not open FCB for invalid file ID 21808 in
database 'USAAREP'. Table or database may be corrupted..
It was followed by series of messages:
Time out occurred while waiting for buffer latch type 3,
bp 0x147cc500, page (21808:808465440), stat 0x40d, object
ID 8:-271269168:0, waittime 500. Continuing to wait.
At this point we cannot do any backups using SQLMAINT or
BACKUP neither through EM nor QA.
Anybody heard of how to fix this? The server is 7.0 SP4.
TIA,
RobertRobert
I had a problem like this, it was fun to fix as I could
find very little info anywhere. I was using SQL 7 and it
hangs the whole database and you need to stop and start
SQL Server to fix it.
You need to identify the process that is causing the
problem, the buffer latch error message should point you
at the object. Try to work out what processes where
running against that object shortly before the problem
occured.
What was happening in my case was that we had a stored
procedure that updated a table, but due to a design fault
in the database, a small amount of duplicate records were
getting inserted to the table. At the end of the stored
procedure it called another stored procedure to delete the
duplicate records. At some time during this process an
automatic checkpoint kicked in. A combination of updated
records not yet physically written to the database, a
checkpoint trying to write them and another process trying
to delete records that had not yet been written, seemed to
cause the problem.
In the short term I changed the job to have three steps,
do the updates, perform a chackpoint and then the deletes.
For the longterm fix, I rewrote the update process to not
insert duplicates.
You possibilly have something similar going on. Out of
interest do you have a checkpoint process trying to run,
but unable to finish?
Hope this helps
Regards
John

Buffer Latch error

quote:

> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>

|||The server runs SMS and every so often during the day it
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June

quote:

>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that

this is a horribly

quote:

>handled error and perhaps a bug. You don't provide the

specific error number

quote:

>but assuming it's the same thing I've seen on numerous

occaisions...

quote:

>this error often points to a) a server with inadequte IO

capacity and/or b)

quote:

>queries that are inneffecient for one reason or another

that are

quote:

>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query

might be slow...

quote:

>but I do NOT like the fact that the query simply times

out. I'd rather let

quote:

>it run and have warning messages written to the log that

indicate a problem

quote:

>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues

at the server and

quote:

>query level.
>Of course it could be something completely different.

There's not enough

quote:

>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>

wrote in message

quote:

>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
on[QUOTE]
EC[QUOTE]
4,bp[QUOTE]
2000[QUOTE]
>
>.
>

Buffer Latch error

I have a backup database job that failed with the following error on sql server 2005

Executed as user: TestUSer. Time-out occurred while waiting for buffer latch type 3 for page (1:10541989), database ID 8. [SQLSTATE 42000] (Error 845) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Does anyone know why this happened? Is it the server or the array?

Any help is appreciated.

Thanks

Check your database storage for hardware errors (disk failures, fibre connection, I/O errors, etc) This was a bug on SQL 2000 and 7.0, solved with a service pack. But i guess this is not the answer here. So, please check your equipment 1st.

Buffer Latch error

I have been having the following time out error message on
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>|||The server runs SMS and every so often during the day it
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June
>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that
this is a horribly
>handled error and perhaps a bug. You don't provide the
specific error number
>but assuming it's the same thing I've seen on numerous
occaisions...
>this error often points to a) a server with inadequte IO
capacity and/or b)
>queries that are inneffecient for one reason or another
that are
>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query
might be slow...
>but I do NOT like the fact that the query simply times
out. I'd rather let
>it run and have warning messages written to the log that
indicate a problem
>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues
at the server and
>query level.
>Of course it could be something completely different.
There's not enough
>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
>> I have been having the following time out error message
on
>> my production server for a while now.
>> Waiting for type 0x4, current count 0xa, current owning
EC
>> 0x5E0B63C8.
>> Time out occurred while waiting for buffer latch type
4,bp
>> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
>> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>>
>> The is an sms server that runs SQL2000 sp3 on Windows
2000
>> sp4. The microsoft suggestion is to apply sp3 - which I
>> already did when the server was built. Has anyone come
>> accross this problem and if so, how did you fix? Is
>> reapplying service pack a good thing to do? Thanks:
>>
>
>.
>

Friday, February 24, 2012

Browsing for Publication error message

I have setup everything from my domain to the dmz following the document from Replication Answers.com "Replication Across Non-Trusted Domains or Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ publication, I see the DMZ server (Publisher) but when I click the (+) to view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the domain!!!! I have setup a sql account on both the domain sql server & the dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription by using Hilary's book & scripts from QA (But couldn't get the Agent to start - Start/Stop Agent were grayed out)
JLS
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the subscriber. In the subscription properties, I told it to look in this folder on the subscriber, but I get an error message that the process could not read the file.
I'm just trying everything & I think I'm down to my problem has to do with the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document from Replication Answers.com "Replication Across Non-Trusted Domains or Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ publication, I see the DMZ server (Publisher) but when I click the (+) to view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the domain!!!! I have setup a sql account on both the domain sql server & the dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription by using Hilary's book & scripts from QA (But couldn't get the Agent to start - Start/Stop Agent were grayed out)
JLS
|||can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||Hi Jude,
While using nosync subscription is a perfectly valid option, the problem that your experienced seems to be the kind that is relatively straightforward to resolve. If I understand the situation correctly, you have manually copied the snapshot files to a different location on the subscriber and attempted to apply the snapshot from the new location via the use of the alternate snapshot folder property of the pull subscription. One non-intuitive quirk that typically catches people off-guard is to set the alternate snapshot location directly to where the snapshot files are rather than to where the unc or ftp folder is. That is, you need to have the <unc|ftp>\<Publisher>_<PublisherDB>_<Publication>\ <timestamp>\<snapshot files> folder structure underneath your alternate snapshot folder. So, if you manually copy the snapshot files around, you would need to recreate the same folder structure that I mention previously in your target location and then point the alternate snapshot folder to the "root" of that sub-structure. I would be interested to know if your scenario has more twists than what I expected.
-Raymond
"JLS" <judes@.email.uophx.edu> wrote in message news:%23i5x8JV%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||Raymond,
Thank you for this answer, I am going to check to make sure I have my alternate snapshot folder set as you suggest.
If I may impose, when browsing for the publication on the DMZ to setup an anonymous pull subscription, why would I receive an error message about the domain account on the Subscriber that runs the SQL Server Agent service?
Why is it referring to this account at all?
Certainly both servers Sql Server agent accounts do not have to be the same, especially when one of the servers is out on the net & not even on the domain?
Any light you can shed on this will be greatly appreciated.
Thanx!!!!
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uw5NrKc%23FHA.912@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
While using nosync subscription is a perfectly valid option, the problem that your experienced seems to be the kind that is relatively straightforward to resolve. If I understand the situation correctly, you have manually copied the snapshot files to a different location on the subscriber and attempted to apply the snapshot from the new location via the use of the alternate snapshot folder property of the pull subscription. One non-intuitive quirk that typically catches people off-guard is to set the alternate snapshot location directly to where the snapshot files are rather than to where the unc or ftp folder is. That is, you need to have the <unc|ftp>\<Publisher>_<PublisherDB>_<Publication>\ <timestamp>\<snapshot files> folder structure underneath your alternate snapshot folder. So, if you manually copy the snapshot files around, you would need to recreate the same folder structure that I mention previously in your target location and then point the alternate snapshot folder to the "root" of that sub-structure. I would be interested to know if your scenario has more twists than what I expected.
-Raymond
"JLS" <judes@.email.uophx.edu> wrote in message news:%23i5x8JV%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||Hi Jude,
It is sort of difficult for me to visualize exactly what is going on with the description you gave previously (or any kind of description that you can possibly provide over the newsgroup) but I would guess that the subscriber SQL Server Agent service account was somehow added to the publication access list of the publication on the DMZ. If this is the case then you will likely see the kind of error message that you saw when browsing the publication properties through the UI (which also enumerates entries in the publication acces list). To find out, select from the MSpublication_access table in the distribution database and see if the account for your Subscriber's SQL Server Agent service account shows up. If so, manually removing the row will likely resolve the issue for you.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23dfKH4c%23FHA.1148@.tk2msftngp13.phx.gbl...
Raymond,
Thank you for this answer, I am going to check to make sure I have my alternate snapshot folder set as you suggest.
If I may impose, when browsing for the publication on the DMZ to setup an anonymous pull subscription, why would I receive an error message about the domain account on the Subscriber that runs the SQL Server Agent service?
Why is it referring to this account at all?
Certainly both servers Sql Server agent accounts do not have to be the same, especially when one of the servers is out on the net & not even on the domain?
Any light you can shed on this will be greatly appreciated.
Thanx!!!!
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uw5NrKc%23FHA.912@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
While using nosync subscription is a perfectly valid option, the problem that your experienced seems to be the kind that is relatively straightforward to resolve. If I understand the situation correctly, you have manually copied the snapshot files to a different location on the subscriber and attempted to apply the snapshot from the new location via the use of the alternate snapshot folder property of the pull subscription. One non-intuitive quirk that typically catches people off-guard is to set the alternate snapshot location directly to where the snapshot files are rather than to where the unc or ftp folder is. That is, you need to have the <unc|ftp>\<Publisher>_<PublisherDB>_<Publication>\ <timestamp>\<snapshot files> folder structure underneath your alternate snapshot folder. So, if you manually copy the snapshot files around, you would need to recreate the same folder structure that I mention previously in your target location and then point the alternate snapshot folder to the "root" of that sub-structure. I would be interested to know if your scenario has more twists than what I expected.
-Raymond
"JLS" <judes@.email.uophx.edu> wrote in message news:%23i5x8JV%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||Sorry for the cryptic description, I posted in another thread describing my problem in detail, so let me do that again with you & perhaps you can 'see' where I am going wrong.
Subscriber = On my domain
DMZ SQL Server = Not on my domain (1 way trust is setup)
There is a firewall involved here (My Network Admin has VERY reluctantly setup rules on the firewall)
================================================== ================================================== ==========
The Subscriber can connect to the Publisher in the DMZ, no problem. As a matter of fact, this particular Subscriber server is publishing to this DMZ server (One way trust is working fine)
I have the alias set & I have an entry in the hosts file for this DMZ server.
I have the publication setup to allow anonymous subscriptions.
Here's what I am down to when I try to setup the Pull Subscription at the Subscriber, from the publication on the DMZ server.
I get an error message about not being able to retrieve info about publications on the DMZ Server AND I get an error message about not being able to retrieve info about the Domain account that is the Service startup account for Sql Agent on the Subscriber. I don't want to use this domain account anyway, I have the publication setup to use a Sql Account that I created specifically for this replication.
Why would it be trying to use the Sql Agent Service Account, and not REPLDMZ sql account I setup on both boxes?
================================================== ================================================== ===========
So, I tried to set everything up by using scripts on the Subscriber.
exec sp_addpullsubscription
@.publisher = 'MARKYMARK',
@.publisher_db = 'SNN',
@.publication = 'SNN',
@.independent_agent = 'True',
@.subscription_type = 'anonymous',
@.update_mode = 'read only'
exec sp_addpullsubscription_agent
@.publisher = 'MARKYMARK',
@.publisher_db = 'SNN',
@.publication = 'SNN',
@.distributor = 'MARKYMARK',
@.subscriber_security_mode = 1,
@.distributor_security_mode = 1,
@.subscriber_login = 'repldmz',
@.subscriber_password = 'insert password here'
I get a connection failure when I try to start synchronizing, it says it can't connect to the distributor on the DMZ server & appears to be trying to connect with the Subscriber domain account that runs sql agent on the subscriber.
I haven't setup replication using scripts before, so this was just a shot in the dark since I cannot browse the DMZ publications from EM.
Is this a better description of my issue? I feel like I'm really close to getting this anonymous pull setup, but one piece of the puzzle is missing?
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:ODHM0Jd%23FHA.740@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
It is sort of difficult for me to visualize exactly what is going on with the description you gave previously (or any kind of description that you can possibly provide over the newsgroup) but I would guess that the subscriber SQL Server Agent service account was somehow added to the publication access list of the publication on the DMZ. If this is the case then you will likely see the kind of error message that you saw when browsing the publication properties through the UI (which also enumerates entries in the publication acces list). To find out, select from the MSpublication_access table in the distribution database and see if the account for your Subscriber's SQL Server Agent service account shows up. If so, manually removing the row will likely resolve the issue for you.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23dfKH4c%23FHA.1148@.tk2msftngp13.phx.gbl...
Raymond,
Thank you for this answer, I am going to check to make sure I have my alternate snapshot folder set as you suggest.
If I may impose, when browsing for the publication on the DMZ to setup an anonymous pull subscription, why would I receive an error message about the domain account on the Subscriber that runs the SQL Server Agent service?
Why is it referring to this account at all?
Certainly both servers Sql Server agent accounts do not have to be the same, especially when one of the servers is out on the net & not even on the domain?
Any light you can shed on this will be greatly appreciated.
Thanx!!!!
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uw5NrKc%23FHA.912@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
While using nosync subscription is a perfectly valid option, the problem that your experienced seems to be the kind that is relatively straightforward to resolve. If I understand the situation correctly, you have manually copied the snapshot files to a different location on the subscriber and attempted to apply the snapshot from the new location via the use of the alternate snapshot folder property of the pull subscription. One non-intuitive quirk that typically catches people off-guard is to set the alternate snapshot location directly to where the snapshot files are rather than to where the unc or ftp folder is. That is, you need to have the <unc|ftp>\<Publisher>_<PublisherDB>_<Publication>\ <timestamp>\<snapshot files> folder structure underneath your alternate snapshot folder. So, if you manually copy the snapshot files around, you would need to recreate the same folder structure that I mention previously in your target location and then point the alternate snapshot folder to the "root" of that sub-structure. I would be interested to know if your scenario has more twists than what I expected.
-Raymond
"JLS" <judes@.email.uophx.edu> wrote in message news:%23i5x8JV%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||Did you see the Subscriber's SQL Server Agent account in MSpublication_access?
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:u50SRVd%23FHA.2704@.TK2MSFTNGP15.phx.gbl...
Sorry for the cryptic description, I posted in another thread describing my problem in detail, so let me do that again with you & perhaps you can 'see' where I am going wrong.
Subscriber = On my domain
DMZ SQL Server = Not on my domain (1 way trust is setup)
There is a firewall involved here (My Network Admin has VERY reluctantly setup rules on the firewall)
================================================== ================================================== ==========
The Subscriber can connect to the Publisher in the DMZ, no problem. As a matter of fact, this particular Subscriber server is publishing to this DMZ server (One way trust is working fine)
I have the alias set & I have an entry in the hosts file for this DMZ server.
I have the publication setup to allow anonymous subscriptions.
Here's what I am down to when I try to setup the Pull Subscription at the Subscriber, from the publication on the DMZ server.
I get an error message about not being able to retrieve info about publications on the DMZ Server AND I get an error message about not being able to retrieve info about the Domain account that is the Service startup account for Sql Agent on the Subscriber. I don't want to use this domain account anyway, I have the publication setup to use a Sql Account that I created specifically for this replication.
Why would it be trying to use the Sql Agent Service Account, and not REPLDMZ sql account I setup on both boxes?
================================================== ================================================== ===========
So, I tried to set everything up by using scripts on the Subscriber.
exec sp_addpullsubscription
@.publisher = 'MARKYMARK',
@.publisher_db = 'SNN',
@.publication = 'SNN',
@.independent_agent = 'True',
@.subscription_type = 'anonymous',
@.update_mode = 'read only'
exec sp_addpullsubscription_agent
@.publisher = 'MARKYMARK',
@.publisher_db = 'SNN',
@.publication = 'SNN',
@.distributor = 'MARKYMARK',
@.subscriber_security_mode = 1,
@.distributor_security_mode = 1,
@.subscriber_login = 'repldmz',
@.subscriber_password = 'insert password here'
I get a connection failure when I try to start synchronizing, it says it can't connect to the distributor on the DMZ server & appears to be trying to connect with the Subscriber domain account that runs sql agent on the subscriber.
I haven't setup replication using scripts before, so this was just a shot in the dark since I cannot browse the DMZ publications from EM.
Is this a better description of my issue? I feel like I'm really close to getting this anonymous pull setup, but one piece of the puzzle is missing?
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:ODHM0Jd%23FHA.740@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
It is sort of difficult for me to visualize exactly what is going on with the description you gave previously (or any kind of description that you can possibly provide over the newsgroup) but I would guess that the subscriber SQL Server Agent service account was somehow added to the publication access list of the publication on the DMZ. If this is the case then you will likely see the kind of error message that you saw when browsing the publication properties through the UI (which also enumerates entries in the publication acces list). To find out, select from the MSpublication_access table in the distribution database and see if the account for your Subscriber's SQL Server Agent service account shows up. If so, manually removing the row will likely resolve the issue for you.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23dfKH4c%23FHA.1148@.tk2msftngp13.phx.gbl...
Raymond,
Thank you for this answer, I am going to check to make sure I have my alternate snapshot folder set as you suggest.
If I may impose, when browsing for the publication on the DMZ to setup an anonymous pull subscription, why would I receive an error message about the domain account on the Subscriber that runs the SQL Server Agent service?
Why is it referring to this account at all?
Certainly both servers Sql Server agent accounts do not have to be the same, especially when one of the servers is out on the net & not even on the domain?
Any light you can shed on this will be greatly appreciated.
Thanx!!!!
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uw5NrKc%23FHA.912@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
While using nosync subscription is a perfectly valid option, the problem that your experienced seems to be the kind that is relatively straightforward to resolve. If I understand the situation correctly, you have manually copied the snapshot files to a different location on the subscriber and attempted to apply the snapshot from the new location via the use of the alternate snapshot folder property of the pull subscription. One non-intuitive quirk that typically catches people off-guard is to set the alternate snapshot location directly to where the snapshot files are rather than to where the unc or ftp folder is. That is, you need to have the <unc|ftp>\<Publisher>_<PublisherDB>_<Publication>\ <timestamp>\<snapshot files> folder structure underneath your alternate snapshot folder. So, if you manually copy the snapshot files around, you would need to recreate the same folder structure that I mention previously in your target location and then point the alternate snapshot folder to the "root" of that sub-structure. I would be interested to know if your scenario has more twists than what I expected.
-Raymond
"JLS" <judes@.email.uophx.edu> wrote in message news:%23i5x8JV%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS
|||No, the publication access list has the sql account I created specifically for this replication (repldmz), the sa account, and distributor_admin listed.
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:%23wnKBed%23FHA.472@.TK2MSFTNGP15.phx.gbl...
Did you see the Subscriber's SQL Server Agent account in MSpublication_access?
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:u50SRVd%23FHA.2704@.TK2MSFTNGP15.phx.gbl...
Sorry for the cryptic description, I posted in another thread describing my problem in detail, so let me do that again with you & perhaps you can 'see' where I am going wrong.
Subscriber = On my domain
DMZ SQL Server = Not on my domain (1 way trust is setup)
There is a firewall involved here (My Network Admin has VERY reluctantly setup rules on the firewall)
================================================== ================================================== ==========
The Subscriber can connect to the Publisher in the DMZ, no problem. As a matter of fact, this particular Subscriber server is publishing to this DMZ server (One way trust is working fine)
I have the alias set & I have an entry in the hosts file for this DMZ server.
I have the publication setup to allow anonymous subscriptions.
Here's what I am down to when I try to setup the Pull Subscription at the Subscriber, from the publication on the DMZ server.
I get an error message about not being able to retrieve info about publications on the DMZ Server AND I get an error message about not being able to retrieve info about the Domain account that is the Service startup account for Sql Agent on the Subscriber. I don't want to use this domain account anyway, I have the publication setup to use a Sql Account that I created specifically for this replication.
Why would it be trying to use the Sql Agent Service Account, and not REPLDMZ sql account I setup on both boxes?
================================================== ================================================== ===========
So, I tried to set everything up by using scripts on the Subscriber.
exec sp_addpullsubscription
@.publisher = 'MARKYMARK',
@.publisher_db = 'SNN',
@.publication = 'SNN',
@.independent_agent = 'True',
@.subscription_type = 'anonymous',
@.update_mode = 'read only'
exec sp_addpullsubscription_agent
@.publisher = 'MARKYMARK',
@.publisher_db = 'SNN',
@.publication = 'SNN',
@.distributor = 'MARKYMARK',
@.subscriber_security_mode = 1,
@.distributor_security_mode = 1,
@.subscriber_login = 'repldmz',
@.subscriber_password = 'insert password here'
I get a connection failure when I try to start synchronizing, it says it can't connect to the distributor on the DMZ server & appears to be trying to connect with the Subscriber domain account that runs sql agent on the subscriber.
I haven't setup replication using scripts before, so this was just a shot in the dark since I cannot browse the DMZ publications from EM.
Is this a better description of my issue? I feel like I'm really close to getting this anonymous pull setup, but one piece of the puzzle is missing?
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:ODHM0Jd%23FHA.740@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
It is sort of difficult for me to visualize exactly what is going on with the description you gave previously (or any kind of description that you can possibly provide over the newsgroup) but I would guess that the subscriber SQL Server Agent service account was somehow added to the publication access list of the publication on the DMZ. If this is the case then you will likely see the kind of error message that you saw when browsing the publication properties through the UI (which also enumerates entries in the publication acces list). To find out, select from the MSpublication_access table in the distribution database and see if the account for your Subscriber's SQL Server Agent service account shows up. If so, manually removing the row will likely resolve the issue for you.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23dfKH4c%23FHA.1148@.tk2msftngp13.phx.gbl...
Raymond,
Thank you for this answer, I am going to check to make sure I have my alternate snapshot folder set as you suggest.
If I may impose, when browsing for the publication on the DMZ to setup an anonymous pull subscription, why would I receive an error message about the domain account on the Subscriber that runs the SQL Server Agent service?
Why is it referring to this account at all?
Certainly both servers Sql Server agent accounts do not have to be the same, especially when one of the servers is out on the net & not even on the domain?
Any light you can shed on this will be greatly appreciated.
Thanx!!!!
Jude
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uw5NrKc%23FHA.912@.TK2MSFTNGP11.phx.gbl...
Hi Jude,
While using nosync subscription is a perfectly valid option, the problem that your experienced seems to be the kind that is relatively straightforward to resolve. If I understand the situation correctly, you have manually copied the snapshot files to a different location on the subscriber and attempted to apply the snapshot from the new location via the use of the alternate snapshot folder property of the pull subscription. One non-intuitive quirk that typically catches people off-guard is to set the alternate snapshot location directly to where the snapshot files are rather than to where the unc or ftp folder is. That is, you need to have the <unc|ftp>\<Publisher>_<PublisherDB>_<Publication>\ <timestamp>\<snapshot files> folder structure underneath your alternate snapshot folder. So, if you manually copy the snapshot files around, you would need to recreate the same folder structure that I mention previously in your target location and then point the alternate snapshot folder to the "root" of that sub-structure. I would be interested to know if your scenario has more twists than what I expected.
-Raymond
"JLS" <judes@.email.uophx.edu> wrote in message news:%23i5x8JV%23FHA.4092@.TK2MSFTNGP10.phx.gbl...
At this point I'll try anything!!!!!!!!!!
What do you mean? Where do I try a nosync?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXtGtM79FHA.3804@.TK2MSFTNGP14.phx.gbl...
can you try a nosync?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ebk7Zx49FHA.1996@.TK2MSFTNGP10.phx.gbl...
I tried yet another approach...
I pulled the initial snapshot folder off the publisher & put it on the
subscriber. In the subscription properties, I told it to look in this
folder on the subscriber, but I get an error message that the process could
not read the file.
I'm just trying everything & I think I'm down to my problem has to do with
the initial snapshot. PLEASE shove me in the right direction....
"JLS" <jlshoop@.hotmail.com> wrote in message
news:OvacGx39FHA.2616@.TK2MSFTNGP15.phx.gbl...
I have setup everything from my domain to the dmz following the document
from Replication Answers.com "Replication Across Non-Trusted Domains or
Using the Internet"
When setting up the anonymous pull subscription, browsing for the DMZ
publication, I see the DMZ server (Publisher) but when I click the (+) to
view publications I am receiving the error message;
"Could not obtain information about Windows NT group / user 'DOMAIN
NAME/SQLAdministrator' "
This is the account that runs my Sql Server Agent of the sql server in the
domain (The Subscriber)
I don't want to use this account, the DMZ server is a part of the
domain!!!! I have setup a sql account on both the domain sql server & the
dmz sql server.
What am I missing? Where would it be retrieving this account?
I feel like I am so close. I was actually able to create a subscription
by using Hilary's book & scripts from QA (But couldn't get the Agent to
start - Start/Stop Agent were grayed out)
JLS

Tuesday, March 20, 2012

bug? DeviceInfo ColorDepth ignored for TIFF image

BUG: SECURITY Problem 1355 error

Monday, March 19, 2012

BUG(?): Distinct + variables TSQL

bug with TABLE variable??

bug with TABLE variable??

Bug with GetFloat?

Bug with GetFloat?

Bug report RS with SP1

Sunday, March 11, 2012

Bug is com.microsoft.sqlserver.jdbc.SQLServerDataSource (2005 Beta

Bug in SELECT TOP (100) PERCENT with ORDER BY in SQLExpress?

Thursday, March 8, 2012

Bug in MS Management Studio for SQL-Server 2005 while creating a publication!

Saturday, February 25, 2012

Buffer Overrun?

Buffer Overrun?

Buffer Overrun?

buffer latch?

Buffer Latch error

Buffer Latch error

Buffer Latch error

Friday, February 24, 2012

Browsing for Publication error message

Boot.ini file

Blog Archive

About Me