closer, but not quite

Hello, thank you Raymond and Paul, please bear with me a little longer:
It really seems like running sp_addscriptexec is the solution for me,
Thank you again.
So I have run:
exec sp_addscriptexec 'myPublication'
, '\\myDataBase\shared\udt_script.sql'
, 0
I know that myPublication is correct because this database only
contains one publication, and if I type in garbage it gives me the
following error:
"Failed to retrieve information about the publication : garbage. Check
the name again."
udt_script.sql contains repeats of user types I am trying to define.
Maybe the problem is there? I wrote:
CREATE TYPE u_members
FROM varchar(30) NULL
CREATE TYPE u_fees
FROM decimal(9,2) NULL
and the list is then saved as .sql
If I have to create the assembly and then register the assembly as per
instructions here
(http://msdn2.microsoft.com/en-us/library/ms131120.aspx) then I am in a
world of trouble, unless anyone can point me towards a step by step
instructions on how to do that. I know C# and VB.net, but have never
written assemblies before, let alone one in the correct format for this
purpose.
After that has run successfully I went to Replication Monitor -->
myDatabase --> myPublication and started the snapshot agent, which went
fine.
And then I attempted to Start Synchronizing the subscription agents,
and the agent failed with the message "cannot find data type u_members"
again
At this point I am thinking that the script file is written
incorrectly. Can anyone give me an example of how it should look like
when i'm trying to create user defined data types in that script file?
Any more help with regards to what to try next would be greatly
appreciated, Thank you very much
-Dorothy Yip
Is this a merge or transactional publication? I would be a bit surprise if
this happens for transactional publication (would be great if you can post
the snapshot history output) although I don't know exactly what will happen
for merge. Just to resolve the issue at hand, you should simply run the UDT
script directly against the subscriber database using sqlcmd.exe or SSMS.
-Raymond
"DorothyY" <dorothyy@.eyi.us> wrote in message
news:1159388445.536683.237310@.e3g2000cwe.googlegro ups.com...
> Hello, thank you Raymond and Paul, please bear with me a little longer:
> It really seems like running sp_addscriptexec is the solution for me,
> Thank you again.
> So I have run:
> exec sp_addscriptexec 'myPublication'
> , '\\myDataBase\shared\udt_script.sql'
> , 0
> I know that myPublication is correct because this database only
> contains one publication, and if I type in garbage it gives me the
> following error:
> "Failed to retrieve information about the publication : garbage. Check
> the name again."
> udt_script.sql contains repeats of user types I am trying to define.
> Maybe the problem is there? I wrote:
> CREATE TYPE u_members
> FROM varchar(30) NULL
> CREATE TYPE u_fees
> FROM decimal(9,2) NULL
> and the list is then saved as .sql
> If I have to create the assembly and then register the assembly as per
> instructions here
> (http://msdn2.microsoft.com/en-us/library/ms131120.aspx) then I am in a
> world of trouble, unless anyone can point me towards a step by step
> instructions on how to do that. I know C# and VB.net, but have never
> written assemblies before, let alone one in the correct format for this
> purpose.
> After that has run successfully I went to Replication Monitor -->
> myDatabase --> myPublication and started the snapshot agent, which went
> fine.
> And then I attempted to Start Synchronizing the subscription agents,
> and the agent failed with the message "cannot find data type u_members"
> again
> At this point I am thinking that the script file is written
> incorrectly. Can anyone give me an example of how it should look like
> when i'm trying to create user defined data types in that script file?
>
> Any more help with regards to what to try next would be greatly
> appreciated, Thank you very much
> -Dorothy Yip
>
|||Hello,
this is a transactional publication. The snapshot history output looks
normal, (and I can't find a way to copy/paste the history details)
Could you please give me more details regarding your suggestion?
How do I run the script against the subscriber database? would I have
to connect to their database through query analyzer, and if so I'm not
sure how. Because the subscription database is located in a different
country (we are in canada ie:not directly on the same network) and any
suggestions on how to connect to the other guys would be nice...
would it work if I ask the subscription company to run the script's
text in their sql query analyzer directly? (if I understand correctly
asking them to run sp_addscriptexec like I did would put the script
onto their publication which isn't going to solve the problem for them,
right?)
I don't think we have Sql Server Management Studio here, unless I am
mistaken about what SSMS is.
Thanks for the speedy reply, Raymond, much appreciated (and sorry about
starting a new thread -- somehow posting reply to the other one messed
up and viola, new thread.)
-dorothy
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Is this a merge or transactional publication? I would be a bit surprise if
> this happens for transactional publication (would be great if you can post
> the snapshot history output) although I don't know exactly what will happen
> for merge. Just to resolve the issue at hand, you should simply run the UDT
> script directly against the subscriber database using sqlcmd.exe or SSMS.
> -Raymond
> "DorothyY" <dorothyy@.eyi.us> wrote in message
> news:1159388445.536683.237310@.e3g2000cwe.googlegro ups.com...
|||This sounds really strange (bug?), I am actually looking for hints in the
snapshot agent history as to whether the user defined data type was included
with the snapshot. Is this a pure SQL2005 environment
(publisher\distributor\subscriber)? As for creating the type manually, you
should ask the subcribing company to create it for if you cannot do it
directly using any query tool of your choice (Query Analyzer, SQL Server
Management Studio, osql.exe, or sqlcmd.exe).
-Raymond
"DorothyY" <dorothyy@.eyi.us> wrote in message
news:1159456819.811473.262010@.d34g2000cwd.googlegr oups.com...
> Hello,
> this is a transactional publication. The snapshot history output looks
> normal, (and I can't find a way to copy/paste the history details)
> Could you please give me more details regarding your suggestion?
> How do I run the script against the subscriber database? would I have
> to connect to their database through query analyzer, and if so I'm not
> sure how. Because the subscription database is located in a different
> country (we are in canada ie:not directly on the same network) and any
> suggestions on how to connect to the other guys would be nice...
> would it work if I ask the subscription company to run the script's
> text in their sql query analyzer directly? (if I understand correctly
> asking them to run sp_addscriptexec like I did would put the script
> onto their publication which isn't going to solve the problem for them,
> right?)
> I don't think we have Sql Server Management Studio here, unless I am
> mistaken about what SSMS is.
> Thanks for the speedy reply, Raymond, much appreciated (and sorry about
> starting a new thread -- somehow posting reply to the other one messed
> up and viola, new thread.)
> -dorothy
>
>
> Raymond Mak [MSFT] wrote:
>
|||Hello,
yes this is a transactional publication. The snapshot seems to be
running fine, and I haven't found a way to effectively capture the
output yet, but I could do that if running the UDT script on their
database doesn't work. Which bring me to my question,
could you please go into a little more detail on how I can run the
script on their end? They are located in the States and we are here in
Canada. (ie: not on the same network that Query Analyzer can connect
to) If I ask them to run the script's text directly in SQL Query
Analyzer to add the UDTs would that be just as good? I don't think I
can ask them to run to run sp_addscriptexec because that'll just add
script to their publication, which isnt what we are trying to do I
guess.
As for using sqlcmd.exe or SSMS, I don't think we have SqlServer
Management Studio, and I don't know how to use sqlcmd.exe. Hopefully
we won't have to do and that asking them to run the script will do
it...
Thank you again,
Dorothy
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Is this a merge or transactional publication? I would be a bit surprise if
> this happens for transactional publication (would be great if you can post
> the snapshot history output) although I don't know exactly what will happen
> for merge. Just to resolve the issue at hand, you should simply run the UDT
> script directly against the subscriber database using sqlcmd.exe or SSMS.
> -Raymond
> "DorothyY" <dorothyy@.eyi.us> wrote in message
> news:1159388445.536683.237310@.e3g2000cwe.googlegro ups.com...
|||Hello,
yes this is a transactional publication. The snapshot seems to be
running fine, and I haven't found a way to effectively capture the
output yet, but I could do that if running the UDT script on their
database doesn't work. Which bring me to my question,
could you please go into a little more detail on how I can run the
script on their end? They are located in the States and we are here in
Canada. (ie: not on the same network that Query Analyzer can connect
to) If I ask them to run the script's text directly in SQL Query
Analyzer to add the UDTs would that be just as good? I don't think I
can ask them to run to run sp_addscriptexec because that'll just add
script to their publication, which isnt what we are trying to do I
guess.
As for using sqlcmd.exe or SSMS, I don't think we have SqlServer
Management Studio, and I don't know how to use sqlcmd.exe. Hopefully
we won't have to do and that asking them to run the script will do
it...
Thank you again,
Dorothy
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Is this a merge or transactional publication? I would be a bit surprise if
> this happens for transactional publication (would be great if you can post
> the snapshot history output) although I don't know exactly what will happen
> for merge. Just to resolve the issue at hand, you should simply run the UDT
> script directly against the subscriber database using sqlcmd.exe or SSMS.
> -Raymond
> "DorothyY" <dorothyy@.eyi.us> wrote in message
> news:1159388445.536683.237310@.e3g2000cwe.googlegro ups.com...
|||Asking them to run the script in SQL in Query Analyzer should work just fine
although I am curious how you folks managed to get replication without some
kind of connection going. In fact, it would be great if you can confirm
whether the subcriber is using pull subscription on a SQL2000 server
(otherwise things don't make much sense to me at the moment).
-Raymond
"DorothyY" <dorothyy@.eyi.us> wrote in message
news:1159473908.516526.219320@.i3g2000cwc.googlegro ups.com...
> Hello,
> yes this is a transactional publication. The snapshot seems to be
> running fine, and I haven't found a way to effectively capture the
> output yet, but I could do that if running the UDT script on their
> database doesn't work. Which bring me to my question,
> could you please go into a little more detail on how I can run the
> script on their end? They are located in the States and we are here in
> Canada. (ie: not on the same network that Query Analyzer can connect
> to) If I ask them to run the script's text directly in SQL Query
> Analyzer to add the UDTs would that be just as good? I don't think I
> can ask them to run to run sp_addscriptexec because that'll just add
> script to their publication, which isnt what we are trying to do I
> guess.
> As for using sqlcmd.exe or SSMS, I don't think we have SqlServer
> Management Studio, and I don't know how to use sqlcmd.exe. Hopefully
> we won't have to do and that asking them to run the script will do
> it...
> Thank you again,
> Dorothy
> Raymond Mak [MSFT] wrote:
>
|||Hello,
sorry abou the triple posting before, apparently google groups was
having a bit of problems.
And I am completely stumped. I tried running the script on their end,
it says they already have the types in question (!)
I don't think I've mentioned:
we are using SQL server 2000, the publication is is Transactional, and
the subscription type having problems is Push. The really curious thing
I've found is that the UDT that raises the error should not be a new
type at all: it is used by a column in an article that already existed
as part of the publication before I tried adding the other articles in.
Would you mind if i sent you snapshot history via email?
Thanks so much!
-dorothy
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Asking them to run the script in SQL in Query Analyzer should work just fine
> although I am curious how you folks managed to get replication without some
> kind of connection going. In fact, it would be great if you can confirm
> whether the subcriber is using pull subscription on a SQL2000 server
> (otherwise things don't make much sense to me at the moment).
> -Raymond
> "DorothyY" <dorothyy@.eyi.us> wrote in message
> news:1159473908.516526.219320@.i3g2000cwc.googlegro ups.com...
|||For SQL2000 pull\anonymous subscribers, you would need to somehow bring over
the UDT manually and the snapshot agent history would not be of much use if
this is the case except it would be interesting to me (well, not really) if
the UDT is not mentioned when the snapshot agent is analyzing dependencies.
Is there any (indeirect) way you can check whether the required data type
exists at the subscriber?
-Raymond
"DorothyY" <dorothyy@.eyi.us> wrote in message
news:1160496410.497244.209740@.m7g2000cwm.googlegro ups.com...
> Hello,
> sorry abou the triple posting before, apparently google groups was
> having a bit of problems.
> And I am completely stumped. I tried running the script on their end,
> it says they already have the types in question (!)
> I don't think I've mentioned:
> we are using SQL server 2000, the publication is is Transactional, and
> the subscription type having problems is Push. The really curious thing
> I've found is that the UDT that raises the error should not be a new
> type at all: it is used by a column in an article that already existed
> as part of the publication before I tried adding the other articles in.
>
> Would you mind if i sent you snapshot history via email?
> Thanks so much!
> -dorothy
>
> Raymond Mak [MSFT] wrote:
>