Database Discussion Boards

Mike Dimmick24-Jul-07 1:17

24-Jul-07 1:17

I tend to reserve the return value for succeeded/failed notifications, where I haven't used RAISERROR (which throws a SqlException in .NET client code). The return value can only be an integer, while output parameters can be any type.

I rarely use output parameters unless it's an out-of-band value, not associated with the other data in a result set, and I don't want to return an extra result set just containing that data.

Stability. What an interesting concept. -- Chris Maunder

Multiple inserts into oracle in one round trip

kjosh23-Jul-07 10:14

kjosh

23-Jul-07 10:14

Hi,

In my form I have so many textboxes and check boxes and Gridview, When the user clicks on a button I need to insert those data into different tables in Oracle database.

Can anyone tell me the best way to do the multiple inserts in to different tables in Oracle database.

Thanks in advance

Re: Multiple inserts into oracle in one round trip

Paul Conrad23-Jul-07 13:59

Paul Conrad

23-Jul-07 13:59

I haven't worked with Oracle in a few years, so bear with me. You could try doing the inserts through a stored procedure.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: Multiple inserts into oracle in one round trip

Mike Dimmick24-Jul-07 1:21

Mike Dimmick

24-Jul-07 1:21

I believe Oracle supports multiple statements in one batch. Ultimately you will have to write one insert statement per table, whether you do that as multiple statements in one batch or in a stored procedure.

SQL Server allows you to update a view if you supply an INSTEAD OF trigger which redirects the updates to the appropriate base tables. I don't know whether Oracle has a similar feature.

Stability. What an interesting concept. -- Chris Maunder

convert varchar to date format

ciacia23-Jul-07 9:04

ciacia

23-Jul-07 9:04

Hello ,
I want to wirte a sql statment which converts varchar into date (mm/dd/yyyy) format. please help
thanks

Re: convert varchar to date format

Mike Dimmick23-Jul-07 10:18

Mike Dimmick

23-Jul-07 10:18

CAST( @var AS datetime ) works well for dates in the current DATEFORMAT or using ISO 8601 format (yyyyMMdd). The DATEFORMAT defaults to the appropriate format for the current LANGUAGE (really a locale). The default value for LANGUAGE comes from the user's login, which in turn gets it (at creation time) from the server's default language option.

Otherwise look at CONVERT.

As a best practice you should pass dates and indeed all other user-supplied data using parameterized queries, for which see the documentation for the provider you're using. That passes the data using a well-understood datatype, rather than parsing the information multiple times.

Stability. What an interesting concept. -- Chris Maunder

How to find rows returned by EXEC statement.

jdkulkarni23-Jul-07 7:09

jdkulkarni

23-Jul-07 7:09

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

Re: How to find rows returned by EXEC statement.

andyharman23-Jul-07 8:09

andyharman

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?

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