How to Change Publication on Local Network to Publish on Internet

Please Help me with this problem , I am working with merge replication , i
have got publication for local network and i want to chage it to publish the
articles on internet without creating new publication.
Please Reply
Thanks
Abulla Fatiya
drop all subscriptions
run the below script - change the name of pubs5 to your publication name
sp_changemergepublication 'pubs5','allow_pull','true'
go
sp_changemergepublication 'pubs5','allow_anonymous','true'
go
sp_changemergepublication 'pubs5','alt_snapshot_folder','c:\inetpub\ftproot'
go
sp_changemergepublication 'pubs5','compress_snapshot','true'
go
declare @.str varchar(200)
set @.str='sp_changemergepublication
''pubs5'',''ftp_address'',''ftp://'+@.@.ServerName+''''
print @.str
exec (@.str)
go
sp_changemergepublication 'pubs5','ftp_port','21'
go
sp_changemergepublication 'pubs5','ftp_subdirectory','ftp'
go
sp_changemergepublication 'pubs5','ftp_login','anonymous'
go
sp_changemergepublication 'pubs5','ftp_password','password'
go
sp_changemergepublication 'pubs5','enabled_for_internet','true'
go
sp_changemergepublication 'pubs5','snapshot_ready','true'
go
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
"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
news:7464DC4B-3760-4DD2-95E3-280ACB8E898C@.microsoft.com...
> Please Help me with this problem , I am working with merge replication , i
> have got publication for local network and i want to chage it to publish
the
> articles on internet without creating new publication.
> Please Reply
> Thanks
> Abulla Fatiya
>
>
|||Fatiya,
my comments re VPN are OK, but Hilary's change script is
extremely neat and avoids the need for reinitialization,
so please disregard my later comments.
Rgds,
Paul Ibison

>--Original Message--
>drop all subscriptions
>run the below script - change the name of pubs5 to your
publication name
>sp_changemergepublication 'pubs5','allow_pull','true'
>go
>sp_changemergepublication 'pubs5','allow_anonymous','true
'
>go
>sp_changemergepublication 'pubs5','alt_snapshot_folder','
c:\inetpub\ftproot'
>go
>sp_changemergepublication 'pubs5','compress_snapshot','tr
ue'
>go
>declare @.str varchar(200)
>set @.str='sp_changemergepublication
>''pubs5'',''ftp_address'',''ftp://'+@.@.ServerName+''''
>print @.str
>exec (@.str)
>go
>sp_changemergepublication 'pubs5','ftp_port','21'
>go
>sp_changemergepublication 'pubs5','ftp_subdirectory','ftp
'
>go
>sp_changemergepublication 'pubs5','ftp_login','anonymous'
>go
>sp_changemergepublication 'pubs5','ftp_password','passwor
d'
>go
>sp_changemergepublication 'pubs5','enabled_for_internet',
'true'
>go
>sp_changemergepublication 'pubs5','snapshot_ready','true'
>go
>--
>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
>"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in
message
>news:7464DC4B-3760-4DD2-95E3-
280ACB8E898C@.microsoft.com...[vbcol=seagreen]
merge replication , i[vbcol=seagreen]
chage it to publish
>the
>
>.
>

How to change location of log files

Hey all,
Can somebody please give me some guidance on how to change the location of
the LogFiles after the server has been working for quite some time? We have
the RSS running everything from C:\ and we're running out of space there,
primarily because of the RS logs. Where would I change this so that the
files could go to somewhere else on my server that has plenty of space?
Further, are there any other logs or other constantly growing file that I
might also want to relocate?
I tried searching through the RSS help and didn't come up with anything on
relocating these LogFiles.
Please advise.
ThanksDid you ever get an answer or figure out how to do this? I have the same
issue.
Thanks.
"rjseh2001" wrote:
> Hey all,
> Can somebody please give me some guidance on how to change the location of
> the LogFiles after the server has been working for quite some time? We have
> the RSS running everything from C:\ and we're running out of space there,
> primarily because of the RS logs. Where would I change this so that the
> files could go to somewhere else on my server that has plenty of space?
> Further, are there any other logs or other constantly growing file that I
> might also want to relocate?
> I tried searching through the RSS help and didn't come up with anything on
> relocating these LogFiles.
> Please advise.
> Thanks|||Nope, never got a response.
"J Noble" wrote:
> Did you ever get an answer or figure out how to do this? I have the same
> issue.
> Thanks.
> "rjseh2001" wrote:
> > Hey all,
> >
> > Can somebody please give me some guidance on how to change the location of
> > the LogFiles after the server has been working for quite some time? We have
> > the RSS running everything from C:\ and we're running out of space there,
> > primarily because of the RS logs. Where would I change this so that the
> > files could go to somewhere else on my server that has plenty of space?
> > Further, are there any other logs or other constantly growing file that I
> > might also want to relocate?
> >
> > I tried searching through the RSS help and didn't come up with anything on
> > relocating these LogFiles.
> >
> > Please advise.
> > Thanks

How to Change Graph Type in a .rpt File

Hi all,

I'm working with Crystal report and a java Swing application. The interaction between the two is made up by a simple wrapper that starts the .rpt file when a button is pressed, passing just a few parameters.

i'd like to know if it's possible (and how :D ) to pass the type of chart to be displayed to the .rpt file as a parameter, letting the java application user to choose the chart type inside the Swing environment.

thanks for the help

EnricoI think you need to create as many reports as the number of charts and show corresponding reports as what the user chooses

