Openquery syntax for function

There is a very complex query where I'm trying to call a function from a
linked server with input paramaters from the local database. This is the
general idea:
select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
from table1 t1
and table t2
where ...
I get a syntax error that doesn't recognize t1 and t2. How should I fix thi
s?
Thanks,Openquery() (i.e. ad-hoc/pass through function) only takes literal strings.
So, it's not possible to pass in any parameters.
Also, it's not possible to call a remote user-defined function in sqlserver
(i.e. srv.db.dbo.udf() is not allowed). So, you would have to create the
function locally.
-oj
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||Lisa
Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Statement
Table-Valued UDF?
Look at this technique written by Itzik Ben-Gan
CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
AS
BEGIN
RETURN(SELECT newinvid FROM OPENQUERY([server_name],
'SET NOCOUNT ON; DECLARE @.invid AS INT;
UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
SELECT @.invid AS newinvid;') AS O)
END
CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
AS
BEGIN
RETURN(
SELECT newinvid
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
INSERT INTO tempdb..Seq2 DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
END
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||I'm looking at more in line with your first function. but I have two
parameters (one an integer) and one a date that is being to the function tha
t
I want to use in the openquery statment. I can't get the sql right for it
though.
"Uri Dimant" wrote:

> Lisa
> Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Stateme
nt
> Table-Valued UDF?
> Look at this technique written by Itzik Ben-Gan
> CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
> AS
> BEGIN
> RETURN(SELECT newinvid FROM OPENQUERY([server_name],
> 'SET NOCOUNT ON; DECLARE @.invid AS INT;
> UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
> SELECT @.invid AS newinvid;') AS O)
> END
> CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
> AS
> BEGIN
> RETURN(
> SELECT newinvid
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> INSERT INTO tempdb..Seq2 DEFAULT VALUES
> ROLLBACK;
> SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
> END
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
>
>

OPENQUERY question

I'd like to use a linked server to a Setcim database to retrieve results fro
m
a procedure in the Setcim database.
The syntax used to call the view in Setcim is:
start record 'BSW1View'
which works when using the SQLplus tools for Setcim.
I was assuming I could OPENQUERY to call this record and then put the data
into a SQL Server database but I get syntax errors when I try various
combinations of the following:
SELECT *
FROM OPENQUERY(PRISM,'start record 'BSW1View'')
The issue appears to be the single quotes around the procedure name.
Can I do what I want using OPENQUERY? If not, what options are available?
Thanks in advance,
RaulDouble them (the inner apostrophes).
SELECT *
FROM OPENQUERY(PRISM,'start record ''BSW1View''')
AMB
"Raul" wrote:

> I'd like to use a linked server to a Setcim database to retrieve results f
rom
> a procedure in the Setcim database.
> The syntax used to call the view in Setcim is:
> start record 'BSW1View'
> which works when using the SQLplus tools for Setcim.
> I was assuming I could OPENQUERY to call this record and then put the data
> into a SQL Server database but I get syntax errors when I try various
> combinations of the following:
> SELECT *
> FROM OPENQUERY(PRISM,'start record 'BSW1View'')
> The issue appears to be the single quotes around the procedure name.
> Can I do what I want using OPENQUERY? If not, what options are available?
> Thanks in advance,
> Raul
>|||Your suggestion worked. The only problem is I got the following error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'start record 'BSW1View''. The OLE DB provider
'MSDASQL' indicates that the object has no columns.
I'll modify the procedure on the Setcim side and try again.
Thanks for the help,
Raul
"Alejandro Mesa" wrote:
> Double them (the inner apostrophes).
> SELECT *
> FROM OPENQUERY(PRISM,'start record ''BSW1View''')
>
> AMB
> "Raul" wrote:
>

Opening a microsoft application from a stored procedure

Is there a way to call/launch a word document from a stored procedure?
Thanks for any help.Technically, yes. But why would you want to do such a crazy thing?
If you decided that you'd rather continue drinking the happy juice, you coul
d
use the sp_OA functions to call out to a COM dll that opens the Word documen
t
and does who knows what with it. If you were really ambitious, you could wri
te
an extended stored procedure in C++ to do the same thing.
Thomas
"Munch" <Munch@.discussions.microsoft.com> wrote in message
news:94F4A9C2-5399-4F64-B2D0-6C654A2DCCF7@.microsoft.com...
> Is there a way to call/launch a word document from a stored procedure?
> Thanks for any help.|||Would it be better to insert the path of the documents /files into a table
and have them executed from the tables? Is this possible? I know we used t
o
house jpgs in a table and referenced different jpgs depending on the user an
d
preferences. Can we do this with word docs and other MS Applications?
Thanks
"Thomas" wrote:

> Technically, yes. But why would you want to do such a crazy thing?
> If you decided that you'd rather continue drinking the happy juice, you co
uld
> use the sp_OA functions to call out to a COM dll that opens the Word docum
ent
> and does who knows what with it. If you were really ambitious, you could w
rite
> an extended stored procedure in C++ to do the same thing.
>
> Thomas
>
> "Munch" <Munch@.discussions.microsoft.com> wrote in message
> news:94F4A9C2-5399-4F64-B2D0-6C654A2DCCF7@.microsoft.com...
>
>|||Yes you can store any type of document in the database by using image column
s. A
column of type image can store any type of binary in its raw format. So, jus
t as
JPEGs can be stored in the db, so can Word documents. However, *storing* Wor
d
documents in the database is a far cry from *launching* Word from SQL Server
.
Determining whether you should store the actual files in the database instea
d of
a path is a cost-benefit analysis. Storing the files in the database makes
things like replication a bit easier and provides some security advantages.
In
addition, it makes accessing the file a little simpler in that everything is
retrieved from the database. The downside is that it makes it difficult to e
dit
the files as you need to make a program to pull and push the file to the
database. In addition, it bloats the size of the database making database
backups take longer.
Storing the path to the files makes it possible to view and edit the files
directly. However, you have to ensure that the path in the database stays in
sync with the path and filename on the disk. Where the path solution gets re
ally
tricky is with multiple servers and multiple sites. If that is not a concern
,
the I'd go with the path solution. I would ensure that websites cannot acces
s
the files directly, but rather go through a proxy that sends the binary to t
he
website code. In this way, you shield the storage mechanism and location fro
m
the website adding security. In addition, it allows you to easily switch
solutions without having to change the website code.
HTH
Thomas
"Munch" <Munch@.discussions.microsoft.com> wrote in message
news:28ACA9AE-D640-449C-9257-6690BA761FAA@.microsoft.com...
> Would it be better to insert the path of the documents /files into a table
> and have them executed from the tables? Is this possible? I know we used
to
> house jpgs in a table and referenced different jpgs depending on the user
and
> preferences. Can we do this with word docs and other MS Applications?
> Thanks
> "Thomas" wrote:
>|||Thomas:
Thanks for the valuable info. I will look into these options.
"Thomas Coleman" wrote:

> Yes you can store any type of document in the database by using image colu
mns. A
> column of type image can store any type of binary in its raw format. So, j
ust as
> JPEGs can be stored in the db, so can Word documents. However, *storing* W
ord
> documents in the database is a far cry from *launching* Word from SQL Serv
er.
> Determining whether you should store the actual files in the database inst
ead of
> a path is a cost-benefit analysis. Storing the files in the database makes
> things like replication a bit easier and provides some security advantages
. In
> addition, it makes accessing the file a little simpler in that everything
is
> retrieved from the database. The downside is that it makes it difficult to
edit
> the files as you need to make a program to pull and push the file to the
> database. In addition, it bloats the size of the database making database
> backups take longer.
> Storing the path to the files makes it possible to view and edit the files
> directly. However, you have to ensure that the path in the database stays
in
> sync with the path and filename on the disk. Where the path solution gets
really
> tricky is with multiple servers and multiple sites. If that is not a conce
rn,
> the I'd go with the path solution. I would ensure that websites cannot acc
ess
> the files directly, but rather go through a proxy that sends the binary to
the
> website code. In this way, you shield the storage mechanism and location f
rom
> the website adding security. In addition, it allows you to easily switch
> solutions without having to change the website code.
>
> HTH
>
> Thomas
>
> "Munch" <Munch@.discussions.microsoft.com> wrote in message
> news:28ACA9AE-D640-449C-9257-6690BA761FAA@.microsoft.com...
>
>

OnSynchronization

can someone please show me how to call the

ISSCEStatusReporting.OnSynchronize(DWORD Percentage)

callback during synchronization? there is a StatusReportingHandler property of the replication object and a OnSynchronizanion method, but i don't see how this all connects.

This page describes the OnSynchronization method, but it doesn't show how to use it.

http://technet.microsoft.com/en-us/library/ms174139(SQL.90).aspx

This page describes the StatusReportingHandler, but it doesn't show how to use it.

http://technet.microsoft.com/en-us/library/ms173247(SQL.90).aspx

It does mention get/put_StatusReportinHandler methods, but i don't see to use them in VB.NET

i need to use the COM interface to the compact edition, not the .NET library Data.System interface because i'm doing a proof of concept for some delphi guys will be using the COM interface.

Public Class Form1
Private Sub btnSynchronize1_Click(ByVal sender As System.Object,

ByVal e As System.EventArgs) Handles btnSynchronize1.Click

Dim repl As SSCE.Replication

repl = CreateObject("SSCE.Replication.3.0")

repl.InternetURL = url
repl.Publisher = "Publisher"
repl.PublisherDatabase = "PublisherDatabase"
repl.PublisherLogin = "PublisherLogin"
repl.PublisherPassword = "PublisherPassword"
repl.Publication = "Publication"
repl.Subscriber = "Subscriber"
repl.SubscriberConnectionString = "Data Source=database.sdf"

repl.AddSubscription(DBADDOPTION.CREATE_DATABASE)

repl.Initialize()
repl.Run()
repl.Terminate()

repl = Nothing
End Sub
End Class

thanks,

bryan

This article shows you how to do background sync in .NET: http://msdn2.microsoft.com/en-us/library/bb380186.aspx|||ErikEJ,

thanks for replying to my question, but that article doesn't help me. i'm using the native com interface, not the .NET interface. the native interface does not have the methods specified or take the parameters shown in that article. i do not have an option, i cannot use the .NET interface at this time.

this is the technet page that shows the available objects, methods and properties for native programming.
http://technet.microsoft.com/en-us/library/ms174014(SQL.90).aspx

i am interesting in seeing an example of how to use ISSCEStatusReporting methods and in particular the OnSynchronization method for native programming shown on this technet page.
http://technet.microsoft.com/en-us/library/ms174131(SQL.90).aspx

thank you,

bryan