Use exactly syntax as I posted - 'SET ROWCOUNT ', when number is zero - you switch off the records limitation feature.
>Right. I am attempting to create a temporary table with the same structure as the table supplied by using SELECT INTO with a where clause of 1=0. That seems to work as in the Query analyzer it reports (0) records affected. > >However, when I attempt to discover the primary key field which is a uniqueidentifer type, and insert a new record into it, I keep getting this error from SQL Server telling me that the temporary table does not exist. > >Hmmm.... But maybe with the ROWCOUNT variable you just mentioned... Is that ROWCOUNT or @@ROWCOUNT? > >Thanks. > >>Hi! >> >>Its because a way how MS XML cpntrols work. I met the same problem in VFP when using wwXML class from West-Wind. Data structure schema not generated when no records. >> >>If you use MS SQL Server, in your case you can make a query to the table that returns only one row by specifying following query string: >> >>SET ROWCOUNT 1 >>select * from tblMyTable >>SET ROWCOUNT 0 >> >>It will have the best performance and will return only one row. This approach, however, will work only for non-empty data tables. >>Another approach is uses left join. Make some host oir even temporary table with only one record. than query like >> >>SET ROWCOUNT 1 >>select MyTable.* from HostTable left outer join MyTable on HostTable.Field = MyTable.Field >>SET ROWCOUNT 0 >> >>This query will guarantee 1 record even when MyTable contains no records - all will be just NULL. I very suspicious, however, about performance of such command. >> >>Have no other ideas... >> >>>Hello all! >>> >>>I am having trouble with accomplishing a specific task. I have poured over the help file and through some books, and by all accounts, what I'm attempting in T-SQL should work. But perhaps there is something else I am missing, so let me explain a little regarding my scenario: >>> >>>I need to generate an XML structure (no records) for any given table. The problem is, if I query SQL Server through a URL that has a zero record result set, all I get back is the root node. I need this structure so that I can create a new record for any table. >>> >>>I know that I could query the system tables and perform a stylesheet translation to convert the result set of fields into the desired structure. However, I have my concerns about performance to do this. >>> >>>I know that I could insert a new blank record into the table and query it back out. However we are in a stateless environment and there is no guarantee that the inserted record would actually be committed. (Someone might lose their internet connection, etc.) >>> >>>So I thought I could create a temporary table with no records based on the structure of the given table. I DO NOT want to explicitly create the structure. So I thought I might do this with SELECT INTO. That seems to work fine. However, when I attempt to insert an empty record into the temporary table, I receive an error stating that the Temporary Table is an invalid object. >>> >>>What am I missing here? >>> >>>TIA.
Vlad Grynchyshyn, Project Manager, MCP vgryn@yahoo.com ICQ #10709245 The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.