How to 'call' UDFs from SPs

New to SQL Server in general (2000 in particular) and working through the
relative difficulty of upsizing an Access '03 database that's currently
used in a small ASP web application. The database is generally hit via the
ADO command object, passing parameters as needed via CommandText.
Could someone show an example or two of how to 'call' a user-defined
function (with one parameter) from a stored procedure (also with one input
parameter, the same parameter that's used in the function), where the
procedure relies on a value returned by the function?
Please excuse if this is entirely too unclear, or is posted to the wrong
section. Thanks.
Message posted via http://www.sqlmonster.com
Without knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQLMonster.c om...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.sqlmonster.com
|||For stored procedures, it is not "necessary," although preferable, to
qualify the object with the owner. For UDFs, however, this is mandatory:
<owner/schema>.<UDF name><parameter list>
SET @.var = dbo.udfname(@.parm1, @.parm2, ..., @.parmn)
Sincerely,
Anthony Thomas

"Michael C#" <xyz@.abcdef.com> wrote in message
news:h2K0e.402$CN3.123@.fe11.lga...
Without knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQLMonster.c om...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.sqlmonster.com
|||> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)
|||Thanks, guys.
I was trying to do something like this, although UDFs and SPs are new to me
and I'm obviously missing something here:
*******
CREATE PROCEDURE dbo.usp_My_Procedure
(
DECLARE @.CountOfWidgets INTEGER
SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
)
AS
DECLARE @.B varchar(255)
SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
CountOfCountOfWidgets / CountOfWidgets AS Expr2
FROM dbo.udf_My_Function(@.B) CROSS JOIN
dbo.some_other_***
dbo.some_other_***
WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
GROUP BY CountOfWidgets, something_here_***
*******
In this scenario, @.B is a parameter that is passed (or needs to be passed)
from the ASP script to the procedure, like this:
*******
objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
*******
The ASP part has been working fine when using Access; I'm just not yet up
to speed with procedures and functions for SQL Server.
Ideas...?
Message posted via http://www.sqlmonster.com
|||I think I see some potential "issues" in your SELECT statement already (I'm
assuming the ***'s are actually just where you blanked out the names for our
benefit of course). It would definitely help narrow down the problem if you
could post DDL, sample data and expected results. Your UDF code would help
troubleshoot it also.
You can try running your usp_My_Procedure in QA to see if it actually works
the way you expect it to also, or to see what error it returns if not.
Thanks
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:316caf07f5ff49419d92cc87abdd0b7e@.SQLMonster.c om...
> Thanks, guys.
> I was trying to do something like this, although UDFs and SPs are new to
> me
> and I'm obviously missing something here:
> *******
> CREATE PROCEDURE dbo.usp_My_Procedure
> (
> DECLARE @.CountOfWidgets INTEGER
> SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
> )
> AS
> DECLARE @.B varchar(255)
> SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
> CountOfCountOfWidgets / CountOfWidgets AS Expr2
> FROM dbo.udf_My_Function(@.B) CROSS JOIN
> dbo.some_other_***
> dbo.some_other_***
> WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
> GROUP BY CountOfWidgets, something_here_***
> *******
> In this scenario, @.B is a parameter that is passed (or needs to be passed)
> from the ASP script to the procedure, like this:
> *******
> objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> *******
> The ASP part has been working fine when using Access; I'm just not yet up
> to speed with procedures and functions for SQL Server.
> Ideas...?
> --
> Message posted via http://www.sqlmonster.com
|||Here is the (upsized) UDF:
*****
ALTER FUNCTION dbo.udf_EDB_Current_Inventory
(@.B varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT COUNT(dbo.tblUnits.UnitInDatabase) AS
CountOfUnitInDatabase
FROM dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B)
GROUP BY dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1) )
*****
And this is the (upsized) SQL statement that needs to be incorporated into
the procedure, at least as 'determined/attempted' in the temporary Access
project (the cross joins are also courtesy of upsizing, and I'll need to
sort those out as well):
*****
SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatab ase) AS
CountOfCountOfUnitInDatabase,
CountOfCountOfUnitInDatabase /
udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
CROSS JOIN
dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) > 30) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) <= 60)
GROUP BY udf_EDB_Current_Inventory.CountOfUnitInDatabase,
dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1)
*****
In trying to save/create the procedure I get the following error: "ADO
error: An insufficient number of arguments were supplied for the procedure
or function dbo.udf_EDB_Current_Inventory."
I'm not familiar enough with this, other than the error message is telling
me that either (i) the needed input parameter is not being passed; or (ii)
the function needs to include an output parameter to send back to the
procedure that's calling it?
Prior to this attempt to upsize, all data resided in SQL tables, and
queries were in Access (linked SQL tables). My hope was just to initially
move the queries into SQL Server and to take Access out of the picture. Too
bad I can't just use views with parameters, and worry about the greater
benefit of UDFs and SPs later. Or is that somehow possible...?
Message posted via http://www.sqlmonster.com
|||Sorry, Mike; I wasn't trying to correct what you said, but clarify a general
error most users have when they first start writing these. You had the
information there, but sometimes they need it spelled out.
Sincerely,
Anthony Thomas

"Michael C#" <xyz@.abcdef.com> wrote in message
news:UVL0e.8937$Qi6.1322@.fe09.lga...
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)
|||On Fri, 25 Mar 2005 06:20:16 GMT, The Gekkster via SQLMonster.com wrote:
(snip)
>In trying to save/create the procedure I get the following error: "ADO
>error: An insufficient number of arguments were supplied for the procedure
>or function dbo.udf_EDB_Current_Inventory."
Hi Gekkster,
From this part of the code you posted:

