Max Query: proc

Showing posts with label proc. Show all posts

Wednesday, March 28, 2012

Help with SQL errors in Profiler

I noticed recently errors logged, each 5 second in the event viewer. I ran
Profiler by adding Errors and warning, RPCcompleted, Stored proc...
I see the same errors logged in profiler as the oners logged in event viewer
but can not find the text or the SP generating the error. errors in Profiler
appear as follows :
Exception : Error: 208, Severity: 16, State: 1
ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16, State: 1
2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
My web site continu to work and I don't see error messages on my pages.
I use DBCC INPUTBUFFER(SPID), I get the following result :
RPC Event0sp_executesql;1
How can I configure or what should I do in my profiler to discover the
statement or SP that causing the error. I should signal that just before
error messages I have RPC:Completed messages for different SP
Hi,
Please make sure that you have events from TSQL group included in your
trace. The most useful would be:
SQL:StmtStarting or SQL:BatchStarting.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:8FEBA1EB-DE4A-47B4-B154-A9B7532F17C3@.microsoft.com...
>I noticed recently errors logged, each 5 second in the event viewer. I ran
> Profiler by adding Errors and warning, RPCcompleted, Stored proc...
> I see the same errors logged in profiler as the oners logged in event
> viewer
> but can not find the text or the SP generating the error. errors in
> Profiler
> appear as follows :
> Exception : Error: 208, Severity: 16, State: 1
> ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16,
> State: 1
> 2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
> My web site continu to work and I don't see error messages on my pages.
> I use DBCC INPUTBUFFER(SPID), I get the following result :
> RPC Event 0 sp_executesql;1
> How can I configure or what should I do in my profiler to discover the
> statement or SP that causing the error. I should signal that just before
> error messages I have RPC:Completed messages for different SP
|||Hi and so many thanks.
I set up profiler sessions where I capture SQL_BatchStarting & RPC:Starting
or SQL:stmtstarting
I get
---Start Profiler
result--
exec [PEP_ETC]..sp_procedure_params_rowset N'CreateSearchSP', 1, NULL, NULL
SET NO_BROWSETABLE ON
RPC:Starting
declare @.P1 int
set @.P1=-1
exec sp_prepare @.P1 output, N'@.P1 varchar(2),@.P2 varchar(8),@.P3
varchar(3),@.P4 varchar(1),@.P5 varchar(2700),@.P6 varchar(255),@.P7
varchar(100),@.P8 varchar(600),@.P9 varchar(3),@.P10 varchar(3),@.P11
varchar(2),@.P12 varchar(100)', N'exec CreateSearchSP @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8, @.P9, @.P10, @.P11, @.P12', 1
select @.P1
SQL_BatchStarting
SET FMTONLY ON exec sp_execute 7,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','
',' ' SET FMTONLY OFF
---End Profiler
result--
then the error comes here
---Start Profiler
result--
EXCEPTION : Error: 208, Severity: 16, State: 1
ErrorLog
2004-09-28 15:18:38.45 spid66 Error: 208, Severity: 16, State: 1
2004-09-28 15:18:38.45 spid66 Invalid object name '##CC'..
---End Profiler
result--
In the SP CreateSearchSP, I have a dynamic insert statement as follows :
EXEC "Select xx, yyyy into ##CC" + @.sTableName + " where labalablab "
This SP procedure is called from several places (ASP pages and from within
other SPs inside SQL) with same user and IIS appli.
So what I need to know is just the text that is calling this stored procedure.
When I browse the page that heavily use it, I don't see any error. So it is
sure either when SQL under heavy load can't execute it correctly or the error
is generated when calling it from somewhere else which I would like to
discover.
So what I need to know is just the text that is calling this stored procedure?
"Maciek Sarnowicz [MSFT]" wrote:

> Hi,
> Please make sure that you have events from TSQL group included in your
> trace. The most useful would be:
> SQL:StmtStarting or SQL:BatchStarting.
> Regards,
> Maciek Sarnowicz
> SQL Server Tools SDE
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:8FEBA1EB-DE4A-47B4-B154-A9B7532F17C3@.microsoft.com...
>
>
sql

