exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Exec not returning a result

Hi all,
How can I tell if a executed dynamically created sql is returning no rows.
As in:
select @.sql= 'insert into PLImport2 select * from
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
exec (@.sql)
Which whould normally retun and insert 5000 rows. I want to this in a loop,
but am afraid to because as soon as it does not find any rows, I want it to
stop. At the momen it can carry on for ever
Thanks
RobertDoes it make a difference that you are INSERTing into [PLImport2] and you
are SELECTing from [Import1]?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robert Bravery" <me@.u.com> wrote in message
news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> How can I tell if a executed dynamically created sql is returning no rows.
> As in:
> select @.sql= 'insert into PLImport2 select * from
> OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
> 8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
> FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
> exec (@.sql)
> Which whould normally retun and insert 5000 rows. I want to this in a
> loop,
> but am afraid to because as soon as it does not find any rows, I want it
> to
> stop. At the momen it can carry on for ever
> Thanks
> Robert
>|||HI,
Yes, PLImport2 is a SQL Table that I am inserting into, and Import1 is
range in an excel File. I am selecting data from the Excel file into the SQL
table
Thanks
Robert
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eWIQ2VunGHA.4104@.TK2MSFTNGP04.phx.gbl...
> Does it make a difference that you are INSERTing into [PLImport2] and you
> are SELECTing from [Import1]?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
rows.
*
>

EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql

Exception handling with full text search

Hello,
I am dynamically generating and SQL query, which could contain illegal
words. I receive an exception and dialog box,
"System.Data.SqlClient.SqlException: A clause of the query contained only
ignored words".
Is there a way to catch that exception and resume processing (of the rest of
the application)?
My C# code looks like:
try {
...
myReader = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
...
}
How can I handle the exception?
Many thanks,
Urban
No, there is no way to do an error resume next.
You have to avoid this error by one of the three methods
1) use a FreeText search which solves this error almost all the time, but
you may get too many results returned
2) remove the noise words from the search phrase by client side parsing.
This is not always the best solution as your search may not always return
what you expect, but in general works well.
3) remove all entries from your noise word list and replace it with a blank
space. Rebuild your catalogs. The problem with this approach is that your
catalogs can get much larger and performance will consequently drop
somewhat.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Urban Bettag" <urban@.bettag.com> wrote in message
news:1092323291.76604.0@.demeter.uk.clara.net...
> Hello,
> I am dynamically generating and SQL query, which could contain illegal
> words. I receive an exception and dialog box,
> "System.Data.SqlClient.SqlException: A clause of the query contained only
> ignored words".
> Is there a way to catch that exception and resume processing (of the rest
of
> the application)?
> My C# code looks like:
> try {
> ...
> myReader = myCommand.ExecuteReader(CommandBehavior.CloseConne ction);
> }
> catch (Exception e)
> {
> Console.WriteLine(e.ToString());
> ...
> }
> How can I handle the exception?
> Many thanks,
> Urban
>
|||> No, there is no way to do an error resume next.
> You have to avoid this error by one of the three methods
> 1) use a FreeText search which solves this error almost all the time, but
> you may get too many results returned
> 2) remove the noise words from the search phrase by client side parsing.
> This is not always the best solution as your search may not always return
> what you expect, but in general works well.
> 3) remove all entries from your noise word list and replace it with a
blank
> space. Rebuild your catalogs. The problem with this approach is that your
> catalogs can get much larger and performance will consequently drop
> somewhat.
Thanks Hilary,
I am using SQL Server 2000 and within a hosted environment I possibly have
not
full control of the noise word list. Or is there a way to programmatically
access
that list?
It looks like I will try to go for 2), filtering out what is in the default
noise
word list.
Many thanks,
Urban
|||There is no way to programmatically access this list other than by using
trial and error methods to determine exactly what this list contains.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Urban Bettag" <urban@.bettag.com> wrote in message
news:1092324646.15314.0@.iris.uk.clara.net...[vbcol=seagreen]
but[vbcol=seagreen]
return[vbcol=seagreen]
> blank
your
> Thanks Hilary,
> I am using SQL Server 2000 and within a hosted environment I possibly have
> not
> full control of the noise word list. Or is there a way to programmatically
> access
> that list?
> It looks like I will try to go for 2), filtering out what is in the
default
> noise
> word list.
> Many thanks,
> Urban
>
>
|||Urban and Hilary,
Well you could do something like this... (from a previous post on this
subject):
Create Table noise_words ( Noiseword varchar(50) Not Null )
Go
Alter Table noise_words Add Constraint PK_noise_words Primary Key Clustered
( Noiseword )
Go
Then you can use BULK INSERT, BCP or DTS task to copy the contents of the
file into the database either at server startup or via a SQLServerAgent job
when the you change the noise word file. Before you copy in the
language-specific noise word file, you will need to make some changes to the
initial file from the end of the file as the noise word files contain a list
of "white space" single letters and characters at the end of the file, for
example, from noise.enu:
a b c d e f g h i j k l m n o p q r s t u v w x y z
BULK INSERT or BCP will fail or think this is one big string (no CR/LF), so
you will need to separate out the row above such that each letter takes up
its own row in the file. Open the language specific noise word file in a
text editor (notepad.exe) and change the above list to:
a
b
c
d
e
f
and so on. Be sure to eliminate any leading or trailing spaces for each
character. Once that's done, you can use BULK INSERT, BCP or DTS to copy the
data from the noise file to the noise_words table. Once the data is imported
correctly, you can use a standard SQL statement such as:
select Noiseword from noise_words where Noiseword = "between"
to use in a string parser function in your C# code to remove the noise words
in your users input string and then pass this edited string to a SQL Server
FTS query.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:elwYwTIgEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> There is no way to programmatically access this list other than by using
> trial and error methods to determine exactly what this list contains.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Urban Bettag" <urban@.bettag.com> wrote in message
> news:1092324646.15314.0@.iris.uk.clara.net...
> but
parsing.[vbcol=seagreen]
> return
> your
have[vbcol=seagreen]
programmatically
> default
>

Excel rendering

Hi, does anyone know if it's possible to dynamically create excel tabs/worksheets possibly via RS web service library or some other method.
Thanks in advance,
JimNo.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:B37D944C-B806-48EB-A991-905E9405A8D9@.microsoft.com...
> Hi, does anyone know if it's possible to dynamically create excel
tabs/worksheets possibly via RS web service library or some other method.
> Thanks in advance,
> Jim|||Hi,
when i export report in excel, sometimes document map color in excel comes
blue and sometimes it comes blue. Why is it so?. Is this a RDL issue or excel
issue? please help me out. My email id is kiran_nandedkar@.rediffmail.com
Thanks in advance