Bug with SET options for table-valued functions.

Hi,

I think I've come across a bug in SQL Server 2000 (SP3):

If I create a table-valued function with the QUOTED_IDENTIFIER and ANSI_NULLS options both set to ON e.g.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.tfn_Test()
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO
Then when Scripting this function out again (or using external tools to analyse it - SQL Compare for example), it is scripted as

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.tfn_Test()
RETURNS TABLE
AS
RETURN (SELECT 1 AS test)
GO

It appears to run OK with the original settings, but obviously this script is incorrect.

And if I run this script against my database :
SELECT Name,
Type,
OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') IsQuotedIdentOn,
OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') IsAnsiNullsOn
FROM sysobjects
WHERE type IN ('FN','IF','TF')

Then I get NULLs back for the ObjectProperty statements when the type is TF. The other function types return 1 or 0 as expected.

This would appear to be a bug in the way that SQL Server reports the properties out via DMO and the system functions for Table-Valued functions. It is breaking our syncronisation process (using SQL compare).

How do I submit this as a bug to Microsoft? And is it likely to be fixed in an update or Service Pack soon?

Thanks

Alex Weatherall

Further to this :

I can't actually modify table valued functions on SQL Server 2000 in the new SQL 2005 Management Studio due to this error:

TITLE: Microsoft SQL Server Management Studio

Property QuotedIdentifierStatus is not available for UserDefinedFunction '[dbo].[fn_nums]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&
EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=QuotedIdentifierStatus&LinkId=20476

This is due to the same problem. This bug needs fixing asap, it means that I can't use the new management tools to manage SQL Server 2000, I need to use Query Analyzer to access the code for all table valued functions.

Please can someone get back to me asap.

Thanks

Alex Weatherall
TeleWare.com
|||

Solution:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=0750596e-9915-42ea-9295-62d1fb31d0a4

|||

You're right - the properties don't apply to table-valued functions in SQL Server 2000. In SQL Server 2000, the behavior is as if QUOTED_IDENTIFIER and ANSI_NULLS were always on.

The defect where Management Studio tries to get these properties for tabled-value functions in SQL Server 2000 servers has been fixed for SP2.

Thanks,
Steve

Bug in SQL Server Reporting Services?

I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...

I am trying to use SSRS to print to a Dymo LabelWriter 400 printer. This is a specialized label printer that prints one label to a page. The label paper I am using 2-1/2 inches by 1-1/2 inches. The Dymo printer driver prints portait layout as being the 2-1/2 inches width and 1-1/2 inches height. I assume that is because the paper rolls through the printer that way and labels normally are printed with more width than height.

Other software programs I use to print to this printer handles it properly (Microsoft Word, Microsoft Office Document Imaging, Dymo Label software). However, SSRS seems to assume that portrait layout must mean paper height > width, as there is no explicity setting in SSRS to for page layout. The result is that no matter how I layout my SSRS report (e.g., switch width-height values, use tb-rl to rotate text), SSRS makes the final decision on whether it is portrait or lanscape. And in this case of the Dymo printer, it always sets it to the opposite of what it should be. Changing the printer settings (whether default or whether in print dialog) does not correct it.

Although assuming that portrait layout must mean paper height > width may seem to make logical sense, not every printer works that way; and other software programs (including Microsoft Office) do not make that assumption. It seems that SSRS and/or RDL spec should allow one to explicitly set portrait/landscape setting and not care about width-height ratio. It should be left to the printer and printer driver to decide.

Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?

I have the same exact issue. Is there any way to force the print orientation (horizontal or vertical) on SSRS?|||In the printers and faxes folder, select properties of tour dymo label printer, select the advance tab. Setup your printer default, label size and orientation and apply. Select the advance tab again in the label properties and select "print directly to printer. This seemed to work for me.

Bug in SQL Server Reporting Services?

I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...

I am trying to use SSRS to print to a Dymo LabelWriter 400 printer. This is a specialized label printer that prints one label to a page. The label paper I am using 2-1/2 inches by 1-1/2 inches. The Dymo printer driver prints portait layout as being the 2-1/2 inches width and 1-1/2 inches height. I assume that is because the paper rolls through the printer that way and labels normally are printed with more width than height.

Other software programs I use to print to this printer handles it properly (Microsoft Word, Microsoft Office Document Imaging, Dymo Label software). However, SSRS seems to assume that portrait layout must mean paper height > width, as there is no explicity setting in SSRS to for page layout. The result is that no matter how I layout my SSRS report (e.g., switch width-height values, use tb-rl to rotate text), SSRS makes the final decision on whether it is portrait or lanscape. And in this case of the Dymo printer, it always sets it to the opposite of what it should be. Changing the printer settings (whether default or whether in print dialog) does not correct it.

