Thursday, March 29, 2012
dynamic reporting
I want to create report on the fly, for given mdx query , I mean without
having a pre build report format, and I am using FoodMart 2000 sample
database.
pls give me some ideas.....
Thanks in Advance...If I understand what you mean correctly (and using RS) . . . . . I guess
you'd need to create the RDL file dynamically and have it executed
(I think) 8^)
- peteZ
"Sumudu Prasad" <sumudu@.logicalasia.com> wrote in message
news:uo%23XGEhmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Hi Experts...
> I want to create report on the fly, for given mdx query , I mean without
> having a pre build report format, and I am using FoodMart 2000 sample
> database.
> pls give me some ideas.....
> Thanks in Advance...
>
>
Dynamic Report Name at run time?
Is it possible to give a name at run time to a report when we try to download it in any format.
Do we have any control over the report name for e.g. the report name can be passed at parameter value.
Thanks,
Novin
Provide an example of the first five report names.
|||Hi,I have created on single dynamic report name as "Generic Report" which will serve the need of 5 diff reports,
Where we go one by one report from the drill down from report no1 to 2 to 3 and....5 and all these 5 reports are generated using the single dynamic general report.
Problem is when ever at any report level i try to download the report its name remain the same as "Generic Report".
but I want the name to be parameteries based on the report level.
Hope you understood my probelm now.
Thanks
Novin
Dynamic Report Name at run time?
Is it possible to give a name at run time to a report when we try to download it in any format.
Do we have any control over the report name for e.g. the report name can be passed at parameter value.
Thanks,
Novin
Provide an example of the first five report names.
|||Hi,I have created on single dynamic report name as "Generic Report" which will serve the need of 5 diff reports,
Where we go one by one report from the drill down from report no1 to 2 to 3 and....5 and all these 5 reports are generated using the single dynamic general report.
Problem is when ever at any report level i try to download the report its name remain the same as "Generic Report".
but I want the name to be parameteries based on the report level.
Hope you understood my probelm now.
Thanks
Novin
Thursday, March 22, 2012
dynamic partitions
I currently have a windows 2003 server connected to a SAN.
Local disk (drive C) on the server is a basic partition.
drives D and E are on the EMC storage but partitioned as "dynamic".
Before I get too far, will I have a problem with SQL server loaded on the
local C drive (basic partition) storing files out on the D and E drive
(dynamic partitions)?
Thanks--Mark ThorsenIf you are using a failover cluster, you would have a problem. See this KB
article http://support.microsoft.com/?id=237853. It states:
"Windows 2000 Advanced Server, Windows 2000 Datacenter Server, Windows
Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition
support only basic shared disks in an environment that uses the Windows
Server failover clustering feature."
Linchi
"Thorsen" wrote:
> Can you format a partition as "dynamic" and use SQL server on it?
> I currently have a windows 2003 server connected to a SAN.
> Local disk (drive C) on the server is a basic partition.
> drives D and E are on the EMC storage but partitioned as "dynamic".
> Before I get too far, will I have a problem with SQL server loaded on the
> local C drive (basic partition) storing files out on the D and E drive
> (dynamic partitions)?
> Thanks--Mark Thorsensql
dynamic partitions
I currently have a windows 2003 server connected to a SAN.
Local disk (drive C) on the server is a basic partition.
drives D and E are on the EMC storage but partitioned as "dynamic".
Before I get too far, will I have a problem with SQL server loaded on the
local C drive (basic partition) storing files out on the D and E drive
(dynamic partitions)?
Thanks--Mark ThorsenIf you are using a failover cluster, you would have a problem. See this KB
article http://support.microsoft.com/?id=237853. It states:
"Windows 2000 Advanced Server, Windows 2000 Datacenter Server, Windows
Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition
support only basic shared disks in an environment that uses the Windows
Server failover clustering feature."
Linchi
"Thorsen" wrote:
> Can you format a partition as "dynamic" and use SQL server on it?
> I currently have a windows 2003 server connected to a SAN.
> Local disk (drive C) on the server is a basic partition.
> drives D and E are on the EMC storage but partitioned as "dynamic".
> Before I get too far, will I have a problem with SQL server loaded on the
> local C drive (basic partition) storing files out on the D and E drive
> (dynamic partitions)?
> Thanks--Mark Thorsen
Dynamic package configurations
I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.
What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
Take a look at DTEXEC and the /SET option. Using that, you can call your pacakge and use SET to override the values of @.originalFilePath and @.destinationFilePath. No configuration file required. It does mean passing the values each time, but given your situation it sounds like that will be the best solution.|||Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003?
I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)
|||
papalarge wrote:
Interesting... I'm looking into it a bit, thanks for that John. How about options available through vb.net 2003? I started tinkering around with it, but couldn't get the Microsoft.SqlServer.ManagedDTS.dll to be added to the references. (side question... does it require 2005 to reference this assembly?)
Strictly speaking, .Net 2.0 is required.|||any ideas why I get the error "A reference to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll' could not be added. This is not a valid assembly or COM component."?
I have Visual Studio 2003, and .NET 1.1 and 2.0 framework installed on my local machine.
|||Does any of this information help?
http://blogs.msdn.com/michen/default.aspx|||it does help, yeah. the part that says:
Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs. This method also can't be used from .NET 1.1 application, unless it is moved to .NET 2.0 (which should be very easy to do, and in my experience improves the performance as well).
So I guess I need VS 2005 in order to use the object model for SSIS?
|||
papalarge wrote:
it does help, yeah. the part that says: Drawbacks: Obviously this is local execution - you need to install SSIS on same machine where your app runs.
which means you need a license on each machine running the server. were you aware of that?
Monday, March 19, 2012
Dynamic Link server
--=_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><FONT face=3DArial color=3D#003366>I am receiving link server name = as a parameter to my stored procedure. The code looks like</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT color=3D#0000ff size=3D2><FONT color=3D#0000ff size=3D2><FONT = color=3D#0000ff size=3D2>
<P>create</FONT><FONT color=3D#000000 size=3D2> </FONT><FONT = color=3D#0000ff size=3D2>procedure</FONT><FONT color=3D#000000 size=3D2> xxxxxx = @.ServerName </FONT><FONT color=3D#0000ff size=3D2>as</FONT><FONT color=3D#000000 = size=3D2> </FONT><FONT color=3D#0000ff size=3D2>char</FONT><FONT color=3D#808080 size=3D2>(</FONT><FONT color=3D#000000 size=3D2>30</FONT><FONT = color=3D#808080 size=3D2>)</FONT><FONT color=3D#000000 size=3D2> </FONT><FONT = color=3D#0000ff size=3D2>as</FONT></P>
<P><FONT color=3D#0000ff size=3D2>begin</P></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2>if</FONT><FONT size=3D2> = </FONT><FONT color=3D#808080 size=3D2>exists</FONT><FONT size=3D2> </FONT><FONT = color=3D#808080 size=3D2>(</FONT><FONT size=3D2> </FONT><FONT color=3D#0000ff size=3D2>Select</FONT><FONT size=3D2> item </FONT><FONT color=3D#0000ff size=3D2>from</FONT><FONT size=3D2> table1 </FONT><FONT color=3D#808080 size=3D2>join</FONT><FONT size=3D2> </FONT><FONT color=3D#808080 = size=3D2>(</FONT><FONT color=3D#0000ff size=3D2>select</FONT><FONT size=3D2> </FONT><FONT = color=3D#808080 size=3D2>*</FONT><FONT size=3D2> </FONT><FONT color=3D#0000ff = size=3D2>from</FONT><FONT size=3D2> </FONT><FONT color=3D#0000ff size=3D2>openquery</FONT><FONT = size=3D2> </FONT><FONT color=3D#808080 size=3D2>(</FONT><FONT = size=3D2>@.ServerName</FONT><FONT color=3D#808080 size=3D2>,</FONT><FONT color=3D#ff0000 size=3D2>'select = column1 from table2 where column2=3D1'</FONT><FONT color=3D#808080 = size=3D2>))</FONT><FONT size=3D2> </FONT><FONT color=3D#0000ff size=3D2>as</FONT><FONT size=3D2> b = </FONT><FONT color=3D#0000ff size=3D2>on</FONT><FONT size=3D2> table1</FONT><FONT = color=3D#808080 size=3D2>.</FONT><FONT size=3D2>item </FONT><FONT color=3D#808080 = size=3D2>=3D</FONT><FONT size=3D2> b</FONT><FONT color=3D#808080 size=3D2>.</FONT><FONT = size=3D2>column1 </FONT><FONT color=3D#0000ff size=3D2>where</FONT><FONT size=3D2> qty = </FONT><FONT color=3D#808080 size=3D2>></FONT><FONT size=3D2> 0 </FONT><FONT = color=3D#808080 size=3D2>)</P></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2>begin</P></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#008000 = size=3D2>--</P></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2>print</FONT><FONT size=3D2> = </FONT><FONT color=3D#ff0000 size=3D2>'IF Body'</P></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#008000 = size=3D2>--</P></FONT><FONT size=3D2>
<P></FONT><FONT color=3D#0000ff size=3D2>end</P>
<P>end</P></FONT></FONT><FONT color=3D#0000ff size=3D2>
<P><FONT face=3DArial color=3D#000000></FONT> </P>
<P><FONT face=3DArial color=3D#003366 size=3D3>Above code I giving error.</FONT></P><FONT size=3D1>
<P><FONT color=3D#ff0000 size=3D2>Msg 170, Level 15, State 1, Procedure = xxxxxx, Line 3</FONT></P>
<P><FONT color=3D#ff0000 size=3D2>Line 3: Incorrect syntax near <A title=3D"mailto:'@.ServerName' CTRL + Click to follow link" href=3D"mailto:'@.ServerName'">'@.ServerName'</A>.</FONT></P>
<P><FONT color=3D#ff0000 size=3D2></FONT> </P>
<P><FONT face=3DArial color=3D#003366 size=3D3>can't we give dynamic = server name in the OpenQuery? Is there any alternative method please suggest me</FONT></P></FONT></FONT></FONT><FONT size=3D2></FONT></DIV>
<DIV><SPAN style=3D"FONT-SIZE: 12px; COLOR: #1f497d">
<DIV><BR><FONT face=3DArial color=3D#003366 size=3D2>Thanks</FONT></DIV>
<DIV><STRONG><FONT face=3DArial color=3D#003366>--</FONT><BR></STRONG></SPAN><BR></DIV><BR></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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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
dynamic decimal parameter for example:
declare idecimal int
select @.idecimal=3
convert(decimal(20,@.idecimal),number) does not work - error message.
How can I accomplish this?Format the value client side. Why would you want to do this in the database?
David Portas
SQL Server MVP
--|||I have a stored that procedure generates data for a report. The data is
stored in the database as decimal (20,7). Are you saying there is no easy wa
y
to do this using T-SQL?
"David Portas" wrote:
> Format the value client side. Why would you want to do this in the databas
e?
> --
> David Portas
> SQL Server MVP
> --
>
>|||Boy, you were one of the kids asleep in the back of my 20+ years of
database classes!! Where do we do display in a tiered architecture
(this is faaaar more fundamental question than SQL)? The front end !!
Never,never in the database!!
SQL is a static data type language. THIS IS A FUNDAMENTAL PROGRAMMING
CONCEPT!! Why did you think you could change data types in a schema?
You so ignorant or stupid that you are dangerous to people.|||Ultimately the display format is controlled by the client application, not
by SQL Server. A query always has fixed metadata and that includes the
precision and scale of each column. I think your options therefore are to
use dynamic SQL or to cast the value as a string and return it that way. You
can use the STR function to output a string with a varying numberof
decimals:
SELECT STR(x,20,@.d)
FROM T1
However, this might cause you some problems if your reporting application
needs to do arithmetic on the results.
David Portas
SQL Server MVP
--
Dynamic Decimal Format
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.