Help with SQL errors in Profiler

I noticed recently errors logged, each 5 second in the event viewer. I ran
Profiler by adding Errors and warning, RPCcompleted, Stored proc...
I see the same errors logged in profiler as the oners logged in event viewer
but can not find the text or the SP generating the error. errors in Profiler
appear as follows :
Exception : Error: 208, Severity: 16, State: 1
ErrorLog : 2004-09-23 09:24:43.82 spid66 Error: 208, Severity: 16, State: 1
2004-09-23 09:24:43.82 spid66 Invalid object name '##CC'...
My web site continu to work and I don't see error messages on my pages.
I use DBCC INPUTBUFFER(SPID), I get the following result :
RPC Event0sp_executesql;1
A microsofot consultanty asked me to set up profiler sessions where I
capture SQL_BatchStarting & RPC:Starting
or SQL:stmtstarting
I get
---Start Profiler
result--
exec [PEP_ETC]..sp_procedure_params_rowset N'CreateSearchSP', 1, NULL, NULL
SET NO_BROWSETABLE ON
RPC:Starting
declare @.P1 int
set @.P1=-1
exec sp_prepare @.P1 output, N'@.P1 varchar(2),@.P2 varchar(8),@.P3
varchar(3),@.P4 varchar(1),@.P5 varchar(2700),@.P6 varchar(255),@.P7
varchar(100),@.P8 varchar(600),@.P9 varchar(3),@.P10 varchar(3),@.P11
varchar(2),@.P12 varchar(100)', N'exec CreateSearchSP @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8, @.P9, @.P10, @.P11, @.P12', 1
select @.P1
SQL_BatchStarting
SET FMTONLY ON exec sp_execute 7,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','
',' ' SET FMTONLY OFF
---End Profiler
result--
then the error comes here
---Start Profiler
result--
EXCEPTION : Error: 208, Severity: 16, State: 1
ErrorLog
2004-09-28 15:18:38.45 spid66 Error: 208, Severity: 16, State: 1
2004-09-28 15:18:38.45 spid66 Invalid object name '##CC'..
---End Profiler
result--
In the SP CreateSearchSP, I have a dynamic insert statement as follows :
EXEC "Select xx, yyyy into ##CC" + @.sTableName + " where labalablab "
This SP procedure is called from several places (ASP pages and from within
other SPs inside SQL) with same user and IIS appli.
So what I need to know is just the text that is calling this stored procedure.
When I browse the page that heavily use it, I don't see any error. So it is
sure either when SQL under heavy load can't execute it correctly or the error
is generated when calling it from somewhere else which I would like to
discover.
So what I need to know is just the text that is calling this stored procedure?
Try following steps.
Place a filter on SPIDS to see only that spid that is resulting into error.
That should give you the commands being executed from that spid only.
Hope fro there you should be able to figure out which SP is calling it.
|||Thanks for the response. How can Ifigure out the SPID before running profiler?
I am not so expert in SPID maniuplating, If I am not mistaken, between the
momemnt where you have a SPID and the moment t for exemple to use dbcc
inputbuffer, the sql phrase might change, no ?
Thanks
"Vikas Kumar" wrote:

> Try following steps.
> Place a filter on SPIDS to see only that spid that is resulting into error.
> That should give you the commands being executed from that spid only.
> Hope fro there you should be able to figure out which SP is calling it.
>

Monday, March 26, 2012

Help with SP syntax

I have the stored proc. below and I'm passing two
parameters. What I'm trying to do is if either one of
the parameters is equal to "All", then change the value
of the paramter to an empty string or set another
variable to an empty string. SQL doesn't like the code I
have below. Please help.
CREATE PROCEDURE GetUSFSUsers
(
@.Role nvarchar(100),
@.Unit nvarchar(20)
)
AS
Declare @.Role2 nvarchar(100)
Declare @.Unit2 nvarchar(20)
If @.Role = 'All'
@.Role2 = ''
Else
@.Role2 = @.Role
If @.Unit = 'All'
@.Unit2 = ''
Else
@.Unit2 = @.UnitYou can change it to
if @.role = 'all'
set @.role2 = ''
else
set @.role2 = @.role
if @.unit = 'all'
set @.unit2 = ''
else
set @.unit2 = @.unit
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help with sp code - cursor