Although assuming that portrait layout must mean paper height > width may seem to make logical sense, not every printer works that way; and other software programs (including Microsoft Office) do not make that assumption. It seems that SSRS and/or RDL spec should allow one to explicitly set portrait/landscape setting and not care about width-height ratio. It should be left to the printer and printer driver to decide.

Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?

I have the same exact issue. Is there any way to force the print orientation (horizontal or vertical) on SSRS?|||In the printers and faxes folder, select properties of tour dymo label printer, select the advance tab. Setup your printer default, label size and orientation and apply. Select the advance tab again in the label properties and select "print directly to printer. This seemed to work for me.

Bug in SQL Server Reporting Services?

I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...

I am trying to use SSRS to print to a Dymo LabelWriter 400 printer. This is a specialized label printer that prints one label to a page. The label paper I am using 2-1/2 inches by 1-1/2 inches. The Dymo printer driver prints portait layout as being the 2-1/2 inches width and 1-1/2 inches height. I assume that is because the paper rolls through the printer that way and labels normally are printed with more width than height.

Other software programs I use to print to this printer handles it properly (Microsoft Word, Microsoft Office Document Imaging, Dymo Label software). However, SSRS seems to assume that portrait layout must mean paper height > width, as there is no explicity setting in SSRS to for page layout. The result is that no matter how I layout my SSRS report (e.g., switch width-height values, use tb-rl to rotate text), SSRS makes the final decision on whether it is portrait or lanscape. And in this case of the Dymo printer, it always sets it to the opposite of what it should be. Changing the printer settings (whether default or whether in print dialog) does not correct it.

Although assuming that portrait layout must mean paper height > width may seem to make logical sense, not every printer works that way; and other software programs (including Microsoft Office) do not make that assumption. It seems that SSRS and/or RDL spec should allow one to explicitly set portrait/landscape setting and not care about width-height ratio. It should be left to the printer and printer driver to decide.

Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?

I have the same exact issue. Is there any way to force the print orientation (horizontal or vertical) on SSRS?|||In the printers and faxes folder, select properties of tour dymo label printer, select the advance tab. Setup your printer default, label size and orientation and apply. Select the advance tab again in the label properties and select "print directly to printer. This seemed to work for me.

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

http://msdn2.microsoft.com/en-us/library/ms146568.aspx

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

http://msdn2.microsoft.com/en-us/library/ms146568.aspx

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

http://msdn2.microsoft.com/en-us/library/ms146568.aspx

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

BUG - SQL Agent Jobs - SP3 - Multiple Steps

I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to a text file. When the job is scripted, it puts the job steps in what seems like alphabetical order by step name and not in step number order. If you try to run this script to c
reate the job, it will fail because it requires the job steps to be in step number order.
I have talked to others that have used this script prior to SP3 so it seems to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and corrected in either a patch or at least the next SP.
This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hawg" <anonymous@.discussions.microsoft.com> wrote in message
news:6115B24E-5496-49CB-ABCC-864C14E56DC1@.microsoft.com...
> I have come across what seems to be a bug in SQL 2K SP3.
> When I have a job with multiple job steps and I right-click to choose
which step I want to start with, the job steps are in no particular order.
> I am also using an ActiveX script that uses SQLDMO to script out the jobs
to a text file. When the job is scripted, it puts the job steps in what
seems like alphabetical order by step name and not in step number order. If
you try to run this script to create the job, it will fail because it
requires the job steps to be in step number order.
> I have talked to others that have used this script prior to SP3 so it
seems to only have occurred in this Service Pack.
> I just wanted to get this information out there so it can be addressed and
corrected in either a patch or at least the next SP.

BUG - SQL Agent Jobs - SP3 - Multiple Steps

I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which
step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to
a text file. When the job is scripted, it puts the job steps in what seems
like alphabetical order by step name and not in step number order. If you
try to run this script to c
reate the job, it will fail because it requires the job steps to be in step
number order.
I have talked to others that have used this script prior to SP3 so it seems
to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and c
orrected in either a patch or at least the next SP.This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hawg" <anonymous@.discussions.microsoft.com> wrote in message
news:6115B24E-5496-49CB-ABCC-864C14E56DC1@.microsoft.com...
> I have come across what seems to be a bug in SQL 2K SP3.
> When I have a job with multiple job steps and I right-click to choose
which step I want to start with, the job steps are in no particular order.
> I am also using an ActiveX script that uses SQLDMO to script out the jobs
to a text file. When the job is scripted, it puts the job steps in what
seems like alphabetical order by step name and not in step number order. If
you try to run this script to create the job, it will fail because it
requires the job steps to be in step number order.
> I have talked to others that have used this script prior to SP3 so it
seems to only have occurred in this Service Pack.
> I just wanted to get this information out there so it can be addressed and
corrected in either a patch or at least the next SP.

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

