OPENQUERY Returning only 2 Rows

I have successfully set up a linked server to an AS400. However, when I try to set up a view for it using OPENQUERY, I aonly get 2 records. The SQL code itself should return all of the records in the file. Does anyone know if this is a quirk of SQL Server and what the workaround might be?I am posting a reply to my own query, as I have found the solution. It turns out that I had the RECBLOCK setting for Client Access Express DSN set to 0. After I changed it to 0, I was able to get all of the records.|||I am posting a reply to my own query, as I have found the solution. It turns out that I had the RECBLOCK setting for Client Access Express DSN set to 0. After I changed it to 1, I was able to get all of the records.|||Sorry for the confusion with the above 2 replies.

The sentence that reads, "After I changed it to 0, I was able to get..." should say, "After I changed it to 1 , I was able to get all of the records."|||Thanks for posting the solution. :)

OPENQUERY question

I'd like to use a linked server to a Setcim database to retrieve results fro
m
a procedure in the Setcim database.
The syntax used to call the view in Setcim is:
start record 'BSW1View'
which works when using the SQLplus tools for Setcim.
I was assuming I could OPENQUERY to call this record and then put the data
into a SQL Server database but I get syntax errors when I try various
combinations of the following:
SELECT *
FROM OPENQUERY(PRISM,'start record 'BSW1View'')
The issue appears to be the single quotes around the procedure name.
Can I do what I want using OPENQUERY? If not, what options are available?
Thanks in advance,
RaulDouble them (the inner apostrophes).
SELECT *
FROM OPENQUERY(PRISM,'start record ''BSW1View''')
AMB
"Raul" wrote:

> I'd like to use a linked server to a Setcim database to retrieve results f
rom
> a procedure in the Setcim database.
> The syntax used to call the view in Setcim is:
> start record 'BSW1View'
> which works when using the SQLplus tools for Setcim.
> I was assuming I could OPENQUERY to call this record and then put the data
> into a SQL Server database but I get syntax errors when I try various
> combinations of the following:
> SELECT *
> FROM OPENQUERY(PRISM,'start record 'BSW1View'')
> The issue appears to be the single quotes around the procedure name.
> Can I do what I want using OPENQUERY? If not, what options are available?
> Thanks in advance,
> Raul
>|||Your suggestion worked. The only problem is I got the following error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'start record 'BSW1View''. The OLE DB provider
'MSDASQL' indicates that the object has no columns.
I'll modify the procedure on the Setcim side and try again.
Thanks for the help,
Raul
"Alejandro Mesa" wrote:
> Double them (the inner apostrophes).
> SELECT *
> FROM OPENQUERY(PRISM,'start record ''BSW1View''')
>
> AMB
> "Raul" wrote:
>

Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com

Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned b
y
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:

> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>sql

Open View from SQL Server Management Studio

Why the order is lost when I Select Open View'
My query really includes an ORDER clause and the Execute query was fine.Hi
From BOL:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned by
the TOP clause. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the query
itself.
You don't say if you are using SP1 or not, there are issues with ORDER BY
when in SQL 2000 compatibility mode.
John
"515331Jack3490" wrote:
> Why the order is lost when I Select Open View'
> My query really includes an ORDER clause and the Execute query was fine.
>
>

Open Transactions

