External web service as datasource for Reporting Service report

Hello,
I would like to make some Reporting Services reports from a datasource
that is a web service. Is this possible? I am using SSRS 2005. Any
suggestions or directions would be greatly appreciated.
photogulliverIt is possible but I haven't done it. Read up in Books On Line on XML.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d23408e5-e65b-4f49-a98f-234454d5d267.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ehausig@.yahoo.com> wrote in message
news:1141931933.379723.119120@.e56g2000cwe.googlegroups.com...
> Hello,
> I would like to make some Reporting Services reports from a datasource
> that is a web service. Is this possible? I am using SSRS 2005. Any
> suggestions or directions would be greatly appreciated.
> photogulliver
>|||Yes. it is possible.
however, there are serious limitations on this approach. see other
postings in this group by me and Teo's responses to my postings.
1. the XML returned by the web service may not be readily consumable by
SSRS.
2. the query syntax to consume the XML is xpath like, but it is neither
xpath not xquery
3. security is a big concern. webservices consumption is non-secure
however, you can write your own custom data processing extension which
can overcome the above limitations.
thanks
- jasthi|||Ok, thanks for the feedback!
Eric

External table is not in the expected format

Hi,

I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called

External Table is not in the expected format

System.Data.OleDb.OleDbException: External table is not in the expected format

Any help will be appreciated.

Regards,

Karen

Try to save the Excel file in cvs format before the importing. There can be something in the excel document that mess up the import.

|||

Thanks for ur answer.. but the other 6 files work fine.|||

There might be something in the 7th file that messes up things. Try CSV and see if it helps. If it doesn't then something else is wrong...

|||

Johram,

I tried converting it to a csv file and getting the same error

|||

OK, you gotta make sure all values in a column is in the same format. Upon import, the first row is scanned in order to determine the datatype of each column. If he find a number in the first column, then he assumes that this is a numerical column. If there is character data in the column somewhere then there will be an error. Maybe it's not character data that's your problem, but such a thing as an empty value or a decimal value when an integer value is expected.

If you open up the 7th file and scan through the rows, then you might spot the deviation?

See this article:http://blog.lab49.com/?p=196

Good luck!

|||

Johram,

Thanks a lot for your help.. the reason i was getting that was i had given

flSubAdvisor.PostedFile.SaveAs(location6)

instead of

flGrowth10K.PostedFile.SaveAs(location6)

and

flSubAdvisor.PostedFile.SaveAs(location6) was equal nothing or it was empty..

any way thanks a lot for ur help.

Regards

Karen

External stored procedures and sockets

I hope this is the right group for this question.
I am trying to deliver a UDP multicast data stream as a notification method
using an extended stored procedure. The multicast message, however, is not
making it out on the network. (At least not in a form that I can recognize.
)
- I can make the multicast work from a stand-alone console application using
the same procedure with dummy data, so I think I'm setting up the multicast
properly.
- I have verified that the extended stored procedure receives the parameters
from the trigger that calls it successfully, so the external stored procedur
e
is being called properly.
- I have verified that there are no error returns from any of the socket (or
other) API calls in the extended stored procedure, so the procedure thinks
it's doing what it ought to.
My conclusion is that there is something in the execution environment that
is keeping the extended stored procedure from working as expected (i.e., as
it does as a simple subroutine in the console application). On the other
hand, I know I've seen people write about using sockets in their extended
stored procedures.
Any ideas as to what I'm doing wrong?
Thanks.
Ed Hoch
--
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksHi
Have you checked that there is not a firewall blocking this port? Also make
sure that your account has the correct access to do this.
John
"Edward Hoch" wrote:

> I hope this is the right group for this question.
> I am trying to deliver a UDP multicast data stream as a notification metho
d
> using an extended stored procedure. The multicast message, however, is no
t
> making it out on the network. (At least not in a form that I can recogniz
e.)
> - I can make the multicast work from a stand-alone console application usi
ng
> the same procedure with dummy data, so I think I'm setting up the multicas
t
> properly.
> - I have verified that the extended stored procedure receives the paramete
rs
> from the trigger that calls it successfully, so the external stored proced
ure
> is being called properly.
> - I have verified that there are no error returns from any of the socket (
or
> other) API calls in the extended stored procedure, so the procedure thinks
> it's doing what it ought to.
> My conclusion is that there is something in the execution environment that
> is keeping the extended stored procedure from working as expected (i.e., a
s
> it does as a simple subroutine in the console application). On the other
> hand, I know I've seen people write about using sockets in their extended
> stored procedures.
> Any ideas as to what I'm doing wrong?
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi John,
I am assuming that since I can run the same code successfully from a console
application, that it is not a firewall issue. I don't think it's possible t
o
"firewall" a specific process (like SQL Server), is it? Also, I have logged
in to the console as the user that SQL Server runs under. The console
application still works. So, unless SQL Server is somehow managinig to
affect the permissions of that account, I don't think it's an access
problem, either.
Feel free to correct me if any of my assumptions are wrong. Thanks very
much for your response.
Ed
"John Bell" wrote:
> Hi
> Have you checked that there is not a firewall blocking this port? Also mak
e
> sure that your account has the correct access to do this.
> John
> "Edward Hoch" wrote:
>