Breaking up a Table

I hope I can get this across clearly.

I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7

Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2

If Col5 is twice the width... haha just kidding...

so Col5 and Col6 go into LookupTable3

There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3

My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row

thanks folks

T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B

Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:

Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table

etc...

--
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.

I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.

INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL

INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL

... etc

INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
... etc

--
David Portas
SQL Server MVP
--|||Thanks folks....

Lookup Table was a term used to help with the understanding of the question

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegro ups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --

Breaking up a Table

I hope I can get this across clearly.
I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7
Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2
If Col5 is twice the width... haha just kidding...
so Col5 and Col6 go into LookupTable3
There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3
My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row
thanks folks
T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B
Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:
Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table
etc...
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.
I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.
INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL
.. etc
INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
.. etc
David Portas
SQL Server MVP
--|||Thanks folks....
Lookup Table was a term used to help with the understanding of the question
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegroups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --
>

break table across pages but keep the control under it on first page

I am trying to create appointment reminder letters, and at the bottom
of the first page of each letter I need the clinic contact information
(phone number and name). Immediately prior to this I must list various
instructions regarding what to bring, what to eat/not eat beforehand,
etc.
Regardless of the length of the list of instructions I still need the
clinic contact information to render at the bottom of the 1st page of
every letter, even if the list of instructions has to continue on to
page 2.
When I create a table footer it only appears on the 1st page of the 1st
child's letter, while the other several hundred letters get no footer.
(surely this can be done in one .PDF instead of a subscription to
create several hundred separate .PDFs?)
Here's what I need for each child:
|Page 1|
<Patient address, etc.>
<Schedule of appointments for the day>
<List of instructions>
<Clinic CONTACT INFO> <--bottom of 1st page of EVERY letter
|Page 1|
|Page 2|
<any remaining items from the list of instructions above>
<rest of my report requirements here...>
Any suggestions on how to do this are greatly appreciated.
Thanks,
Stewart Whaley
Arkansas Children's HospitalPut the information in the Page footer and set the page footer properties to
only print on the first page.
If you have multiple items in the footer--some for page 1 and some for page
2, you will have to set the visibility in a formula.
"Stewart" <whaleysa@.archildrens.org> wrote in message
news:1121353944.165681.77900@.o13g2000cwo.googlegroups.com...
>I am trying to create appointment reminder letters, and at the bottom
> of the first page of each letter I need the clinic contact information
> (phone number and name). Immediately prior to this I must list various
> instructions regarding what to bring, what to eat/not eat beforehand,
> etc.
> Regardless of the length of the list of instructions I still need the
> clinic contact information to render at the bottom of the 1st page of
> every letter, even if the list of instructions has to continue on to
> page 2.
> When I create a table footer it only appears on the 1st page of the 1st
> child's letter, while the other several hundred letters get no footer.
> (surely this can be done in one .PDF instead of a subscription to
> create several hundred separate .PDFs?)
> Here's what I need for each child:
> |Page 1|
> <Patient address, etc.>
> <Schedule of appointments for the day>
> <List of instructions>
> <Clinic CONTACT INFO> <--bottom of 1st page of EVERY letter
> |Page 1|
> |Page 2|
> <any remaining items from the list of instructions above>
> <rest of my report requirements here...>
> Any suggestions on how to do this are greatly appreciated.
> Thanks,
> Stewart Whaley
> Arkansas Children's Hospital
>|||My problem now is that I have the footer on every page but the last of
every letter except for the very last page of the last kid's letter.
For example, I have:
<PAGE 1>
Bob Jones
1233 Jones Street
Allergy Clinic 9:00
Dental Clinic 10:30
Don't forget:
* Please don't eat Oreos before you dental appointment
* Don't pet tigers
Clinic contact info (this is in a the footer because it MUST be at the
bottom of the 1st page and only the 1st page of each kid's letter).
<PAGE 2>
Continuation of the "Don't Forget" list from page 2
Image of map for the particular clinic(s)
Clinic contact info footer is repeating here and I DON'T want this
repeated within the SAME letter.
PAGE 3 (Start of the next letter)
Jon Smith
111 1st Street
Allergy Clinic 9:30
Orthopedic Clinic 11:00
Don't forget:
* Please don't jump off of buildings
* Still don't pet tigers
Clinic contact information (as expected)
<PAGE 2>
Continuation of "Don't Forget" stuff here if it is long enough to
stretch to page 2
Clinic contact information footer repeated again but I don't want it
repeated.
..........
I don't know what to do but I just want the clinic contact info at the
bottom of only the 1st page of each letter.
Unfortunately, a single letter's isn't guaranteed to be 2 pages,
either, so I'm not sure I can come up with a formula for hiding the
footer on every page but the 1st for each kid...
Thanks,
Stewart