Is there way to view what trasactions are open at any given time? Ideally I
would like to be able to get the SPIDS for any open transactions.Check DBCC OPENTRAN in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> Is there way to view what trasactions are open at any given time? Ideally
> I
> would like to be able to get the SPIDS for any open transactions.|||Hi Dan
With DBCC OPENTRAN you have to supply a SPID.
You can look at the open_tran column in sysprocesses and get the spid for
any rows where open_tran > 0
--
HTH
Kalen Delaney, SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> Is there way to view what trasactions are open at any given time? Ideally
> I
> would like to be able to get the SPIDS for any open transactions.|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23wwnO34rGHA.1284@.TK2MSFTNGP05.phx.gbl...
> Hi Dan
> With DBCC OPENTRAN you have to supply a SPID.
>
Umm, I know you wrote the book and all, but "are you sure?" :-)
Seriously. Both trying it and looknig at the Books Online I don't see SPID
as a possible parameter.
Database ID is available though.
> You can look at the open_tran column in sysprocesses and get the spid for
> any rows where open_tran > 0
That of course works too.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> > Is there way to view what trasactions are open at any given time?
Ideally
> > I
> > would like to be able to get the SPIDS for any open transactions.
>|||Oops. I was thinking of another command. You're right about DBCC OPENTRAN.
But, I also wanted to make the point that this command would only show you a
single transaction, and the OP wanted to see all spids that had an open
transaction.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OZppPO6rGHA.2304@.TK2MSFTNGP04.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23wwnO34rGHA.1284@.TK2MSFTNGP05.phx.gbl...
>> Hi Dan
>> With DBCC OPENTRAN you have to supply a SPID.
> Umm, I know you wrote the book and all, but "are you sure?" :-)
> Seriously. Both trying it and looknig at the Books Online I don't see
> SPID
> as a possible parameter.
> Database ID is available though.
>
>> You can look at the open_tran column in sysprocesses and get the spid for
>> any rows where open_tran > 0
> That of course works too.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Dan" <Dan@.discussions.microsoft.com> wrote in message
>> news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
>> > Is there way to view what trasactions are open at any given time?
> Ideally
>> > I
>> > would like to be able to get the SPIDS for any open transactions.
>>
>|||- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
requires sysadmin or db_owner.
- Viewing sysprocesses gives you one row per spid(if it's not a parallel
quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH TABLERESULTS'.
- Viewing sysprocesses gives you all transactions in a single instance, DBCC
OPENTRAN gives you the transactions in the current database.
A funny little one - this transaction does not show up in the DBCC OPENTRAN:
use master
go
begin tran
select * from sysprocesses
rollback
This should count in the favour for sysprocesses :-)
You can off course use the Enterprice Mananager -> Management -> Current
Activity -> Process Info...but if the system hangs then you can forget about
it :-)
By the way. If you are running SS2005 you should use the DMV
sys.dm_tran_active_transactions.
Good luck :-)
"Dan" wrote:
> Is there way to view what trasactions are open at any given time? Ideally I
> would like to be able to get the SPIDS for any open transactions.|||Hi Skaale
I believe DBCC OPENTRAN will only show processes that have done some 'work'.
And selecting from sysprocesses doesn't count as work. The open_tran value
in sysprocesses just counts how many times you have executed BEGIN TRAN with
a COMMIT or ROLLBACK.
--
HTH
Kalen Delaney, SQL Server MVP
"Skaale" <Skaale@.discussions.microsoft.com> wrote in message
news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
>- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
> requires sysadmin or db_owner.
> - Viewing sysprocesses gives you one row per spid(if it's not a parallel
> quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
> TABLERESULTS'.
> - Viewing sysprocesses gives you all transactions in a single instance,
> DBCC
> OPENTRAN gives you the transactions in the current database.
> A funny little one - this transaction does not show up in the DBCC
> OPENTRAN:
> use master
> go
> begin tran
> select * from sysprocesses
> rollback
> This should count in the favour for sysprocesses :-)
> You can off course use the Enterprice Mananager -> Management -> Current
> Activity -> Process Info...but if the system hangs then you can forget
> about
> it :-)
> By the way. If you are running SS2005 you should use the DMV
> sys.dm_tran_active_transactions.
> Good luck :-)
>
>
> "Dan" wrote:
>> Is there way to view what trasactions are open at any given time? Ideally
>> I
>> would like to be able to get the SPIDS for any open transactions.|||Hi Kalen
You are right and that is not good :-(
If someone makes a cursor for update and locks a big part of a table without
doing an update, you will not be able to see it via DBCC OPENTRAN.
Unfortunatly that is what Axapta and Navision is doing from time to time. In
the former version of Axapta the select method's update property was true by
default...a lot of developers did not think about that.
"Kalen Delaney" wrote:
> Hi Skaale
> I believe DBCC OPENTRAN will only show processes that have done some 'work'.
> And selecting from sysprocesses doesn't count as work. The open_tran value
> in sysprocesses just counts how many times you have executed BEGIN TRAN with
> a COMMIT or ROLLBACK.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Skaale" <Skaale@.discussions.microsoft.com> wrote in message
> news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
> >- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
> > requires sysadmin or db_owner.
> > - Viewing sysprocesses gives you one row per spid(if it's not a parallel
> > quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
> > TABLERESULTS'.
> > - Viewing sysprocesses gives you all transactions in a single instance,
> > DBCC
> > OPENTRAN gives you the transactions in the current database.
> >
> > A funny little one - this transaction does not show up in the DBCC
> > OPENTRAN:
> >
> > use master
> > go
> > begin tran
> > select * from sysprocesses
> > rollback
> >
> > This should count in the favour for sysprocesses :-)
> >
> > You can off course use the Enterprice Mananager -> Management -> Current
> > Activity -> Process Info...but if the system hangs then you can forget
> > about
> > it :-)
> >
> > By the way. If you are running SS2005 you should use the DMV
> > sys.dm_tran_active_transactions.
> >
> > Good luck :-)
> >
> >
> >
> >
> > "Dan" wrote:
> >
> >> Is there way to view what trasactions are open at any given time? Ideally
> >> I
> >> would like to be able to get the SPIDS for any open transactions.
>
>|||Another OOPS... I meant:
The open_tran value in sysprocesses just counts how many times you have
executed BEGIN TRAN __WITHOUT__
a COMMIT or ROLLBACK.
--
HTH
Kalen Delaney, SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%231ugyFGsGHA.3324@.TK2MSFTNGP04.phx.gbl...
> Hi Skaale
> I believe DBCC OPENTRAN will only show processes that have done some
> 'work'. And selecting from sysprocesses doesn't count as work. The
> open_tran value in sysprocesses just counts how many times you have
> executed BEGIN TRAN with a COMMIT or ROLLBACK.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Skaale" <Skaale@.discussions.microsoft.com> wrote in message
> news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
>>- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
>> requires sysadmin or db_owner.
>> - Viewing sysprocesses gives you one row per spid(if it's not a parallel
>> quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
>> TABLERESULTS'.
>> - Viewing sysprocesses gives you all transactions in a single instance,
>> DBCC
>> OPENTRAN gives you the transactions in the current database.
>> A funny little one - this transaction does not show up in the DBCC
>> OPENTRAN:
>> use master
>> go
>> begin tran
>> select * from sysprocesses
>> rollback
>> This should count in the favour for sysprocesses :-)
>> You can off course use the Enterprice Mananager -> Management -> Current
>> Activity -> Process Info...but if the system hangs then you can forget
>> about
>> it :-)
>> By the way. If you are running SS2005 you should use the DMV
>> sys.dm_tran_active_transactions.
>> Good luck :-)
>>
>>
>> "Dan" wrote:
>> Is there way to view what trasactions are open at any given time?
>> Ideally I
>> would like to be able to get the SPIDS for any open transactions.
>|||This is a multi-part message in MIME format.
--080102090304040007030808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
<chuckle>...not enough sleep last night? ;)
--
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
>Another OOPS... I meant:
> The open_tran value in sysprocesses just counts how many times you have
>executed BEGIN TRAN __WITHOUT__
> a COMMIT or ROLLBACK.
>
>
--080102090304040007030808
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><chuckle>...not enough sleep last night? ;)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Kalen Delaney wrote:
<blockquote cite="mid%23b9hiBSsGHA.3952@.TK2MSFTNGP03.phx.gbl"
type="cite">
<pre wrap="">Another OOPS... I meant:
The open_tran value in sysprocesses just counts how many times you have
executed BEGIN TRAN __WITHOUT__
a COMMIT or ROLLBACK.
</pre>
</blockquote>
</body>
</html>
--080102090304040007030808--

