Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query from packet capture

Author  Topic 

justinh20
Starting Member

4 Posts

Posted - 2014-04-22 : 11:21:07
I'm trying to organize this SQL query from a packet capture and I'm more of a network/application guy, not so much of a DBA. Can anyone at least tell me the end of the SQL query and I can organize the rest? To me it looks like they are using variables in their query and the "@" is a delimiter. Forgive me if this doesn't turn out pretty.


S E L E C T [ t 0 ] . [ S E C T I O N N A M E ] , [ t 0 ] . [ P A R A M E T E R N A M E ] , [ t
0 ] . [ I N T V A L U E ] , [ t 0 ] . [ S T R I N G V A L U E ] , [ t 0 ] . [ D A T E V A L U E ] , [ t 0 ] . [ I N F R A S T R U C T U R A L ]
, [ t 0 ] . [ S i t e P a r a m e t e r s I d ] F R O M [ d b o ] . [ S I T E _ P A R A M E T E R S ] A S [ t 0 ] W H E R E ( [ t 0
] . [ S E C T I O N N A M E ] = @ p 0 ) A N D ( [ t 0 ] . [ P A R A M E T E R N A M E ] = @ p 1 ) @ 4@ @ p 0 n v a r c h a r ( 3
) , @ p 1 n v a r c h a r ( 1 0 ) @ p 0 4 W e b @ p 1 4 M a x Q u e r i e s
[ < d 4 S E C T I O N N A M E d 4 P A R A M E T E R N A M E & I N T V A L U E 4 S T R I N G V A
L U E o D A T E V A L U E 2 I N F R A S T R U C T U R A L S i t e P a r a m e t e r s I d W e b M a x Q u e r i e s
0 y
@ 4 E X E C @ R E T U R N _ V A L U E = [ d b o ] . [ p _ S a l e s O r d e r s _ S e a r c h ] @
M a x R e s u l t s = @ p 0 , @ U s e r I d = @ p 1 , @ I s R o o t = @ p 2 , @ L i s t C u s t o m e r = @ p 3 , @ P r o j e
c t N u m b e r = @ p 4 , @ L a s t M o d i f i e d B y N a m e = @ p 5 , @ C o n t r a c t M a n a g e r M a i l I D = @ p 6 , @ P
r o j e c t M a n a g e r M a i l I D = @ p 7 , @ K A M M a i l I D = @ p 8 , @ R e s p o n s i b l e S a l e s A d m i n M a i l I D =
@ p 9 , @ O r d e r M a n a g e r M a i l I D = @ p 1 0 , @ S h i p m e n t R e s p M a i l I D = @ p 1 1 , @ C a r a t C o d e =
@ p 1 2 , @ I s A r c h i v e d = @ p 1 3 , @ L i s t S o u r c e A p p l i = @ p 1 4 , @ L i s t P r o c e s s S t a t u s = @ p 1
5 , @ L i s t E d i t i o n S t a t u s = @ p 1 6 , @ L i s t B u s i n e s s U n i t = @ p 1 7 , @ L i s t L e g a l U n i t = @ p
1 8 , @ L i s t C u s t o m e r C o u n t r y = @ p 1 9 , @ A l l i a n c e C r e a t i o n F r o m = @ p 2 0 , @ A l l i a n c e C r e
a t i o n T o = @ p 2 1 , @ E R P S e n t F r o m = @ p 2 2 , @ E R P S e n t T o = @ p 2 3 , @ C u s t o m e r P O R e c e i p t D
a t e F r o m = @ p 2 4 , @ C u s t o m e r P O R e c e i p t D a t e T o = @ p 2 5 , @ L a s t M o d i f i c a t i o n D a t e F r o m
= @ p 2 6 , @ L a s t M o d i f i c a t i o n D a t e T o = @ p 2 7 , @ I s I n v o i c e d = @ p 2 8 , @ I s C a n c e l l e d =
@ p 2 9 , @ C u s t o m e r P O I D = @ p 3 0 , @ A l l i a n c e O r d e r I d = @ p 3 1 , @ E r p S a l e s O r d e r I d = @ p 3
2 , @ C u s t o m e r C a t a l o g N u m b e r = @ p 3 3 , @ C a t a l o g N u m b e r = @ p 3 4 , @ E R P R e f e r e n c e N u m b e
r = @ p 3 5 , @ L i s t C a t e g o r y = @ p 3 6 , @ O p p o r t u n i t y R e f e r e n c e = @ p 3 7 , @ C u s t o m e r P r o g
r a m C o d e = @ p 3 8 , @ A g g r e g a t i o n R e f e r e n c e I d = @ p 3 9 , @ B a c k O f f i c e O A M a i l I d = @ p 4 0 ,
@ A n t i c i p a t i o n O r d e r I d = @ p 4 1 , @ C r e a t i o n D a t e O f L a s t E d i t i o n F r o m = @ p 4 2 , @ C r e a t
i o n D a t e O f L a s t E d i t i o n T o = @ p 4 3 , @ O f f e r R e f e r e n c e = @ p 4 4 , @ A l l i a n c e F A C D R I d = @
p 4 5 , @ L i s t B i l l i n g P l a n C o n d i t i o n s S t a t u s e s = @ p 4 6 , @ O r d e r D e s c r i p t i o n = @ p 4 7 , @
N o t V i s i b l e F o r C D R = @ p 4 8 , @ L i s t A t t a c h m e n t T y p e s = @ p 4 9 4 @ p 0 i n t , @ p 1 i n t , @
p 2 b i t , @ p 3 n v a r c h a r ( 4 0 0 0 ) , @ p 4 n v a r c h a r ( 4 0 0 0 ) , @ p 5 n v a r c h a r ( 4 0 0 0 ) , @ p 6 n v a r c h a
r ( 4 0 0 0 ) , @ p 7 n v a r c h a r ( 4 0 0 0 ) , @ p 8 n v a r c h a r ( 4 0 0 0 ) , @ p 9 n v a r c h a r ( 4 0 0 0 ) , @ p 1 0 n v a r c
h a r ( 4 0 0 0 ) , @ p 1 1 n v a r c h a r ( 4 0 0 0 ) , @ p 1 2 n v a r c h a r ( 4 0 0 0 ) , @ p 1 3 b i t , @ p 1 4 n v a r c h a r ( 7 )
, @ p 1 5 n v a r c h a r ( 5 ) , @ p 1 6 n v a r c h a r ( 9 5 ) , @ p 1 7 n v a r c h a r ( 6 6 6 ) , @ p 1 8 n v a r c h a r ( 7 6 3 ) , @
p 1 9 n v a r c h a r ( 8 7 2 ) , @ p 2 0 d a t e t i m e , @ p 2 1 d a t e t i m e , @ p 2 2 d a t e t i m e , @ p 2 3 d a t e t i m e , @
p 2 4 d a t e t i m e , @ p 2 5 d a t e t i m e , @ p 2 6 d a t e t i m e , @ p 2 7 d a t e t i m e , @ p 2 8 b i t , @ p 2 9 b i t , @ p
3 0 n v a r c h a r ( 4 0 0 0 ) , @ p 3 1 n v a r c h a r ( 4 0 0 0 ) , @ p 3 2 n v a r c h a r ( 4 0 0 0 ) , @ p 3 3 n v a r c h a r ( 4 0 0
0 ) , @ p 3 4 n v a r c h a r ( 4 0 0 0 ) , @ p 3 5 n v a r c h a r ( 4 0 0 0 ) , @ p 3 6 n v a r c h a r ( 1 7 ) , @ p 3 7 n v a r c h a r (
4 0 0 0 ) , @ p 3 8 n v a r c h a r ( 4 0 0 0 ) , @ p 3 9 n v a r c h a r ( 4 0 0 0 ) , @ p 4 0 n v a r c h a r ( 4 0 0 0 ) , @ p 4 1 n v a r
c h a r ( 4 0 0 0 ) , @ p 4 2 d a t e t i m e , @ p 4 3 d a t e t i m e , @ p 4 4 n v a r c h a r ( 4 0 0 0 ) , @ p 4 5 n v a r c h a r ( 4 0
0 0 ) , @ p 4 6 n v a r c h a r ( 1 5 ) , @ p 4 7 n v a r c h a r ( 4 0 0 0 ) , @ p 4 8 b i t , @ p 4 9 n v a r c h a r ( 2 4 ) , @ R E T U R
N _ V A L U E i n t o u t p u t @ p 0 & @ p 1 & f) @ p 2 h @ p 3 4 @ p 4 4 @ p 5 4 @ p 6 4
@ p 7 4 @ p 8 4 @ p 9 4 @ p 1 0 4 @ p 1 1 4 @ p 1 2 4 @ p 1 3 h @ p 1 4 4 1
, 2 , 3 , 4 @ p 1 5 4 1 , 2 , 3 @ p 1 6 4 1 , 4 , 1 4 , 1 5 , 3 6 , 5 , 3 5 , 1 0 , 2 0 , 2 5 , 2 6 , 2 7 , 2 , 8 , 1 2 , 1 8 , 2
8 , 6 , 1 1 , 1 6 , 2 2 , 1 3 , 3 , 7 , 9 , 1 7 , 1 9 , 2 9 , 2 1 , 3 7 , 3 1 , 3 3 , 3 2 , 3 4 , 2 3 @ p 1 7 4 44 1 , 2 6 2 , 2 6 0 , 2 6 1 ,
2 3 6 , 2 5 8 , 2 5 9 , 2 3 7 , 2 3 8 , 2 3 9 , 2 4 0 , 2 4 1 , 2 4 2 , 2 4 3 , 2 4 4 , 2 4 5 , 2 4 6 , 2 4 7 , 2 4 8 , 2 4 9 , 2 5 0 , 2 5 1 , 2 5 2
, 2 5 3 , 2 5 4 , 2 6 3 , 2 6 4 , 2 6 5 , 2 6 6 , 1 5 1 , 5 , 1 9 2 , 6 , 7 , 1 6 0 , 1 6 7 , 1 8 5 , 1 7 6 , 1 5 4 , 1 4 5 , 1 5 2 , 8 , 9 , 2 2 4 ,
1 0 , 1 1 , 2 0 3 , 2 3 5 , 1 2 , 1 4 , 2 2 5 , 1 6 , 1 8 , 2 0 , 2 2 , 2 4 , 1 4 1 , 1 9 3 , 2 6 , 2 8 , 1 4 6 , 1 4 7 , 2 0 0 , 1 9 4 , 2 9 , 3 0 ,
3 1 , 3 2 , 1 6 1 , 1 5 7 , 1 5 8 , 1 6 2 , 3 3 , 3 4 , 3 5 , 2 0 4 , 3 6 , 3 7 , 1 9 5 , 3 9 , 4 0 , 4 1 , 1 0 3 , 1 6 3 , 2 2 6 , 1 7 8 , 1 9 6 , 2
0 5 , 2 0 6 , 2 0 7 , 1 5 5 , 1 6 9 , 1 9 7 , 2 0 8 , 2 0 9 , 2 1 0 , 1 7 7 , 2 3 4 , 1 9 8 , 1 8 2 , 1 7 4 , 2 1 1 , 1 5 9 , 2 5 5 , 2 5 6 , 1 7 3 ,
1 8 0 , 2 5 7 , 2 1 2 , 2 1 3 , 2 1 4 , 2 1 5 , 1 8 7 , 2 1 6 , 2 1 7 , 2 1 8 , 2 2 2 , 2 1 9 , 2 2 0 , 1 9 9 , 4 2 , 1 4 8 , 1 4 9 , 4 3 , 4 4 , 4 5
, 1 0 9 , 1 7 2 , 1 7 1 , 1 8 3 , 2 2 7 , 2 2 8 , 1 8 1 , 4 6 , 2 2 9 , 1 1 2 , 4 8 , 1 5 0 , 2 3 0 , 2 3 1 , 2 3 2 , 4 9 , 5 0 , 1 7 9 , 1 1 8 , 1 1
9 , 1 2 0 , 1 2 1 , 1 2 3 , 1 5 6 , 5 1 , 5 3 , 1 7 5 , 5 5 , 5 7 , 5 9 , 6 1 , 1 6 4 , 1 6 8 , 2 2 1 , 6 3 , 6 4 , 6 5 , 6 6 , 1 8 4 , 1 4 2 , 2 3 3
, 1 7 0 , 1 8 6 , 1 9 1 , 1 8 8 , 1 8 9 , 1 9 0 , 1 4 3 , 1 4 4 , 1 6 5 , 1 6 6 , 2 0 1 , 2 2 3 , 2 0 2 @ p 1 8 4 4 3 2 , 3 4 9 , 1 5 0 , 4
3 1 , 9 6 , 1 5 2 , 9 7 , 1 5 4 , 1 5 5 , 7 , 1 5 9 , 1 6 2 , 4 1 0 , 1 6 3 , 3 7 2 , 3 6 0 , 1 6 4 , 9 9 , 1 6 6 , 3 9 7 , 4 , 1 7 0 , 1 7 2 , 1 0 1
, 1 7 4 , 1 7 6 , 1 7 7 , 1 7 8 , 1 8 0 , 1 8 1 , 3 5 5 , 3 4 8 , 3 7 3 , 3 5 1 , 3 5 2 , 3 4 7 , 3 4 2 , 1 8 2 , 1 8 3 , 3 7 4 , 1 8 4 , 1 8 5 , 1 8
7 , 1 8 9 , 1 0 2 , 1 9 2 , 1 0 3 , 1 9 4 , 1 0 4 , 1 9 7 , 1 5 , 1 0 5 , 2 0 0 , 4 2 8 , 2 0 2 , 2 0 3 , 2 0 6 , 2 0 7 , 3 , 1 0 7 , 2 1 4 , 2 1 5 ,
3 5 7 , 2 1 6 , 4 0 6 , 2 1 7 , 1 0 8 , 2 , 2 2 1 , 4 2 9 , 2 2 2 , 2 2 4 , 2 2 5 , 3 9 4 , 3 6 9 , 2 2 6 , 5 , 2 2 9 , 2 3 0 , 2 3 3 , 2 3 6 , 2 3 7
, 2 4 0 , 2 4 4 , 2 4 6 , 2 4 7 , 2 4 8 , 1 1 1 , 2 5 0 , 4 3 0 , 2 5 2 , 2 5 3 , 3 6 3 , 2 5 4 , 4 1 5 , 2 5 5 , 3 7 5 , 3 6 4 , 1 1 2 , 2 5 8 , 2 5
9 , 2 6 1 , 2 6 2 , 2 6 4 , 3 9 5 , 1 1 3 , 2 6 7 , 3 5 8 , 4 2 7 , 1 1 4 , 2 6 9 , 3 6 5 , 3 4 1 , 2 7 5 , 3 6 8 , 3 3 9 , 2 7 7 , 2 7 8 , 2 7 9 , 2
8 0 , 2 8 1 , 2 8 2 , 3 7 0 , 1 1 5 , 4 0 0 , 2 8 4 , 2 8 5 , 2 8 6 , 3 6 6 , 2 9 0 , 1 4 , 2 9 2 , 1 1 7 , 2 9 5 , 3 6 2 , 2 9 7 , 2 9 8 , 2 9 9 , 3
7 1 , 1 2 , 3 5 6 , 3 0 1 , 1 1 9 , 3 0 5 , 3 0 6 , 3 0 7 , 1 2 0 , 4 2 5 , 3 0 9 , 1 2 1 , 4 0 2 , 3 7 7 , 3 6 1 , 3 1 1 , 3 1 2 , 3 1 4 , 3 1 5 , 1
2 2 , 4 2 6 , 3 6 7 , 3 1 7 , 3 1 9 , 4 3 4 , 3 2 1 , 3 7 9 , 3 2 2 , 4 2 4 , 3 2 3 , 4 2 2 , 3 2 4 , 3 2 5 , 3 2 6 , 4 0 4 , 3 8 0 , 3 8 1 , 3 8 2 ,
3 8 3 , 3 8 4 , 3 8 5 , 3 8 6 , 3 4 3 , 3 8 8 , 3 4 4 , 3 8 9 , 3 9 0 , 3 9 1 , 3 4 6 , 3 2 9 , 3 3 0 , 3 3 2 , 3 4 5 , 3 3 4 , 3 3 5 , 3 4 0 , 3 3 7
@ p 1 9 4 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 F , 9 , 1 0 , 1 1 , 1 2 , 1 3 , 1 4 , 1 5 , 1 6 , 1 7 , 1 8 , 1 9 , 2 0 , 2 1 , 2 2 , 2 3 , 2
4 , 2 5 , 2 6 , 2 7 , 2 8 , 2 9 , 3 0 , 3 1 , 3 2 , 3 3 , 3 4 , 3 5 , 3 6 , 3 7 , 3 8 , 3 9 , 4 0 , 4 1 , 4 2 , 4 3 , 4 4 , 4 5 , 4 6 , 4 7 , 4 8 , 5
0 , 5 1 , 5 2 , 5 3 , 5 4 , 5 5 , 5 6 , 5 7 , 5 8 , 5 9 , 6 0 , 6 1 , 6 2 , 6 3 , 6 4 , 6 5 , 6 6 , 6 7 , 6 8 , 6 9 , 7 0 , 7 1 , 7 2 , 7 3 , 7 4 , 7
5 , 7 6 , 7 7 , 7 8 , 7 9 , 8 0 , 8 1 , 8 2 , 8 3 , 8 4 , 8 5 , 8 6 , 8 7 , 8 8 , 8 9 , 9 0 , 9 1 , 9 2 , 9 3 , 9 4 , 9 5 , 9 6 , 9 7 , 9 8 , 9 9 , 1
0 0 , 1 0 1 , 1 0 2 , 1 0 3 , 1 0 4 , 1 0 5 , 1 0 6 , 1 0 7 , 1 0 8 , 1 0 9 , 1 1 0 , 1 1 1 , 1 1 2 , 1 1 3 , 1 1 4 , 1 1 5 , 1 1 6 , 1 1 7 , 1 1 8 ,
1 1 9 , 1 2 0 , 1 2 1 , 1 2 2 , 1 2 3 , 1 2 4 , 1 2 5 , 1 2 6 , 1 2 7 , 1 2 8 , 1 2 9 , 1 3 0 , 1 3 1 , 1 3 2 , 1 3 3 , 1 3 4 , 1 3 5 , 1 3 6 , 1 3 7
, 2 4 4 , 1 3 8 , 1 3 9 , 1 4 0 , 1 4 1 , 1 4 2 , 1 4 3 , 1 4 4 , 1 4 5 , 1 4 6 , 1 4 7 , 1 4 8 , 1 4 9 , 1 5 0 , 1 5 1 , 1 5 2 , 1 5 3 , 1 5 4 , 1 5
5 , 1 5 6 , 1 5 7 , 1 5 8 , 1 5 9 , 1 6 0 , 1 6 1 , 1 6 2 , 1 6 3 , 1 6 4 , 1 6 5 , 1 6 6 , 1 6 7 , 1 6 8 , 1 6 9 , 1 7 0 , 1 7 1 , 1 7 2 , 1 7 3 , 1
7 4 , 1 7 5 , 1 7 6 , 1 7 7 , 1 7 8 , 1 7 9 , 1 8 0 , 1 8 1 , 1 8 2 , 1 8 3 , 1 8 4 , 1 8 5 , 2 4 3 , 1 8 6 , 1 8 7 , 1 8 8 , 1 8 9 , 1 9 0 , 1 9 1 ,
1 9 2 , 1 9 3 , 1 9 4 , 1 9 5 , 1 9 6 , 1 9 7 , 1 9 8 , 1 9 9 , 2 0 0 , 2 0 1 , 2 0 2 , 2 0 3 , 2 0 4 , 2 0 5 , 2 0 6 , 2 0 7 , 2 0 8 , 2 0 9 , 2 1 0
, 2 1 1 , 2 1 2 , 2 1 3 , 2 1 4 , 2 1 5 , 2 1 6 , 2 1 7 , 2 4 5 , 2 1 8 , 2 1 9 , 2 2 0 , 2 2 1 , 2 2 2 , 2 2 3 , 2 2 4 , 2 2 5 , 2 2 6 , 2 2 7 , 2 2
8 , 2 2 9 , 2 3 0 , 2 3 1 , 2 3 2 , 2 3 3 , 2 3 4 , 2 3 5 , 2 3 6 , 2 3 7 , 2 3 8 , 2 3 9 , 2 4 0 , 2 4 1 , 2 4 2 , 2 4 8 @ p 2 0 o @ p 2 1 o @
p 2 2 o @ p 2 3 o @ p 2 4 o @ p 2 5 o @ p 2 6 o @ p 2 7 o @ p 2 8 h @ p 2 9 h @ p 3 0 4 @ p 3 1 4 @ p 3 2
4 @ p 3 3 4 @ p 3 4 4 @ p 3 5 4 @ p 3 6 " 4" 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 @ p 3 7 4 @ p
3 8 4 @ p 3 9 4 @ p 4 0 4 @ p 4 1 4 @ p 4 2 o @ p 4 3 o @ p 4 4 4 @ p 4 5 4 @ p 4 6
4 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 @ p 4 7 4 @ p 4 8 h @ p 4 9 0 40 0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 1 8 , 2 1 , 2 6 @ R E
T U R N _ V A L U E &

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-22 : 14:14:24
who are you trying to hack, justin?
Just ask whoever submitted the sql request to provide the statements to you.

