Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
Manny
Deferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.c om...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
sql

Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny

Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
--
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny

Invalid object name...

Hello all,
however, this is my first question to this news. I am working with RS SP1,
and have question. I have example procedure:
CREATE PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
CREATE TABLE #table (ID SQL_VARIANT)
INSERT INTO #table(ID) VALUES (@.ord)
SELECT * FROM #table
DROP TABLE #table
GO
When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID) I
get following error:
Could not generate a list of fields for the querry...
Invalid object name #table
Ofcourse, this procedure works good in Query Analyser. Anyone has idea, why
this is not working ?
--
Ing. Branislav GerzoI got it to work w/o a problem. However, I do see the same error if I enter
the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation dialog
box while attempting to define the dataset it uses. Try actually executing
the procedure with a parameter or passing in a static value from the Generic
Query Designer data window once you've defined the dataset. If you use
static value like (EXECUTE GEGE_test_a '11'), simply change it after to use
your query parm.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
> I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
> why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Use a table ariable instead of the temp table:
ALTER PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
DECLARE @.table TABLE(ID SQL_VARIANT)
INSERT INTO @.table(ID) VALUES (@.ord)
SELECT * FROM @.table
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Dejan Sarka [DS], on Friday, October 29, 2004 at 17:17 (+0200)
contributed this to our collective wisdom:
DS> Use a table ariable instead of the temp table:
DS> ALTER PROCEDURE GEGE_test_a
DS> @.ord SQL_VARIANT AS
DS> SET NOCOUNT ON
DS> DECLARE @.table TABLE(ID SQL_VARIANT)
DS> INSERT INTO @.table(ID) VALUES (@.ord)
DS> SELECT * FROM @.table
DS> GO
thanks, I was afraid that someone will answer like this. Ofcourse,
this works, but my problem is, that in my situation I have to fill
@.table_var with result of another procedure. And I found this:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
A3:
1. Tables variables cannot be used in a INSERT EXEC or SELECT INTO
statement.
2. You cannot use the EXEC statement or the sp_executesql stored
procedure to run a dynamic SQL Server query that refers a table
variable, if the table variable was created outside the EXEC statement
or the sp_executesql stored procedure. Because table variables can be
referenced in their local scope only, an EXEC statement and a
sp_executesql stored procedure would be outside the scope of the table
variable. However, you can create the table variable and perform all
processing inside the EXEC statement or the sp_executesql stored
procedure because then the table variables local scope is in the EXEC
statement or the sp_executesql stored procedure.
Ofcourse, i'd like to use table variables, they are fast, they are
cool. But, how to fill them with result of another procedure ?
I can't cheat them in any way, I have only one idea for that -
procedure which fill @.tabl_var using cursors. But I hope there is
better way do this.
Dejan, please help.
--
...m8s, cu l8r, Brano.
[Alright, who g r e a s e d the tagline?.]|||John H. Miller [JHM], on Friday, October 29, 2004 at 11:14 (-0400)
typed the following:
JHM> I got it to work w/o a problem. However, I do see the same error if I
enter
JHM> the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation
dialog
JHM> box while attempting to define the dataset it uses.
anyone knows, why this error occurs ? I can't use temp tables in my
procedures ?
JHM> Try actually executing
JHM> the procedure with a parameter or passing in a static value from the
Generic
JHM> Query Designer data window once you've defined the dataset. If you use
JHM> static value like (EXECUTE GEGE_test_a '11'), simply change it after to
use
JHM> your query parm.
No, it also doesn't work, I get the same message back. (could not
generate a list...). I really don't know why, it is known bug, or
what?
Thanks a lot. My all work stops on this :(((
--
...m8s, cu l8r, Brano.
[Applaflammaphobia: A vacation fear that the house will bu]

interval dates

i want to create a user-defined function that accepts 2 dates and returns
the time difference in days, hours, minutes, and seconds-
for example, a possible result would be something like:
2 days, 17 hours, 46 minutes, 12 seconds
has anyone out there done this before in sql server? any help would be much
appreciated.
thanks,
jtjtl
CREATE FUNCTION dbo.dates_range
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
"JTL" <jliautaud@.hotmail.com> wrote in message
news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>i want to create a user-defined function that accepts 2 dates and returns
>the time difference in days, hours, minutes, and seconds-
> for example, a possible result would be something like:
> 2 days, 17 hours, 46 minutes, 12 seconds
> has anyone out there done this before in sql server? any help would be
> much appreciated.
> thanks,
> jt
>|||thanks!
do you know how i can return days as well?
jtl
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
> jtl
> CREATE FUNCTION dbo.dates_range
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
>
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>|||You could do something like this with my TTimeSpan UDT at [0]:
declare @.ts TTimeSpan
set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
select @.ts.ToString(), @.ts.ToLongString()
Output:
-- ---
11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
[0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"JTL" <jliautaud@.hotmail.com> wrote in message
news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks!
> do you know how i can return days as well?
> jtl
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||i didn't see where to get the source for TTimeSpan- can you help?
jt
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
> You could do something like this with my TTimeSpan UDT at [0]:
> declare @.ts TTimeSpan
> set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
> select @.ts.ToString(), @.ts.ToLongString()
> Output:
> -- ---
--
> 11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
> [0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> --
> William Stacey [MVP]
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||JTL (jliautaud@.hotmail.com) writes:
> i didn't see where to get the source for TTimeSpan- can you help?
What about reading William's post in full?
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Intersect?

Dose SQL 2000 support the intersect function? If so...could someone show me an example. If not...how do you work around the problem.

Thanks,
TreyThis is what I am trying to do:

SELECT DISTINCT dbo.[table].*
FROM dbo.x INNER JOIN
dbo.y ON dbo.x.id = dbo.y.id
WHERE (dbo.y.reason_id = '4744')

Intersect

SELECT DISTINCT dbo.[table].*
FROM dbo.x INNER JOIN
dbo.y ON dbo.x.id = dbo.y.id
WHERE (dbo.y.reason_id <> '4786')

interrelated report parameters

Is it possible to have parameter available values (coming from different
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>