>ALTER FUNCTION dbo.udf_EDB_Current_Inventory
>(@.B varchar(255))
>RETURNS TABLE
I see that your user-defined function requires one argument.
In the stored procedure where you call the function, the call looks like
this:

>FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
And between the parentheses, there is no argument. The error message is
right: an insufficient number of arguments were supplied. Since I have
no idea what you are trying to accomplish, I can't give any suggestions
WHAT to put between the parentheses, but you better put SOMETHING there!
Oh, by the way:

>SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatab ase) AS
>CountOfCountOfUnitInDatabase,
> CountOfCountOfUnitInDatabase /
>udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
is illegal in SQL Server. The whole SELECT statement is executed at
once; the alias CountOfCountOfUnitInDatabase can't be used in other
parts of the SELECT clause. The only place in a query where a column
alias can be used in in the ORDER BY clause.
The workaround is to either repeat the expression, or use a derived
table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
Thanks for the input. I've tried to rework this a bit, and now have the
following view and function (I've dropped some of the WHEREs for now to
help sort this out):
*******
SELECT dbo.tblUnits.UnitInDatabase, dbo.tblRetailers.Active,
dbo.tblUnits.CurrentlyInStock, dbo.SQUISH_V2.CLASS_TYPE,
dbo.Units.AddedToInventory, dbo.tblRetailers.Name
FROM dbo.SQUISH_V2 INNER JOIN
dbo.tblUnits ON dbo.SQUISH_V2.UnitID =
dbo.tblUnits.UnitID INNER JOIN
dbo.tblRetailers ON dbo.tblRetailers.RetailerID =
dbo.tblUnits.RetailerID
*******
*******
ALTER FUNCTION dbo.udf_EDB_Current_Inventory (
@.ClassTypeRequested varchar(255))
RETURNS TABLE
AS
RETURN (SELECT COUNT(UnitInDatabase) AS CountOfUnitInDatabase
FROM vw_EDB_Current_InventoryView
WHERE CLASS_TYPE = @.ClassTypeRequested
AND Active = 1
AND CurrentlyInStock = 1)
*******
So I can now retrieve the needed count via the function, which is properly
'restrictive' based on the WHERE clause it contains. When I test this in
SQL Server QA I get the correct result, like so:
DECLARE @.B varchar(255)
SET @.B = 'Custom'
SELECT *
FROM dbo.udf_EDB_Current_Inventory(@.B)
So my question now is this: To minimize (or hopefully avoid) having to
recode the ASP I'm using, is there a way to do this via a stored procedure
(or even the above function) so that I can continue to make the call like
this:
*******
objCommand1.CommandText = "usp_My_Procedure '" & objRS("SomeID") & "'"
objCommand1.CommandType = adCmdStoredProc
set objRS1 = objCommand1.Execute
*******
This part is still giving me some difficulty, as I don't seem to be getting
the 'parameter' passed properly as was the case with using Access.
Sorry for being so slow to get this - I can only plead ignorance from being
new to UDFs and SPs. Thanks.
Message posted via http://www.sqlmonster.com

How to 'call' UDFs from SPs