External stored procedure, performance?

Hi everybody,
I should begin to write a DLL library for Sql2000 server.
The functions I like to implement are mathematical functions, like standard
deviation, and similar, nothing really complex. Often I have to use more
then one standard deviation inside the sama function, using subset of a
record set.
Of course I can use sql2000, that implemets standard deviation and basic
mathematical function, so the question is: is it opportune to write a DDL to
improve performance, or is it worse, or just the same?
thanks a lot for any kindly advice
cesareI think the key words here are "using a subset of a recordset". Based on
that I would implement it in T-SQL, or on the application side but not in an
XP. The cost of connecting back to the SQL Server to grab a subset of rows
is going to be big, especially if you perform this std dev calc several
times in a row.
"Cesare" <cvairetti@.mcgestioni.it> wrote in message
news:uO9XdpvjGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi everybody,
> I should begin to write a DLL library for Sql2000 server.
> The functions I like to implement are mathematical functions, like
> standard
> deviation, and similar, nothing really complex. Often I have to use more
> then one standard deviation inside the sama function, using subset of a
> record set.
> Of course I can use sql2000, that implemets standard deviation and basic
> mathematical function, so the question is: is it opportune to write a DDL
> to
> improve performance, or is it worse, or just the same?
> thanks a lot for any kindly advice
> cesare
>sql

External Stored Procedure in SQL Server 2005(x64)

I have generated a DLL file in VC++ 2005 by a 'C' file. It works fine when I put in a 32bits machine(32bits Windows Server 2003 + 32 bits SQL Server 2005).

However, when I build it into 64 bits, it doesn't work in a 64 bits machine. I have checked by Dependenct Walker, the DLL generated is linked with KERNEL32.DLL / OPENDS60.DLL / MSVCR80D.DLL, all of these DLL files are on the 64 bits machines and linked correctly.

I used the command

sp_addextendedproc 'abc', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\abc.dll'

to create a ext. stored procedure. When I run it, the error message shows that

Could not load the DLL C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\abc.dll, or one of the DLLs it references. Reason: 126(error not found).

I would like to ask what is cause of the problem? Do I need to use CLR instead?

Thank you very much!!~

BTW, it is because of your thread I posted the "32Bit Vs 64Bit" SQLCLR platform differences thread. If there are any definite differences this would be a great addition to my upcoming book. Have you experienced any other problems on 64bit platform for sqlclr?

thanks,

derek

external stored procedure (DLL) in Java?

Hi,

I am going to be writing an external stored procedure (my first) for SQL server 2000.

Has anyone out there written a DLL in Java (J++ or .NET) and then accessed the functions within the DLL as an external stored procedure in T/SQL?

I ask the question becaues I'm likely to get it done considerably faster if I write it in Java then VB ;-)

Any advice / suggestions most welcome.

Cheers,

EwanIt shouldn't really make a difference what language you write your dll in. As long as its compiled as a dll, you should be able to call it from a Stored Proc.

Just ensure that the dll is registered on the server that as executing the StoredProc (not the client).

As far as I know , there is no way to return values from the dll into the Stored Proc. Please let me know if there is.

I take no credit for the information below. I copied it from an old posting and saved it, and I cannot remember who posted it originally.

Good luck.

Lionel.

You can do it with the SP_OA* extened stored procedures, located in the
MASTER database (of ms-sql 7.0/2000). Look at this example (for sending
email
through jmail);

CREATE PROCEDURE sp_Send_JMail

@.fromName as char(50),
@.fromEmail as char(50),
@.toName as char(50),
@.toEmail as char(50),
@.subject as char(100),
@.Body as char(500)

AS
DECLARE @.ObjTok int
DECLARE @.RetVal int

EXEC @.RetVal=sp_OACreate'JMail.SMTPMail',@.ObjTok OUT
EXEC sp_OASetProperty @.ObjTok, 'ServerAddress','yourmailserver.com'
EXEC sp_OASetProperty @.ObjTok, 'SenderName', @.fromName
EXEC sp_OASetProperty @.ObjTok, 'Sender', @.fromEmail
EXEC sp_OASetProperty @.ObjTok,'Subject', @.Subject
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'AddRecipient', Null,@.toEmail
EXEC sp_OASetProperty @.ObjTok, 'Body', @.Body
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'Execute'
EXEC sp_OADestroy @.ObjTok GO

