adding user-defined data type to existing publication/subscription

Hello, I am not a sql server programmer.
I have a database which already has a publication set up, which sends a
bunch of tables to subscribers. Everything was okay, but now I need to
publish two more tables. These tables contain columns which are user
defined types, which means my subscribers do not have them defined on
their side.
When I try to start synchronizing I get an error: Column or parameter
#1: Cannot find data type u_Blah. (error number 2715)
which is I guess, fair enough since the tables is trying to call
something that doesn't exist for the subscribers yet. If I understand
MSDN correctly (I tried reading so many articles...) in the Publication
--> Properties --> Articles tab; then, when I go into the properties
of the new articles, I can specify "Convert User Defined To base data
types", and then the subscribers will be able to pick up the user
defined types just fine. However (PLEASE tell me I understood this
wrong) that only works when publication is being set up the very first
time. That, in my situation, because I'm only adding articles, I cannot
do that.
The solution in MSDN seems to be to "manually copy the type (and the
associated assembly for a user-defined type) to each subscriber".
(msdn2.microsoft.com/en-us/library/ms152559.aspx) I have no idea what
this means. Can anyone shed some light on what must be done now?
Thank you
-Dorothy
This is definable at the article level, and if you select to use the base
types for your new articles, there should be no requirement to have them
exist on the subscriber. If you want to continue to use user-defined data
types, you can create a script to add them (using sp_addtype and operating
in the correct database ie a 'USE' statement at the top) and then run this
script on the subscriber. How to do this? There are several options:
(a) manually open up Query Analyser and connect to the subscriber and then
run there
(b) use a pre-snapshot script on the publication - not for you as you are
just adding articles to an initialized publication
(c) use sp_addscriptexec, which will run the script on the subscriber the
next time it synchronizes
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Just to clarify the current situation:
1) SQL2005 snapshot processing can handle column dependencies on
user-defined data types (both TSQL and CLR) by including the necessary
type\assembly definitions as part of the snapshot. There should be no need
to manually create the necessary types at the subscriber ahead of time using
a pre-snapshot script anymore as folks typically need to do on SQL2000.
2) SQL2005 DDL replication currently lacks capability to figure out new data
type dependencies introduced by a DDL action (such as adding a new column
depending on a UDT that doesn't exist at the subscriber as you attempted
below) As such, you would need to implement some other mechanisms
(sp_addexecscript being one) to bring the necessary data type (+assembly)
definition over to the subscriber before the DDL action is replicated.
-Raymond
<dorothyy@.eyi.us> wrote in message
news:1159311634.662309.166020@.d34g2000cwd.googlegr oups.com...
> Hello, I am not a sql server programmer.
> I have a database which already has a publication set up, which sends a
> bunch of tables to subscribers. Everything was okay, but now I need to
> publish two more tables. These tables contain columns which are user
> defined types, which means my subscribers do not have them defined on
> their side.
> When I try to start synchronizing I get an error: Column or parameter
> #1: Cannot find data type u_Blah. (error number 2715)
> which is I guess, fair enough since the tables is trying to call
> something that doesn't exist for the subscribers yet. If I understand
> MSDN correctly (I tried reading so many articles...) in the Publication
> --> Properties --> Articles tab; then, when I go into the properties
> of the new articles, I can specify "Convert User Defined To base data
> types", and then the subscribers will be able to pick up the user
> defined types just fine. However (PLEASE tell me I understood this
> wrong) that only works when publication is being set up the very first
> time. That, in my situation, because I'm only adding articles, I cannot
> do that.
> The solution in MSDN seems to be to "manually copy the type (and the
> associated assembly for a user-defined type) to each subscriber".
> (msdn2.microsoft.com/en-us/library/ms152559.aspx) I have no idea what
> this means. Can anyone shed some light on what must be done now?
> Thank you
> -Dorothy
>
|||Hello,
thread has been moved to here mysteriously.
http://groups.google.com/group/micro...65417554cc611c
thank you
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Just to clarify the current situation:
> 1) SQL2005 snapshot processing can handle column dependencies on
> user-defined data types (both TSQL and CLR) by including the necessary
> type\assembly definitions as part of the snapshot. There should be no need
> to manually create the necessary types at the subscriber ahead of time using
> a pre-snapshot script anymore as folks typically need to do on SQL2000.
> 2) SQL2005 DDL replication currently lacks capability to figure out new data
> type dependencies introduced by a DDL action (such as adding a new column
> depending on a UDT that doesn't exist at the subscriber as you attempted
> below) As such, you would need to implement some other mechanisms
> (sp_addexecscript being one) to bring the necessary data type (+assembly)
> definition over to the subscriber before the DDL action is replicated.
> -Raymond
> <dorothyy@.eyi.us> wrote in message
> news:1159311634.662309.166020@.d34g2000cwd.googlegr oups.com...

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rick
select serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegr oups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rickselect serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegroups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding sql licenses