Open Transactions

Is there way to view what trasactions are open at any given time? Ideally I
would like to be able to get the SPIDS for any open transactions.Check DBCC OPENTRAN in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> Is there way to view what trasactions are open at any given time? Ideally
> I
> would like to be able to get the SPIDS for any open transactions.|||Hi Dan
With DBCC OPENTRAN you have to supply a SPID.
You can look at the open_tran column in sysprocesses and get the spid for
any rows where open_tran > 0
HTH
Kalen Delaney, SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> Is there way to view what trasactions are open at any given time? Ideally
> I
> would like to be able to get the SPIDS for any open transactions.|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23wwnO34rGHA.1284@.TK2MSFTNGP05.phx.gbl...
> Hi Dan
> With DBCC OPENTRAN you have to supply a SPID.
>
Umm, I know you wrote the book and all, but "are you sure?" :-)
Seriously. Both trying it and looknig at the Books Online I don't see SPID
as a possible parameter.
Database ID is available though.

> You can look at the open_tran column in sysprocesses and get the spid for
> any rows where open_tran > 0
That of course works too.

> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
Ideally[vbcol=seagreen]
>|||Oops. I was thinking of another command. You're right about DBCC OPENTRAN.
But, I also wanted to make the point that this command would only show you a
single transaction, and the OP wanted to see all spids that had an open
transaction.
HTH
Kalen Delaney, SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OZppPO6rGHA.2304@.TK2MSFTNGP04.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23wwnO34rGHA.1284@.TK2MSFTNGP05.phx.gbl...
> Umm, I know you wrote the book and all, but "are you sure?" :-)
> Seriously. Both trying it and looknig at the Books Online I don't see
> SPID
> as a possible parameter.
> Database ID is available though.
>
> That of course works too.
>
> Ideally
>|||- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
requires sysadmin or db_owner.
- Viewing sysprocesses gives you one row per spid(if it's not a parallel
quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH TABLERESULTS'
.
- Viewing sysprocesses gives you all transactions in a single instance, DBCC
OPENTRAN gives you the transactions in the current database.
A funny little one - this transaction does not show up in the DBCC OPENTRAN:
use master
go
begin tran
select * from sysprocesses
rollback
This should count in the favour for sysprocesses :-)
You can off course use the Enterprice Mananager -> Management -> Current
Activity -> Process Info...but if the system hangs then you can forget abou
t
it :-)
By the way. If you are running SS2005 you should use the DMV
sys.dm_tran_active_transactions.
Good luck :-)
"Dan" wrote:

> Is there way to view what trasactions are open at any given time? Ideally
I
> would like to be able to get the SPIDS for any open transactions.|||Hi Skaale
I believe DBCC OPENTRAN will only show processes that have done some 'work'.
And selecting from sysprocesses doesn't count as work. The open_tran value
in sysprocesses just counts how many times you have executed BEGIN TRAN with
a COMMIT or ROLLBACK.
HTH
Kalen Delaney, SQL Server MVP
"Skaale" <Skaale@.discussions.microsoft.com> wrote in message
news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...[vbcol=seagreen]
>- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
> requires sysadmin or db_owner.
> - Viewing sysprocesses gives you one row per spid(if it's not a parallel
> quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
> TABLERESULTS'.
> - Viewing sysprocesses gives you all transactions in a single instance,
> DBCC
> OPENTRAN gives you the transactions in the current database.
> A funny little one - this transaction does not show up in the DBCC
> OPENTRAN:
> use master
> go
> begin tran
> select * from sysprocesses
> rollback
> This should count in the favour for sysprocesses :-)
> You can off course use the Enterprice Mananager -> Management -> Current
> Activity -> Process Info...but if the system hangs then you can forget
> about
> it :-)
> By the way. If you are running SS2005 you should use the DMV
> sys.dm_tran_active_transactions.
> Good luck :-)
>
>
> "Dan" wrote:
>|||Hi Kalen
You are right and that is not good :-(
If someone makes a cursor for update and locks a big part of a table without
doing an update, you will not be able to see it via DBCC OPENTRAN.
Unfortunatly that is what Axapta and Navision is doing from time to time. In
the former version of Axapta the select method's update property was true by
default...a lot of developers did not think about that.
"Kalen Delaney" wrote:

> Hi Skaale
> I believe DBCC OPENTRAN will only show processes that have done some 'work
'.
> And selecting from sysprocesses doesn't count as work. The open_tran value
> in sysprocesses just counts how many times you have executed BEGIN TRAN wi
th
> a COMMIT or ROLLBACK.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Skaale" <Skaale@.discussions.microsoft.com> wrote in message
> news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
>
>|||Another OOPS... I meant:
The open_tran value in sysprocesses just counts how many times you have
executed BEGIN TRAN __WITHOUT__
a COMMIT or ROLLBACK.
HTH
Kalen Delaney, SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%231ugyFGsGHA.3324@.TK2MSFTNGP04.phx.gbl...
> Hi Skaale
> I believe DBCC OPENTRAN will only show processes that have done some
> 'work'. And selecting from sysprocesses doesn't count as work. The
> open_tran value in sysprocesses just counts how many times you have
> executed BEGIN TRAN with a COMMIT or ROLLBACK.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Skaale" <Skaale@.discussions.microsoft.com> wrote in message
> news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
>|||<chuckle>...not enough sleep last night? ;)
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:

>Another OOPS... I meant:
> The open_tran value in sysprocesses just counts how many times you have
>executed BEGIN TRAN __WITHOUT__
> a COMMIT or ROLLBACK.
>
>

Open Table Query View Only Cross Joins!URGENT

Does anyone know why when I open a table in the enterprise manager in the
query view I don't see the real table name I see table_1 and any query built
that way only produces cross joins any ideas?EM enumerates the table names so that you can refer to more than one alias
of the same table in self-joins.
EM will automatically generate an INNER JOIN in the designer if a foreign
key exists between two of the tables. If no usable foreign key exists then
the query defaults to a CROSS JOIN. You can still add or change the join
type and criteria yourself.
--
David Portas
SQL Server MVP
--|||Because Enterprise Manager tries to think for you. As David states, you can
toggle the SQL button and construct the query however you really want it.
Or, you can use Query Analyzer -- Enterprise Manager is rarely the best
choice for viewing/manipulating data.
See http://www.aspfaq.com/2455
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Chris Calhoun" <calhoun_chris@.hotmail.com> wrote in message
news:u#EzQK9qEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Does anyone know why when I open a table in the enterprise manager in the
> query view I don't see the real table name I see table_1 and any query
built
> that way only produces cross joins any ideas?
>

Open Table Query View Only Cross Joins!URGENT

Does anyone know why when I open a table in the enterprise manager in the
query view I don't see the real table name I see table_1 and any query built
that way only produces cross joins any ideas?
EM enumerates the table names so that you can refer to more than one alias
of the same table in self-joins.
EM will automatically generate an INNER JOIN in the designer if a foreign
key exists between two of the tables. If no usable foreign key exists then
the query defaults to a CROSS JOIN. You can still add or change the join
type and criteria yourself.
David Portas
SQL Server MVP
|||Because Enterprise Manager tries to think for you. As David states, you can
toggle the SQL button and construct the query however you really want it.
Or, you can use Query Analyzer -- Enterprise Manager is rarely the best
choice for viewing/manipulating data.
See http://www.aspfaq.com/2455
http://www.aspfaq.com/
(Reverse address to reply.)
"Chris Calhoun" <calhoun_chris@.hotmail.com> wrote in message
news:u#EzQK9qEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Does anyone know why when I open a table in the enterprise manager in the
> query view I don't see the real table name I see table_1 and any query
built
> that way only produces cross joins any ideas?
>

Open or Download a file in Sql Reports Resources using Report Service API

Using the reporting service web service to get the file path for a reports and the report viewer control on a Web page, it is possible to view reports. This works fine. I built a tree view that shows the reports. I also show resource files ( like .xls or .doc files ) that a user may have uploaded to the report directory. I show these filenames in the treeview as well as actual sql reports. If the user clicks on a report name, I set the path in the report viewer control and the report is rendered.

Now I would also like the user to be able to download or open any xls or doc file that may also be in the report directory from this web application.

Is this possible?

Normally in the Report Manager it uses Resources.aspx to download or open the file.

Is there any way to access these files programatically?

thanks

-Barb

Okay I found the answer, it is using the Report Web ServicegetResourceContents()

