Query - Database Discussion Boards

Re: How to find rows returned by EXEC statement.

andyharman23-Jul-07 8:09

23-Jul-07 8:09

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".

Re: How to find rows returned by EXEC statement.

jdkulkarni23-Jul-07 8:55

jdkulkarni

23-Jul-07 8:55

Hey Andy,
Thanks a lot for gr8 answer..
It worked well.

I voted 5...

Jayant

Problem converting datetime in sqlserver

shankhan bhandari23-Jul-07 5:55

shankhan bhandari

23-Jul-07 5:55

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

Re: Problem converting datetime in sqlserver

Rob Graham23-Jul-07 9:13

Rob Graham

23-Jul-07 9:13

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.

Dataset to XML

Shaareable23-Jul-07 4:25

Shaareable

23-Jul-07 4:25

Hi!

I have a problem. D'Oh! | :doh:

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. Smile | :)

Thank you,

Antoine Dubuc
Transcontinental

Re: Dataset to XML

Paddy Boyd23-Jul-07 5:19

Paddy Boyd

23-Jul-07 5:19

If they change often and per permission level (do you mean per user?), then the cache is probably not the right place for these.

Re: Dataset to XML

Shaareable23-Jul-07 12:24

Shaareable

23-Jul-07 12:24

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

Query

sangramkp23-Jul-07 3:48

sangramkp

23-Jul-07 3:48

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

Re: Query

Blue_Boy23-Jul-07 4:12

Blue_Boy

23-Jul-07 4:12

select max(row_num) as maxRowNum, max(Col_num) as maxCol_num from table1

I Love SQL

Problem in restoring database

Care Career23-Jul-07 0:41

Care Career

23-Jul-07 0:41

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

Re: Problem in restoring database

Pratik Vasant Shah23-Jul-07 2:17

Pratik Vasant Shah

23-Jul-07 2:17

Do you mean you want to overwrite the data of db1 database with that of db2.

Thanking you in Advance

Regards
Pratik Shah

Re: Problem in restoring database

Care Career23-Jul-07 16:18

Care Career

23-Jul-07 16:18

ya

SO how to do?

Thnx

Re: Problem in restoring database

Pratik Vasant Shah25-Jul-07 18:55

Pratik Vasant Shah

25-Jul-07 18:55

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

Re: Problem in restoring database

Eliz.k27-Jul-07 0:36

Eliz.k

27-Jul-07 0:36

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

Little replication snag...

MatthysDT22-Jul-07 22:01

MatthysDT

22-Jul-07 22:01

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

Re: Little replication snag...

MatthysDT23-Jul-07 2:34

MatthysDT

23-Jul-07 2:34

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

How to create Backup of database for a fixed time?

Care Career22-Jul-07 21:21

Care Career

22-Jul-07 21:21

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

Re: How to create Backup of database for a fixed time?

Marek Grzenkowicz22-Jul-07 23:16

Marek Grzenkowicz

22-Jul-07 23:16

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?

Re: How to create Backup of database for a fixed time?

Care Career23-Jul-07 0:09

Care Career

23-Jul-07 0:09

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

Re: How to create Backup of database for a fixed time?

Rob Graham23-Jul-07 9:24

Rob Graham

23-Jul-07 9:24

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.

Re: How to create Backup of database for a fixed time?

Care Career23-Jul-07 16:29

Care Career

23-Jul-07 16:29

thnx lot

Can u tell how to start Sql server agent?

1069 logon faliure error is coming when i start sql server agent.

Thnx

Re: How to create Backup of database for a fixed time?

Marek Grzenkowicz23-Jul-07 21:44

Marek Grzenkowicz

23-Jul-07 21:44

Try to search for yourself before you ask - this is the first result Google gave me:
http://www.mssqlcity.com/FAQ/Trouble/error_1069.htm[^].

What account does SQL Server Agent use on your machine?

Re: How to create Backup of database for a fixed time?

Marek Grzenkowicz23-Jul-07 21:46

Marek Grzenkowicz

23-Jul-07 21:46

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.

Reportviewer control

Rupesh Kumar Swami22-Jul-07 21:21

Rupesh Kumar Swami

22-Jul-07 21:21

hi,
i want to know that whether or not reportviewer control support subscript & superscript ?

Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)

Re: Reportviewer control

Paul Conrad24-Jul-07 5:56

Paul Conrad

24-Jul-07 5:56

Rupesh Kumar Swami wrote:
reportviewer control support subscript & superscript ?

Have you actually tried it?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.