As I recall, I set the licensing format and number of licenses when I
installed SQL Server.
a) How can I tell how many and type of licenses that are setup
b) How can I increase the number.
I can't seem to get back to that original screen.
Thanks,
Rickselect serverproperty('LicenseType') as LicenseType
go
select serverproperty('NumLicenses') as NumLicenses
Thanks
Hari
"Rick" <rick@.abasoftware.com> wrote in message
news:1174501125.493472.118810@.l75g2000hse.googlegroups.com...
> As I recall, I set the licensing format and number of licenses when I
> installed SQL Server.
> a) How can I tell how many and type of licenses that are setup
> b) How can I increase the number.
> I can't seem to get back to that original screen.
> Thanks,
> Rick
>

adding image dynamically to crystal report

hello
please could anyone help me to insert an image into crystal report if I saved the link in the database . I tried to change the type to "ole object" it works, but i don't have to change the type in my project.If you are using SQL Server then search for TextCopy.exe, you will get lot of related code.|||it is very easy in the Crystal 11.5 and later.

Place the OLE object -> Bitmap Image on the report. Right click the OLE Object -> Format Graphics -> Picture (tab) -> Graphic Location.

And enter the URL where the image file is placed.
Now the crystal Report will show the image on runtime by picking from the location mentioned.|||i'm using v8.0 , i'll download the XI version and I'll try

Adding data to database with other application not SQL

Hi,
I have a database that it have and int type with identity (1,1).when I add
data from sql everything is ok but when I want to add data from fro example
Delphi it says that field ID must have a value and auto numbering doesn't
work.
Please tell me how can I solve my problem.
Regards
Ali"Ali.M" <AliM@.discussions.microsoft.com> wrote in message
news:4D01366E-E296-4C7E-84FF-58EA93329088@.microsoft.com...
> Hi,
> I have a database that it have and int type with identity (1,1).when I add
> data from sql everything is ok but when I want to add data from fro
example
> Delphi it says that field ID must have a value and auto numbering doesn't
> work.
> Please tell me how can I solve my problem.
> Regards
> Ali
It sounds to me like Delphi is trying to be helpful and wants to generate a
value for each column that you pass in. If that is the case and you are
picking a table to update from Delphi, you may try creating a View in SQL
Server for the table that leaves off the Identity column. Point Delphi at
the View instead and see if that doesn't solve your problem.
Example:
CREATE TABLE Foo (
IDCol int IDENTITY(1, 1) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NOT NULL
SSN varchar(9) NOT NULL
)
CREATE VIEW vFoo AS
SELECT FirstName, LastName, SSN FROM Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Instead of accessing your tables from applications directly, make it a
standard practice to use stored procedures.
When in doubt search for 'procedure' in this newsgroup. And of course Books
Online will provide lots of help.
ML

Adding data from disconnected subscriber

Abdul,
what type of replication are you using?
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you Paul, I'm using merge replication
"Paul Ibison" wrote:

> Abdul,
> what type of replication are you using?
> Rgds,
> Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Abdul,
in your first post you mention "Then I recreate the subscription but I find
that the record does not replicate on the other subscriber and publisher. ".
Merge replication is not normally continuous, rather the merge agent is run
on a schedule. You could have a schedule of once a minute and enter records
on the subscriber between synchronizations. To simulate a broken connection,
you could disconnect the network card, or set one of the databases to
read-only, then synchronize. Dropping the subscription as you have done is
something else entirely and is a simulation of a subscriber exceeding the
maximum retention period. In this case you could run sp_removedbreplication,
resubscribe as a noinit subscription and do a dummy update. However, I think
that this isn't what you really require.
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding an int to a datetime type as minutes pleasse help!

Hi,
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.
Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.
|||On Mar 20, 3:55Xpm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. XImplicit DATETIME math is always in number of
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL
> Server will know that when you typed "10" you meant minutes? XWhat if I did
> the same, and expected seconds, and my co-worker expects months? XSQL Server
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
>
>
>
> - Show quoted text -
Aaron,
Thank you very much.
Al.

Adding an int to a datetime type as minutes pleasse help!