-Barb

Open a table from Diagram

In SQL 2000 when you see the database diagram, you can select

a table and open it to view the data. SQL 2005, however does not allow me

to do this. Is this feature missing or I am just able to find it.

Thanks for your help

I guess there is no such functionality in SSMS? At least does someone know if we can programmatically add this functionality?

Best regards,

Andreas Botsikas

|||

Why do you need to use visual tools to view the data?

This may have performance issues if you have huge data volume to view.

|||You are right, but it may be a time saver when you want to edit a table containing lookup values...

Open a table from Diagram

In SQL 2000 when you see the database diagram, you can select

a table and open it to view the data. SQL 2005, however does not allow me

to do this. Is this feature missing or I am just able to find it.

Thanks for your help

I guess there is no such functionality in SSMS? At least does someone know if we can programmatically add this functionality?

Best regards,

Andreas Botsikas

|||

Why do you need to use visual tools to view the data?

This may have performance issues if you have huge data volume to view.

|||You are right, but it may be a time saver when you want to edit a table containing lookup values...

only when i use a grid view!

hi, i have done some testing and its only when i put a grid view or any other type of data viewer on the page, and then connect it to the sql datasource that i get an error

Line 1: Incorrect syntax near ')'.

now i really cant figure out what it is, here is the code i am using

SQL data source code :

asp

:SqlDataSourceID="SQLDS_view_one_wish"runat="server"ConnectionString="<%$ ConnectionStrings:wishbank_DBCS %>"SelectCommand="SELECT [msg], [Date_Time] FROM [tbl_MSG] WHERE (([Activated] = @.Activated) AND ([msgID = @.msgID]) )ORDER BY [Date_Time] DESC"><SelectParameters><asp:ParameterDefaultValue="Y"Name="Activated"Type="String"/><asp:SessionParameterName="msgID"SessionField="sWV"Type="String"/></SelectParameters></asp:SqlDataSource>

session variable code which sends it to this page

protectedvoid GridView1_SelectedIndexChanged(object sender,EventArgs e)

{

GridViewRow row = GridView1.SelectedRow;

Session[

"sWV"] = row.Cells[1].Text;

Response.Redirect(

"www/viewwf.aspx");

}

if you have an idea please let me know as im stuck!

TRY THIS:

<asp:SqlDataSource ID="SQLDS_view_one_wish" runat="server"SelectCommandType="Text" ConnectionString="<%$ ConnectionStrings:wishbank_DBCS%>"SelectCommand="SELECT [msg], [Date_Time] FROM [tbl_MSG] WHERE (([Activated] = @.Activated) AND ([msgID = @.msgID]) )ORDER BY [Date_Time] DESC"><SelectParameters><asp:Parameter DefaultValue="Y" Name="Activated" Type="String" /><asp:SessionParameter Name="msgID" SessionField="sWV" Type="String" /></SelectParameters></asp:SqlDataSource>

Only put a period "." if there is a middle intial.

I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)

I need my resultant field data to look like the following:
"Doe, John P."

I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".

I have tried the following CASE statement:

CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END

However, I get an error indicating that the CASE statement is not supported in the Query Designer.

How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?Do you mean that you get an error in Query Analyzer? Or some other tool? CASE statements most assuredly are supported in Query Analyzer.

This code works fine for me:

CASE
 WHEN middleinitial IS NOT NULL THEN ' ' + middleinitial + '.'
 ELSE ''
END

What is the complete query you're trying to build?

Don|||I found my answer on a different forum. For those who are interested...

If you SET CONCAT_NULL_YIELDS_NULL ON (which I believe is the default value), you can accomplish the task in the following way:

SELECT LastName + ', ' + FirstName + ISNULL(' ' + MiddleInitial + '.', '')
FROM MyTable|||donkeily,

I am sorry, I did not see your post. I must have been posting at about the same time as you.

I was attempting to do use the CASE statement in the Query Designer. I wonder why the CASE statement is supported in the Query Analyzer, but not in the Query Designer? Seems strange to me.|||Hmm. That is bizzare. I didn't know QD didn't support it. Ick!

Don