Friends,
When I step through the following code in QA it works fine - when I alter
the proc and run it, I get an error (you should be able to copy the code as
it is into your own QA for testing) - Anybody know where I am making the
mistke? Thanks in advance for your help ... Bill Morgan
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values
(@.dater)
set @.dater = @.dater + 1
end
update #main
set ca = 'a'
select * from #main
set nocount off
returnYour table #main doesn't have a column "ca" in it, just a column "Dates":

> update #main
> set ca = 'a'
"bill_morgan" <bill_morgan@.discussions.microsoft.com> wrote in message
news:7F945501-89B0-4B6C-81BC-4767C92B0464@.microsoft.com...
> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code
> as
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Look at where you create the #Main table, there is no "ca" column defined in
it, only a "dates" column... Therefore, later on where you try to update the
'ca' column, it fails... Can;t begin to suggest a fix until I know what
Stored Proc is SUpposed t odo...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Sorry, Now I see what you're doing...
Wat's wrong is that You have an Open uncommitted transaction
just delete the Begin Tran line and try it again... If you need the tran,
then you have to put in a corresponding Commit tran...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Here is the explanation of what is happening.
http://groups.google.ca/groups?selm...FTNGP11.phx.gbl
Also, you have a begin transaction without a matching commit / rollback.
Example:
-- I commented the begin transaction
use northwind
go
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
--begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values (@.dater)
set @.dater = @.dater + 1
end
exec ('update #main set ca = ''a''')
select * from #main
set nocount off
return
go
exec tester
go
drop procedure tester
go
AMB
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Bill, What is going on is that SQL7/2000 has what is called delayed
verification o(or something like that) which basically does NOT check the
column names of tables whoch ddo not currently exist when you create the
Stored Proc. It waits until run time... Then it checks again, BEFORE The
stored Proc runs, to make sure that every column and table exists...
So what's going on here is that the compiler sees that you're going to
create the #Main table, and that it will have a column named 'dates', but it
doesn't (no way it can) see that you're going to alter the table and add all
those state name columns, so the Update #Main Set CA = 'a' line fails the
compiler test...
If you comment that line out, (and fx the Open Transaction issue), the code
will work.
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||my apologies for the begin tran statement - i was monkeying with the
procedure and forgot to take that out - once it's removed you can step
through the procedure, but trying to run it all at once fails ...
"CBretana" wrote:
> Sorry, Now I see what you're doing...
> Wat's wrong is that You have an Open uncommitted transaction
> just delete the Begin Tran line and try it again... If you need the tran,
> then you have to put in a corresponding Commit tran...
> "bill_morgan" wrote:
>|||Thanks for the guidance ... my apologies for the begin tran - i forgot to
take that out before I posted this question (I thought the begin tran /
commit tran) might fix things ... I am visiting the sight you suggest ...
thanks ...
"Alejandro Mesa" wrote:
> Here is the explanation of what is happening.
> [url]http://groups.google.ca/groups?selm=uxV68C33DHA.3468%40TK2MSFTNGP11.phx.gbl[/url
]
> Also, you have a begin transaction without a matching commit / rollback.
> Example:
> -- I commented the begin transaction
> use northwind
> go
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> --begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values (@.dater)
> set @.dater = @.dater + 1
> end
> exec ('update #main set ca = ''a''')
> select * from #main
> set nocount off
> return
> go
> exec tester
> go
> drop procedure tester
> go
>
> AMB
>
> "bill_morgan" wrote:
>|||Yes ..!! I created a new proc to handle that final update ... Proc 1 calls
Proc 2 and it works great ... thank you for the new knowledge ...
"CBretana" wrote:
> Bill, What is going on is that SQL7/2000 has what is called delayed
> verification o(or something like that) which basically does NOT check the
> column names of tables whoch ddo not currently exist when you create the
> Stored Proc. It waits until run time... Then it checks again, BEFORE The
> stored Proc runs, to make sure that every column and table exists...
> So what's going on here is that the compiler sees that you're going to
> create the #Main table, and that it will have a column named 'dates', but
it
> doesn't (no way it can) see that you're going to alter the table and add a
ll
> those state name columns, so the Update #Main Set CA = 'a' line fails the
> compiler test...
> If you comment that line out, (and fx the Open Transaction issue), the cod
e
> will work.
> "bill_morgan" wrote:
>sql