btw, the "@"s are prefixes for parameters.


Be One with the Optimizer
TG
Go to Top of Page

justinh20
Starting Member

4 Posts

Posted - 2014-04-23 : 07:30:00
Thanks, I promise I'm not hacking anyone. The client is in a situation where they no longer have DBAs available need the SQL statements. We've been waiting for 5 weeks to get access to the SQL server itself and we're running out of time.

So you're saying the SQL query is this:
SELECT [t0].[SECTIONNAME], [t0].[PARAMETERNAME], [t0].[INTVALUE], [t0].[STRINGVALUE], [t0].[DATEVALUE], [t0].[INFRASTRUCTURAL], [t0].[SiteParametersId] FROM [dbo].[SITE_PARAMETERS] AS [t0] WHERE ([t0.[SECTIONNAME] = @p0) AND ([t0].[PARAMETERNAME] = @p1)

After that, is it defining the parameters like this?
@UserId = @p1, @IsRoot = @p2

I've seen this query return about 66 columns matching those same terms (MaxResults, LastModifiedByName, etc)
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 11:52:38
Yeah, that is the first statement. But after that is not clear as there seems to be some stuff missing. It is not valid sql. but from what's there it does seem to declare new parameters and pass them to a stored procedure call: p_SalesOrders_Search. Then more parameter declarations then I don't know what all those comma separated numbers are towards the end.

Be One with the Optimizer
TG
Go to Top of Page

justinh20
Starting Member

4 Posts

Posted - 2014-04-23 : 12:51:56
You can tell that because of this line?
EXEC @RETURN_VALUE = [dbo].[p_SalesOrders_Search]

This query should also only return 1000 rows max. Is that somewhere in here or is that within the stored procedure? I'm assuming a stored procedure will not be visible via packet capture and it's actually stored on the DB somewhere?

Thanks for your help so far!

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-23 : 12:59:48
you're right that the code of the stored procedure will not be in the packet capture. the 1000 row max could be built into the procedure or it could be by virtue of those "from-to" parameters. Those indicate some sort of range to honor.

Be One with the Optimizer
TG
Go to Top of Page

justinh20
Starting Member

4 Posts

Posted - 2014-04-24 : 07:54:10
OK, Thanks TG!
Go to Top of Page
   

- Advertisement -