|
Friends,
Can somebody tell me how to find number of rows returned by EXEC statement?
I have following example.
SET @Sql = 'SELECT COUNT(*) FROM Table where columnA = 10'
EXEC (@Sql).
Where can I get count?
Jayant
|
|
|
|
|
Hi Jayant
You would only the "EXEC" if your SQL statement is dynamic. The following code would allow you to get the count for your above static SQL:
DECLARE @Rows INT
SELECT @Rows = COUNT(*) FROM Table where ColumnA = 10 If you want to search for different values then use:
DECLARE @Rows INT, @MySearch INT
SET @MySearch = 9
SELECT @Rows = COUNT(*) FROM Table where ColumnA = @MySearch If your SQL truely needs to be dynamic then use:
DECLARE @Rows INT, @SQL VARCHAR(500)
--Create temporary table to hold results.
CREATE TABLE #temp_result (Rows INT NOT NULL)
--Construct and execute dynamic SQL to be used (would normally be more complex).
SET @SQL = 'INSERT INTO #temp_result (Rows)
SELECT COUNT(*) FROM Table where columnA < 10'
EXEC (@SQL)
--Get results.
SELECT @Rows = Rows FROM #temp_result Hope that helps.
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hey Andy,
Thanks a lot for gr8 answer..
It worked well.
I voted 5...
Jayant
|
|
|
|
|
hello sir
I am trying to execute a querry which i get through Visual Studio and getting the AM/PM in Korean One but it give some error "cant not convertion failed when convert datetime from charectestring".i get the following querry.
update WS_STOKHDRTICKET set SELLSTART= '2007-07-09',
SELLEND='2007-07-25',VALIDITYEXPIRY= convert(datetime,'2007-07-23 오전12:00:00',120) ,
MAXIMUMQTY=1,WEBDESCRIPTION='ss',TERMSCONDITIONS='',VALIDITYOPTION=0,
VALIDITYPERIOD=1,CONCESSIONWARNING=0,CONCESSIONWARNINGTEXT='',WEBPRICINGMETHOD=2,TICKETTYPE=5 ,SHOWANYDAYTICKET =1,SHOWPREBOOKEDVISIT =1 ,QTYINCLUDEINTICKETCOUNT =12 where STOKHDRUCODE=8
if there is any way to solve the problem then please help me
Thanks Regards
Biplab Kumar Saha
|
|
|
|
|
Biplab saha wrote: convert(datetime,'2007-07-23 오전12:00:00',120)
The 120 in the 3rd parameter of the convert specifies that the string will be ODBC Canonical format (yyyy-mm-dd hh:mm:ss) where the time is 24hr clock time, but the string you are passing it (2007-07-23 오전12:00:00) contains extra, unexpected characters in the middle. You need to remove those characters and convert the time to 24hr time if necessary.
|
|
|
|
|
Hi!
I have a problem.
I don't know how to attach properly my menu web control to my database.
No. Don't laugh, please...
It's a bigger issue than you think, just read on.
You see, the menuitems are changing often and per permission level. This means that the menuitems must be stored in an XML file and cached. Each time there's an update, its recached.
I am using a typed dataset (myMenuItems.xsd) from wizard with the table adapter
<br />
private MenuItemsTableAdapters.MenuItemsTableAdapter myMITA = <br />
new test.MenuItemsTableAdapters.MenuItemsTableAdapter();<br />
<br />
private test.myMenuItems.MenuItemsDataTable myMI;<br />
<br />
private DataSet myDS = new DataSet();<br />
<br />
private XmlDataDocument myTemp;<br />
<br />
[...].br />
<br />
protected void Page_Load(object sender, EventArgs e)<br />
{<br />
if (!Page.IsCallback)<br />
{<br />
myMI = myMITA.GetData();<br />
myTemp = new XmlDataDocument();<br />
[...].br />
<br />
The humble question I ask is where do I go from there? I mean, how do I stuff the XML document with the menuitems, and how do I then stuff it into the cache and into the menu control?
My boss laid a project on me that is slightly above my technical competence for the moment. I would appreciate any help.
Thank you,
Antoine Dubuc
Transcontinental
|
|
|
|
|
If they change often and per permission level (do you mean per user?), then the cache is probably not the right place for these.
|
|
|
|
|
Hi,
The permissions vary from not logged in, logged in, and admin. Its the internationalization that makes that a headache. Any idea how to manage dynamically from a database a menu control?
Thank you,
Antoine Dubuc
Transcontinental
|
|
|
|
|
I have a table table1 which sores row and column number as:
Row_num Col_num
1 1
2 1
3 1
1 2
2 2
I want to find out the maximum of the row_ num and maximum og the col_num..
Plz send me the query
|
|
|
|
|
select max(row_num) as maxRowNum, max(Col_num) as maxCol_num from table1
I Love SQL
|
|
|
|
|
Hi
I have to database in sql sever 2000
db1 & db2
I want db2 backup in db1 .
I have taken db1 bacckup
After tht how i do restore
Plz Help
|
|
|
|
|
Do you mean you want to overwrite the data of db1 database with that of db2.
Thanking you in Advance
Regards
Pratik Shah
|
|
|
|
|
|
Hi,
Since you already have the backup of db1 (assumed, as said by you) you can just drop the database db1 and restore the data of db2 in a new database and give the dbname as db1. (This can be done using the Enterprise Manager in SQL 2000).
Kind regards
PS
Thanking you in Advance
Regards
Pratik Shah
|
|
|
|
|
I assume you want to copy everything from db2 to db1.
1. Backup db2.
2. Right click db1, go to All Task/Restore Database..
3. Check "FROM Device".
4. Select the device by click on button "Select Devices".
5. Add the backup file that you backup from db2. Click button "OK".
6. Go to tab "Option". Change the physical file name to db1.mdf and db1.ldf.
7. Check option "Force restore over existing database".
8. You should know how to continue now.
btw, please check Management/Current Activity/Process Info to make sure no connection is connected to db1.
Eliz.K
www.oin1.com
|
|
|
|
|
I need some advice on replication...
I set up a new push-subscription on my server, when I change the data (which should then be replicated) the server reports "No replicated transactions available"
In my Replication Monitor the changes are noted under the "Publisher to Distributor History" but still it reports the above message upon synchronization.
Why could this be?
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
|
This problem occurs when SQL replication goes KooKoo, typically after a database restore.
The quickest (and probably only) fix is to remove all the Publications and start from scratch.
Search SQL Server 2005 Books Online for "Removing Replication"
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
|
Hi
HOw to create Backup of database using query for a fixed time?
I do not want to use Wizard.
plz help as soon as possible.
Thanks
|
|
|
|
|
This will create a backup:
BACKUP DATABASE pubs TO DISK = N'c:\temp\pubs.bak'
but I have no idea if that's what you need, since I don't know what you mean by 'for a fixed time'. Could you explain?
|
|
|
|
|
Thnks for replay
BACKUP DATABASE Parikrama TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\SQLSpyNetJob.bak'
WITH NOINIT, NOSKIP, STATS = 10
can anyone tell wht is the meaning of STATS =10 in above query.
And plz tell me i want query tht taking automatically bacup of database with in interval0f 10-10 minutes by job scheduler .
Thanks
|
|
|
|
|
Care Career wrote: database
SQL Server Books On Line[^] are your friend. That means with statistics update based on a 10% sample of the data.
Creating a backup every 10 minutes is only likely to work for a very small database, as backup can take more time than this. Creating a job to do this on a scheduled interval takes more than one query, and is best done using the database maintenance wizard. It is possible manually, but the procedure is more lengthy than can be described here. Use the reference I have provided, and look up SQL Server Agent tasks, or Database maintenance.
|
|
|
|
|
thnx lot
Can u tell how to start Sql server agent?
1069 logon faliure error is coming when i start sql server agent.
Thnx
|
|
|
|
|
|
Rob Graham wrote: That means with statistics update based on a 10% sample of the data.
Are you sure?
This is what I found:
STATS [ = percentage ]
Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.
|
|
|
|
|
hi,
i want to know that whether or not reportviewer control support subscript & superscript ?
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|