New to SQL Server in general (2000 in particular) and working through the
relative difficulty of upsizing an Access '03 database that's currently
used in a small ASP web application. The database is generally hit via the
ADO command object, passing parameters as needed via CommandText.
Could someone show an example or two of how to 'call' a user-defined
function (with one parameter) from a stored procedure (also with one input
parameter, the same parameter that's used in the function), where the
procedure relies on a value returned by the function?
Please excuse if this is entirely too unclear, or is posted to the wrong
section. Thanks.
--
Message posted via http://www.sqlmonster.comWithout knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQLMonster.com...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||For stored procedures, it is not "necessary," although preferable, to
qualify the object with the owner. For UDFs, however, this is mandatory:
<owner/schema>.<UDF name><parameter list>
SET @.var = dbo.udfname(@.parm1, @.parm2, ..., @.parmn)
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:h2K0e.402$CN3.123@.fe11.lga...
Without knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQLMonster.com...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)|||Thanks, guys.
I was trying to do something like this, although UDFs and SPs are new to me
and I'm obviously missing something here:
*******
CREATE PROCEDURE dbo.usp_My_Procedure
(
DECLARE @.CountOfWidgets INTEGER
SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
)
AS
DECLARE @.B varchar(255)
SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
CountOfCountOfWidgets / CountOfWidgets AS Expr2
FROM dbo.udf_My_Function(@.B) CROSS JOIN
dbo.some_other_***
dbo.some_other_***
WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
GROUP BY CountOfWidgets, something_here_***
*******
In this scenario, @.B is a parameter that is passed (or needs to be passed)
from the ASP script to the procedure, like this:
*******
objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
*******
The ASP part has been working fine when using Access; I'm just not yet up
to speed with procedures and functions for SQL Server.
Ideas...?
--
Message posted via http://www.sqlmonster.com|||I think I see some potential "issues" in your SELECT statement already (I'm
assuming the ***'s are actually just where you blanked out the names for our
benefit of course). It would definitely help narrow down the problem if you
could post DDL, sample data and expected results. Your UDF code would help
troubleshoot it also.
You can try running your usp_My_Procedure in QA to see if it actually works
the way you expect it to also, or to see what error it returns if not.
Thanks
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:316caf07f5ff49419d92cc87abdd0b7e@.SQLMonster.com...
> Thanks, guys.
> I was trying to do something like this, although UDFs and SPs are new to
> me
> and I'm obviously missing something here:
> *******
> CREATE PROCEDURE dbo.usp_My_Procedure
> (
> DECLARE @.CountOfWidgets INTEGER
> SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
> )
> AS
> DECLARE @.B varchar(255)
> SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
> CountOfCountOfWidgets / CountOfWidgets AS Expr2
> FROM dbo.udf_My_Function(@.B) CROSS JOIN
> dbo.some_other_***
> dbo.some_other_***
> WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
> GROUP BY CountOfWidgets, something_here_***
> *******
> In this scenario, @.B is a parameter that is passed (or needs to be passed)
> from the ASP script to the procedure, like this:
> *******
> objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> *******
> The ASP part has been working fine when using Access; I'm just not yet up
> to speed with procedures and functions for SQL Server.
> Ideas...?
> --
> Message posted via http://www.sqlmonster.com|||Here is the (upsized) UDF:
*****
ALTER FUNCTION dbo.udf_EDB_Current_Inventory
(@.B varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT COUNT(dbo.tblUnits.UnitInDatabase) AS
CountOfUnitInDatabase
FROM dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B)
GROUP BY dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1) )
*****
And this is the (upsized) SQL statement that needs to be incorporated into
the procedure, at least as 'determined/attempted' in the temporary Access
project (the cross joins are also courtesy of upsizing, and I'll need to
sort those out as well):
*****
SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatabase) AS
CountOfCountOfUnitInDatabase,
CountOfCountOfUnitInDatabase /
udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
CROSS JOIN
dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) > 30) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) <= 60)
GROUP BY udf_EDB_Current_Inventory.CountOfUnitInDatabase,
dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1)
*****
In trying to save/create the procedure I get the following error: "ADO
error: An insufficient number of arguments were supplied for the procedure
or function dbo.udf_EDB_Current_Inventory."
I'm not familiar enough with this, other than the error message is telling
me that either (i) the needed input parameter is not being passed; or (ii)
the function needs to include an output parameter to send back to the
procedure that's calling it?
Prior to this attempt to upsize, all data resided in SQL tables, and
queries were in Access (linked SQL tables). My hope was just to initially
move the queries into SQL Server and to take Access out of the picture. Too
bad I can't just use views with parameters, and worry about the greater
benefit of UDFs and SPs later. Or is that somehow possible...?
--
Message posted via http://www.sqlmonster.com|||Sorry, Mike; I wasn't trying to correct what you said, but clarify a general
error most users have when they first start writing these. You had the
information there, but sometimes they need it spelled out.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:UVL0e.8937$Qi6.1322@.fe09.lga...
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)|||On Fri, 25 Mar 2005 06:20:16 GMT, The Gekkster via SQLMonster.com wrote:
(snip)
>In trying to save/create the procedure I get the following error: "ADO
>error: An insufficient number of arguments were supplied for the procedure
>or function dbo.udf_EDB_Current_Inventory."
Hi Gekkster,
From this part of the code you posted:
>ALTER FUNCTION dbo.udf_EDB_Current_Inventory
>(@.B varchar(255))
>RETURNS TABLE
I see that your user-defined function requires one argument.
In the stored procedure where you call the function, the call looks like
this:
>FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
And between the parentheses, there is no argument. The error message is
right: an insufficient number of arguments were supplied. Since I have
no idea what you are trying to accomplish, I can't give any suggestions
WHAT to put between the parentheses, but you better put SOMETHING there!
Oh, by the way:
>SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatabase) AS
>CountOfCountOfUnitInDatabase,
> CountOfCountOfUnitInDatabase /
>udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
is illegal in SQL Server. The whole SELECT statement is executed at
once; the alias CountOfCountOfUnitInDatabase can't be used in other
parts of the SELECT clause. The only place in a query where a column
alias can be used in in the ORDER BY clause.
The workaround is to either repeat the expression, or use a derived
table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
Thanks for the input. I've tried to rework this a bit, and now have the
following view and function (I've dropped some of the WHEREs for now to
help sort this out):
*******
SELECT dbo.tblUnits.UnitInDatabase, dbo.tblRetailers.Active,
dbo.tblUnits.CurrentlyInStock, dbo.SQUISH_V2.CLASS_TYPE,
dbo.Units.AddedToInventory, dbo.tblRetailers.Name
FROM dbo.SQUISH_V2 INNER JOIN
dbo.tblUnits ON dbo.SQUISH_V2.UnitID =dbo.tblUnits.UnitID INNER JOIN
dbo.tblRetailers ON dbo.tblRetailers.RetailerID =dbo.tblUnits.RetailerID
*******
*******
ALTER FUNCTION dbo.udf_EDB_Current_Inventory (
@.ClassTypeRequested varchar(255))
RETURNS TABLE
AS
RETURN (SELECT COUNT(UnitInDatabase) AS CountOfUnitInDatabase
FROM vw_EDB_Current_InventoryView
WHERE CLASS_TYPE = @.ClassTypeRequested
AND Active = 1
AND CurrentlyInStock = 1)
*******
So I can now retrieve the needed count via the function, which is properly
'restrictive' based on the WHERE clause it contains. When I test this in
SQL Server QA I get the correct result, like so:
DECLARE @.B varchar(255)
SET @.B = 'Custom'
SELECT *
FROM dbo.udf_EDB_Current_Inventory(@.B)
So my question now is this: To minimize (or hopefully avoid) having to
recode the ASP I'm using, is there a way to do this via a stored procedure
(or even the above function) so that I can continue to make the call like
this:
*******
objCommand1.CommandText = "usp_My_Procedure '" & objRS("SomeID") & "'"
objCommand1.CommandType = adCmdStoredProc
set objRS1 = objCommand1.Execute
*******
This part is still giving me some difficulty, as I don't seem to be getting
the 'parameter' passed properly as was the case with using Access.
Sorry for being so slow to get this - I can only plead ignorance from being
new to UDFs and SPs. Thanks.
--
Message posted via http://www.sqlmonster.com|||CREATE PROCEDURE dbo.usp_My_Procedure @.SomeID VARCHAR(255) AS
SELECT *
FROM dbo.udf_EDB_Current_Inventory(@.SomeID)
GO
Run that in QA in your database. It will create your SP for you. You might
consider modifying your ASP code to use a parameterized query to eliminate
the possibility of SQL Injection attacks.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:2be6e53d92854b93b2d238a8a16d82fb@.SQLMonster.com...
> Hi Hugo,
> Thanks for the input. I've tried to rework this a bit, and now have the
> following view and function (I've dropped some of the WHEREs for now to
> help sort this out):
> *******
> SELECT dbo.tblUnits.UnitInDatabase, dbo.tblRetailers.Active,
> dbo.tblUnits.CurrentlyInStock, dbo.SQUISH_V2.CLASS_TYPE,
> dbo.Units.AddedToInventory, dbo.tblRetailers.Name
> FROM dbo.SQUISH_V2 INNER JOIN
> dbo.tblUnits ON dbo.SQUISH_V2.UnitID => dbo.tblUnits.UnitID INNER JOIN
> dbo.tblRetailers ON dbo.tblRetailers.RetailerID => dbo.tblUnits.RetailerID
> *******
> *******
> ALTER FUNCTION dbo.udf_EDB_Current_Inventory (
> @.ClassTypeRequested varchar(255))
> RETURNS TABLE
> AS
> RETURN (SELECT COUNT(UnitInDatabase) AS CountOfUnitInDatabase
> FROM vw_EDB_Current_InventoryView
> WHERE CLASS_TYPE = @.ClassTypeRequested
> AND Active = 1
> AND CurrentlyInStock = 1)
> *******
> So I can now retrieve the needed count via the function, which is properly
> 'restrictive' based on the WHERE clause it contains. When I test this in
> SQL Server QA I get the correct result, like so:
> DECLARE @.B varchar(255)
> SET @.B = 'Custom'
> SELECT *
> FROM dbo.udf_EDB_Current_Inventory(@.B)
> So my question now is this: To minimize (or hopefully avoid) having to
> recode the ASP I'm using, is there a way to do this via a stored procedure
> (or even the above function) so that I can continue to make the call like
> this:
> *******
> objCommand1.CommandText = "usp_My_Procedure '" & objRS("SomeID") & "'"
> objCommand1.CommandType = adCmdStoredProc
> set objRS1 = objCommand1.Execute
> *******
> This part is still giving me some difficulty, as I don't seem to be
> getting
> the 'parameter' passed properly as was the case with using Access.
> Sorry for being so slow to get this - I can only plead ignorance from
> being
> new to UDFs and SPs. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, Michael - all works now as it should.
--
Message posted via http://www.sqlmonster.com

