No idea if this is the right place to post... Need stored proc help

Hello,
I am trying to make a stored proc that will tell me if there are any rows in a table. The table name is a variable that I get at the start of the proc it is not sent in. What I am looking for in the table is sent into the stored proc.

here is where the problem is, i can not save it. It does not seem to allow me to use the if exists with variables. I have tried it also without the + and with the + ' to create a string I cant seem to get it anyway I try.
if exists (SELECT * FROM + @.TableName + WHERE + @.ColumnName + = + @.SearchStr2)

print 'got here ' +@.TableName + ' '+ @.ColumnName

END

Thanks for any help

You need to build the entire query string in a variable, and then use the dbo.sp_executesql stored procedure to execute that query string. As you've discovered, you can't use 'if exists' with variables.

For example:

declare @.sql nvarchar(max)

set @.sql = N'

if exists (select * from ' + @.TableName + N' where ' + @.ColumnName + ' = ''' + @.SearchStr2 + ''')

print ''Got here ' + @.TableName + ', ' + @.ColumnName + ''''

exec dbo.sp_executesql @.sql

|||

All of this of course is true; however, you also need to know that this is exactly the style of code that a hacker is looking to exploit for an "SQL Injection" attack. Please do some additional research before you implement this. This kind of code CREATES a security hole.

Dave

|||

Thanks Iain I will give it a shot and let you know. Also thanks Mugambo but this is a one time thing i need to run to do the following if anyone has a better idea how to do it I would love ideas.

I need to update our SQL database for an individual user. The problem is somehow this user got two ID's with different numbers and it has been about 4 months now that it has gone unnoticed and. So I now need to change the two id's into one. So what I was planning to do was to do was search ever column of every table and it would let me know if there is an exact match to the ID I put in. If so it will display the column and table so I can decide if it is data I need to change or not. We have about 200 tables so to do it one table at a time wouldnt be impossible to do but this way I learn a little more about SQL and stored procs which is what I really want anyway.

Thanks again

|||And thanks again Iain works great.|||Hi,

Can you mark a correct answer for us please.

Thanks!

No HTML page breaks and slow expanding rows

I have a report with a single table, single grouping level, single data set and no sub-reports. It has 3 rows for a grouping header and 3 rows per dataset row of detail. The detail rows are initially hidden and can be expanded by clicking on the header +. Its a fairly standard master-detail report.

Regardless of data size, I get NO page breaks in HTML. I have the Interactive size set to 8.5x11, KeepTogether is set to False, and PageBreakAtEnd is set to False. I would like it to break based on the visible grouping rows.

As it is now, everytime you expand any section, it takes forever to reload for a larger recordset.

I know that "HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.", but how do I get this report to page break? I've seen a lot of posts on this, but none that seem to have an answer.

Anyone? Can I programmatically add the soft page breaks? The report is useless as it is now.|||

Thanks to others, I found a solution. Use the query to calculate the count of headers using the dense_rank() function:

select ...

dense_rank() over ( order by cl.last_name, cl.first_name, cl.client_id ) AS ClientRank

Then add a top level grouping on the table set to "Page Break at End" with the formula:

=Ceiling(Fields!ClientRank.Value/15)

replace 15 with how many group headers you want per page.

-Dave

sql

No Error but Export to Excel does not finish

No Error but Export to Excel does not finish

When the report has 2 pages with total 500 rows exporting to Excel is not a problem.

If it has 100 pages 5000 rows exporting to excel does not end and it does not return any error but the process does not end either. What might the problem be?

How you are exporting?

Are you using SSIS or DTS package?

I don't see any relation between reporting services & export to excel or are you trying to save as EXCEL spreadsheet.

|||

In there report manager, I run the report and then choose excel and export.

|||

Try increasing the value of MemoryLimit in the RSReportServer.config file available under Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder. The default value is 60, try increasing to a higher value but keep it under 120.

Shyam

sql

No Data Returned for report

Hi,

In RS 2005, if a specific query does not return any rows, the table also is not displayed in the report.

Is there any way to get around this and show an empty table?

Manish

Hi,

select all the cells and in the properties make "borderstyle" - "Solid"

and select the table and make same as above in the properties.

you can see the empty table.

Amarnath

|||

One option is to set the "NoRows" property of the table. You can also apply styles to that message.

-- Robert

|||

This is correct but only if the table has a Header or Footer row.

If the dataset associated with a table returns no data, and there is no "NoRows" message specified for the table, AND if there is a header or footer, the table will be shown.

-chris

no bookmark lookup noticed when i think i need to see it

SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3 returns 20 rows...
It is doing a nested loop join with t1 being the outer table and t2 being
the inner table. it is doing a non clustered index seek on t2. The
nonclustered index it is using is on (col2,col3). What I dont understand is
why on my execution plan I dont see a bookmark lookup to fetch the
corresponding row since i need t2.col1
When i run
SELECT t2.* FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
I see a bookmark lookup with the same indexes being used as the previous
query for the 2 tables...Perhaps you have a clustered index on t2(col1)? Remember that the nc indexes uses the clustering key
as row locator.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OTXLtMWiDHA.1456@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3 returns 20 rows...
> It is doing a nested loop join with t1 being the outer table and t2 being
> the inner table. it is doing a non clustered index seek on t2. The
> nonclustered index it is using is on (col2,col3). What I dont understand is
> why on my execution plan I dont see a bookmark lookup to fetch the
> corresponding row since i need t2.col1
> When i run
> SELECT t2.* FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> I see a bookmark lookup with the same indexes being used as the previous
> query for the 2 tables...
>sql

No "Open Table" In SQL Server 2005?

In Enterprise Manager for SQL Server 2000, I used to choose "Open Table..."
and then "Return All rows" to be able to update data directly into a grid.
This tool was rapid and useful, but i'm not able to find a such tool in SQL
Server 2005 environment... Is there a tool like this? If not, somebody know
why Microsoft has removed a awesome tool like it'
David.
Thanks(a) you must be using an old beta:
http://www.aspfaq.com/sql2005/show.asp?id=5
(b) please post SQL Server 2005 questions to the SQL Server 2005 newsgroups:
http://www.aspfaq.com/sql2005/show.asp?id=1
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:2344C393-7600-4041-BE01-5E0A10AE33D4@.microsoft.com...
> In Enterprise Manager for SQL Server 2000, I used to choose "Open
> Table..."
> and then "Return All rows" to be able to update data directly into a grid.
> This tool was rapid and useful, but i'm not able to find a such tool in
> SQL
> Server 2005 environment... Is there a tool like this? If not, somebody
> know
> why Microsoft has removed a awesome tool like it'
> David.
> Thanks

Newby JOIN question

I'm attempting to use a JOIN for the first time and I'm getting about 94,000 rows returned when I should only be getting about 270. Something must not be unique (possible DSN) but I can't figure out why the join is not working. I know it must be difficult to determine the problem without actually seeing the tables but could somebody with a lot of SQL experience spot any glaring problems with this query? Thanks!

SELECT DS.DSNName AS DSN,
S.ServerName AS WebServer,
DBs.DBName,
A.ServerName AS SQLServer
FROM DSNs DS,
Servers S,
DBs,
DSNs LEFT OUTER JOIN Servers A
ON DSNs.SQLServerID = S.ServerID
WHERE DBs.Status = 1
AND DS.WebserverID = S.ServerID
AND DBs.DBID = DS.DBID
ORDER BY DSN

Hi reformatted, I got this:

SELECT DS.DSNName AS DSN,
S.ServerName AS WebServer,
DBs.DBName,
A.ServerName AS SQLServer
FROM DSNs DS,
INNER JOIN Servers S,
ON DS.WebserverID = S.ServerID
INNER JOIN DBs
ON DBs.DBID = DS.DBID
INNER JOIN DSNs
ON DSNs.SQLServerID = S.ServerID
LEFT OUTER JOIN Servers A
ON DSNs.SQLServerID = A.ServerID --I guess this was wrong as it was pointing to the S.Server not the A.Server, see if this helps.
WHERE DBs.Status = 1
AND DS.WebserverID = S.ServerID
AND DBs.DBID = DS.DBID
ORDER BY DSN

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your reply. I removed a couple unnecessary commas and the query runs but does not pull back any data. I will play around with it. Please let me know if you have any additional suggestions.|||If I am not sure about a query, I always do a step.by-step approach, starting with the base query and adding more joins one by one controlling the expected results after each step. This is much easier than doing the whole query and then guessing by the results which JOIN was not the right one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

newbie: TSQL command for number of rows ?

I couldnt find on google a command to get the number of rows in a table.
Any suggestions ?
TIA
-steveSELECT COUNT(*)
FROM YourTable
David Portas
SQL Server MVP
--|||Use the TOP statement
Example:
USE NORTHWIND
GO
Select TOP 5 From Orders
But remember that TOP is also recommended with the use of order because the
TOP clause will produce an unpredictable subset of n-Rows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"steve" <steve@.here.com> schrieb im Newsbeitrag
news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||SELECT COUNT(*) FROM tbl
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <steve@.here.com> wrote in message news:Ph4ie.56688$th3.616072@.wagner.videotron.net.
.
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>|||Ok, guess I didnt get your questions that right X-)
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:u7Wq6ljWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Use the TOP statement
> Example:
> USE NORTHWIND
> GO
> Select TOP 5 From Orders
> But remember that TOP is also recommended with the use of order because
> the TOP clause will produce an unpredictable subset of n-Rows.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steve" <steve@.here.com> schrieb im Newsbeitrag
> news:Ph4ie.56688$th3.616072@.wagner.videotron.net...
>|||I am useless!
Thanx a lot!!
-steve
"steve" <steve@.here.com> a crit dans le message de news:
Ph4ie.56688$th3.616072@.wagner.videotron.net...
>I couldnt find on google a command to get the number of rows in a table.
> Any suggestions ?
> TIA
> -steve
>