Convert Access Function to SQL

I'm going crazy trying to convert an Access Function to SQL.
From what I've read, it has to be done as a stored procedure.
I'm trying to take a field that is "minutes.seconds" and convert it to minutes.

This is what I have in Access:

Function ConvertToTime (myAnswer As Variant)
Dim myMinutes
myMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))
ConvertToTime =(myMinutes)
End Function

When I tried to modify it in SQL:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
Function ConvertToTime(myAnswer As Variant)
Dim myMinutes
myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
ConvertToTime=(myMinutes)
End

I get an error after ConverToTime.Transact-SQL is not VB!

If you are using SQL2000 you can create a user-defined function:

CREATE FUNCTION dbo.ConvertToMinutes (@.minsec DECIMAL(5,2))
RETURNS DECIMAL(5,2)
BEGIN
RETURN ROUND(@.minsec,0,1)+(@.minsec-ROUND(@.minsec,0,1))*10/6
END

GO

SELECT dbo.ConvertToMinutes(100.30)

Result:

---
-100.50

(1 row(s) affected)

--
David Portas
----
Please reply only to the newsgroup
--|||Mich wrote:
> CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
> Function ConvertToTime(myAnswer As Variant)
> Dim myMinutes
> myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
> ConvertToTime=(myMinutes)
> End
T-SQL uses "Return <value>" for functions, like C or Java, not
"<Function Name> = <value>", like VB (including Access) or Pascal.

Bill

Conversion query

I have 2 date variables passed into my store procedure as follows
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks

Conversion of procedure making crosstab to function