How to 'call' UDFs from SPs

New to SQL Server in general (2000 in particular) and working through the
relative difficulty of upsizing an Access '03 database that's currently
used in a small ASP web application. The database is generally hit via the
ADO command object, passing parameters as needed via CommandText.
Could someone show an example or two of how to 'call' a user-defined
function (with one parameter) from a stored procedure (also with one input
parameter, the same parameter that's used in the function), where the
procedure relies on a value returned by the function?
Please excuse if this is entirely too unclear, or is posted to the wrong
section. Thanks.
Message posted via http://www.droptable.comWithout knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQ
droptable.com...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.droptable.com|||For stored procedures, it is not "necessary," although preferable, to
qualify the object with the owner. For UDFs, however, this is mandatory:
<owner/schema>.<UDF name><parameter list>
SET @.var = dbo.udfname(@.parm1, @.parm2, ..., @.parmn)
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:h2K0e.402$CN3.123@.fe11.lga...
Without knowing exactly what you're trying to do, you would call a UDF that
returns an integer as follows:
DECLARE @.i INTEGER
SET @.i = dbo.udfname(parameter)
Your stored procedure could make this call and use the variable @.i in
whatever fashion it needs. Not sure entirely what you're trying to do, or
how you're trying to do it, or the types you're dealing with, but that's the
basics of it.
Now if you're talking about the mechanics of the call from the ASP web app
side, we need to look at other stuff.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:17b4719d63284899b65bdc6083ed0ef8@.SQ
droptable.com...
> New to SQL Server in general (2000 in particular) and working through the
> relative difficulty of upsizing an Access '03 database that's currently
> used in a small ASP web application. The database is generally hit via the
> ADO command object, passing parameters as needed via CommandText.
> Could someone show an example or two of how to 'call' a user-defined
> function (with one parameter) from a stored procedure (also with one input
> parameter, the same parameter that's used in the function), where the
> procedure relies on a value returned by the function?
> Please excuse if this is entirely too unclear, or is posted to the wrong
> section. Thanks.
> --
> Message posted via http://www.droptable.com|||> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)|||Thanks, guys.
I was trying to do something like this, although UDFs and SPs are new to me
and I'm obviously missing something here:
*******
CREATE PROCEDURE dbo.usp_My_Procedure
(
DECLARE @.CountOfWidgets INTEGER
SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
)
AS
DECLARE @.B varchar(255)
SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
CountOfCountOfWidgets / CountOfWidgets AS Expr2
FROM dbo.udf_My_Function(@.B) CROSS JOIN
dbo.some_other_***
dbo.some_other_***
WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
GROUP BY CountOfWidgets, something_here_***
*******
In this scenario, @.B is a parameter that is passed (or needs to be passed)
from the ASP script to the procedure, like this:
*******
objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
*******
The ASP part has been working fine when using Access; I'm just not yet up
to speed with procedures and functions for SQL Server.
Ideas...?
Message posted via http://www.droptable.com|||I think I see some potential "issues" in your SELECT statement already (I'm
assuming the ***'s are actually just where you blanked out the names for our
benefit of course). It would definitely help narrow down the problem if you
could post DDL, sample data and expected results. Your UDF code would help
troubleshoot it also.
You can try running your usp_My_Procedure in QA to see if it actually works
the way you expect it to also, or to see what error it returns if not.
Thanks
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:316caf07f5ff49419d92cc87abdd0b7e@.SQ
droptable.com...
> Thanks, guys.
> I was trying to do something like this, although UDFs and SPs are new to
> me
> and I'm obviously missing something here:
> *******
> CREATE PROCEDURE dbo.usp_My_Procedure
> (
> DECLARE @.CountOfWidgets INTEGER
> SET @.CountOfWidgets = dbo.udf_My_Function(@.B)
> )
> AS
> DECLARE @.B varchar(255)
> SELECT COUNT(CountOfWidgets ) AS CountOfCountOfWidgets,
> CountOfCountOfWidgets / CountOfWidgets AS Expr2
> FROM dbo.udf_My_Function(@.B) CROSS JOIN
> dbo.some_other_***
> dbo.some_other_***
> WHERE dbo.udf_My_Function(@.B)) AND some_other_criteria_***
> GROUP BY CountOfWidgets, something_here_***
> *******
> In this scenario, @.B is a parameter that is passed (or needs to be passed)
> from the ASP script to the procedure, like this:
> *******
> objCommand.CommandText = "usp_My_Procedure '" & objRS("WidgetBrand") & "'"
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> *******
> The ASP part has been working fine when using Access; I'm just not yet up
> to speed with procedures and functions for SQL Server.
> Ideas...?
> --
> Message posted via http://www.droptable.com|||Here is the (upsized) UDF:
*****
ALTER FUNCTION dbo.udf_EDB_Current_Inventory
(@.B varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT COUNT(dbo.tblUnits.UnitInDatabase) AS
CountOfUnitInDatabase
FROM dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B)
GROUP BY dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1) )
*****
And this is the (upsized) SQL statement that needs to be incorporated into
the procedure, at least as 'determined/attempted' in the temporary Access
project (the cross joins are also courtesy of upsizing, and I'll need to
sort those out as well):
*****
SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatabase) AS
CountOfCountOfUnitInDatabase,
CountOfCountOfUnitInDatabase /
udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
CROSS JOIN
dbo.SQUISH_V2 CROSS JOIN
dbo.tblRetailers CROSS JOIN
dbo.tblUnits
WHERE (dbo.SQUISH_V2.CLASS_TYPE = @.B) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) > 30) AND (DATEDIFF(d,
dbo.tblUnits.AddedToInventory, GETDATE()) <= 60)
GROUP BY udf_EDB_Current_Inventory.CountOfUnitInDatabase,
dbo.tblUnits.CurrentlyInStock, dbo.tblRetailers.Active
HAVING (dbo.tblUnits.CurrentlyInStock = 1) AND
(dbo.tblRetailers.Active = 1)
*****
In trying to save/create the procedure I get the following error: "ADO
error: An insufficient number of arguments were supplied for the procedure
or function dbo.udf_EDB_Current_Inventory."
I'm not familiar enough with this, other than the error message is telling
me that either (i) the needed input parameter is not being passed; or (ii)
the function needs to include an output parameter to send back to the
procedure that's calling it?
Prior to this attempt to upsize, all data resided in SQL tables, and
queries were in Access (linked SQL tables). My hope was just to initially
move the queries into SQL Server and to take Access out of the picture. Too
bad I can't just use views with parameters, and worry about the greater
benefit of UDFs and SPs later. Or is that somehow possible...?
Message posted via http://www.droptable.com|||Sorry, Mike; I wasn't trying to correct what you said, but clarify a general
error most users have when they first start writing these. You had the
information there, but sometimes they need it spelled out.
Sincerely,
Anthony Thomas
"Michael C#" <xyz@.abcdef.com> wrote in message
news:UVL0e.8937$Qi6.1322@.fe09.lga...
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:h2K0e.402$CN3.123@.fe11.lga...
...
> SET @.i = dbo.udfname(parameter)|||On Fri, 25 Mar 2005 06:20:16 GMT, The Gekkster via droptable.com wrote:
(snip)
>In trying to save/create the procedure I get the following error: "ADO
>error: An insufficient number of arguments were supplied for the procedure
>or function dbo.udf_EDB_Current_Inventory."
Hi Gekkster,
From this part of the code you posted:

>ALTER FUNCTION dbo.udf_EDB_Current_Inventory
>(@.B varchar(255))
>RETURNS TABLE
I see that your user-defined function requires one argument.
In the stored procedure where you call the function, the call looks like
this:

>FROM dbo.udf_EDB_Current_Inventory() udf_EDB_Current_Inventory
And between the parentheses, there is no argument. The error message is
right: an insufficient number of arguments were supplied. Since I have
no idea what you are trying to accomplish, I can't give any suggestions
WHAT to put between the parentheses, but you better put SOMETHING there!
Oh, by the way:

>SELECT COUNT(udf_EDB_Current_Inventory.CountOfUnitInDatabase) AS
>CountOfCountOfUnitInDatabase,
> CountOfCountOfUnitInDatabase /
>udf_EDB_Current_Inventory.CountOfUnitInDatabase AS Expr2
is illegal in SQL Server. The whole SELECT statement is executed at
once; the alias CountOfCountOfUnitInDatabase can't be used in other
parts of the SELECT clause. The only place in a query where a column
alias can be used in in the ORDER BY clause.
The workaround is to either repeat the expression, or use a derived
table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
Thanks for the input. I've tried to rework this a bit, and now have the
following view and function (I've dropped some of the WHEREs for now to
help sort this out):
*******
SELECT dbo.tblUnits.UnitInDatabase, dbo.tblRetailers.Active,
dbo.tblUnits.CurrentlyInStock, dbo.SQUISH_V2.CLASS_TYPE,
dbo.Units.AddedToInventory, dbo.tblRetailers.Name
FROM dbo.SQUISH_V2 INNER JOIN
dbo.tblUnits ON dbo.SQUISH_V2.UnitID =
dbo.tblUnits.UnitID INNER JOIN
dbo.tblRetailers ON dbo.tblRetailers.RetailerID =
dbo.tblUnits.RetailerID
*******
*******
ALTER FUNCTION dbo.udf_EDB_Current_Inventory (
@.ClassTypeRequested varchar(255))
RETURNS TABLE
AS
RETURN (SELECT COUNT(UnitInDatabase) AS CountOfUnitInDatabase
FROM vw_EDB_Current_InventoryView
WHERE CLASS_TYPE = @.ClassTypeRequested
AND Active = 1
AND CurrentlyInStock = 1)
*******
So I can now retrieve the needed count via the function, which is properly
'restrictive' based on the WHERE clause it contains. When I test this in
SQL Server QA I get the correct result, like so:
DECLARE @.B varchar(255)
SET @.B = 'Custom'
SELECT *
FROM dbo.udf_EDB_Current_Inventory(@.B)
So my question now is this: To minimize (or hopefully avoid) having to
recode the ASP I'm using, is there a way to do this via a stored procedure
(or even the above function) so that I can continue to make the call like
this:
*******
objCommand1.CommandText = "usp_My_Procedure '" & objRS("SomeID") & "'"
objCommand1.CommandType = adCmdStoredProc
set objRS1 = objCommand1.Execute
*******
This part is still giving me some difficulty, as I don't seem to be getting
the 'parameter' passed properly as was the case with using Access.
Sorry for being so slow to get this - I can only plead ignorance from being
new to UDFs and SPs. Thanks.
Message posted via http://www.droptable.com