External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if SQLServer/MSDE
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in SQLServer/MSDE
Best regards
Peter Sullvan
hi Peter,
Peter Sullvan wrote:
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if SQLServer/MSDE
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in SQLServer/MSDE
> Best regards
> Peter Sullvan
you can write so called "extended stored procedures" externded by dll... at
http://www.sqldev.net/xp.htm you can find some examples as long as usefull
links..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if MS-SQLServer
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in MS-SQLServer.
Best regards
Peter SullvanCurrently only as (extended) stored procedures.
EXEC xp_procname -- which really is a function in a DLL file.
SQL Server 2005 will implement CLR inside SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||In SQL Server 2000, you can create an extended stored procedure. See the
Books Online for details. For scalar functions, you can encapsulate an
extended stored procedure in a Transact-SQL scalar function like the example
below.
CREATE FUNCTION get_roof_angle()
RETURNS int
AS
BEGIN
DECLARE @.RoofAngle int
EXEC master..xp_get_roof_angle @.RoofAngle OUT
RETURN @.RoofAngle
END
GO
SELECT dbo.get_roof_angle()
GO
The planned SQL Server 2005 version will allow you to develop functions
using CLR managed code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||To clarify my response, you can create extended stored procedures in all
versions of SQL Server. However, Transact-SQL functions are available only
in SQL 2000+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23YTFuOkJFHA.1280@.TK2MSFTNGP09.phx.gbl...
> In SQL Server 2000, you can create an extended stored procedure. See the
> Books Online for details. For scalar functions, you can encapsulate an
> extended stored procedure in a Transact-SQL scalar function like the
> example below.
> CREATE FUNCTION get_roof_angle()
> RETURNS int
> AS
> BEGIN
> DECLARE @.RoofAngle int
> EXEC master..xp_get_roof_angle @.RoofAngle OUT
> RETURN @.RoofAngle
> END
> GO
> SELECT dbo.get_roof_angle()
> GO
> The planned SQL Server 2005 version will allow you to develop functions
> using CLR managed code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
> news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server only supports Extended Stored Procedures, no Extended Functions,
but you could wrap an extended stored procedure inside a user defined T-SQL
function and get the same behavior, but you need to use output parameters in
your extended stored procedure to return values, you can not return a result
set.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>

External scripts / imports / updates

Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.

As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.

Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.

FYI, they are running SQL 2000 on both XP Pro and W2K3 server.

Thanks
StarbYou can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.|||You can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.
Thanks. Dont want to use a third party tool (OSQL etc) and can't use such as DTS and Exec as it is the 'End User' that will use the funtion. It must be run via a simple script from the Mac app.

Cheers
Starbsql

External Scheduler Integration

Anyone have a simple script that might be used by an external scheduler
such as Control-M to kick off .sql files and return back appropriate
return codes and error messages.
SpencerHi
Check out the osql utilitiy in books online and use the -b option to return
a value in %ERRORLEVEL%
John
"stabbert" wrote:
> Anyone have a simple script that might be used by an external scheduler
> such as Control-M to kick off .sql files and return back appropriate
> return codes and error messages.
> Spencer
>

External Scheduler Integration

Anyone have a simple script that might be used by an external scheduler
such as Control-M to kick off .sql files and return back appropriate
return codes and error messages.
SpencerHi
Check out the osql utilitiy in books online and use the -b option to return
a value in %ERRORLEVEL%
John
"stabbert" wrote:

> Anyone have a simple script that might be used by an external scheduler
> such as Control-M to kick off .sql files and return back appropriate
> return codes and error messages.
> Spencer
>

External scheduler for reports

Hi, here is my problem:
Because of some usability terms, I need to use SSRS scheduler to determine reports creation frequency (minimum frequency is once a day)I need to use an external scheduler to SSRS in order to launch reports creation daily, after some other batch tasks execution that must be completed before launching report creation.
And I have no idea on how to do that properly. Can somebody help me?

Thanks!

Hi,

Reporting Services has scheduling functionality included by default. You can use both report based schedules as global schedules to update your reports.

To use schedules, you must make sure that your reports are using fixed credentials to connect to the database (so no integrated security).

You can set up the schedules via the report manager.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks for the answer.

I think I expressed myself in a bad way.
Creating my reports needs some batch tasks to be completed. These batch tasks are planed by a company scheduler.
So, my problem is that I want to be able to change report creation time (e.g. from 2am to 3 or 4am) with this company scheduler to ensure that all batch tasks had been totally completed before report creation starts.|||