Friday, March 9, 2012

help with proc

i wrote a procedure which calculates how many minutes a person is late to th
e
office on a particular day
what is the best method for converting it to calculate the minutes late for
the
whole monthHi Raghu
You can sum up all the minutes and show it as a monthly late.
just see if this helps you:
SELECT
NAME, SUM( DATEDIFF(mi, timearrived, officetime ) ),
datepart(month,timearrived)
FROM ATTENDANCE
GROUP BY NAME, datepart(month,timearrived)
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"raghu veer" wrote:

> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||Raghu
use datediff(m.date1,date2) in query
date1 or date1 is stored using getdate() default so that the time is also
stored in databse
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:

> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||I mean
use datediff('mi'.date1,date2) in query
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Raghu
> use datediff(m.date1,date2) in query
> date1 or date1 is stored using getdate() default so that the time is also
> stored in databse
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "raghu veer" wrote:
>

Wednesday, March 7, 2012

help with OPENXML

I'm having problem with inserting xml into sql server.
I have procedure:
------
CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
------
and then I save xml data from dataset:
------
Dim connStr As String =
"Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated Security=SSPI"
Dim cmd As New SqlXmlCommand(connStr)
cmd.RootTag = "ROOT"
cmd.CommandType = SqlXmlCommandType.Sql
cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT ListaIzvjID,
BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje, Potrazuje, U, Jed, Odj,
Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID = L.ListaIzvjID WHERE
(L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
Dim DA As New SqlXmlAdapter(cmd)
Dim ds As New DataSet
DA.Fill(ds)
ds.WriteXml("D:\BGListaIzvj.xml")
------
and this is the xml file:
------
<?xml version="1.0" standalone="yes"?>
<ROOT>
<L>
<ListaIzvjID>16</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>1</RedBroj>
<Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
<PocStanje>328.79</PocStanje>
<ZavStanje>143.18</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>01.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>16</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-01-07T00:00:00</Datum>
<Primljen>-</Primljen>
<Opis>plaa za 12/03 za Amaliju Cigler</Opis>
<Dokum>Upl1</Dokum>
<Konto>1009</Konto>
<Duguje>222.4</Duguje>
<Potrazuje>0</Potrazuje>
Zagreb
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
 1
<Godina2>2004</Godina2>
</BGIzvj>
</L>
<L>
<ListaIzvjID>17</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>2</RedBroj>
<Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
<PocStanje>143.18</PocStanje>
<ZavStanje>1797.79</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>06.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>17</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-06-15T00:00:00</Datum>
<Primljen>gotovina</Primljen>
<Opis>mat. trokove i isplata PN</Opis>
<Dokum>Upl2</Dokum>
<Konto>1009</Konto>
<Duguje>15000</Duguje>
<Potrazuje>0</Potrazuje>
Zagreb
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
 2
<Godina2>2004</Godina2>
</BGIzvj>
</L>
</ROOT>
------
Now, I'm trying to save that xml into sql database with this:
Dim sr As New StreamReader("d:\BGListaIzvj.xml")
Dim strXML As String
strXML = sr.ReadToEnd
sr.Close()
cmd.Parameters("@.strXML").Value = strXML
SqlConnection1.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
SqlConnection1.Close()
but I'm getting error, XML Parsing error: An Invalid character found in text
content.
I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of xml
file, but it won't work.
And why is that <L> </L> there?
Can somebody help me?
thanks!
Try NTEXT for the type of @.strXML (I assume that the data is UTF-16
encoded).
The L element is there because of your FOR XML query.
Best regards
Michael
"green_eye" <bernard@.form.hr> wrote in message
news:clt9kv$cnq$1@.ls219.htnet.hr...
> I'm having problem with inserting xml into sql server.
> I have procedure:
> ------
> CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ------
>
> and then I save xml data from dataset:
> ------
> Dim connStr As String =
> "Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated
> Security=SSPI"
> Dim cmd As New SqlXmlCommand(connStr)
> cmd.RootTag = "ROOT"
> cmd.CommandType = SqlXmlCommandType.Sql
>
> cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
> L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
> I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
> I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
> I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT
> ListaIzvjID, BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje,
> Potrazuje, U, Jed, Odj, Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID =
> L.ListaIzvjID WHERE (L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
>
> Dim DA As New SqlXmlAdapter(cmd)
> Dim ds As New DataSet
> DA.Fill(ds)
>
> ds.WriteXml("D:\BGListaIzvj.xml")
>
> ------
>
> and this is the xml file:
> ------
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <L>
> <ListaIzvjID>16</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>1</RedBroj>
> <Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
> <PocStanje>328.79</PocStanje>
> <ZavStanje>143.18</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>01.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>16</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-01-07T00:00:00</Datum>
> <Primljen>-</Primljen>
> <Opis>plaa za 12/03 za Amaliju Cigler</Opis>
> <Dokum>Upl1</Dokum>
> <Konto>1009</Konto>
> <Duguje>222.4</Duguje>
> <Potrazuje>0</Potrazuje>
> Zagreb
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> 1
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> <L>
> <ListaIzvjID>17</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>2</RedBroj>
> <Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
> <PocStanje>143.18</PocStanje>
> <ZavStanje>1797.79</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>06.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>17</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-06-15T00:00:00</Datum>
> <Primljen>gotovina</Primljen>
> <Opis>mat. trokove i isplata PN</Opis>
> <Dokum>Upl2</Dokum>
> <Konto>1009</Konto>
> <Duguje>15000</Duguje>
> <Potrazuje>0</Potrazuje>
> Zagreb
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> 2
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> </ROOT>
> ------
> Now, I'm trying to save that xml into sql database with this:
>
> Dim sr As New StreamReader("d:\BGListaIzvj.xml")
> Dim strXML As String
> strXML = sr.ReadToEnd
> sr.Close()
>
> cmd.Parameters("@.strXML").Value = strXML
> SqlConnection1.Open()
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> MsgBox(ex.Message)
> End Try
> SqlConnection1.Close()
>
> but I'm getting error, XML Parsing error: An Invalid character found in
> text content.
> I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of
> xml file, but it won't work.
> And why is that <L> </L> there?
>
> Can somebody help me?
>
> thanks!
>
>
|||OK, thanks! It works with nText type, but I have some other problem.
With this procedure:
CREATE PROC sp_insert_BGListaIzvj @.strXML nText
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '//BGIzvj')
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
and this XML file:
<?xml version="1.0" standalone="yes"?>
<ROOT>
<BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="1" Godina2="2004" />
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03" Dokum="Ispl1"
Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="1" Godina2="2004" />
</BGListaIzvj>
<BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18" ZavStanje="1797.79"
Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="2" Godina2="2004" />
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="5" Godina2="2004" />
</BGListaIzvj>
</ROOT>
I get rows in second table (BGIzvj) with NULL values in columns ListaIzvjID,
BlgID and Godina. These three tables are the same as in BGListaIzvj table.
Is that a problem? Is there way to fix this?
Thanks!
|||Hi Bernard
What are the schemas of your relational tables?
Since you get the rows, your path expressions find the nodes that map to
rows, but it looks like that the relative paths implied by the relational
schema does not find any values for the nodes where you get NULL.
This probably means that your Schema names and attribute names do not fit.
Best regards
Michael
"Bernard" <bernard@.form.hr> wrote in message
news:cm0576$r2e$1@.ls219.htnet.hr...
> OK, thanks! It works with nText type, but I have some other problem.
> With this procedure:
> ----
> CREATE PROC sp_insert_BGListaIzvj @.strXML nText
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '//BGIzvj')
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ----
>
> and this XML file:
> ----
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
> Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
> Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
> Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="1" Godina2="2004" />
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03"
> Dokum="Ispl1" Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3"
> Odj="0" Tip="I" BR="1" Godina2="2004" />
> </BGListaIzvj>
> <BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
> Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18"
> ZavStanje="1797.79" Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
> Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
> Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="2" Godina2="2004" />
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
> Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
> Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
> BR="5" Godina2="2004" />
> </BGListaIzvj>
> </ROOT>
> ----
>
> I get rows in second table (BGIzvj) with NULL values in columns
> ListaIzvjID, BlgID and Godina. These three tables are the same as in
> BGListaIzvj table. Is that a problem? Is there way to fix this?
>
> Thanks!
>
>
>

