Long MDX statement locks up report designer. Unknown bug?

The following query string locks up report designer on dev studio. Is there
a limitation or unknown bug that causes report designer to not be able to
parse this length of statement into xml? ( It's not too long for the query
designer text box, since the whole statement fits. I've had other queries
that needed to be redone do to length issues. something like 2k+ character
limit ) The query runs fine with the actual values instead of the parameters
that are being used here.
="WITH " &
"SET [TOP 20 CLIENTS ALL BILL TYPES] AS
'Filter([Client].[Client].Members,[Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """2D""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """6A""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """2A""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """HV""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VK""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VN""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """WM""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """0D""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """V5""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VP""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """R2""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """RN""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VT""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """CH""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """NX""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """T7""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """YV""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """N4""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """MX""" & " OR
INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "), " &
"""WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
"""Client Name""" & "), " & """WEST WHOLESALE""" & "))' " &
"SET [All Clients] as '[Client].[(All)].Members' " &
"MEMBER [Measures].[Pror#] as 'IIF([Client].CurrentMember IS [Top 20
SubTotal], " & """@.@.""" & ",IIF([Client].CurrentMember IS [All Other], " &
"""^^""" & ",IIF([Client].CurrentMember IS [Total], " & """**""" & ",
[Client].CurrentMember.Properties(" & """Client""" & "))))' " &
"SET [Weeks] as '{LastPeriods(49,[Margin Time].[Calendar].[Calendar Day].["
& Parameters!pWeekEndingDate.Label & "])}' " &
"SET [Months] as '{LastPeriods(6, [Margin Time].[Calendar].[Calendar
Month].[" & code.MonthYearPeriod(Parameters!pWeekEndingDate.Label, 0) & "])}'
" &
"SET [Expenses] as '{[Margin].&[Commission Exp].&[STD],[Margin].&[Direct
Mail Exp], [Margin].&[Internet Exp], [Margin].&[Memberlink Exp],
[Margin].&[Telemarketing Cost] }' " &
"MEMBER [Measures].[Period Ending DisplayName] as 'IIF([Margin
Time].[Calendar].CurrentMember.Level.Name = " & """Calendar Day""" & ", " &
"""Week """ & " + CSTR(VBA!DatePart(" & """ww""" & ", [Margin
Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
Time].[Calendar].CurrentMember.Name)), " & """Month """ & " +
CSTR(VBA!DatePart(" & """m""" & ", [Margin
Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
Time].[Calendar].CurrentMember.Name)))' " &
"MEMBER [Measures].[Client DisplayName] as 'IIF([Client].CurrentMember IS
[Top 20 SubTotal], " & """Top 20 SubTotal""" & ", IIF([Client].CurrentMember
IS [All Other], " & """All Other""" & ", IIF([Client].CurrentMember IS
[Total], " & """Total""" & ", IIF([Client].CurrentMember.Properties(" &
"""Client Group""" & ") <> " & """WEST TELESERVICES""" & ",
[Client].CurrentMember.Properties(" & """Client Group""" & "), " & """WEST
RETAIL""" & "))))' " &
"MEMBER [Measures].[Column Order] as 'IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(5)), 6, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(4)), 5, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(3)), 4, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(2)), 3, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(1)), 2, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(0)), 1, 9))))))' " &
"MEMBER [Client].[Top 20 SubTotal] as '[Top 20 SubTotal]' " &
"MEMBER [Client].[All Other] as '[All Other]' " &
"MEMBER [Client].[Total] as '[Total]' " &
"MEMBER [Measures].[Rank] as ' IIF([Client].CurrentMember IS [Top 20
SubTotal], 21, IIF([Client].CurrentMember IS [All Other], 22,
IIF([Client].CurrentMember IS [Total],
23,IIF(INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "), "
& """WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
"""Client Name""" & "), " & """WEST WHOLESALE""" & ") ,
1,IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """2D"""
& " , 2, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""6A""" & " , 3, IIF([Client].CurrentMember.Properties(" & """Client""" & ")
= " & """2A""" & " , 4, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """HV""" & " , 5,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VK""" &
" , 6, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""VN""" & " , 7, IIF([Client].CurrentMember.Properties(" & """Client""" & ")
= " & """WM""" & " , 8, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """0D""" & " , 9,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """V5""" &
" , 10, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""VP""" & " , 11, IIF([Client].CurrentMember.Properties(" & """Client""" &
") = " & """R2""" & " , 12, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """RN""" & " , 13,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VT""" &
" , 14, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""CH""" & " , 15, IIF([Client].CurrentMember.Properties(" & """Client""" &
") = " & """NX""" & " , 16, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """T7""" & " , 17,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """YV""" &
" , 18, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""N4""" & " , 19, IIF([Client].CurrentMember.Properties(" & """Client""" &
") = " & """MX""" & " , 20, 99)))))))))))))))))))))))' " &
"MEMBER [Measures].[Actual] AS 'IIF([Client].CurrentMember IS [Top 20
SubTotal], SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
[Measures].[Dollars]) / 1000, IIF([Client].CurrentMember IS [All Other],
((SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars])) -
(SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
[Measures].[Dollars]))) / 1000, IIF([Client].CurrentMember IS [Total],
SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars]) / 1000,
SUM({ [Expenses] }, [Measures].[Dollars]) / 1000)))' " &
"SELECT {[Measures].[Rank], [Measures].[Column Order], [Measures].[Client
DisplayName], [Measures].[Pror#], [Measures].[Period Ending DisplayName],
[Measures].[Actual], [Measures].[Dollars] } ON COLUMNS, " &
"NON EMPTY {CROSSJOIN( {[TOP 20 CLIENTS ALL BILL TYPES], [Client].[Top 20
SubTotal] , [Client].[All Other], [Client].[Total] }, {[Weeks], [Months] } )
} ON ROWS " &
"FROM [Margins] WHERE ([Bill Type].[Bill Group].&[New] )"Created named set on the cube to shorten the length. No further help
needed.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:C3338E73-870C-4425-B40E-99C8DCCC1D5E@.microsoft.com...
> The following query string locks up report designer on dev studio. Is
there
> a limitation or unknown bug that causes report designer to not be able to
> parse this length of statement into xml? ( It's not too long for the query
> designer text box, since the whole statement fits. I've had other queries
> that needed to be redone do to length issues. something like 2k+
character
> limit ) The query runs fine with the actual values instead of the
parameters
> that are being used here.
> ="WITH " &
> "SET [TOP 20 CLIENTS ALL BILL TYPES] AS
> 'Filter([Client].[Client].Members,[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """2D""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """6A""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """2A""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """HV""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VK""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VN""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """WM""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """0D""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """V5""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VP""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """R2""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """RN""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VT""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """CH""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """NX""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """T7""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """YV""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """N4""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """MX""" & " OR
> INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "), " &
> """WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
> """Client Name""" & "), " & """WEST WHOLESALE""" & "))' " &
> "SET [All Clients] as '[Client].[(All)].Members' " &
> "MEMBER [Measures].[Pror#] as 'IIF([Client].CurrentMember IS [Top 20
> SubTotal], " & """@.@.""" & ",IIF([Client].CurrentMember IS [All Other], " &
> """^^""" & ",IIF([Client].CurrentMember IS [Total], " & """**""" & ",
> [Client].CurrentMember.Properties(" & """Client""" & "))))' " &
> "SET [Weeks] as '{LastPeriods(49,[Margin Time].[Calendar].[Calendar
Day].["
> & Parameters!pWeekEndingDate.Label & "])}' " &
> "SET [Months] as '{LastPeriods(6, [Margin Time].[Calendar].[Calendar
> Month].[" & code.MonthYearPeriod(Parameters!pWeekEndingDate.Label, 0) &
"])}'
> " &
> "SET [Expenses] as '{[Margin].&[Commission Exp].&[STD],[Margin].&[Direct
> Mail Exp], [Margin].&[Internet Exp], [Margin].&[Memberlink Exp],
> [Margin].&[Telemarketing Cost] }' " &
> "MEMBER [Measures].[Period Ending DisplayName] as 'IIF([Margin
> Time].[Calendar].CurrentMember.Level.Name = " & """Calendar Day""" & ", "
&
> """Week """ & " + CSTR(VBA!DatePart(" & """ww""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
> CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)), " & """Month """ & " +
> CSTR(VBA!DatePart(" & """m""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
> CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)))' " &
> "MEMBER [Measures].[Client DisplayName] as 'IIF([Client].CurrentMember IS
> [Top 20 SubTotal], " & """Top 20 SubTotal""" & ",
IIF([Client].CurrentMember
> IS [All Other], " & """All Other""" & ", IIF([Client].CurrentMember IS
> [Total], " & """Total""" & ", IIF([Client].CurrentMember.Properties(" &
> """Client Group""" & ") <> " & """WEST TELESERVICES""" & ",
> [Client].CurrentMember.Properties(" & """Client Group""" & "), " & """WEST
> RETAIL""" & "))))' " &
> "MEMBER [Measures].[Column Order] as 'IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(5)), 6, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(4)), 5, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(3)), 4, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(2)), 3, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(1)), 2, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(0)), 1, 9))))))' " &
> "MEMBER [Client].[Top 20 SubTotal] as '[Top 20 SubTotal]' " &
> "MEMBER [Client].[All Other] as '[All Other]' " &
> "MEMBER [Client].[Total] as '[Total]' " &
> "MEMBER [Measures].[Rank] as ' IIF([Client].CurrentMember IS [Top 20
> SubTotal], 21, IIF([Client].CurrentMember IS [All Other], 22,
> IIF([Client].CurrentMember IS [Total],
> 23,IIF(INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "),
"
> & """WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
> """Client Name""" & "), " & """WEST WHOLESALE""" & ") ,
> 1,IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""2D"""
> & " , 2, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """6A""" & " , 3, IIF([Client].CurrentMember.Properties(" & """Client""" &
")
> = " & """2A""" & " , 4, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """HV""" & " , 5,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VK"""
&
> " , 6, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """VN""" & " , 7, IIF([Client].CurrentMember.Properties(" & """Client""" &
")
> = " & """WM""" & " , 8, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """0D""" & " , 9,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """V5"""
&
> " , 10, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """VP""" & " , 11, IIF([Client].CurrentMember.Properties(" & """Client"""
&
> ") = " & """R2""" & " , 12, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """RN""" & " , 13,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VT"""
&
> " , 14, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """CH""" & " , 15, IIF([Client].CurrentMember.Properties(" & """Client"""
&
> ") = " & """NX""" & " , 16, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """T7""" & " , 17,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """YV"""
&
> " , 18, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """N4""" & " , 19, IIF([Client].CurrentMember.Properties(" & """Client"""
&
> ") = " & """MX""" & " , 20, 99)))))))))))))))))))))))' " &
> "MEMBER [Measures].[Actual] AS 'IIF([Client].CurrentMember IS [Top 20
> SubTotal], SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
> [Measures].[Dollars]) / 1000, IIF([Client].CurrentMember IS [All Other],
> ((SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars])) -
> (SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
> [Measures].[Dollars]))) / 1000, IIF([Client].CurrentMember IS [Total],
> SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars]) /
1000,
> SUM({ [Expenses] }, [Measures].[Dollars]) / 1000)))' " &
> "SELECT {[Measures].[Rank], [Measures].[Column Order], [Measures].[Client
> DisplayName], [Measures].[Pror#], [Measures].[Period Ending DisplayName],
> [Measures].[Actual], [Measures].[Dollars] } ON COLUMNS, " &
> "NON EMPTY {CROSSJOIN( {[TOP 20 CLIENTS ALL BILL TYPES], [Client].[Top 20
> SubTotal] , [Client].[All Other], [Client].[Total] }, {[Weeks],
[Months] } )
> } ON ROWS " &
> "FROM [Margins] WHERE ([Bill Type].[Bill Group].&[New] )"
>

long html string

Hi All
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:

> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org

login works with SQL but not MSDE

Any idea why the following dmo connection string would not work on MSDE, but
does work on SQL Personal Edition? It appears to connects, but then can't
find the server name. In both cases we are using the latest SQL Personal,
MSDE, and Windows XP.
Thanks for any guidance.
Neil
connection string:
server=myserver;trusted_connection="yes";database= northwind;connection
timeout=10;
exception:
[INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
timeout=10;
[EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login, Object
Password)
at SQLServer.Connect()
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||This took me a little bit further. I can now actually see the tables in in
my program using the dmo calls. Thanks! owever, when I try to access the
individual columns, I continue to get errors such as:
EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving info
from test2: Server user 'mymachine\mylogin' is not a valid user in database
'test2'.
Again, this all works fine using SQL Personal Edition.
Neil
"Jim Young" <thorium48@.hotmail.com> wrote in message
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||Carefully read the error message. You need to make the user you are
connecting with, in this case a Windows login, a user in the database you
are trying to use. Everyone that can connect to a server can see the schema
of any database, but you can't select or change any data until you are given
explicit permissions in a database.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:OShgsvmPEHA.2468@.TK2MSFTNGP11.phx.gbl...
> This took me a little bit further. I can now actually see the tables in
in
> my program using the dmo calls. Thanks! owever, when I try to access the
> individual columns, I continue to get errors such as:
> EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving
info
> from test2: Server user 'mymachine\mylogin' is not a valid user in
database[vbcol=seagreen]
> 'test2'.
> Again, this all works fine using SQL Personal Edition.
> Neil
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> Was MSDE installed with the network protocols enabled? With SP3a they are
> turned off by default.
> Jim
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> but
can't[vbcol=seagreen]
Personal,
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection[vbcol=seagreen]
or
> Object
>
>

Login problem publishing to my machine from VS2005

So I'm trying my first Report project from Visual Studio 2005 and I
get a good connection string and run the project. When I run it it
tries to deploy to http://localhost/reportserver, and asks for a login
and password. But nothing works. I don't even think I have a password
set up for reporting services on this machine.
Once it fails and I run it again, it runs fine in local mode on my
machine. Does anyone know what is going on here?So this never happens to anyone else? Very weird. Maybe it's time to
just format the damn thing and install everything again.