Hi,

You can also create a .NET executable that creates the snapshot and then call the executable after the batch has been finished. This way you are sure that the batches are finished.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

External Procedure error

I'm trying to compile the following function:

CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2)
RETURN PLS_INTEGER
AS
EXTERNAL LIBRARY EXTPROCSHELL_LIB
NAME "extprocsh"
LANGUAGE C
PARAMETERS (cmd STRING);

but keep getting an error that says EXTPROCSHELL_LIB needs to be declared. Somewhere I read that this probably means that the shared library doesn't exist and try recreating it.

Our DBA assures me that he created the library in the Oracle home lib directory and I do see the compiled C program there and also saw the path of his library creation and everything looks fine.

Any suggestions here?Hello,

do you have created a library object with the same name ?
See "CREATE LIBRARY ..."

The error you have posted does not mean, that you dont have created the lib in the oracle directory.
Its says, that Oracle can not find the library object that points to the library in the oracle directoy ...

Hope that helps ?

Manfred Peter
Alligator Company GmbH
http://www.alligatorsql.comsql

External proc

Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
Aju
Aju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com

External proc

Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com

External proc

Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com

External notification of database updates

MS-SQL 2005, C# 2.0

I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.

Transfering the data (Web Services, Remoting) is not a problem.

What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).

Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?

Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...

There are some BLOB's involved (1-2 page PDF's), if that makes a difference.

I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.

I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)

Thanks

Rob.

One interesting technique to consider would be using triggers and service broker. When an insert, update or delete occurs, send a message via service broker to your central server informing it of the change along with whatever data is necessary to identify it. The central server can then asynchronously receive and process the message.

Dan

External logs collection and monitoring

Essentially I am looking for a way to externally store db
audit logs and to be able to parse the data or filter for specific events an
d
ids for review by a security team. Something less manual than copying trace
files from the server to another server and going over each using profiler
(we're talking about 30 servers here!)... but not necessarily as hands-off a
s
flagging and email alerting only.
In going through support docs and threads here a couple questions have also
arisen...
Does the table the trace dumps to have to be part of the local db? Is it
possible to have the trace dump to an external db server?
In order to have trace dump the output to a table, does this require setting
up a job using SQL Trace stored procedures or can it be done just by changin
g
the server's auditing configuration?Server side traces (i.e. without using the Profiler GUI) can only write to
trace files. You can load trace files into a sql table (for example on a
central "audit" server) using the sytem function fn_trace_gettable. See BOL
for details
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JMBickham" <JMBickham@.discussions.microsoft.com> wrote in message
news:AF4060D6-6986-4999-85E3-5B090EC39A53@.microsoft.com...
> Essentially I am looking for a way to externally store db
> audit logs and to be able to parse the data or filter for specific events
> and
> ids for review by a security team. Something less manual than copying
> trace
> files from the server to another server and going over each using profiler
> (we're talking about 30 servers here!)... but not necessarily as hands-off
> as
> flagging and email alerting only.
> In going through support docs and threads here a couple questions have
> also
> arisen...
> Does the table the trace dumps to have to be part of the local db? Is it
> possible to have the trace dump to an external db server?
> In order to have trace dump the output to a table, does this require
> setting
> up a job using SQL Trace stored procedures or can it be done just by
> changing
> the server's auditing configuration?sql

External Linked Servers

Hi All,

I am doing some research about External Linked Servers and am hoping that someone can point me towards some best practices information and let me know about any gotchas that I should look out for when using this capability in applications.

Thanks in advance,

Jay, I'm doing the same thing. If you find out anything would you let me know and I will do the same for you. Thanks|||

Have you seen this information about linked servers in the SQL Server 2005 Books Online?

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

Hope this helps,
Vaughn

external link table

Does SQL server support external link database like DB2 or Oracle through
ODBC?
Any information is appreciated.
See the topic, "Configuring Linked Servers" in SQL Server Books
Online. It describes how to set up and configure a linked server to
access OLE DB data sources.
--Mary
On Sat, 8 Jan 2005 19:42:31 -0500, "souris" <inungh@.videotron.ca>
wrote:

>Does SQL server support external link database like DB2 or Oracle through
>ODBC?
>Any information is appreciated.
>
|||Thanks millions,
Souris
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:s4b3u0hai65rjbo35v78m3a1gc0gc44i8g@.4ax.com...
> See the topic, "Configuring Linked Servers" in SQL Server Books
> Online. It describes how to set up and configure a linked server to
> access OLE DB data sources.
> --Mary
> On Sat, 8 Jan 2005 19:42:31 -0500, "souris" <inungh@.videotron.ca>
> wrote:
>