How to call functions via ODBC

Hello.

How can I call some functions on MSDE when working in Access (.mdb) that is
connected to MSDE via ODBC linked tables ? Especially
in-line functions, that I would like to use as recordset for my forms and
reports.
Can I call in-line functions using ADO ? I tried, but it seems that only
stored procedures are allowed (adCmdStoredProc)...

Thanks.Hi

Inline functions are not intended to be called directly. You are not using
them correctly. You can not call it via ODBC, ADO, ADO.NET or Query
Analyzer.

In Inline function should be called as part of a select statement or stored
procedure. SQL Server BOL shows how they should be used.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d3ua9b$icu$1@.ss405.t-com.hr...
> Hello.
> How can I call some functions on MSDE when working in Access (.mdb) that
> is connected to MSDE via ODBC linked tables ? Especially
> in-line functions, that I would like to use as recordset for my forms and
> reports.
> Can I call in-line functions using ADO ? I tried, but it seems that only
> stored procedures are allowed (adCmdStoredProc)...
> Thanks.

How to build a Custom Delivery Protocol of SMS

Appreciate for any comment about how to build a custom delivery protocol of SMS (short message service). I am working on a Notification Service project but I have no idea how to write a custom delivery protocol for SMS user.

thank you,

Hi Nemo -

Some links that may help...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/669d84f2ff70e872/cbca58445c41735e?q=sms+&rnum=2#cbca58445c41735e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/6efc2f9083a1e848/1901ade55f731a4e?q=sms+&rnum=7#1901ade55f731a4e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/64881889a372fdf6/2e859f334105a583?q=sms+&rnum=5#2e859f334105a583

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/e85aab616313e031/5f054efc9af85ae3?q=sms+&rnum=4#5f054efc9af85ae3

HTH...

Joe

|||

Hi Joe,

I have similar requirements to Nemo, i.e. need to develop a custom delivery protocol for SMS. However, I cannot use the external providers as one of the requirements is that the application should be able to send notifications via an SMPP (Short Message Peer to Peer) Telstra (Aus) server.

Any ideas on how to go about this?

Thanks,

Kevin.

|||