Hi,
I'm trying to add an int type to a datetime type to produce a
datatime type that interprets the int as *minutes*, for example:
2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
Problem is though - I'm stuck. Whenever I try to do this SQL Server
2005 interprets the "int" as days and I get the wrong answer. Can
ayone help me please? Any comments/suggestions/code-samples much, much
appreciated.
Thank,
Al.Use the DATEADD function. Implicit DATETIME math is always in number of
days.
SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
While the default is not entirely intuitive, why would you assume that SQL
Server will know that when you typed "10" you meant minutes? What if I did
the same, and expected seconds, and my co-worker expects months? SQL Server
can't be psychic...
A
<almurph@.altavista.com> wrote in message
news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
> Hi,
> I'm trying to add an int type to a datetime type to produce a
> datatime type that interprets the int as *minutes*, for example:
> 2008-03-20 15:36:09.920 + 10 = 2008-03-20 15:46:09.920
>
> Problem is though - I'm stuck. Whenever I try to do this SQL Server
> 2005 interprets the "int" as days and I get the wrong answer. Can
> ayone help me please? Any comments/suggestions/code-samples much, much
> appreciated.
> Thank,
> Al.|||On Mar 20, 3:55=A0pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Use the DATEADD function. =A0Implicit DATETIME math is always in number of=
> days.
> SELECT DATEADD(MINUTE, 10, '2008-03-20 15:36:09.920');
> While the default is not entirely intuitive, why would you assume that SQL=
> Server will know that when you typed "10" you meant minutes? =A0What if I =did
> the same, and expected seconds, and my co-worker expects months? =A0SQL Se=rver
> can't be psychic...
> A
> <almu...@.altavista.com> wrote in message
> news:36124fcf-6486-43cc-8297-90972b9d9817@.h11g2000prf.googlegroups.com...
>
> > Hi,
> > I'm trying to add an int type to a datetime type to produce a
> > datatime type that interprets the int as *minutes*, for example:
> > 2008-03-20 15:36:09.920 + 10 =3D 2008-03-20 15:46:09.920
> > Problem is though - I'm stuck. Whenever I try to do this SQL Server
> > 2005 interprets the "int" as days and I get the wrong answer. Can
> > ayone help me please? Any comments/suggestions/code-samples much, much
> > appreciated.
> > Thank,
> > Al.- Hide quoted text -
> - Show quoted text -
Aaron,
Thank you very much.
Al.

adding an image to a database table

Hi,

I am using SQL Server 2005. I have a table in my database which has two columns :

ImageId (Type: nvarchar (50))

Image (Type: image)

I need to store images in this table. How can I do this?

Depends on what do you use for writing client applications.|||

Example using VB 6.0, SQL Server 2005 and SQLNCLI as the provider.

Private Sub Main()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mystream As New ADODB.Stream

cn.CursorLocation = adUseClient
cn.Open "Provider=SQLNCLI;Data Source=.;Integrated Security=SSPI;"

mystream.Type = adTypeBinary
rs.Open "select * from imagetable where imagetable.ImageId=''", cn, adOpenStatic, adLockOptimistic
rs.AddNew
mystream.Open
mystream.LoadFromFile "c:\\test.jpg"
rs!ImageId = "image1"
rs!MyImage = mystream.Read
rs.Update
mystream.Close
rs.Close

cn.Close

End Sub

adding an image to a database table

Hi,

I am using SQL Server 2005. I have a table in my database which has two columns :

ImageId (Type: nvarchar (50))

Image (Type: image)

I need to store images in this table. How can I do this?

Depends on what do you use for writing client applications.|||

Example using VB 6.0, SQL Server 2005 and SQLNCLI as the provider.

Private Sub Main()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mystream As New ADODB.Stream

cn.CursorLocation = adUseClient
cn.Open "Provider=SQLNCLI;Data Source=.;Integrated Security=SSPI;"

mystream.Type = adTypeBinary
rs.Open "select * from imagetable where imagetable.ImageId=''", cn, adOpenStatic, adLockOptimistic
rs.AddNew
mystream.Open
mystream.LoadFromFile "c:\\test.jpg"
rs!ImageId = "image1"
rs!MyImage = mystream.Read
rs.Update
mystream.Close
rs.Close

cn.Close

End Sub

Adding an image

Hi, I have a pretty simple question. I have a table, and I need one of my columns to be an 'image' column. I've set the column type to 'image', but now I don't know how to actually add images to that column. Can someone please help me.

Hello, SuperNove289 (wow! :)

Simple question- simple answer:

Assume, you have this Table_1 with SelectedImages and Path fields.

CREATE TABLE [dbo].[Table_1](

[SelectedImages] [image] NOT NULL,

[Path] [ntext] NOT NULL

)

to add an image, do this:

INSERT INTO [testing].[dbo].[Table_1]

([SelectedImages]

,[Path])

VALUES

('D:\desktop\05022006\free_chart1.gif' ,'D:\desktop\05022006\free_chart1.gif' )

So, then you will do this :

SELECT [SelectedImages]

,[Path]

FROM [testing].[dbo].[Table_1]

You will get something like this:( <Binary data>)

SelectedImages Path

- -

0x443A5C6465736B746F705C30353032323030365C667265655F6368617274312E676966 D:\desktop\05022006\free_chart1.gif

(1 row(s) affected)

Probably the next question will be is how to see the entered image J

|||

Hi SuperNova,

As suggested you may definately add images to your database but this is not the best practise you should avoid such BLOB operation refer http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45 research done at MS, instead store file path of documents and handle it from Front End Applciation.

HTH

Hemantgiri S. Goswami

|||That depends on your coding language how to upload the data to the server, for C#, see this sample which is quite simple.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622943&SiteID=1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de