Monday, February 27, 2012

help with my select

I hope this is the right place.

I have a proc here, it selects the data needed, but when i put in a sum aggrate the service if sumed up, but it sums all of the records. what i am trying to do is sum up the first service fees that appear.

would you folks be kind and point me in the right direction as how i can acomplish this?

here is my proc code.

DECLARE @.EOBFileName NVARCHAR(50)
SET @.EOBFileName = 'B835255227__CHSEP__2107032414052256619'
SELECT DISTINCT ISNULL(slp.pkServiceLinePayment, 0) AS PaymentKey
, ISNULL(slp.fkClaim, 0) AS ClaimKey
, ISNULL(slp.fkServiceLine, 0) AS ServiceLineKey
, ISNULL(slp.fkInsurance, 0) AS InsuranceKEy
, ISNULL(slp.ServiceDate, 0) AS ServiceDate
, ISNULL(slp.ServiceCode, 0) AS ServiceCode
, ISNULL(slp.ServiceFee, '') AS ServiceFee
, ISNULL(slp.InsurancePayment, '') AS InsurancePayment
, ISNULL(c.fkRenderingServiceProvider, '') AS ProviderKey
, ISNULL(ent.NM103, '') AS ProviderName
, ISNULL(slp.CurrentStatus, '') AS Status
, ISNULL(pat.NM103, '') + ', ' + ISNULL(pat.NM104, '') AS PatientName
, ISNULL(ins.PlanID, '') AS Policy
, ISNULL(ins.GroupPlanID, '') AS GroupID
, ISNULL(slp.PayerClaimTrace, '') AS ClaimTrace
, ISNULL(slpa.GroupCode, '') AS GroupCode
, ISNULL(grp.CodeDescription, '') AS GroupDescription
, ISNULL(slpa.ReasonCode1, '') AS ReasonCode1
, ISNULL(rcode1.CodeDescription, '') AS ReasonDesc1
, ISNULL(slpa.MonetaryAmount1, '') AS Amount1
, ISNULL(slpa.Quantity1, 1) AS Qt1
, ISNULL(slpa.ReasonCode2, '') AS ReasonCode2
, ISNULL(rcode2.CodeDescription, '') AS ReasonDesc2
, ISNULL(slpa.MonetaryAmount2, '') AS Amount2
, ISNULL(slpa.Quantity2, '') AS Qt2
, ISNULL(slpa.ReasonCode3, '') AS ReasonCode3
, ISNULL(rcode3.CodeDescription, '') AS ReasonDesc3
, ISNULL(slpa.MonetaryAmount3, '') AS Amount3
, ISNULL(slpa.Quantity3, '') AS Qt3
, ISNULL(slpa.ReasonCode4, '') AS ReasonCode4
, ISNULL(rcode4.CodeDescription, '') AS ReasonDesc4
, ISNULL(slpa.MonetaryAmount4, '') AS Amount4
, ISNULL(slpa.Quantity4, '') AS Qt4
, ISNULL(slpa.ReasonCode5, '') AS ReasonCode5
, ISNULL(rcode5.CodeDescription, '') AS ReasonDesc5
, ISNULL(slpa.MonetaryAmount5, '') AS Amount5
, ISNULL(slpa.Quantity5, '') AS Qt5
, ISNULL(slpa.ReasonCode6, '') AS ReasonCode6
, ISNULL(rcode6.CodeDescription, '') AS ReasonDesc6
, ISNULL(slpa.MonetaryAmount6, '') AS Amount6
, ISNULL(slpa.Quantity6, '') AS Qt6
, ISNULL(slpr.Qualifier + ' ' + slpr.RemarkCode + ' - ' + rkcode.CodeDescription, '') AS Remark
FROM tbl_ServiceLine_Payments slp
INNER JOIN tbl_Claim_Info c
ON slp.fkClaim = c.pkClaim
INNER JOIN tbl_Entities ent
ON c.fkRenderingServiceProvider = ent.pkEntity
INNER JOIN tbl_Entities pat
ON c.fkPatient = pat.pkEntity
INNER JOIN tbl_Patient_Insurance_Plans ins
ON slp.fkInsurance = ins.pkInsurance
LEFT OUTER JOIN tbl_ServiceLine_Payments_AdjustmentCodes slpa
ON slp.pkServiceLinePayment = slpa.fkServiceLinePayment
LEFT OUTER JOIN tbl_Claim_Adjustment_Group_Codes grp
ON slpa.GroupCode = grp.ClaimAdjustmentGroupCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode1
ON slpa.ReasonCode1 = rcode1.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode2
ON slpa.ReasonCode2 = rcode2.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode3
ON slpa.ReasonCode3 = rcode3.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode4
ON slpa.ReasonCode4 = rcode4.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode5
ON slpa.ReasonCode5 = rcode5.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode6
ON slpa.ReasonCode6 = rcode6.ClaimAdjustmentReasonCode
LEFT OUTER JOIN dbo.tbl_ServiceLine_Payments_RemarkCodes slpr
ON slp.pkServiceLinePayment = slpr.fkServiceLinePayment
LEFT OUTER JOIN dbo.tbl_Claim_Advice_Remark_Codes rkcode
ON slpr.RemarkCode = rkcode.ClaimAdviceRemarkCode
WHERE (slp.EOBFileName LIKE @.EOBFileName)
ORDER BY ClaimKey, PaymentKey

here is the table that is created.

PaymentKey ClaimKey ServiceLineKey InsuranceKEy ServiceDate ServiceCode ServiceFee InsurancePayment ProviderKey ProviderName Status PatientName Policy GroupID ClaimTrace GroupCode GroupDescription ReasonCode1 ReasonDesc1 Amount1 Qt1 ReasonCode2 ReasonDesc2 Amount2 Qt2 ReasonCode3 ReasonDesc3 Amount3 Qt3 ReasonCode4 ReasonDesc4 Amount4 Qt4 ReasonCode5 ReasonDesc5 Amount5 Qt5 ReasonCode6 ReasonDesc6 Amount6 Qt6 Remark
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
1107 52 5589 416 02/12/2007 97124 22.00 17.6 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 4.40 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1108 52 5588 416 02/12/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1110 52 5586 416 02/10/2007 72100 38.00 30.4 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.60 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1111 52 5585 416 02/10/2007 72040 35.00 28 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1112 52 5584 416 02/10/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1113 52 5583 416 02/10/2007 98943 25.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 185 The rendering provider is not eligible to perform the service billed. 25.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1114 52 5582 416 02/10/2007 99211 22.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations 97 Payment is included in the allowance for another service/procedure. 22.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE M144 - Pre-/post-operative care payment is included in the allowance for the surgery/procedure.
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 9.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0

(11 row(s) affected)

Hi,

could you post it in a more readable format, please?

help with my select

I hope this is the right place.

would you folks be kind and point me in the right direction as how i can acomplish this?

here is my proc code.

here is the table that is created.

(11 row(s) affected)

Hi,

could you post it in a more readable format, please?

Wednesday, March 28, 2012

Help with SQL errors in Profiler

Help with SQL errors in Profiler

Monday, March 26, 2012

Help with SP syntax

Help with sp code - cursor

Friday, March 9, 2012

help with proc

Wednesday, March 7, 2012

help with OPENXML

Monday, February 27, 2012

help with my select

help with my select

Max Query

Blog Archive

About Me