You cannot send SMS messages directly from you application without special hardware (the simplest and the cheapest solution is just a cell phone plugged into your server, but it's not as simple as it sounds... and not very scalable either).

I am now in Phase 1 of my project, and for now our management is happy to have my app send emails to subscribers. However, when we go to Phase 2 (more core functionality + SMS and AIM), I'll just do my best to convince them to buy an out-of-the-box SMS-related solution from a third party.

...or, maybe, we'll buy a GSM device (or, whatever it's called...), and I'll play with programming it as much as I want. ;-)

By the way, many modern cell phones (let alone more sophisticated and expensive devices) have AIM installed by default (my cell phone that I got for free from T-Mobile has AIM), and you can relatively easily develop a custom delivery channel using AIM SDK. -> http://developer.aim.com/aimccMain.jsp

|||

Hi Kevin,

When working with with a 'SMPP server' you'll need to create a TCP connection to their server and communicate using the SMPP protocol. One of the first questions they'll need to answer is what version(s) of SMPP they support. Most network providers support 3.4 (with backward compatibility to 3.3).

Regards

How to build a Custom Delivery Protocol of SMS

Appreciate for any comment about how to build a custom delivery protocol of SMS (short message service). I am working on a Notification Service project but I have no idea how to write a custom delivery protocol for SMS user.

thank you,

Hi Nemo -

Some links that may help...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/669d84f2ff70e872/cbca58445c41735e?q=sms+&rnum=2#cbca58445c41735e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/6efc2f9083a1e848/1901ade55f731a4e?q=sms+&rnum=7#1901ade55f731a4e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/64881889a372fdf6/2e859f334105a583?q=sms+&rnum=5#2e859f334105a583

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/e85aab616313e031/5f054efc9af85ae3?q=sms+&rnum=4#5f054efc9af85ae3

HTH...

Joe

|||

Hi Joe,

I have similar requirements to Nemo, i.e. need to develop a custom delivery protocol for SMS. However, I cannot use the external providers as one of the requirements is that the application should be able to send notifications via an SMPP (Short Message Peer to Peer) Telstra (Aus) server.

Any ideas on how to go about this?

Thanks,

Kevin.

|||

You cannot send SMS messages directly from you application without special hardware (the simplest and the cheapest solution is just a cell phone plugged into your server, but it's not as simple as it sounds... and not very scalable either).

I am now in Phase 1 of my project, and for now our management is happy to have my app send emails to subscribers. However, when we go to Phase 2 (more core functionality + SMS and AIM), I'll just do my best to convince them to buy an out-of-the-box SMS-related solution from a third party.

...or, maybe, we'll buy a GSM device (or, whatever it's called...), and I'll play with programming it as much as I want. ;-)

By the way, many modern cell phones (let alone more sophisticated and expensive devices) have AIM installed by default (my cell phone that I got for free from T-Mobile has AIM), and you can relatively easily develop a custom delivery channel using AIM SDK. -> http://developer.aim.com/aimccMain.jsp

|||

Hi Kevin,

When working with with a 'SMPP server' you'll need to create a TCP connection to their server and communicate using the SMPP protocol. One of the first questions they'll need to answer is what version(s) of SMPP they support. Most network providers support 3.4 (with backward compatibility to 3.3).

Regards

How to build a Custom Delivery Protocol of SMS

Appreciate for any comment about how to build a custom delivery protocol of SMS (short message service). I am working on a Notification Service project but I have no idea how to write a custom delivery protocol for SMS user.

thank you,

Hi Nemo -

Some links that may help...

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/669d84f2ff70e872/cbca58445c41735e?q=sms+&rnum=2#cbca58445c41735e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/6efc2f9083a1e848/1901ade55f731a4e?q=sms+&rnum=7#1901ade55f731a4e

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/64881889a372fdf6/2e859f334105a583?q=sms+&rnum=5#2e859f334105a583

http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/e85aab616313e031/5f054efc9af85ae3?q=sms+&rnum=4#5f054efc9af85ae3

HTH...

Joe

|||

Hi Joe,

I have similar requirements to Nemo, i.e. need to develop a custom delivery protocol for SMS. However, I cannot use the external providers as one of the requirements is that the application should be able to send notifications via an SMPP (Short Message Peer to Peer) Telstra (Aus) server.

Any ideas on how to go about this?

Thanks,

Kevin.

|||

You cannot send SMS messages directly from you application without special hardware (the simplest and the cheapest solution is just a cell phone plugged into your server, but it's not as simple as it sounds... and not very scalable either).

I am now in Phase 1 of my project, and for now our management is happy to have my app send emails to subscribers. However, when we go to Phase 2 (more core functionality + SMS and AIM), I'll just do my best to convince them to buy an out-of-the-box SMS-related solution from a third party.

...or, maybe, we'll buy a GSM device (or, whatever it's called...), and I'll play with programming it as much as I want. ;-)

By the way, many modern cell phones (let alone more sophisticated and expensive devices) have AIM installed by default (my cell phone that I got for free from T-Mobile has AIM), and you can relatively easily develop a custom delivery channel using AIM SDK. -> http://developer.aim.com/aimccMain.jsp

|||

Hi Kevin,

When working with with a 'SMPP server' you'll need to create a TCP connection to their server and communicate using the SMPP protocol. One of the first questions they'll need to answer is what version(s) of SMPP they support. Most network providers support 3.4 (with backward compatibility to 3.3).

Regards

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.net
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net
|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC
|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC
|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC
|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>
|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/conten...ogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC