sql dwh problem: text

Showing posts with label text. Show all posts

Thursday, March 29, 2012

Dynamic Report Title and keeping it centered

I have a text box in a header for my report title, its reads something like this:

=RTRIM( Parameters!state.Value) + " Summary of Total Trust Assets - Sorted by Industry"

Although I have the textbox centered in the page, since the state.value is dynamic the lenght of the state name could be different than throwing off how centered the title appears? I am pulling my hair out trying to figure out a solution and would greatly appreciate any help or knowledge people might pass on.

Thanks

Duane

Center the textbox horizontally on the page, and also center the text inside the textbox (center justified). It should work.

Monday, March 19, 2012

Dynamic Link server

This is a multi-part message in MIME format.
--=_NextPart_000_00B6_01C877D9.2CFBD7D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am receiving link server name as a parameter to my stored procedure. = The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery = (@.ServerName,'select column1 from table2 where column2=3D1')) as b on = table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any = alternative method please suggest me
Thanks
--
--=_NextPart_000_00B6_01C877D9.2CFBD7D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type = content=3Dtext/html;charset=3Diso-8859-1>
<META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR></HEAD>
<BODY id=3DMailContainerBody style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px" bgColor=3D#ffffff leftMargin=3D0 topMargin=3D0 CanvasTabStop=3D"true" name=3D"Compose message area">
<DIV>I am receiving link server name = as a parameter to my stored procedure. The code looks like</DIV>
<DIV> </DIV>
<DIV>
create procedure xxxxxx = @.ServerName as char(30) as
begin
if = exists ( Select item from table1 join (select * from openquery (@.ServerName,'select = column1 from table2 where column2=3D1')) as b = on table1.item =3D b.column1 where qty = > 0 )
begin
--
print = 'IF Body'
--
end
end
 
Above code I giving error.
Msg 170, Level 15, State 1, Procedure = xxxxxx, Line 3
Line 3: Incorrect syntax near <A title=3D"mailto:'@.ServerName' CTRL + Click to follow link" href=3D"mailto:'@.ServerName'">'@.ServerName'</A>.
 
can't we give dynamic = server name in the OpenQuery? Is there any alternative method please suggest me</DIV>
<DIV>
<DIV> Thanks</DIV>
<DIV>-- </DIV> </DIV></BODY><= /HTML>
--=_NextPart_000_00B6_01C877D9.2CFBD7D0--This is a multi-part message in MIME format.
--=_NextPart_000_0040_01C877BC.C87A3BB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. =The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery =(@.ServerName,'select column1 from table2 where column2=3D1')) as b on =table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any =alternative method please suggest me
Thanks
--
--=_NextPart_000_0040_01C877BC.C87A3BB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')
IF EXISTS (SELECT * FROM table1 JOIN =#tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" =wrote in message news:AAC=B61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server =name as a parameter to my stored procedure. The code looks like

create procedure xxxxxx =@.ServerName as char(30) as
begin
if =exists ( Select item from table1 join (select * =from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')) as b =on table1.item =3D b.column1 where qty > 0 =)
begin
--
print ='IF Body'
--
end
end

Above code I giving error.
Msg 170, Level 15, State 1, =Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.

can't we give dynamic =server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
--

--=_NextPart_000_0040_01C877BC.C87A3BB0--|||This is a multi-part message in MIME format.
--=_NextPart_000_00FD_01C877DC.823C75D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The problem here is, it is not accepting linked server name in the =variable.
DECLARE @.Server as varchar(30)
SET @.Server =3D 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored =procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery =(@.ServerName,'select column1 from table2 where column2=3D1')) as b on =table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any =alternative method please suggest me
Thanks
--
--=_NextPart_000_00FD_01C877DC.823C75D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

The problem here is, it is not =accepting linked server name in the variable.
DECLARE @.Server as =varchar(30)
SET @.Server =3D 'MyServer'
select * from =openquery ( @.Server , 'select * from table1')
is not working.
select * from =openquery ( MyServer , 'Select * from table1')
Is working fine.
--Rajesh =A+91-9886124372http://www.s7software.com/">S7 =Software Solutions"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" wrote in =message news:%23BWvov6dIHA.4=84@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')

IF EXISTS (SELECT * FROM table1 =JOIN #tbl ON ........ WHERE......)
BEGIN
END

"RajeshA" =wrote in message news:AAC=B61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server =name as a parameter to my stored procedure. The code looks like

create procedure xxxxxx =@.ServerName as char(30) as
begin
if =exists ( Select item from table1 join (select * =from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')) as b =on table1.item =3D b.column1 where qty > 0 )
begin
--
print ='IF Body'
--
end
end

Above code I giving error.
Msg 170, Level 15, State 1, =Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.

can't we give dynamic =server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
--

--=_NextPart_000_00FD_01C877DC.823C75D0--|||This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C877C0.C62A8280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try this one
DECLARE @.sql nvarchar(4000),@.sql_statement nvarchar(1000)
set @.sql_statement=3D'select * from table1'
SET @.sql =3D N' SET NOCOUNT ON'
+ ' SELECT *'
+ ' INTO #tbl'
+ ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE( =@.sql_statement,
'''', ''' ) + ''' )'
+ ' SET NOCOUNT OFF'
select @.sql
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:2A7E555E-7727-41C6-AA86-31434B900847@.microsoft.com...
The problem here is, it is not accepting linked server name in the =variable.
DECLARE @.Server as varchar(30)
SET @.Server =3D 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
--
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ =WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored =procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery =(@.ServerName,'select column1 from table2 where column2=3D1')) as b on =table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any =alternative method please suggest me
Thanks
--
--=_NextPart_000_000A_01C877C0.C62A8280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try this one
DECLARE @.sql =nvarchar(4000),@.sql_statement nvarchar(1000)set @.sql_statement=3D'select * from =table1'
SET @.sql =3D =N' SET NOCOUNT ON' =+ ' SELECT =*' + ' INTO #tbl' =+ ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE( @.sql_statement,'''', ''' ) + ''' )' =+ ' SET NOCOUNT OFF' select @.sql
"RajeshA" =wrote in message news:2A7=E555E-7727-41C6-AA86-31434B900847@.microsoft.com...
The problem here is, it is not =accepting linked server name in the variable.

DECLARE @.Server =as varchar(30)
SET @.Server =3D = 'MyServer'
select * from =openquery ( @.Server , 'select * from table1')

is not working.

select * from =openquery ( MyServer , 'Select * from table1')

Is working fine.

--Rajesh A+91-9886124372http://www.s7software.com/">S7 =Software Solutions"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" wrote in =message news:%23BWvov6dIHA.4=84@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from =table2 where column2=3D1')

IF EXISTS (SELECT * FROM table1 =JOIN #tbl ON ........ WHERE......)
BEGIN
END

"RajeshA" =wrote in message news:AAC=B61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server =name as a parameter to my stored procedure. The code looks like

create procedure xxxxxx =@.ServerName as char(30) as
begin
if =exists ( Select item from table1 join (select * =from openquery (@.ServerName,'select column1 =from table2 where column2=3D1')) as b =on =table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end

Above code I giving = error.
Msg 170, Level 15, State 1, =Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.

can't we give =dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
--

--=_NextPart_000_000A_01C877C0.C62A8280--|||This is a multi-part message in MIME format.
--=_NextPart_000_014C_01C877E2.79E9AA50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
select @.sql will return value in the @.sql variable. --
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:%23tYHm$6dIHA.4144@.TK2MSFTNGP05.phx.gbl...
Try this one
DECLARE @.sql nvarchar(4000),@.sql_statement nvarchar(1000)
set @.sql_statement=3D'select * from table1'
SET @.sql =3D N' SET NOCOUNT ON'
+ ' SELECT *'
+ ' INTO #tbl'
+ ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE( =@.sql_statement,
'''', ''' ) + ''' )'
+ ' SET NOCOUNT OFF'
select @.sql
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:2A7E555E-7727-41C6-AA86-31434B900847@.microsoft.com...
The problem here is, it is not accepting linked server name in the =variable.
DECLARE @.Server as varchar(30)
SET @.Server =3D 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
--
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 =from table2 where column2=3D1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ =WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored =procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from =openquery (@.ServerName,'select column1 from table2 where column2=3D1')) =as b on table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any =alternative method please suggest me
Thanks
--
--=_NextPart_000_014C_01C877E2.79E9AA50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

select @.sql will return value in the =@.sql variable. --Rajesh =A+91-9886124372http://www.s7software.com/">S7 =Software Solutions"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" wrote in =message news:%23tYHm$6dIHA.=4144@.TK2MSFTNGP05.phx.gbl...
Try this one
DECLARE @.sql =nvarchar(4000),@.sql_statement nvarchar(1000)set @.sql_statement=3D'select * from =table1'

SET @.sql =3D =N' SET NOCOUNT =ON' + ' SELECT *' =+ ' INTO =#tbl' + ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE( @.sql_statement,'''', ''' ) + ''' )' =+ ' SET NOCOUNT =OFF' select @.sql

"RajeshA" =wrote in message news:2A7=E555E-7727-41C6-AA86-31434B900847@.microsoft.com...
The problem here is, it is not =accepting linked server name in the variable.

DECLARE @.Server as varchar(30)
SET @.Server ==3D 'MyServer'
select * from =openquery ( @.Server , 'select * from table1')

is not working.

select * from =openquery ( MyServer , 'Select * from table1')

Is working fine.

--Rajesh A+91-9886124372http://www.s7software.com/">S7 =Software Solutions"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" wrote in =message news:%23BWvov6dIHA.4=84@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 =from table2 where column2=3D1')

IF EXISTS (SELECT * FROM =table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END

"RajeshA" wrote in message news:AAC=B61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link =server name as a parameter to my stored procedure. The code looks =like

create procedure xxxxxx =@.ServerName as char(30) as
begin
if exists =( Select item from table1 join (select * =from openquery (@.ServerName,'select column1 =from table2 where column2=3D1')) as b =on =table1.item ==3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end

Above code I =giving error.
Msg 170, Level 15, State 1, =Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near ='@.ServerName'.

can't we give =dynamic server name in the OpenQuery? Is there any alternative method please =suggest me
Thanks
--

--=_NextPart_000_014C_01C877E2.79E9AA50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0014_01C877C5.D925B990
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Run
EXEC sp_executesql @.sql
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:eb7xvR7dIHA.3940@.TK2MSFTNGP05.phx.gbl...
select @.sql will return value in the @.sql variable.
--
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:%23tYHm$6dIHA.4144@.TK2MSFTNGP05.phx.gbl...
Try this one
DECLARE @.sql nvarchar(4000),@.sql_statement nvarchar(1000)
set @.sql_statement=3D'select * from table1'
SET @.sql =3D N' SET NOCOUNT ON'
+ ' SELECT *'
+ ' INTO #tbl'
+ ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE( =@.sql_statement,
'''', ''' ) + ''' )'
+ ' SET NOCOUNT OFF'
select @.sql
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:2A7E555E-7727-41C6-AA86-31434B900847@.microsoft.com...
The problem here is, it is not accepting linked server name in the =variable.
DECLARE @.Server as varchar(30)
SET @.Server =3D 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
--
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 =from table2 where column2=3D1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ =WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message =news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored =procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from =openquery (@.ServerName,'select column1 from table2 where column2=3D1')) =as b on table1.item =3D b.column1 where qty > 0 )
begin
--
print 'IF Body'
--
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there =any alternative method please suggest me
Thanks
--
--=_NextPart_000_0014_01C877C5.D925B990
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Run
EXEC sp_executesql @.sql
"RajeshA" =wrote in message news:eb7xvR7dIHA.3940=@.TK2MSFTNGP05.phx.gbl...
select @.sql will return value in the =@.sql variable.
--Rajesh A+91-9886124372http://www.s7software.com/">S7 =Software Solutions"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" wrote in =message news:%23tYHm$6dIHA.=4144@.TK2MSFTNGP05.phx.gbl...
Try this one
DECLARE @.sql nvarchar(4000),@.sql_statement nvarchar(1000)set =@.sql_statement=3D'select * from table1'

SET @.sql ==3D N' SET NOCOUNT =ON' + ' SELECT *' =+ ' INTO =#tbl' + ' FROM OPENQUERY( ' + HOST_NAME() + ',''' + REPLACE( @.sql_statement,'''', ''' ) + ''' )' =+ ' SET NOCOUNT =OFF' select @.sql

"RajeshA" =wrote in message news:2A7=E555E-7727-41C6-AA86-31434B900847@.microsoft.com...
The problem here is, it is not =accepting linked server name in the variable.

DECLARE @.Server as varchar(30)
SET @.Server ==3D 'MyServer'
select * =from openquery ( @.Server , 'select * from table1')

is not working.

select * =from openquery ( MyServer , 'Select * from table1')

Is working fine.

--Rajesh A+91-9886124372http://www.s7software.com/">S7 =Software Solutions"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN = POSSIBLE."
"Uri Dimant" wrote =in message news:%23BWvov6dIHA.4=84@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 =from table2 where column2=3D1')

IF EXISTS (SELECT * FROM =table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END

"RajeshA" wrote in message news:AAC=B61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link =server name as a parameter to my stored procedure. The code looks =like

create procedure =xxxxxx @.ServerName as char(30) as
begin
if exists ( =Select item =from table1 =join =(select * from openquery (@.ServerName,'select =column1 from table2 where column2=3D1')) as b on = table1.item =3D b.column1 =where qty => 0 =)
begin
--
print 'IF =Body'
--
end
end

Above code I =giving error.
Msg 170, Level 15, State 1, =Procedure xxxxxx, Line 3
Line 3: Incorrect syntax =near '@.ServerName'.

can't we give =dynamic server name in the OpenQuery? Is there any alternative method please =suggest me
Thanks
--

--=_NextPart_000_0014_01C877C5.D925B990--

Wednesday, March 7, 2012

Dynamic Decimal Format

I have a text field = 3303.123456.
I want to display decimals based on a dynanic parameter.
For example if 4 decimals = 3303.1234. If 2 = 3303.12.
The dynamic parameter will be a field in the data set.You can dynamically generate a format code string on a textbox. Assuming you
have an integer parameter called Decimals, you would set the Format property
of the textbox to an expression like this: ="N" &
Parameters!Decimals.Value.ToString()
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott2624" <Scott2624@.discussions.microsoft.com> wrote in message
news:55FC071E-E0A0-4AF4-8015-5F2F2DA7D082@.microsoft.com...
>I have a text field = 3303.123456.
> I want to display decimals based on a dynanic parameter.
> For example if 4 decimals = 3303.1234. If 2 = 3303.12.
> The dynamic parameter will be a field in the data set.

Sunday, February 19, 2012

Dynamic columnName

Howdy all,
I'm trying to create a sproc that has the column name and search text as input parameters with dynamic SQL... what I have is:
-- Create a variable @.SQLStatement
DECLARE @.SQLStatement varchar(255)
SELECT @.SQLStatement = 'SELECT * FROM myTable WHERE (''' + @.ColumnName + ''' = ''' + @.SearchText + ''')'
-- Execute the SQL statement
EXEC(@.SQLStatement)
The procedure works (at least in Query Analyzer, once I declare/set @.columnname and @.searchtext) in that I don't get any errors. But it returns a blank table with "0 rows affected". However, if I hardcode the variables, it returns the proper data. What's wrong with my select statement?? Confused [*-)]
Thanks!!
JP

DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'SELECT * FROM myTable WHERE (' + @.ColumnName + ' = ''' + @.SearchText + ''')'
-- Execute the SQL statement
EXEC(@.SQLStatement)|||That's it! Thanks, ndinakar. Those darn quotes...

dynamic column usage in query?

Is there anyway without using a EXEC and a dynamic query (text string
concatanated) to say used this column and compare it... like I want to do
this
'WHERE ' + @.columnToSearch + ' like ''' + @.query + '%'')'
just not as a concatanaged string... the @.columnToSearch is a user picked
column in the application from a listing of possible choices like address,
city, street, etc then they do a text search on it to find similar ones that
start with what they typed into the applications text box. but right now i
can only get it to work using a string build like above and executed by the
EXEC command. I want to get rid of all dynamic SQL in my application though,
anyone have any suggestions? thanks!Brian
This is very good that you want to get rid of dynamic sql
http://www.sommarskog.se/dyn-search.html
"Brian Henry" <nospam@.nospam.com> wrote in message
news:ekc4OqxpFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Is there anyway without using a EXEC and a dynamic query (text string
> concatanated) to say used this column and compare it... like I want to do
> this
> 'WHERE ' + @.columnToSearch + ' like ''' + @.query + '%'')'
> just not as a concatanaged string... the @.columnToSearch is a user picked
> column in the application from a listing of possible choices like address,
> city, street, etc then they do a text search on it to find similar ones
> that start with what they typed into the applications text box. but right
> now i can only get it to work using a string build like above and executed
> by the EXEC command. I want to get rid of all dynamic SQL in my
> application though, anyone have any suggestions? thanks!
>|||See more here
http://www.sommarskog.se/dynamic_sql.html|||Start here
http://www.sommarskog.se/dynamic_sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Brian Henry" <nospam@.nospam.com> wrote in message
news:ekc4OqxpFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Is there anyway without using a EXEC and a dynamic query (text string
> concatanated) to say used this column and compare it... like I want to do
> this
> 'WHERE ' + @.columnToSearch + ' like ''' + @.query + '%'')'
> just not as a concatanaged string... the @.columnToSearch is a user picked
> column in the application from a listing of possible choices like address,
> city, street, etc then they do a text search on it to find similar ones
> that start with what they typed into the applications text box. but right
> now i can only get it to work using a string build like above and executed
> by the EXEC command. I want to get rid of all dynamic SQL in my
> application though, anyone have any suggestions? thanks!
>|||http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
"Brian Henry" <nospam@.nospam.com> wrote in message
news:ekc4OqxpFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Is there anyway without using a EXEC and a dynamic query (text string
> concatanated) to say used this column and compare it... like I want to do
> this
> 'WHERE ' + @.columnToSearch + ' like ''' + @.query + '%'')'
> just not as a concatanaged string... the @.columnToSearch is a user picked
> column in the application from a listing of possible choices like address,
> city, street, etc then they do a text search on it to find similar ones
> that start with what they typed into the applications text box. but right
> now i can only get it to work using a string build like above and executed
> by the EXEC command. I want to get rid of all dynamic SQL in my
> application though, anyone have any suggestions? thanks!
>

Friday, February 17, 2012

Dynamic Column Delimiter in Text files

Hi,

is there any way to change the delimiter in a FlatFile Source Adapter dynamically? You can do that with the row delimiter but there is no expression for the column delimiter... Is there any other way besides changing the XML code?

Thanks,

That has to be a no, since the column level information is not exposed. Time to roll your own I think if you really need this. It should not be that hard, I have done something similar recently. Provided your buffer structure does not need to change, then it is possible.

dynamic archiving

I need to set up a stored procedure or DTS that can export data to a text file - based on parametres passed when it's called. We have a scheduling server, where all our logging is stored and procedures are called. The data I need to export can be on diffierent servers in different databases.

I started with the following:

DECLARE
-- @.TableName varchar(128),
-- @.Filename varchar(200),
-- @.date_field varchar(20),
@.SQL varchar(2000)
-- SET @.TableName =

SET @.SQL = ''''+ 'bcp "SELECT * FROM '+@.TableName+ ' WHERE ' + @.Date_Field+
' > GETDATE()"'+ ' queryout "c:\test.txt" -c -q -U"sa" -P"" '+ ''''
print @.SQL

-- set @.SQL = 'EXEC master.dbo.xp_cmdshell ' + @.SQL
-- EXEC (@.SQL)

However, I'm not sure how to work in the dynamic server names. A variation of the above works fine locally on my machine - but I'm thinking I may need to use a DTS to get the servers in there...if so, does bcp even work through DTS? WHat about the variables? Does anyone have a better way of doing this?

for server name, you specify -S. Does this not work?

|||

Well, der. Thanks, I forgot about that.

I have come up with another problem, however:

I'm running BCP through a stored procedure so that the exports can be dynamic - however, I can't get the proc to fail if the bcp command fails. It returns an out put indicating failure, but won't actually fail the proc - I can't seem to use raiserror.

Is there a way to take the output from queryout and do something with it?

|||

I think I figured this out.

Still testing, however, it looks like adding "NO_OUTPUT" forces SQL server to interpret the results as either failure or success - if you don't specify this, it seems to consider the execution of xp_cmdshell rather than the result of the execution. (i.e., I would get the output results returned indicating the specifics of the error, but the value of @.@.ERROR within the proc would not change)

I could put the output results into a file, but that would complicate the process as I would then need to evaluate the output before proceeding (which would be a pain with a text file).

If anyone knows how to get the output into a table on execution, especially a temp table, that would be nice to know...

|||This might be fixed in SQL 2005, where if command inside xp_cmdshell fails, then the batch fails, but not sure, you should test it out. I'm assuming you're on SQL 2000.|||

We don't have 2005. Still on 2000.

This is terribly frustrating. :(

Any further suggestions would be greatly appreciated, as this still seems to be wonky.

|||

did you try checking the return value from xp_cmdshell?

i.e.

declare @.ret int

exec @.ret = xp_cmdshell 'bcp xxxxx'

if @.ret <> 0 raiserror ('Error!', 16,1)

|||

This worked great! it forces an error when run from a DTS.

In addition, I forgot that most system stuff can be saved in a temp table - i.e.,

create table ##output

(output varchar(255))

Insert ##output

(output)

exec xp_cmdshell @.SQL

So this was great! thanks so much for your help.

Wednesday, February 15, 2012

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
Rob
A couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
--
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
RobA couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Thursday, March 29, 2012

Dynamic Report Title and keeping it centered

Monday, March 19, 2012

Dynamic Link server

Wednesday, March 7, 2012

Dynamic Decimal Format

Sunday, February 19, 2012

Dynamic columnName

dynamic column usage in query?

Friday, February 17, 2012

Dynamic Column Delimiter in Text files

dynamic archiving

Wednesday, February 15, 2012

dynamic (xml text fields) for update sproc

dynamic (xml text fields) for update sproc

sql dwh problem

Blog Archive

About Me