Hello Everybody,
I have the following problem. I found procedure alolowing me to create
dynamic crosstab and it works fine, but I cannot save the results as a
table. Is there any way to do this? Maybe someone is in possesion of
function which works as below procedure? Or someone is able to change
this procedure to function which returns table which could be used with
command CREATE TABLE?
Procedure looks as follows:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@.itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@.Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@.Aggregate_Column
name of column for aggregate
@.TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@.Select_Column
Column for first column in result table
for this column row values are displayed
@.Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@.DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @.Aggregate_Column='Sales',
@.TableOrViewName='aTable',
@.Select_Column='Product',
@.Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
--+--+--+--+--
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
--+--+--+--+--
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@.Aggregate_Function nvarchar(30) = 'SUM',
@.Aggregate_Column nvarchar(255),
@.TableOrView_Name nvarchar(255),
@.Select_Column nvarchar(255),
@.Pivot_Column nvarchar(255),
@.DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @.TransformPart nvarchar(4000)
DECLARE @.SQLColRetrieval nvarchar(4000)
DECLARE @.SQLSelectIntro nvarchar(4000)
DECLARE @.SQLSelectFinal nvarchar(4000)
IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
@.Aggregate_Function) END
ELSE
BEGIN
SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@.Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@.Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@.Select_Column) +
', '
IF @.DEBUG = 1 PRINT @.sqlselectintro
SET @.SQLColRetrieval =
N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
N'''' + @.Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(@.Pivot_Column) +
N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
+
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + ''' THEN ' +
@.Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' FROM ' + @.TableOrView_Name + ') SelInner'
IF @.DEBUG = 1 PRINT @.SQLColRetrieval
EXEC sp_executesql @.SQLColRetrieval,
N'@.TransformPart nvarchar(4000) OUTPUT',
@.TransformPart OUTPUT
IF @.DEBUG = 1 PRINT @.TransformPart
SET @.SQLSelectFinal =
N', ' + @.Aggregate_Function + N'(' +
CAST(@.Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @.TableOrView_Name + N'
GROUP BY ' +
@.Select_Column + N' WITH CUBE'
IF @.DEBUG = 1 PRINT @.SQLSelectFinal
EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
END
GO
Thank you very much for any help,
Rafal
*** Sent via Developersdex http://www.examnotes.net ***You can try something like this...
INSERT INTO TableName
EXEC proc_name
For example
CREATE TABLE #HelpDB(name VARCHAR(255), db_size VARCHAR(255), owner
VARCHAR(255), dbid INT, created VARCHAR(255), status VARCHAR(255),
compatibility_level VARCHAR(255))
INSERT INTO #HelpDB
EXEC sp_helpdb
SELECT *
FROM #HelpDB
DROP TABLE #HelpDB
"Rafal Ba" <ash@.robertjanowski.pl> wrote in message
news:ONdDCvbmFHA.2904@.tk2msftngp13.phx.gbl...
> Hello Everybody,
> I have the following problem. I found procedure alolowing me to create
> dynamic crosstab and it works fine, but I cannot save the results as a
> table. Is there any way to do this? Maybe someone is in possesion of
> function which works as below procedure? Or someone is able to change
> this procedure to function which returns table which could be used with
> command CREATE TABLE?
> Procedure looks as follows:
> CREATE PROCEDURE sp_TRANSFORM
> /*
> Purpose: Creates a Pivot(tm) table for the specified table,
> view or select statement
> Author: svenh@.itrain.de
> Version: 1.1
> History: march 2000 version 1.0
> july 2002 version 1.1
> Input parameters:
> @.Aggregate_Function (optional)
> the aggregate function to use for the pivot
> default function is SUM
> @.Aggregate_Column
> name of column for aggregate
> @.TableOrView_Name
> name of table or view to use
> if name contains spaces or other special
> characters [] should be used
> Can also be a valid SELECT statement
> @.Select_Column
> Column for first column in result table
> for this column row values are displayed
> @.Pivot_Column
> Column that is transformed into columns
> for this column column values are displayed
> @.DEBUG
> Set this flag to 1 to get debug-information
> Example usage:
> Table given aTable
> content: Product Salesman Sales
> P1 Sa 12
> P2 Sb 10
> P2 Sb 3
> P3 Sa 12
> P1 Sc 8
> P3 Sa 1
> P2 Sa NULL
> CALL
> EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
> or EXEC sp_Transform @.Aggregate_Column='Sales',
> @.TableOrViewName='aTable',
> @.Select_Column='Product',
> @.Pivot_Column='Salesman'
> Result:
> Product| Sa | Sb | Sc | Total
> --+--+--+--+--
> P1 | 12,00 | 0,00 | 8,00 | 20,00
> P2 | 0,00 | 13,00 | 0,00 | 13,00
> P3 | 13,00 | 0,00 | 0,00 | 13,00
> --+--+--+--+--
> Total | 25,00 | 13,00 | 8,00 | 46,00
>
> */
> @.Aggregate_Function nvarchar(30) = 'SUM',
> @.Aggregate_Column nvarchar(255),
> @.TableOrView_Name nvarchar(255),
> @.Select_Column nvarchar(255),
> @.Pivot_Column nvarchar(255),
> @.DEBUG bit = 0
> AS
> SET NOCOUNT ON
> DECLARE @.TransformPart nvarchar(4000)
> DECLARE @.SQLColRetrieval nvarchar(4000)
> DECLARE @.SQLSelectIntro nvarchar(4000)
> DECLARE @.SQLSelectFinal nvarchar(4000)
> IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
> 'STDEV', 'VAR', 'VARP', 'STDEVP')
> BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
> @.Aggregate_Function) END
> ELSE
> BEGIN
> SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
> QUOTENAME(@.Select_Column) +
> ') = 1) THEN ''Total'' ELSE ' +
> 'CAST( + ' +
> QUOTENAME(@.Select_Column) +
> ' AS NVARCHAR(255)) END As ' +
> QUOTENAME(@.Select_Column) +
> ', '
> IF @.DEBUG = 1 PRINT @.sqlselectintro
> SET @.SQLColRetrieval =
> N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
> N'''' + @.Aggregate_Function + N'(CASE CAST(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' AS VARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(@.Pivot_Column) +
> N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
> +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
> N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
> N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
> N' AS nVARCHAR(255)) + ''' THEN ' +
> @.Aggregate_Column +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') END FROM (SELECT DISTINCT ' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' FROM ' + @.TableOrView_Name + ') SelInner'
> IF @.DEBUG = 1 PRINT @.SQLColRetrieval
> EXEC sp_executesql @.SQLColRetrieval,
> N'@.TransformPart nvarchar(4000) OUTPUT',
> @.TransformPart OUTPUT
> IF @.DEBUG = 1 PRINT @.TransformPart
> SET @.SQLSelectFinal =
> N', ' + @.Aggregate_Function + N'(' +
> CAST(@.Aggregate_Column As Varchar(255)) +
> N') As Total FROM ' + @.TableOrView_Name + N'
> GROUP BY ' +
> @.Select_Column + N' WITH CUBE'
> IF @.DEBUG = 1 PRINT @.SQLSelectFinal
> EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
> END
> GO
>
> Thank you very much for any help,
> Rafal
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Unfortunately, I can't make new table because number of her columns is
not constant in time. Probably, there is in all crosstables.
I have solved this problem as follows:
1) Create View from procedure using OPENROWSET
CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('SQLOLEDB','seattle1';'manage
r';'MyPass',
'EXEC MyProcedure')
2) Create table from view
SELECT * INTO NewTable FROM MyView
Maybe somebody has other idea?
*** Sent via Developersdex http://www.examnotes.net ***

Conversion of non Ansi standard queries to ANSI Standard queries

Hi,
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anythingsqlsql

Conversion error when calling stored procedure

Hi all,
I want to execute a stored procedure from a report. The stored procedure
takes nvarchar(50) parameters. When I call the procedure from a report with
string paramerers, I get the error 'Implicit conversion from data type
sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
reporting services parameters when calling Exec to run the stored procedure.
Help! Anyone experience this kind of problem before? Any suggestions are
welcome!hey.
Not sure why it happens as I get those all the time to. I have found that
if i create a make the first dataset something simple, like the query to
populate a parameter drop down list, then add a second data source I can then
call the execute statement for the proc. Boqus I know but it works. Buggy
software is my quess, I think it may have been the service pack as I did not
do this a few months back.
hth
"Bas" wrote:
> Hi all,
> I want to execute a stored procedure from a report. The stored procedure
> takes nvarchar(50) parameters. When I call the procedure from a report with
> string paramerers, I get the error 'Implicit conversion from data type
> sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
> reporting services parameters when calling Exec to run the stored procedure.
> Help! Anyone experience this kind of problem before? Any suggestions are
> welcome!
>

ControlParameter and Stored Procedures

I'm sure I'm missing something silly. I have 3 textboxes, a stored procedure and a gridview. The user will put something in the 3 boxes, click submit, and see a grid with stuff (I hope). However, the grid will only return data is I EXCLUDE the controlparameters and only use the sessionparameter. It's like the stored proc won't even fire!

HTML:

<formid="form1"runat="server">
<div>
Lastname: <asp:textboxid="Lastname"runat="server"></asp:textbox>
Hobbies: <asp:textboxid="Hobbies"runat="server"></asp:textbox><br/>
Profession: <asp:textboxid="Profession"runat="server"></asp:textbox>
<asp:buttonid="Button1"runat="server"text="Button"/><br/>
<asp:gridviewskinid="DataGrid"id="GridView1"runat="server"allowpaging="True"allowsorting="True"autogeneratecolumns="False"datasourceid="SqlDataSource1">
<columns>
<asp:boundfielddatafield="Username"headertext="Username"sortexpression="Username"/>
<asp:boundfielddatafield="Lastname"headertext="Lastname"sortexpression="Lastname"/>
<asp:boundfielddatafield="Firstname"headertext="Firstname"sortexpression="Firstname"/>
</columns>
</asp:gridview><asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:HOAConnectionString %>"
selectcommand="spAddressBookSelect"selectcommandtype="StoredProcedure">
<selectparameters>
<asp:sessionparameterdefaultvalue="0"name="CommunityID"sessionfield="CommunityID"type="Int32"/>
<asp:controlparametercontrolid="Lastname"name="Lastname"propertyname="Text"type="String"/>
<asp:controlparametercontrolid="Profession"name="Profession"propertyname="Text"type="String"/>
<asp:controlparametercontrolid="Hobbies"name="Hobbies"propertyname="Text"type="String"/>
</selectparameters>
</asp:sqldatasource></div></form>

sp signature:
ALTER PROCEDURE[dbo].[spAddressBookSelect]
@.CommunityIDint= 0,
@.Lastnamevarchar(200) =NULL,
@.Professionvarchar(200) =NULL,
@.Hobbiesvarchar(200) =NULL

I'm guessing that you mean it doesn't work when all three textboxes aren't filled in. It should work if you fill out all of them. If that is the case, then your problem is that you are passing a NULL value if the textbox is blank, and you haven't set the "CancelSelectOnNullParameter" property of the sqldatasource control to false, so yes, it's not firing the SELECT.

|||Nicely done! That was it, thanks!

Control-of-flow Temp Tables

My question is:
I am having problems with a recompiling stored procedure and am trying to
pinpoint where it is recompiling.
I know that it is not recommended to use temp tables in control-of-flow
statements but.........if you do and the creating of the temp table does
not fit the IF statement will it still spot the creation of a temp table and
recompile?
EG, with num=1
BEGIN
IF @.num=2
CREATE TABLE #TEMP
ELSE
select * from blah
END
Will this still recompile or will it skip the create temp table altogther?
ThanksHi
Without seeing the whole procedure it is hard to recommend anything
concrete! It is recommended that you create your temporary tables at the
start of the procedure to avoid recompilation. Other options may be to use a
derived table and/or split the (parts of the) procedure into multiple
procedures.
John
"Wendy" wrote:

> My question is:
> I am having problems with a recompiling stored procedure and am trying to
> pinpoint where it is recompiling.
> I know that it is not recommended to use temp tables in control-of-flow
> statements but.........if you do and the creating of the temp table doe
s
> not fit the IF statement will it still spot the creation of a temp table a
nd
> recompile?
> EG, with num=1
> BEGIN
> IF @.num=2
> CREATE TABLE #TEMP
> ELSE
> select * from blah
> END
> Will this still recompile or will it skip the create temp table altogther?
'
> Thanks

Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris

Controlling errors in Stored Procedure

Hi everyone:

I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like theTRY-CATCH-FINALLY statement (inSQL-92 forSQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error israised.

Can it be done?

Thanks for any help.

No, I'm afraid in SQL2000 we can not do the error handling like usingTRY-CATCH-FINALLY block. If you only want to limit the rows returned by SELECT statements, you can use TOP key word instead. For example:

select top 1 * from sysobjects

|||

Ok, thanks Lori_jay.

Control rights

Hi,
I am looking for a better of administer login for our
users, all on microsoft environment.
Here is my sistuation:
Our backup procedure cause the database to override every
day. This procedure cannot be changed.
We currently have a scripts that run every night to
give 'datareader' right to each database.
We frequently have issue with the script fail and new user
need to add in manually which sometime cause error.
What is the best pratice for this senario?
culam"Culam" <anonymous@.discussions.microsoft.com> wrote in message
news:bcb701c3ece8$69b95fb0$a001280a@.phx.gbl...
> I am looking for a better of administer login for our
> users, all on microsoft environment.
> Here is my sistuation:
> Our backup procedure cause the database to override every
> day. This procedure cannot be changed.
> We currently have a scripts that run every night to
> give 'datareader' right to each database.
> We frequently have issue with the script fail and new user
> need to add in manually which sometime cause error.
> What is the best pratice for this senario?
I do not understand why you are running a script to add datareader to each
database...
Best practice would involve planning role based security by creating NT
Global groups, placing the global groups in local groups, granting login
permissions to SQL Server to the local groups and database permissions (as
required). Add (or remove) users to the global groups to give (or remove)
the security rights.
Steve

Control Result of ExecuteScalar

The user is calling a Stored Procedure with ExecuteScalar. When the SQL doesn't find a match, I'd like to return the results of a different SQL.

For Example:

If this doesn't find a match:

select amount from Lookup Where application = @.app

Then I'd like to return:

select amount from Lookup Where application = "DEFAULT"

My actual situation is more complex than this. The first SQL is in a CASE statement. After my CASE is done, can I check the current ExecuteScalar return value? Or someone determine how many records are in the last SQL to execute?

Also, ExecuteScalar always seems to get the 1st column of the 1st query. Can I have it get the 1st column from the 3rd query?

ExecuteScalar returns only one value -what that value is depends upon the stored procedure.

Yes, you can return any one value from any combination of queries.

To best assist you, please post the entire stored procedure, a description of what results are desired.

|||

I'm including my SP below. The Lookup table has a column named amount. If the CASE statement, when method="LIST", I'd like to return Lookup.amount if there are no matching records in the LookupList table.

USE [SharedDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PR_LookupPrice]

(

@.app varchar(8),

@.billcode varchar(4),

@.code varchar(20),

@.value sql_variant

)

AS

SET NOCOUNT OFF;

SELECT CASE

WHEN method = 'SET'

THEN (select amount from Lookup Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'VAR'

THEN (Select LookupVariable.amount from Lookup INNER JOIN LookupVariable

On Lookup.lookupid = LookupVariable.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and low <= @.value and high >= @.value)

WHEN method = 'COMP'

THEN (select SUM(LookupCompounding.amount) from Lookup INNER JOIN LookupCompounding

On Lookup.lookupid = LookupCompounding.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'LIST'

THEN (select LookupList.amount from Lookup INNER JOIN LookupList

On Lookup.lookupid = LookupList.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and lookuplist.value = @.value)

END AS 'result'

FROM Lookup WHERE application = @.app AND billcode = @.billcode AND code = @.code

|||

Thanks, that helps.

I've revised the procedure for readibiltiy.

Code Snippet

ALTER PROCEDURE [dbo].[PR_LookupPrice]
( @.App varchar(8),
@.BillCode varchar(4),
@.Code varchar(20),
@.Value sql_variant
)
AS

BEGIN

SET NOCOUNT OFF;

DECLARE @.ReturnValue decimal(10,2)

SELECT @.ReturnValue = CASE Method
WHEN 'SET'
THEN Amount
WHEN 'VAR'
THEN (SELECT lv.Amount
FROM Lookup l
INNER JOIN LookupVariable lv
ON l.LookupID = lv.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ( lv.Low <= @.Value
AND lv.High >= @.Value
)
)
)
WHEN 'COMP'
THEN (SELECT sum( lc.Amount )
FROM Lookup l
INNER JOIN LookupCompounding lc
ON l.LookupID = lc.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
)
)
WHEN 'LIST'
THEN (SELECT isnull( ll.Amount, l.Amount )
FROM Lookup l
INNER JOIN LookupList ll
ON l.LookupID = ll.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ll.Value = @.Value
)
)
END
FROM Lookup
WHERE ( Application = @.App
AND BillCode = @.BillCode
AND Code = @.Code
)

IF @.ReturnValue IS NULL
SELECT Amount
FROM Lookup
WHERE Application = "DEFAULT"

RETURN @.ReturnValue

END

The changes are in yellow, I think that everything else is just formatting.

Control of flow around "CREATE PROCEDURE"

Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:

> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:

> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>

Control jobs using SQL code?

Is there a way (system stored proc) to schedule/reschedule jobs through
SQL code (stored procedure) instead of GUI? We have a job set up on SQL
server and we are trying to control scheduling piece of it through
stored proc. Any help would be appreciated. Thanx!
*** Sent via Developersdex http://www.examnotes.net ***Have a look at sp_update_jobschedule in Books Online. This procedure is in t
he
msdb database, so it is used like
EXEC msdb.dbo.sp_update_jobschedule
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||have a look at following system stored procedures in BOL, there are few more
as well which are related to scheduling the job, you can have a look at them
in BOL.
sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_delete_job
sp_help_job
sp_help_jobstep
sp_update_job
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Mark. It really helps!
*** Sent via Developersdex http://www.examnotes.net ***

control flow of execution of statement

is there a way to check to see if the previous sql statement has completely executed before executing the next statement?

I have a stored procedure that basically has several insert statements. At the end of the insert statements I call bcp to write the table to a text file. The first insert will write a header record into the table. Then it will insert a bunch of records that are selected from other tables and then lastly will write the footer record. My dilemna is that for some reason the first insert of the header record isn't actually happening until the middle of the second set of inserts where it inserts several records from another table. so basically my file ends up looking like this

payment record
payment record
payment record
Header Record
payment record
payment record
payment record
payment record
Footer Record

Can I tell it to wait for the first insert to complete before starting the other insert?

Can you post your sp, table structure and a actual sample of the data? SP's by their nature do not execute the next statement until the previous one has completed. I wonder if you have an index on your table that is causing the data to sort in the format that you have shown even though the insert is happening in the correct order...|||

Here is the stored procedure

The table I am inserting stuff into literally is one field. It is just a way to grab and format data from another table and then call bcp to write the data to a text file.

ALTER PROCEDURE [dbo].[PREPAREFILE]
@.DATE_PAID as char(8), @.HEADER as varchar(MAX), @.FOOTER as varchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
END

BEGIN
DELETE FROM Temp_Formatted
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.HEADER)
END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)

SELECT '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

END

BEGIN
INSERT INTO Temp_Formatted
(formattedRecord)
VALUES (@.FOOTER)
END

Then I call bcp to write the Temp_formatted data to a text file.

What happens though in both the table and the file I get this:
Stub
Stub
Stub
Header
Stub
Stub
Payment
Payment
Payment
Payment
Payment
Footer

What I need is:
Header
stub
stub
stub
stub
stub
payment
payment
payment
payment
Footer

Of course my example output is scaled down. I have over 40,000 stub and payment records.

|||

Does your table have an index on the formattedRecord column?

Looks the first byte for a stub is always "6", first byte for a payment is "7". What is does the Header record look like, especially the first byte (you are passing as an arguement), what does the Footer record look like, especially the first byte (you are passing as an arguement)?

If a table does not have an index, it will store the data in the format that it receives it. The insert statements in your proc run sequentially (meaning each insert has to complete successfully before the next insert statement executes).

|||well, for testing purposes i've been just passing 'header' for @.header and 'footer' for @.footer. But it will always be different. No, there are no indexes on the the formatted table. No keys no indexes...nothing. I even ran a test with only writing header/payments/footer without stubs and it does basically the same thing. I will get a bunch of payments then the header and then the rest of the payments and then the footer. It is really really weird.|||

I'm at a loss. Do you know what the header and footer rows will look like (really what the first byte will be)? Will it always be the same?

You could get around this by using a query with an order by clause to load your bcp.

As an example, let's say your header will always start with 'h' and your footer will always start with 'f'

Select formattedRecord
From Temp_Formatted
Order by
Case left(formattedRecord, 1)
When 'h' then 1
When '6' then 6
When '7' then 7
When 'f' then 9
Else 8 -- This forces everything else to sort before the footer
End

Try the above query and see if that gives you the order you want.

|||yeah they are going to be different. Gee, you would think this would be pretty simple. I don't get why it is doing it this way. its really odd that it inserts between the stubs. You don't know of anyway to do the check to see if the header is there first?|||If you know what the header will look like (and it is formatted differently than the footer), you can probably modify the order by clause I posted. Only other thing I can suggest is to drop the table and recreate it. I've never seen this happen before.|||

SQL is a set-based language and tables are unordered set of rows. So even if you insert some rows in a particular order you will not be able to read it in the same order without specifying an ORDER BY clause in your SELECT statement. Any other assumption to the order of the rows based on index or query plan is incorrect. The easiest way to solve this problem is to add an identity column to the table and then modify your BCP to use queryout option & issue a SELECT on the table with the ORDER BY clause specifying the identity column. This will ensure that the you can retrieve rows in the order in which you inserted and this assumes that there is only one instance of SP inserting data into the table at any point in time.

Alternatively, you can do this without any table at all like below:

-- PrepareFile SP

SELECT t.Data

FROM (

SELECT 0, @.HEADER

UNION ALL
SELECT 1, '6' + '000000000000001' + bill_number + installment + space(224) as stub
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 1, '7' + '000000000000001' + @.DATE_PAID + space(1) + replace(right('000000000' + rtrim(cast(amount as decimal(9,2))), 12),'.','') + space(224) as payment
FROM dbo.Temp_Unformatted

UNION ALL

SELECT 2, @.FOOTER

) as t(SortCol, Data)
ORDER BY t.SortCol

Now, change your BCP to just call this SP using queryout option.

Continuing a SP after an error

Dear All,
Can nayone tell me how to make a Store Procedure continue
after an error, rather than just return. The reason is we
want some proper error reporting.
Thanks
PeterHi Peter,
Have a look into @.@.error and RAISERROR in Books online. This will define the
error hadling process.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a51e01c40a83$4ab13ee0$a601280a@.phx.gbl...
> Dear All,
> Can nayone tell me how to make a Store Procedure continue
> after an error, rather than just return. The reason is we
> want some proper error reporting.
> Thanks
> Peter|||Peter,
SQL Server DOES continue after most errors, unless you have SET AXACT_ABORT
ON. However, for some errors, the batch is terminated and there is nothing
you can do about this. I suggest you read Erland's articles about error
handling on www.sommarskog.se
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a51e01c40a83$4ab13ee0$a601280a@.phx.gbl...
> Dear All,
> Can nayone tell me how to make a Store Procedure continue
> after an error, rather than just return. The reason is we
> want some proper error reporting.
> Thanks
> Peter

Continue SP after Database Access Failure

Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:

> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:

> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>

Continue INSERT after key violation?

Hi there.
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:

> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>

Context Class in AS 2005 Stored Procedure

We are trying to use the CurrentTuple property of the Context Class available in the assembly Microsoft.AnalysisServices.AdomdServer. The CurrentTuple property is documented in the standard AS2005 doc (see http://msdn2.microsoft.com/en-US/library/ms175329.aspx). However this seems not available in the class definition. The only property available are

public static CultureInfo ClientCultureInfo

public static CubeCollection Cubes

public static CubeDef CurrentCube

public static string CurrentDatabaseName

public static MiningModel CurrentMiningModel

public static string CurrentServerID

public static bool ExecuteForPrepare

public static MiningModelCollection MiningModels

public static MiningServiceCollection MiningServices

public static MiningStructureCollection MiningStructures

public static int Pass

Any suggestion?

Thanks a lot.

Riccardo

Hi Riccardo,

Looks like there are inconsistencies in BOL documentation - the link below doesn't list CurrentTuple. But this entry in Chris Webb's blog may help - it shows how to find the current member on each attribute hierarchy of the CurrentCube:

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!586.entry

>>

The first problem I tried to solve was this: how do you return the name of the currentmember on all dimensions in your cube in a query?

>>

http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver.context_members.aspx

>>

Context Members

Provides the execution context for the stored procedure.

The following tables list the members exposed by the Context type.

Public Properties

Name Description ClientCultureInfo Gets the culture for the current client. Cubes Gets a collection of cubes that are available in the current database or context. CurrentCube Gets the current cube. CurrentDatabaseName Gets the current database name for the current session. CurrentMiningModel Gets the current mining model. CurrentServerID Gets the server identifier (server\instance) for the current session. ExecuteForPrepare Gets a value that indicates whether the stored procedure is being called for preparation purposes. MiningModels Gets the mining models in the current database. MiningServices Gets the mining services in the current database. MiningStructures Gets the mining structures in the current database. Pass

Gets the pass number that the user-defined function (UDF) or stored procedure is running under.

>>

|||

Thanks a lot, Deepak.

Riccardo

Containstable variable usage

I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....

Can you not use a variable as a column list? I have a variable for search criteria and it works fine...

Here is my syntax

containstable([tablename],@.columnlist,@.srch)

I have been looking online and can't seem to find anything that says I can or cannot use a variable.

Column list cannot be replaced by variable. You have to use dynamic SQL to form and execute the SELECT statement if you want to parameterize CONTAINS/CONTAINSTABLE column list.