Hi All,
I have an store procedure with a 100 and some lines, and I want to analize
the result from another sp.
How can I get the values to do a select statement?
create procedure myPro
As
select * from orders bla...
go
create procedure analizemyPro
As
declare @.temp varchar(100)
select @.temp = ordername from (here the result of myPro)
Go
Tks in advance
JFBOutput parameters,look up (stored procedures, output parameters) in BOL
or create a table and do an insert with exec
insert into table
exec myPro
http://sqlservercode.blogspot.com/|||Tks for you reply and help... We got it :)
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1131643669.810105.317790@.g47g2000cwa.googlegroups.com...
> Output parameters,look up (stored procedures, output parameters) in BOL
> or create a table and do an insert with exec
> insert into table
> exec myPro
> http://sqlservercode.blogspot.com/
>
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Tuesday, March 20, 2012
An unexpected token "." was found following "". Expected tokens may include: "
I'm a little bit lost on this one. I use a SQL 2000 datbase and have been running a stored procedure for several years that hits a DB2 database. The DB2 server is linked. I need to make a small modification to the procedure that will pull in an additional date value to a temp table. What I thought would be very simple has me lost. The procedure is as follows and I've highlighted the area I added.
CREATE PROCEDURE sp_ivSqlAcquireCWMM_With_15K
@.claim_nbr varchar(30)
AS
/* Declare variables */
declare @.insert_sql varchar(1000)
declare @.openquery_sql varchar(1000)
declare @.fields_sql1 varchar(1000)
declare @.from_sql varchar(1000)
declare @.where_sql varchar(1000)
declare @.claim_nbr_2 char(30)
declare @.pri_con_type char(4)
--declare @.Where_Por Char(30)
CREATE TABLE #tCaseContact
(
CLAIM_NBR char(30),
CASE_NBR CHAR(11),
INJ_SSN CHAR(9),
INJ_FNAME CHAR(20),
INJ_MID_INIT CHAR(1),
INJ_LNAME char(30),
INJ_NME_SUF CHAR(3),
TPLOC_NAME CHAR(60),
TPLOC_POLICY_NBR CHAR(20),
DT_OF_INJ DATETIME,
CASE_STS CHAR(4),
TEAM_NBR CHAR(4),
USER_KEY char(16),
TPA_ID CHAR(9),
CON_TYPE char(4),
CON_ID int,
PRIMARY_IND char(1),
EMP_Tier char(1),
Non_UM char(1),
POR char(42) NULL,
Cont_Type char(4),
Thousand_Date datetime)
/* Assert Claim Number from @.claim_nbr parameter */
set @.claim_nbr_2 = cast(@.claim_nbr as char(30))
set @.insert_sql = 'INSERT INTO #tCaseContact '
set @.openquery_sql = ' ' +
' SELECT * FROM OPENQUERY( CWMM,''SELECT'
set @.fields_sql1 = ' ' +
'a.twccs_claim_nbr, ' +
'a.twccs_case_nbr, ' +
'a.twccs_inj_ssn, ' +
'a.twccs_inj_fname, ' +
'a.twccs_inj_mid_init, ' +
'a.twccs_inj_lname, ' +
'a.twccs_inj_nme_suf, ' +
'mcdb.polloc_cw.tploc_name, ' +
'mcdb.polloc_cw.tploc_policy_nbr, ' +
'a.twccs_dt_of_inj, ' +
'a.twccs_case_sts, ' +
'mcdb.polloc_cw.tploc_team_nbr, ' +
'c.tctyp_user_key, ' +
'a.twccs_tpa_id, ' +
'b.twcon_con_type, ' +
'b.twcon_con_id_pers, ' +
'b.twcon_primary_ind, ' +
'e.tpoli_prem_tier, ' +
'e.tpoli_non_um, ' +
'f.NAME, ' +
'b.twcon_con_type, ' +
'a.twccs_1000_lmt_dt'
set @.from_sql = '' +
'FROM ' +
'MCDB.WCCASE_CW AS a ' +
'INNER JOIN MCDB.WCCONTT AS b ON ' +
'(a.TWCCS_CASE_NBR = b.TWCON_CASE_NBR) ' +
'INNER JOIN MCDB.CONTYPT AS c ON ' +
'(b.TWCON_CON_ID_PERS = c.TCTYP_CON_ID) ' +
'INNER JOIN MCDB.POLLOC_CW AS d ON ' +
'(a.TWCCS_POLICY_NBR = d.TPLOC_POLICY_NBR) ' +
'INNER JOIN MCDB.POLICY_CW AS e ON ' +
'(d.TPLOC_POLICY_NBR = e.TPOLI_POLICY_NBR) ' +
'Left Outer JOIN PRODSYS.CWPV001_PROV_MASTER as f ON ' +
'(a.TWCCS_POR_NO = f.PROV_NO)'
SET @.where_sql = '' +
'WHERE ' +
'b.TWCON_CON_TYPE IN (''''MCS'''', ''''MCN'''', ''''RRS'''') ' +
'AND ' +
'c.TCTYP_CON_TYPE = ''''USER'''' ' +
'AND ' +
'b.TWCON_STS = ''''A'''' ' +
'AND ' +
'a.TWCCS_CLAIM_NBR = '' + @.claim_nbr_2 + ''' +
''')'
exec( @.insert_sql + @.openquery_sql + @.fields_sql1 +
@.from_sql + @.where_sql )
/*
SELECT * FROM #tCaseContact
*/
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCN' AND PRIMARY_IND = '1'))
set @.pri_con_type = cast('MCN' as char(4))
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCS' AND PRIMARY_IND = '2'))
set @.pri_con_type = cast('MCS' as char(4))
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCS' AND CAST(PRIMARY_IND AS VARCHAR(4)) = '') AND
(exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'RRS' AND PRIMARY_IND = '4')))
set @.pri_con_type = cast('RRS' as char(4))
if @.pri_con_type is null set @.pri_con_type = cast('MCS' as char(4))
/*sets the MCS as the Primary Contact if the primary contact is returned as null*/
/* if (exists
(Select POR
FROM #tCaseContact set POR=f.
where POR is not null))
if Por='' or Por is null
Else set POR=null
*/
SELECT
TOP 1
a.CLAIM_NBR ,
a.CASE_NBR ,
a.INJ_SSN ,
a.INJ_FNAME ,
a.INJ_MID_INIT ,
a.INJ_LNAME ,
a.INJ_NME_SUF ,
a.TPLOC_NAME ,
a.TPLOC_POLICY_NBR ,
convert(varchar(10),a.DT_OF_INJ, 101) as TWCCS_DT_OF_INJ,
a.CASE_STS ,
a.TEAM_NBR ,
(
SELECT top 1 a1.USER_KEY FROM #tCaseContact AS a1 WHERE a1.CON_TYPE = @.pri_con_type
) as USER_KEY ,
a.TPA_ID,
a.EMP_TIER,
a.NON_UM,
a.POR,
(
SELECT top 1 a1.Con_Type FROM #tCaseContact AS a1 WHERE a1.CON_TYPE = @.pri_con_type
) as Cont_Type,
convert(varchar(10),a. Thousand_Date,101) as twccs_1000_lmt_dt
FROM
#tCaseContact AS a
GOdeclare @.openquery_sql varchar(1000)
Up the varchar limit to 2000 and give it a whirl. I think the variable is truncating.|||I still get the same error. Just out of curiousity, does SQL have a problem where the column name has numeric values in it? The reason I'm wondering is when I first added the Thousand_Date to the Temp table I called it K_1000 and SQL basically told me the it was an invalid value, when I chenged it a=it accepted it OK. The DB2 column is twccs_1000_lmt_dt in the @.fields_sql1 portion and I was wondering if that may have something to do with it.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "MCDB" was found following "". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||This looks like the DB2 server is having trouble with the query. Can you print out the select query that is being generated, and see if that runs via DB2's query tools?|||The only problem I have is my access to the DB2 database is limited to a view in a linked server. It's a remote database and they have it locked down. I don't have any DB2 utilities except for the OBDC conntection.|||Time to get to know someone with the tools, then.
You may not even have to go that far, though. Even if you spit out the query from all the dynamic string concat functions, you may see the problem right off.|||I'll see what I can do.|||Just a quick follow up in case anyone else runs into a similar situation. The problem turned out to be a problem with the DB2 date format. One of the other SQL Admins ran into it when he was creating our data warehouse. to resolve the problem the following code had to be inserted into the open query statement.
' CASE ' +
' WHEN CAST(TWCCS_1000_LMT_DT AS DATE) < ''''1753-01-01'' +
' THEN DATE(''''1753-01-01'''')' +
' ELSE CAST(TWCCS_1000_LMT_DT AS DATE)' +
' END ' +
' AS TWCCS_1000_LMT_DT '|||Just a quick follow up in case anyone else runs into a similar situation. The problem turned out to be a problem with the DB2 date format. One of the other SQL Admins ran into it when he was creating our data warehouse. to resolve the problem the following code had to be inserted into the open query statement.
' CASE ' +
' WHEN CAST(TWCCS_1000_LMT_DT AS DATE) < ''''1753-01-01'' +
' THEN DATE(''''1753-01-01'''')' +
' ELSE CAST(TWCCS_1000_LMT_DT AS DATE)' +
' END ' +
' AS TWCCS_1000_LMT_DT '
With that date is it a "Pilgrim Tracking System" ?
Happy turkey day.
CREATE PROCEDURE sp_ivSqlAcquireCWMM_With_15K
@.claim_nbr varchar(30)
AS
/* Declare variables */
declare @.insert_sql varchar(1000)
declare @.openquery_sql varchar(1000)
declare @.fields_sql1 varchar(1000)
declare @.from_sql varchar(1000)
declare @.where_sql varchar(1000)
declare @.claim_nbr_2 char(30)
declare @.pri_con_type char(4)
--declare @.Where_Por Char(30)
CREATE TABLE #tCaseContact
(
CLAIM_NBR char(30),
CASE_NBR CHAR(11),
INJ_SSN CHAR(9),
INJ_FNAME CHAR(20),
INJ_MID_INIT CHAR(1),
INJ_LNAME char(30),
INJ_NME_SUF CHAR(3),
TPLOC_NAME CHAR(60),
TPLOC_POLICY_NBR CHAR(20),
DT_OF_INJ DATETIME,
CASE_STS CHAR(4),
TEAM_NBR CHAR(4),
USER_KEY char(16),
TPA_ID CHAR(9),
CON_TYPE char(4),
CON_ID int,
PRIMARY_IND char(1),
EMP_Tier char(1),
Non_UM char(1),
POR char(42) NULL,
Cont_Type char(4),
Thousand_Date datetime)
/* Assert Claim Number from @.claim_nbr parameter */
set @.claim_nbr_2 = cast(@.claim_nbr as char(30))
set @.insert_sql = 'INSERT INTO #tCaseContact '
set @.openquery_sql = ' ' +
' SELECT * FROM OPENQUERY( CWMM,''SELECT'
set @.fields_sql1 = ' ' +
'a.twccs_claim_nbr, ' +
'a.twccs_case_nbr, ' +
'a.twccs_inj_ssn, ' +
'a.twccs_inj_fname, ' +
'a.twccs_inj_mid_init, ' +
'a.twccs_inj_lname, ' +
'a.twccs_inj_nme_suf, ' +
'mcdb.polloc_cw.tploc_name, ' +
'mcdb.polloc_cw.tploc_policy_nbr, ' +
'a.twccs_dt_of_inj, ' +
'a.twccs_case_sts, ' +
'mcdb.polloc_cw.tploc_team_nbr, ' +
'c.tctyp_user_key, ' +
'a.twccs_tpa_id, ' +
'b.twcon_con_type, ' +
'b.twcon_con_id_pers, ' +
'b.twcon_primary_ind, ' +
'e.tpoli_prem_tier, ' +
'e.tpoli_non_um, ' +
'f.NAME, ' +
'b.twcon_con_type, ' +
'a.twccs_1000_lmt_dt'
set @.from_sql = '' +
'FROM ' +
'MCDB.WCCASE_CW AS a ' +
'INNER JOIN MCDB.WCCONTT AS b ON ' +
'(a.TWCCS_CASE_NBR = b.TWCON_CASE_NBR) ' +
'INNER JOIN MCDB.CONTYPT AS c ON ' +
'(b.TWCON_CON_ID_PERS = c.TCTYP_CON_ID) ' +
'INNER JOIN MCDB.POLLOC_CW AS d ON ' +
'(a.TWCCS_POLICY_NBR = d.TPLOC_POLICY_NBR) ' +
'INNER JOIN MCDB.POLICY_CW AS e ON ' +
'(d.TPLOC_POLICY_NBR = e.TPOLI_POLICY_NBR) ' +
'Left Outer JOIN PRODSYS.CWPV001_PROV_MASTER as f ON ' +
'(a.TWCCS_POR_NO = f.PROV_NO)'
SET @.where_sql = '' +
'WHERE ' +
'b.TWCON_CON_TYPE IN (''''MCS'''', ''''MCN'''', ''''RRS'''') ' +
'AND ' +
'c.TCTYP_CON_TYPE = ''''USER'''' ' +
'AND ' +
'b.TWCON_STS = ''''A'''' ' +
'AND ' +
'a.TWCCS_CLAIM_NBR = '' + @.claim_nbr_2 + ''' +
''')'
exec( @.insert_sql + @.openquery_sql + @.fields_sql1 +
@.from_sql + @.where_sql )
/*
SELECT * FROM #tCaseContact
*/
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCN' AND PRIMARY_IND = '1'))
set @.pri_con_type = cast('MCN' as char(4))
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCS' AND PRIMARY_IND = '2'))
set @.pri_con_type = cast('MCS' as char(4))
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCS' AND CAST(PRIMARY_IND AS VARCHAR(4)) = '') AND
(exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'RRS' AND PRIMARY_IND = '4')))
set @.pri_con_type = cast('RRS' as char(4))
if @.pri_con_type is null set @.pri_con_type = cast('MCS' as char(4))
/*sets the MCS as the Primary Contact if the primary contact is returned as null*/
/* if (exists
(Select POR
FROM #tCaseContact set POR=f.
where POR is not null))
if Por='' or Por is null
Else set POR=null
*/
SELECT
TOP 1
a.CLAIM_NBR ,
a.CASE_NBR ,
a.INJ_SSN ,
a.INJ_FNAME ,
a.INJ_MID_INIT ,
a.INJ_LNAME ,
a.INJ_NME_SUF ,
a.TPLOC_NAME ,
a.TPLOC_POLICY_NBR ,
convert(varchar(10),a.DT_OF_INJ, 101) as TWCCS_DT_OF_INJ,
a.CASE_STS ,
a.TEAM_NBR ,
(
SELECT top 1 a1.USER_KEY FROM #tCaseContact AS a1 WHERE a1.CON_TYPE = @.pri_con_type
) as USER_KEY ,
a.TPA_ID,
a.EMP_TIER,
a.NON_UM,
a.POR,
(
SELECT top 1 a1.Con_Type FROM #tCaseContact AS a1 WHERE a1.CON_TYPE = @.pri_con_type
) as Cont_Type,
convert(varchar(10),a. Thousand_Date,101) as twccs_1000_lmt_dt
FROM
#tCaseContact AS a
GOdeclare @.openquery_sql varchar(1000)
Up the varchar limit to 2000 and give it a whirl. I think the variable is truncating.|||I still get the same error. Just out of curiousity, does SQL have a problem where the column name has numeric values in it? The reason I'm wondering is when I first added the Thousand_Date to the Temp table I called it K_1000 and SQL basically told me the it was an invalid value, when I chenged it a=it accepted it OK. The DB2 column is twccs_1000_lmt_dt in the @.fields_sql1 portion and I was wondering if that may have something to do with it.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "MCDB" was found following "". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].|||This looks like the DB2 server is having trouble with the query. Can you print out the select query that is being generated, and see if that runs via DB2's query tools?|||The only problem I have is my access to the DB2 database is limited to a view in a linked server. It's a remote database and they have it locked down. I don't have any DB2 utilities except for the OBDC conntection.|||Time to get to know someone with the tools, then.
You may not even have to go that far, though. Even if you spit out the query from all the dynamic string concat functions, you may see the problem right off.|||I'll see what I can do.|||Just a quick follow up in case anyone else runs into a similar situation. The problem turned out to be a problem with the DB2 date format. One of the other SQL Admins ran into it when he was creating our data warehouse. to resolve the problem the following code had to be inserted into the open query statement.
' CASE ' +
' WHEN CAST(TWCCS_1000_LMT_DT AS DATE) < ''''1753-01-01'' +
' THEN DATE(''''1753-01-01'''')' +
' ELSE CAST(TWCCS_1000_LMT_DT AS DATE)' +
' END ' +
' AS TWCCS_1000_LMT_DT '|||Just a quick follow up in case anyone else runs into a similar situation. The problem turned out to be a problem with the DB2 date format. One of the other SQL Admins ran into it when he was creating our data warehouse. to resolve the problem the following code had to be inserted into the open query statement.
' CASE ' +
' WHEN CAST(TWCCS_1000_LMT_DT AS DATE) < ''''1753-01-01'' +
' THEN DATE(''''1753-01-01'''')' +
' ELSE CAST(TWCCS_1000_LMT_DT AS DATE)' +
' END ' +
' AS TWCCS_1000_LMT_DT '
With that date is it a "Pilgrim Tracking System" ?
Happy turkey day.
An odd quandry.
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.
object link embedding procedure.
Basically importing filenames.
The three fields in question are identical.
Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value
And these three fields are populated from a substring of the filenames
which are generated in another table.
Filenametbl.pickno
here is the rub.
If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)
should return 200 records.
It returns 0 records.
So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)
returns 200 records in this scenario,
this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)
I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))
Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
WHERE ... NOT IN.
select
*
from
Filenametbl f
where not exists (select * from
document_link_staging dls
where dls.key2_value = f.pickno)
and not exists (select * from
document_link_storage dls
where dls.key2_value = f.pickno)
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.
The three fields in question are identical.
Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value
And these three fields are populated from a substring of the filenames
which are generated in another table.
Filenametbl.pickno
here is the rub.
If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)
should return 200 records.
It returns 0 records.
So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)
returns 200 records in this scenario,
this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)
I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))
Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.|||Hey, whoa, that works.
You're the man Tom.
So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?
Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.|||(KyussWren@.gmail.com) writes:
> Hey, whoa, that works.
> You're the man Tom.
> So just use EXISTS when there are nulls in the select list?
> Kinda like coalesce, but for subqueries?
EXISTS and NOT EXISTS have wider applicability than so. You also need
EXISTS / NOT EXISTS when the condition involves more than one column.
IN + subquery is mainly something I use when I'm writing ad hoc-queries
and I'm lazy. In programming code I use EXISTS / NOT EXISTS 90% of
the time.
--
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|||I'm a big EXISTS fan. The NULLs here are to do with the column you chose in
your subquery. I wouldn't think of it like COALESCE. Basically, NULL <>
anything, even another NULL. An IN predicate can be broken down like this:
x IN (1, 2, 3 null)
... means:
x = 1 or x = 2 or x = 3 or x = null
So, if x is 1, 2 or 3, it will be true. If x is null, then the result is
false, since x is really unknown and not equal to anything.
Now consider this:
x NOT IN (1, 2, 3 null)
... means:
x <> 1 and x <> 2 and x <> 3 and x <> null
Google de Morgan's Law.
What if x is 4? All conditions must be met. It passes the first 3, but
fails on the last, since 4 <> null is unknown, and is treated as false.
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146665188.587472.63170@.v46g2000cwv.googlegro ups.com...
Hey, whoa, that works.
You're the man Tom.
So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?
Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead
> of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.
Monday, March 19, 2012
An interesting issue with adding columns.
I've written this procedure that is a meta data manager if you will. It looks at a vocabulary table and will create or alter tables to be in line with the meta data. It works great and has for a couple of years now. However, I just rewrote it in an effort to make it more compact, efficient, etc. Now, our system uses at least three databases. One contains shared data, the next holds only system data for the app like individual user settings, etc (atslogin is the name) then would be an actual database for client data or accounts as we call them. Clients could have one or 100 of those. Please, no comments about moving everything in to one DB as that is not always possible to to hierarchy of multiple business models.
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)ooohhh dba-one, a padwon problem this is. meaningless the order of the data in the database is as Jedi Kaiser will readily remind you he will.|||Personally, some things are just not meant to be automated in my opinion
You'd have to post some code snipets though on what you are doing.
I use ERWin to do what you are doing, but I usually unload, drop and create the objects, then load|||Yoda say this ordinal problem not be a 0 to 1 issues. The voc file reads 0 for the first position while the system stuffs begins at 1. You didn't really think a Jedi master like me would be so stupid as to over look that did you?|||exec usp_madeof 'wcsub'
select colid,* from syscolumns where object_name(id)='wcsub' order by colid
Both will return the following in the results:
DOHFILING varchar 1 48
FORMTYPE varchar 2 53
(from sysobjects)
48 DOHFILING
53 FORMTYPE
Now, usp_made of is a procedure I wrote. It returns results in a fashion like Oracle's "desc" function. All that procedure does is read the INFORMATION_SCHEMA.[columns] view and order by ordinal position. Now, everytime I drop formtype and re add the column 53 will become 54 and so on. dohfiling will remain 48. This is nuts.|||Personally, some things are just not meant to be automated in my opinion
I very much agree. When I find columns out of order I refuse to automate the process. However, the columns are not out of order technically. I just can believe what syscolumns or other entries read. Even running alter table .. add column ... in the local DB as the owner is causing this so I can even remove my procedure from the matter.|||I tore out a couple hairs this week trying to get a Data Dictionary Collection script to work. Eventually, I just dumped the idea of checking columns by column ID and resorted to sorting the lower cased names of the columns and going by that. I figured if the column name exists, then I am OK. If it is not there at all, then I have to delete the column from the data dictionary. Or, if I find a column that is out of order, it must have been added, so in the data dictionary it goes. If I went by columnID I could be dropping good columns and re-adding them later on, when I find them again at the "end" of the table. Would that work for you?
EDIT: Bottom line is you have to work around the quirks in your RDBMS. And a non-contiguous set of column IDs counts as a quirk to me.|||Here read this.
http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
Once I read that, it only re-enforced dropping and re-creating the tables|||This has never happened and it is not an acceptable flaw of any RDBMS. Besides, my application expects things to be in a certain order and if it isn't, it will puke. I didn't design it that way but it is the way of things. If the table is created wrong then fine, manual intervention. If the system tables are wrong then how can I buy anything else they may tell me?|||This application is run all over the place at my clients sites. If it were an in house app I wouldn't mind dropping and recreating tables but that is horribly impractical in this case. Plus, I've had a few programmers here attempt to be clever and do things like that only to see it result in data loss. I can't attempt to automate the recreation of tables and moving data around. Too much risk for my taste.|||Personally, some things are just not meant to be automated in my opinionSo true. Experience is the difference between knowing what CAN be done, and what SHOULD be done. "Fail-safe systems fail by failing to fail safely."|||This application is run all over the place at my clients sites.
I don't understand, I would have a script that would run all the sql and apply the changes.
What kind of development lifecycle do you use?
And what's the frontt end written and how do you deploy that?|||The app is a VB/.Net app for insurance claims. It is a very complicated application as well. Scripts are fine in many cases but calls to my procedure are built in to it. When we make changes to table structures we simply send the meta data table. The app will then call my procedure to inspect and alter if needed, any involved tables. No single client has a standard version of our app because we will customize anything they want but the databases are largely the same with the exception of "user tables"
If we send an update that would say depend on a new table or two or many, we send the meta data file. The app will then test for existence and if they are not there my procedure creates the tables, index, keys, so on. Why send a meta data file and then have to manually do everything?
It seems everyone thinks I'm some kind of dummy for doing this! Our app has been doing things like this for nearly 20 years and the last 10 has been with Oracle or SQL Server depending on which DB the client wants to run it against. I just don't understand how this issue has only appeared now and not before.|||I can't be the only one who has run in to this can I?|||No single client has a standard version of our app because we will customize anything they want...and that is the root of the problem. Trying to be all things to all customers. I saw this as a problem back when I was a consumer of software, and I see it as a problem now that I am a producer of software. Client database get customized out the wazoo until they become administrative nightmares requiring the type of code you have had to implement (albeit for 20 years).
Software vendors should be experts in their industry and code their products according to best practices. Client modification of the database schema should not be allowed.
"You can customize it any way you want" is ultimately a flawed philosophy, and its reductio-ad-absurdum conclusion is an empty box with instructions to install the database server of the client's choice.|||You misunderstand. You may not tamper with the database but we will customize our programs. That is what I meant by that. Our Workers' Comp claim table is called WCCLM1 for example. Every client has this and if they get an update, they may need additional columns or columns expanded. if they add something to it, the app wont see it, etc
Oh and we don't ship a shrink wrapped product and tell them to plug it in to whatever they want. We support either Oracle or SQL Server only. We do a data conversion in most cases as well. We are hardly as stupid or sloppy as some may have understood.
Basically, client modifications to the database are not allowed and there is not a single thing that is arbitrary about the database design. Geez, all I need to do is add some columns or expand them without having to write a script every time. This isn't rocket science or reinvention of the wheel.|||Oh and one more thing. "You can customize it anyway you want" is true as long as you pay us to modify it. We don't ship code. We don't let clients ever have that, much less modify it. This isn't some open source trash. This is a 100k plus application.|||So you still run into the headache of maintaining code for as many versions as you have clients, but you have found a way to charge for the administrative hassle.
I'm not dissing your business plan. Whatever you and your clients agree to and makes you money is fair game. But I don't see how you can be surprised that rolling out updates in such a situation is a pain in the keister. It is difficult by design.|||Well, technically we only have two versions, 5.4 of our old system and 6.424 of out newer .NET version. Both use the same database, both use the same programs at any client site. What may be different is tables for a customized screen for a client claim program, Perhaps a custom AP, HR, etc interface. Our code and/or business model is not the mess some of you think it is. I guess I'm just not explaining it well.
Still, forget about all that. Even if it was a wreck, what does that have to do with what I consider a substantial flaw in this RDMS? Not a damn thing. The bottom line is that I should be able to add a column (oh and this happens even if I add the column manually and not via stored procedure in case anyone missed that) and have that recorded in the proper ordinal position in the system tables and views.
The feed back I'm getting here is that I'm stupid for expecting to actually utilize the alter table command. Someone explain why it's there if it can't be relied upon? You guys can talk all day long about how we distribute our app but no one can seem to offer anything of value with regard to the issue at hand.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!
Funny how I don't have this issue in Oracle. You don't even know what the hell you are talking about. Since when did I say what order they are in, inside the database matters to me? I never did. The application depends on things being a certain way. I didn't design that shit. Programmers did and now I have to deal with it. Still, how dare I expect things to be correct in SYSTEM TABLES! I'm not an idiot so I wish you people would stop assuming I am.
Thanks for nothing. All of you.|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.
I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?|||sweet Jesus the booty pleaser.
I bang this drum everywhere I go. You make money by coding a version of your product once and selling it a million times, not by selling your product and coding it a million times. my current employer still does not understand this concept yet either.|||I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?I realize that it doesn't help you much that your code relies on things that it shouldn't, and that is causing your problems. I think that we've all been there with code that was written long ago by folks that made assumptions that don't hold true with newer tools.
If your code relies on specific orders of attributes, and no gaps in the values in system tables (when your code shouldn't even know what those number are or that those gaps exist), then your code is faulty. You can work around the fault in your code by using the drop and create that Blindman suggested.
I'm sorry that you are stuck maintaining this code, but all we are trying to do is help... Getting hostile with us won't make your job any easier, and if you irritate the people that help you, that will probably make your job harder.
-PatP|||See, no one is helping me. That is why I'm getting a bit hot. Your wrote that my code is faulty. No it isn't. I'm under certain constraints of an application. One of them is my procedure being called by the application. Now suppose I do this drop/create deal. What if the table has ten million rows? That will take some time to run. Meanwhile, the user thinks that the app is hung up and does something stupid that maybe even implicit transactions may not be able to help?
Again, I have to work around what programmers have done, not them working around me. Just so everyone knows, I could note care less what order columns are in the table. The programmers, for whatever reason, do. If a table is created or altered outside of the order of the data dictionary table bad things happen. That IS NOT MY DESIGN.
I could create some rolling number sequence in SQL Server to get past this. My point is, why should I have to? I can't irritate people who help me because I've got no help. ya know? Everyone is talking like this is just normal. It just isn't.
Go ahead and lock this thread. It's going nowhere. Fast.|||Oh and before it is locked, if it is. Understand that my gripe isn't with this ordinal position as much as it makes me wonder what else I can't believe from the system. What else is bad? Is that so nuts of me?|||The system tables are correct, based on the rules for SQL Server. Please explain what you can't believe. I don't get what the problem is.
If your code isn't faulty, then it works "as is". If your code is faulty, then Blindman's suggestion will help you work around that fault. Again, I don't get what the problem is.
Plase help me to understand.
-PatP|||Why would you say the tables are correct? There is a gap in the ordinal value? What rules specifically are you speaking of. If I've got a table with ten columns, I add a column, it is eleven in ordinal position. If I drop it for the sake of doing it and re add it, it is still eleven in reality, not twelve as SQL Server will record it. Then on to higher numbers if I kept dropping it and re adding it. Now of course that isn't going to happen (continuous dropping/re add) but I'm just trying to get the point across.
Now in Oracle, I can do this all day long and user_tab_columns will show the correct column_id (1,2,3,4,5,6,7,8,9,10,11) no matter how many times I drop and re add a column. As I've stated before, I could easily create some rolling number while looking at syscolumns or something and just override what the ordinal value is in the table but I just don't like that. I'd rather do "fieldlocation+1<>colid" (note field location in my meta file begins with zero as opposed to 1 in syscolumns) to find something out of order, or a column that needs to be added. If I find something out of order, my procedure returns a message saying I wont do anything. In this case, manual intervention is required and that is how it should be. It is the application that will puke if columns are out of order, not my code. I really have to detect this though.
So, to sum it up, I'd love for colid to be in an order without gaps regardless of a column being dropped and then a new one added. That isn't right that SQL Server does anything else. And I'll say it again, what else is screwy if that is happening? Just like Blinds example about adding length to a column. My procedure does that, too. Now I can't expand a column correctly because maybe what is in syscolumns is actually wrong?
I'm not saying I can't get past this particular issue, I just wonder what else is wrong and I resent the fact I should have to jump through hoops to get a true sequential order from syscolumns.|||Being a user forum, we can really only deal with "what is", rather than "what should be". None of us can change the way syscolumns records column IDs. Perhaps you should take this to Microsoft, and see what they say?|||A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that.Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.|||I could poop in one hand and wish in the other to see which fills up faster! Those people at MS would be of no use. I'm sure they would offer a work around of "don't do that". I've found all kinds of Crazy things with SQL Server. Like looking at sysindexes in a particular database as another user in a different database. I get totally erratic results. That is just an example.
I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.|||Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.
Dude, get over yourself. I know that they are two different RDMS. I never said one set of code should run for both. I wrote that functionality that exists for one back end needs to exist for the other. That means two different procedures. However, I'd like to keep things simple. Thanks for the heads up though. And if the system tables are not intended to be used by anything other than the system, How should I determine a tables true structure? You can save your smart ass responses. They've been as little help as the rest of your post.|||I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.
Don't think so. You got upset because you define Oracle as sensible. I disagree. Cursors suck. Since oracle doesn't fit the SQL Server paradigm, Oracle must not be sensible, and a piece of c**p when it comes to the efficient use of limited system resources.
We agree to disagree!|||I never said one was perfect. Getting data from Oracle is like getting blood from a rock at times. Sometimes things are easy over "here" and hard "there" I never said one was better than the other. I mentioned Oracle as something to compare the situation with. Regardless, I've got to deal with things in both Oracle and SQL. It is just how things are.|||dba_one, we can all sympathize with your predicament, and i'm sure many of us can understand your frustration
however, at no point are you allowed to say things like "Dude, get over yourself" or "You can save your smart ass responses"
that's just not allowed|||Heck, that is tame but regardless, I didn't come here to fight. I didn't come here to have anyone just talk down to me, either. Regardless, I was looking for insight on a particular issue but there isn''t much that can be done about this particular thing so I've got to deal with it. So be it. I'm not mad, I'm just not the nice guy all the time. No harm intended and no harm done.|||OK.
Basically, the answer to your post boils down to:
A) The internals of SQL Server was not designed with your application in mind.
B) We can't change the internals of SQL Server.
C) No, not many people on this forum have run into the problem you are experiencing, because we have not designed applications such as yours.
D) We sympathize with your predicament.
Now lets all go back to playing nice.|||What the hell...fow did I miss all of this?|||Did you ever show us the code that is making these changes btw?
That might help out alot|||What the hell...fow did I miss all of this?
fow indeed!
:)
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)ooohhh dba-one, a padwon problem this is. meaningless the order of the data in the database is as Jedi Kaiser will readily remind you he will.|||Personally, some things are just not meant to be automated in my opinion
You'd have to post some code snipets though on what you are doing.
I use ERWin to do what you are doing, but I usually unload, drop and create the objects, then load|||Yoda say this ordinal problem not be a 0 to 1 issues. The voc file reads 0 for the first position while the system stuffs begins at 1. You didn't really think a Jedi master like me would be so stupid as to over look that did you?|||exec usp_madeof 'wcsub'
select colid,* from syscolumns where object_name(id)='wcsub' order by colid
Both will return the following in the results:
DOHFILING varchar 1 48
FORMTYPE varchar 2 53
(from sysobjects)
48 DOHFILING
53 FORMTYPE
Now, usp_made of is a procedure I wrote. It returns results in a fashion like Oracle's "desc" function. All that procedure does is read the INFORMATION_SCHEMA.[columns] view and order by ordinal position. Now, everytime I drop formtype and re add the column 53 will become 54 and so on. dohfiling will remain 48. This is nuts.|||Personally, some things are just not meant to be automated in my opinion
I very much agree. When I find columns out of order I refuse to automate the process. However, the columns are not out of order technically. I just can believe what syscolumns or other entries read. Even running alter table .. add column ... in the local DB as the owner is causing this so I can even remove my procedure from the matter.|||I tore out a couple hairs this week trying to get a Data Dictionary Collection script to work. Eventually, I just dumped the idea of checking columns by column ID and resorted to sorting the lower cased names of the columns and going by that. I figured if the column name exists, then I am OK. If it is not there at all, then I have to delete the column from the data dictionary. Or, if I find a column that is out of order, it must have been added, so in the data dictionary it goes. If I went by columnID I could be dropping good columns and re-adding them later on, when I find them again at the "end" of the table. Would that work for you?
EDIT: Bottom line is you have to work around the quirks in your RDBMS. And a non-contiguous set of column IDs counts as a quirk to me.|||Here read this.
http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
Once I read that, it only re-enforced dropping and re-creating the tables|||This has never happened and it is not an acceptable flaw of any RDBMS. Besides, my application expects things to be in a certain order and if it isn't, it will puke. I didn't design it that way but it is the way of things. If the table is created wrong then fine, manual intervention. If the system tables are wrong then how can I buy anything else they may tell me?|||This application is run all over the place at my clients sites. If it were an in house app I wouldn't mind dropping and recreating tables but that is horribly impractical in this case. Plus, I've had a few programmers here attempt to be clever and do things like that only to see it result in data loss. I can't attempt to automate the recreation of tables and moving data around. Too much risk for my taste.|||Personally, some things are just not meant to be automated in my opinionSo true. Experience is the difference between knowing what CAN be done, and what SHOULD be done. "Fail-safe systems fail by failing to fail safely."|||This application is run all over the place at my clients sites.
I don't understand, I would have a script that would run all the sql and apply the changes.
What kind of development lifecycle do you use?
And what's the frontt end written and how do you deploy that?|||The app is a VB/.Net app for insurance claims. It is a very complicated application as well. Scripts are fine in many cases but calls to my procedure are built in to it. When we make changes to table structures we simply send the meta data table. The app will then call my procedure to inspect and alter if needed, any involved tables. No single client has a standard version of our app because we will customize anything they want but the databases are largely the same with the exception of "user tables"
If we send an update that would say depend on a new table or two or many, we send the meta data file. The app will then test for existence and if they are not there my procedure creates the tables, index, keys, so on. Why send a meta data file and then have to manually do everything?
It seems everyone thinks I'm some kind of dummy for doing this! Our app has been doing things like this for nearly 20 years and the last 10 has been with Oracle or SQL Server depending on which DB the client wants to run it against. I just don't understand how this issue has only appeared now and not before.|||I can't be the only one who has run in to this can I?|||No single client has a standard version of our app because we will customize anything they want...and that is the root of the problem. Trying to be all things to all customers. I saw this as a problem back when I was a consumer of software, and I see it as a problem now that I am a producer of software. Client database get customized out the wazoo until they become administrative nightmares requiring the type of code you have had to implement (albeit for 20 years).
Software vendors should be experts in their industry and code their products according to best practices. Client modification of the database schema should not be allowed.
"You can customize it any way you want" is ultimately a flawed philosophy, and its reductio-ad-absurdum conclusion is an empty box with instructions to install the database server of the client's choice.|||You misunderstand. You may not tamper with the database but we will customize our programs. That is what I meant by that. Our Workers' Comp claim table is called WCCLM1 for example. Every client has this and if they get an update, they may need additional columns or columns expanded. if they add something to it, the app wont see it, etc
Oh and we don't ship a shrink wrapped product and tell them to plug it in to whatever they want. We support either Oracle or SQL Server only. We do a data conversion in most cases as well. We are hardly as stupid or sloppy as some may have understood.
Basically, client modifications to the database are not allowed and there is not a single thing that is arbitrary about the database design. Geez, all I need to do is add some columns or expand them without having to write a script every time. This isn't rocket science or reinvention of the wheel.|||Oh and one more thing. "You can customize it anyway you want" is true as long as you pay us to modify it. We don't ship code. We don't let clients ever have that, much less modify it. This isn't some open source trash. This is a 100k plus application.|||So you still run into the headache of maintaining code for as many versions as you have clients, but you have found a way to charge for the administrative hassle.
I'm not dissing your business plan. Whatever you and your clients agree to and makes you money is fair game. But I don't see how you can be surprised that rolling out updates in such a situation is a pain in the keister. It is difficult by design.|||Well, technically we only have two versions, 5.4 of our old system and 6.424 of out newer .NET version. Both use the same database, both use the same programs at any client site. What may be different is tables for a customized screen for a client claim program, Perhaps a custom AP, HR, etc interface. Our code and/or business model is not the mess some of you think it is. I guess I'm just not explaining it well.
Still, forget about all that. Even if it was a wreck, what does that have to do with what I consider a substantial flaw in this RDMS? Not a damn thing. The bottom line is that I should be able to add a column (oh and this happens even if I add the column manually and not via stored procedure in case anyone missed that) and have that recorded in the proper ordinal position in the system tables and views.
The feed back I'm getting here is that I'm stupid for expecting to actually utilize the alter table command. Someone explain why it's there if it can't be relied upon? You guys can talk all day long about how we distribute our app but no one can seem to offer anything of value with regard to the issue at hand.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!
Funny how I don't have this issue in Oracle. You don't even know what the hell you are talking about. Since when did I say what order they are in, inside the database matters to me? I never did. The application depends on things being a certain way. I didn't design that shit. Programmers did and now I have to deal with it. Still, how dare I expect things to be correct in SYSTEM TABLES! I'm not an idiot so I wish you people would stop assuming I am.
Thanks for nothing. All of you.|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.
I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?|||sweet Jesus the booty pleaser.
I bang this drum everywhere I go. You make money by coding a version of your product once and selling it a million times, not by selling your product and coding it a million times. my current employer still does not understand this concept yet either.|||I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?I realize that it doesn't help you much that your code relies on things that it shouldn't, and that is causing your problems. I think that we've all been there with code that was written long ago by folks that made assumptions that don't hold true with newer tools.
If your code relies on specific orders of attributes, and no gaps in the values in system tables (when your code shouldn't even know what those number are or that those gaps exist), then your code is faulty. You can work around the fault in your code by using the drop and create that Blindman suggested.
I'm sorry that you are stuck maintaining this code, but all we are trying to do is help... Getting hostile with us won't make your job any easier, and if you irritate the people that help you, that will probably make your job harder.
-PatP|||See, no one is helping me. That is why I'm getting a bit hot. Your wrote that my code is faulty. No it isn't. I'm under certain constraints of an application. One of them is my procedure being called by the application. Now suppose I do this drop/create deal. What if the table has ten million rows? That will take some time to run. Meanwhile, the user thinks that the app is hung up and does something stupid that maybe even implicit transactions may not be able to help?
Again, I have to work around what programmers have done, not them working around me. Just so everyone knows, I could note care less what order columns are in the table. The programmers, for whatever reason, do. If a table is created or altered outside of the order of the data dictionary table bad things happen. That IS NOT MY DESIGN.
I could create some rolling number sequence in SQL Server to get past this. My point is, why should I have to? I can't irritate people who help me because I've got no help. ya know? Everyone is talking like this is just normal. It just isn't.
Go ahead and lock this thread. It's going nowhere. Fast.|||Oh and before it is locked, if it is. Understand that my gripe isn't with this ordinal position as much as it makes me wonder what else I can't believe from the system. What else is bad? Is that so nuts of me?|||The system tables are correct, based on the rules for SQL Server. Please explain what you can't believe. I don't get what the problem is.
If your code isn't faulty, then it works "as is". If your code is faulty, then Blindman's suggestion will help you work around that fault. Again, I don't get what the problem is.
Plase help me to understand.
-PatP|||Why would you say the tables are correct? There is a gap in the ordinal value? What rules specifically are you speaking of. If I've got a table with ten columns, I add a column, it is eleven in ordinal position. If I drop it for the sake of doing it and re add it, it is still eleven in reality, not twelve as SQL Server will record it. Then on to higher numbers if I kept dropping it and re adding it. Now of course that isn't going to happen (continuous dropping/re add) but I'm just trying to get the point across.
Now in Oracle, I can do this all day long and user_tab_columns will show the correct column_id (1,2,3,4,5,6,7,8,9,10,11) no matter how many times I drop and re add a column. As I've stated before, I could easily create some rolling number while looking at syscolumns or something and just override what the ordinal value is in the table but I just don't like that. I'd rather do "fieldlocation+1<>colid" (note field location in my meta file begins with zero as opposed to 1 in syscolumns) to find something out of order, or a column that needs to be added. If I find something out of order, my procedure returns a message saying I wont do anything. In this case, manual intervention is required and that is how it should be. It is the application that will puke if columns are out of order, not my code. I really have to detect this though.
So, to sum it up, I'd love for colid to be in an order without gaps regardless of a column being dropped and then a new one added. That isn't right that SQL Server does anything else. And I'll say it again, what else is screwy if that is happening? Just like Blinds example about adding length to a column. My procedure does that, too. Now I can't expand a column correctly because maybe what is in syscolumns is actually wrong?
I'm not saying I can't get past this particular issue, I just wonder what else is wrong and I resent the fact I should have to jump through hoops to get a true sequential order from syscolumns.|||Being a user forum, we can really only deal with "what is", rather than "what should be". None of us can change the way syscolumns records column IDs. Perhaps you should take this to Microsoft, and see what they say?|||A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that.Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.|||I could poop in one hand and wish in the other to see which fills up faster! Those people at MS would be of no use. I'm sure they would offer a work around of "don't do that". I've found all kinds of Crazy things with SQL Server. Like looking at sysindexes in a particular database as another user in a different database. I get totally erratic results. That is just an example.
I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.|||Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.
Dude, get over yourself. I know that they are two different RDMS. I never said one set of code should run for both. I wrote that functionality that exists for one back end needs to exist for the other. That means two different procedures. However, I'd like to keep things simple. Thanks for the heads up though. And if the system tables are not intended to be used by anything other than the system, How should I determine a tables true structure? You can save your smart ass responses. They've been as little help as the rest of your post.|||I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.
Don't think so. You got upset because you define Oracle as sensible. I disagree. Cursors suck. Since oracle doesn't fit the SQL Server paradigm, Oracle must not be sensible, and a piece of c**p when it comes to the efficient use of limited system resources.
We agree to disagree!|||I never said one was perfect. Getting data from Oracle is like getting blood from a rock at times. Sometimes things are easy over "here" and hard "there" I never said one was better than the other. I mentioned Oracle as something to compare the situation with. Regardless, I've got to deal with things in both Oracle and SQL. It is just how things are.|||dba_one, we can all sympathize with your predicament, and i'm sure many of us can understand your frustration
however, at no point are you allowed to say things like "Dude, get over yourself" or "You can save your smart ass responses"
that's just not allowed|||Heck, that is tame but regardless, I didn't come here to fight. I didn't come here to have anyone just talk down to me, either. Regardless, I was looking for insight on a particular issue but there isn''t much that can be done about this particular thing so I've got to deal with it. So be it. I'm not mad, I'm just not the nice guy all the time. No harm intended and no harm done.|||OK.
Basically, the answer to your post boils down to:
A) The internals of SQL Server was not designed with your application in mind.
B) We can't change the internals of SQL Server.
C) No, not many people on this forum have run into the problem you are experiencing, because we have not designed applications such as yours.
D) We sympathize with your predicament.
Now lets all go back to playing nice.|||What the hell...fow did I miss all of this?|||Did you ever show us the code that is making these changes btw?
That might help out alot|||What the hell...fow did I miss all of this?
fow indeed!
:)
An INSERT EXEC statement cannot be nested.
I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @.dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @.dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @.dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@.doctype nvarchar(2),
@.datefrom datetime,
@.dateto datetime,
@.includegrn char(1),
@.includesa char(1),
@.includedata nvarchar(5)
AS
BEGIN
declare @.dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@.DOCTYPE = 'SI')
BEGIN
Insert Into @.dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
ELSE
BEGIN
Insert Into @.dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
Select doccode,docdate,debtoraccount From @.dtReturnData
END
So how can I select a nested store procedure? can someone help me
Jeremy,
This is a problem that comes up from time to time in SQL Server. My first suggestion when this comes up is to look at both points in which you are using the INSERT ... EXEC syntax. See if it is possible to convert at least one of the procedures into a user defined function. There is a "Plan C" for this but it is not nearly as clean as the option of converting to a function (if possible).
Also, for future keep in mind that it is a good idea to consider using functions -- especially inline functions -- instead of stored procedure when it is the intent to load the output from a procedure into a table.
See if SalesInvoiceRegister and DeliveryOrderRegister can be converted to functions.
Kent
Saturday, February 25, 2012
An Access OnFormat procedure would be replaced in SQL Reporting Services by what?
In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:
Weather - clear(1), cloudy(2), rain(4) or snow(8).
If the operator selected cloudy and rain, the value of Weather would be 6.
I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.
In Access, it was easy - I plugged a decode procedure into the OnFormat event...
cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If
Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.
Any help would be greatly appreciated.
There are probably a number of other ways to do this, this is just the first that came to mind.
Add the following on the Code tab of your project (select Report..Report Properties):
Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If
End Function
Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:
=Code.DecodeWeather(Fields!ID.Value, "SNOW")
Repeat for the other fields, passing the appropriate name to the function.
Hope this helps.
|||
Excellent answer!
Thank you.
An Access OnFormat procedure would be replaced in SQL Reporting Services by what?
In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:
Weather - clear(1), cloudy(2), rain(4) or snow(8).
If the operator selected cloudy and rain, the value of Weather would be 6.
I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.
In Access, it was easy - I plugged a decode procedure into the OnFormat event...
cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If
Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.
Any help would be greatly appreciated.
There are probably a number of other ways to do this, this is just the first that came to mind.
Add the following on the Code tab of your project (select Report..Report Properties):
Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If
End Function
Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:
=Code.DecodeWeather(Fields!ID.Value, "SNOW")
Repeat for the other fields, passing the appropriate name to the function.
Hope this helps.
|||
Excellent answer!
Thank you.
Sunday, February 19, 2012
Amending a stored procedure
Hi
I have a stored procedure that I have deployed with a database to an sqlserver. To access the database and the stored procedure I am now using SQL Server Management Studio Express. When I was amending the stored procedure locally, I simply "opened" it, made the amendments and then "saved" it. With S.M.S.E. if you click on "modify" a SQLQuery opens up and then after making the amendments you can save the SQLQuery. How do you go from here to effect that change to the stored procedure resident on the sqlserver.
Chris
I've worked out that all I have to do is run the query after amending the stored procedure detail.
Chris
|||
When you click the Modify button, it should show you the stored procedure and it will have an ALTER PROCEDURE command at the top. This just tells the database that you want to overwrite the existing stored procedure with whatever text follows. So, if you click the Execute button (it should have an exclamation mark next to it) the command should execute against the database and your stored procedure will be updated.
Mark
Thanks for your reply
Chris
Thursday, February 16, 2012
ambigous column name message
I am modifying an existing stored procedure in SQL server 2005. I have added a new field to the sp and am now receiving an ambiguous column name message. The column being referred to was in the sp before I modified. The column is on the line above where I added my new field ( EMPLOYEE NUMBER) to the sp. I am at a loss to why I'm getting this error message when executing the sp because this column existed before I modified. Can anyone help me understand why I'm getting this message all of a sudden and/ or where to look for help? Thanks in advance for any light you can help shed on this matter. Code snippet is below:
[BILL DUE DATE], [PAYMENT DATE AND TIME],
[EMPLOYEE NUMBER] )
Msg 209, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 34
Ambiguous column name 'PAYMENT DATE AND TIME'.
There is no way we can 'guess' what is happening without seeing the code.
Please post the entire stored procedure code.
|||Sorry, here is the sp:
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]
AS
setnocounton
TRUNCATETABLE [NO REASON LETTER];
INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],
[CONTACT PERSON], [ADDRESS LINE 1],
[ADDRESS LINE 2], [ADDRESS LINE 3],
[EMPLOYEE SSN], [SERVICE CODE],
[EMPLOYEE NAME], [BILLING PERIOD],
[BILL DUE DATE], [PAYMENT DATE AND TIME],
[EMPLOYEE NUMBER] )
SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],
[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],
[COMPANY ADDRESS].[CONTACT PERSON],
[COMPANY ADDRESS].[ADDRESS LINE 1],
[COMPANY ADDRESS].[ADDRESS LINE 2],
[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],
[NIGHT BATCH TABLE].[EMPLOYEE SSN],
[NIGHT BATCH TABLE].[SERVICE CODE],
[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),
[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],
[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],
[MAIN EMPLOYEE].[EMPLOYEE NUMBER]
FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND
([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))
INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])
INNERJOIN [NO REASON LETTER] ON [MAIN EMPLOYEE].[EMPLOYEE NUMBER] = [NO REASON LETTER].[EMPLOYEE NUMBER]
INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND
([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])
WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND
[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));
UPDATE NRL
SET NRL.[CARRIER NAME] = CA.[FULL NAME],
NRL.[LOGO PATH] = CA.[CONTACT PERSON],
NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND
CA.[ADDRESS TYPE] ='R')
UPDATE NRL
SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
ME.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 1),
NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
ME.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 2),
NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])
INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])
INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])
|||I have changed my code slightly and am now getting a differenet message (new code is below error message:Msg 4104, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 17
The multi-part identifier "NO REASON LETTER.EMPLOYEE NUMBER" could not be bound.
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]
AS
setnocounton
TRUNCATETABLE [NO REASON LETTER];
INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],
[CONTACT PERSON], [ADDRESS LINE 1],
[ADDRESS LINE 2], [ADDRESS LINE 3],
[EMPLOYEE SSN], [SERVICE CODE],
[EMPLOYEE NAME], [BILLING PERIOD],
[BILL DUE DATE], [PAYMENT DATE AND TIME],
[EMPLOYEE NUMBER])
SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],
[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],
[COMPANY ADDRESS].[CONTACT PERSON],
[COMPANY ADDRESS].[ADDRESS LINE 1],
[COMPANY ADDRESS].[ADDRESS LINE 2],
[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],
[NIGHT BATCH TABLE].[EMPLOYEE SSN],
[NIGHT BATCH TABLE].[SERVICE CODE],
[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),
[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],
[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],
[MAIN EMPLOYEE].[EMPLOYEE NUMBER]
FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND
([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))
INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])
INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]
INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND
([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])
WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND
[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));
UPDATE NRL
SET NRL.[CARRIER NAME] = CA.[FULL NAME],
NRL.[LOGO PATH] = CA.[CONTACT PERSON],
NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND
CA.[ADDRESS TYPE] ='R')
UPDATE NRL
SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
MET.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 1),
NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
MET.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 2),
NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])
INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])
INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])
|||Hi,
you are not referencing the Table in the Select clause, therefore you cannot use it in the join part.
BTW. Did I mention that it is horrorible to use space and special characters in defintions ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Now I'm confused.
First you TRUNCATE the [NO REASON LETTER] table.
Then you attempt to use JOIN conditions to that EMPTY table (but there is no previous mention of a JOIN to that table.. What are you trying to accomplish?
INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]
Even if it 'could' happen (and it just can't), since the table is empty, this would serve to filter out ALL possible rows and nothing would be inserted.
So what's the point?
|||One other thing to keep in mind is that if you are going to Alias a table in a join clause, you probably should use it in the join.
INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])
I would think that you would want to use MET.[EMPLOYEE NUMBER] instead of ME.
Ben Miller
Sunday, February 12, 2012
Alternative to xp_sendmail on 64 Bit SQL Server 2000
I'm looking for any alternative methods that can be used in place of the
xp_sendmail extended stored procedure when running on a 64 bit machine with
the 64 bit version of SQL Server 2000.
Any proven suggestions would be extremely helpful.
Not sure if this can help:
http://www.sqldev.net/xp/xpsmtp.htm
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Gordon Radley" <GordonRadley@.discussions.microsoft.com> wrote in message
news:DF298AAC-3340-47E4-BFA4-5006512B070D@.microsoft.com...
> I'm looking for any alternative methods that can be used in place of the
> xp_sendmail extended stored procedure when running on a 64 bit machine
> with
> the 64 bit version of SQL Server 2000.
> Any proven suggestions would be extremely helpful.
|||xp_smtp_sendmail doesn't work (probably because it's compiled for win32).
If anybody find a solution please tell me too.
"Gordon Radley" <GordonRadley@.discussions.microsoft.com> wrote in message
news:DF298AAC-3340-47E4-BFA4-5006512B070D@.microsoft.com...
> I'm looking for any alternative methods that can be used in place of the
> xp_sendmail extended stored procedure when running on a 64 bit machine
> with
> the 64 bit version of SQL Server 2000.
> Any proven suggestions would be extremely helpful.
xp_sendmail extended stored procedure when running on a 64 bit machine with
the 64 bit version of SQL Server 2000.
Any proven suggestions would be extremely helpful.
Not sure if this can help:
http://www.sqldev.net/xp/xpsmtp.htm
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Gordon Radley" <GordonRadley@.discussions.microsoft.com> wrote in message
news:DF298AAC-3340-47E4-BFA4-5006512B070D@.microsoft.com...
> I'm looking for any alternative methods that can be used in place of the
> xp_sendmail extended stored procedure when running on a 64 bit machine
> with
> the 64 bit version of SQL Server 2000.
> Any proven suggestions would be extremely helpful.
|||xp_smtp_sendmail doesn't work (probably because it's compiled for win32).
If anybody find a solution please tell me too.
"Gordon Radley" <GordonRadley@.discussions.microsoft.com> wrote in message
news:DF298AAC-3340-47E4-BFA4-5006512B070D@.microsoft.com...
> I'm looking for any alternative methods that can be used in place of the
> xp_sendmail extended stored procedure when running on a 64 bit machine
> with
> the 64 bit version of SQL Server 2000.
> Any proven suggestions would be extremely helpful.
Alternative to using USE in a PROCEDURE
is there a way to get around not using USE in a PROCEDURE?
I need to because I have a main site that inserts information into other DB's that i use for various subdomains. But without being able to use USE i cant select which database is needed.
thx in advanceshould i use the master database and prefix my procedures with sp_?|||Huh second time in 2 mins. Use the fully qualified path to the entity
<database>.<owner>.<entity>
Alternative to Temporary table to store stored procedure results
I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.
Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>
|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.
Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>
|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
Alternative to Temporary table to store stored procedure results
I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
Alternative to Temporary table to store stored procedure results
I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
> >I have come across the error "INSERT EXEC statement cannot be nested"
> > when trying to store the results of a stored procedure in a temporary
> > table. I understand why this is happening - because there is already
> > an INSERT EXEC in the stored procedure I am executing - but I need to
> > be able to store the results in some way.
> >
> > Unfortunately re-writing the stored procedure I am calling is not an
> > option, and I was wondering if there is another way for me to evaluate
> > the results from my stored procedure.
> >
> > I have looked into table variables and functions, but they do not work
> > here either.
> >
> > I would appreciate anyone's input on this.
> >
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
> >I have come across the error "INSERT EXEC statement cannot be nested"
> > when trying to store the results of a stored procedure in a temporary
> > table. I understand why this is happening - because there is already
> > an INSERT EXEC in the stored procedure I am executing - but I need to
> > be able to store the results in some way.
> >
> > Unfortunately re-writing the stored procedure I am calling is not an
> > option, and I was wondering if there is another way for me to evaluate
> > the results from my stored procedure.
> >
> > I have looked into table variables and functions, but they do not work
> > here either.
> >
> > I would appreciate anyone's input on this.
> >
Thursday, February 9, 2012
Alternative to dynamic sql?
I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.
If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @.lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @.lcIdNr
ELSE
SELECT *
FROM table
2. Use dynamic SQL.
The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?
Regards
JennyMaybe:
SELECT *
FROM TableX
WHERE idnr = @.lcIdNr OR @.lcIdNr IS NULL
This article explains some of the things you should consider before using
Dynamic SQL:
http://www.algonet.se/~sommar/dynamic_sql.html
--
David Portas
----
Please reply only to the newsgroup
--|||SELECT *
FROM Foobar
WHERE idnr = COALESCE(@.lcIdNr, idnr);|||[posted and mailed, vnligen svara i nys]
Jenny (jenny@.megasol.se) writes:
> If this id is submitted then I will return only the posts with this
> idnr, but if this is not submitted, I will return all posts in table.
> As I can see I have two options
> 1. IF @.lcIdNr IS NOT NULL
> SELECT *
> FROM table
> WHERE idnr = @.lcIdNr
> ELSE
> SELECT *
> FROM table
> 2. Use dynamic SQL.
> The first example can work with just one parameter but with a couple
> of different input paramters this could be difficult, anyway this is
> not a good solution. The second example works fine but as I understand
> dynamic sql is not good from the optimizing point of view.
Actually in this case it's the opposite. For these kind of queries,
dynamic SQL usually gives you the best combination performance and
maintainability.
For a longer discussion on the topic, see this article on my web site:
http://www.algonet.se/~sommar/dyn-search.html. (This is not the same
that David referred you too.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Alternative for LEFT() and CHARINDEX() that works with text Data Type
I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this
@.array_value=LEFT(@.ContactList, @.separator_position- 1)
The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.
Thanks,
Erick
what database & version are you using?
|||SQL Server 2005
|||Use varchar(max) instead of varchar(8000) then.
|||Can you change the datatype? Try using varchar(MAX) or nvarchar(MAX). MSDN says built-in string functions have been built to work with the new large object types (MAX).
http://msdn2.microsoft.com/en-us/library/ms178158.aspx
|||
I just changed, I didn't know that worked. I just read thats new in sql 2005, that didn't work on 2000.
Thanks
|||That worked but, Can I give max value to varchar in asp.net vb?
|||http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx
|||It looks like you just set the type to the normal DBType, i.e. use varchar for varchar(MAX), then set the length to -1 (this tells the parameter to use whatever length the data is, rather than specifying how much of the data to use).
Subscribe to:
Posts (Atom)