sql dwh problem: link

Showing posts with label link. Show all posts

Thursday, March 29, 2012

Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

|||Thanks for the feedback.

Monday, March 19, 2012

Dynamic Link server

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=1')) as b on table1.item = 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
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
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=1')) as b on table1.item = 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
|||The problem here is, it is not accepting linked server name in the variable.
DECLARE @.Server as varchar(30)
SET @.Server = '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=1')
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=1')) as b on table1.item = 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

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, 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 Security in SSAS 2005

Monday, March 19, 2012

Dynamic Link server

Dynamic Link server

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