Database Discussion Boards

Re: Deleting a member's account

Abhishek Sur16-Aug-09 7:43

Abhishek Sur

16-Aug-09 7:43

call dbo.aspnet_Users_DeleteUser to delete the user account.
Hope this helps. Rose | [Rose]

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

is there a more effective way to insert new row of data in multiple table [modified]

neodeaths15-Aug-09 4:29

neodeaths

15-Aug-09 4:29

hi
i am currently using vs.net 2005 c# window application and using ms sql as the database.

usually when i create database it is good practice to separate our database to such as below:
table: dad
table: child

where by when inserting data we have to add the dad data first then the child.

insert data into dad table
insert data ,max(index) from dad into child table

my question is there a more effective way to do this like inserting the data in one sql command while inserting the newest index from the dad table to the child table as a reference.

and thus if there are any error during the adding of data to any of the table both table will be then unaffected.

modified on Saturday, August 15, 2009 1:01 PM

Re: is there a more effective way to insert new row of data in multiple table

Henry Minute15-Aug-09 13:13

Henry Minute

15-Aug-09 13:13

neodeaths wrote:
and thus if there are any error during the adding of data to any of the table both table will be then unaffected.

There is a mechanism built into SqlServer to take care of situations like that. It is called Transactions this article on transactions[^] covers the basics. I found it by Googling for sqlserver transactions c# which gives loads of hits.

Explore for yourself, experiment, have fun. Smile | :)

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: is there a more effective way to insert new row of data in multiple table

Mycroft Holmes15-Aug-09 16:16

Mycroft Holmes

15-Aug-09 16:16

As Henry suggested transactions are the way to control this process. However I have trouble with you using max(index). You should be using an identity field for your primary key and then get the scope_identity() value from the dad insert to apply to the child record.

I rarely use transactions but I consider them valid for processing related DATA, while the dad/child data is related I don't think a transaction is warranted. I would have a proc that inserts dad and returns the scope_identity() as DadID and then insert the child in a separate proc.

Never underestimate the power of human stupidity
RAH

Re: is there a more effective way to insert new row of data in multiple table

Ashfield16-Aug-09 20:57

Ashfield

16-Aug-09 20:57

Mycroft Holmes wrote:
while the dad/child data is related I don't think a transaction is warranted

A bit of a sweeping staement there. It all depends, if you need to have both records inserted or niether of them then it should be a done in a transaction, but if orphan records are acceptable then there is no need.

Mycroft Holmes wrote:
I would have a proc that inserts dad and returns the scope_identity() as DadID and then insert the child in a separate proc.

Fine, unless you must have them both inserted. In your senario it would be possible to have Dad records with no Child records - maybe, depending on the application, this could be an invalid case.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: is there a more effective way to insert new row of data in multiple table

Mycroft Holmes16-Aug-09 22:07

Mycroft Holmes

16-Aug-09 22:07

Ashfield wrote:
A bit of a sweeping staement there

That's why I stated it as a personal view, and yes it must be driven by the business requirements every time. I generally work with batch processing and rarely need trans.

Ashfield wrote:
Dad records with no Child records

I would assume that unless the business required the relationship - dad does imply it I must admit.

How to use reporting engine features effiectvely

kiranp50714-Aug-09 21:28

kiranp507

14-Aug-09 21:28

Hi
i have developed stored procedure and able to generate SSRS report for the same.

i have two questions here
1)i am getting data from the sql server and i need to do lot of caluclations based on some conditions,so I would like to know how to implement the code without using stored procedures

2)How to use reporting engine features effiectvely

Regards,
Kiran

Re: How to use reporting engine features effiectvely

Mycroft Holmes14-Aug-09 22:55

Mycroft Holmes

14-Aug-09 22:55

I would have thought you would be better to do the calcs in the stored proc, thats generally what it is there for. We do almost no work at the report layer unless it involves report elements (layout and sum totally etc). While the report layer has functions etc I don't think you should be doing "a lot of calculations" using them.

As for you second question, what features, what engine?

Never underestimate the power of human stupidity
RAH

Synchronizing different locations in sql server 2005

εїзεїзεїз14-Aug-09 20:31

εїзεїзεїз

14-Aug-09 20:31

Thanks in advance

I have to implement one project in which i have SQL SERVER 2005 database and i have total 4 locations which are having a very high speed internet connection.

Now all of these locations wants real time information that if one transactions is done on one location then it will show or update the other location also that lets suppose that now we have stock of item A remaining 10.

What i need to know is what is the best way to implement this through linked Server/ cubes or
Xml etc while my front end application is using ODBC connection

Re: Synchronizing different locations in sql server 2005

Adam Maras14-Aug-09 21:51

Adam Maras

14-Aug-09 21:51

I believe what you're looking for is known as replication[^].

Adam Maras | Software Developer
Microsoft Certified Professional Developer

Re: Synchronizing different locations in sql server 2005

εїзεїзεїз14-Aug-09 21:55

εїзεїзεїз

14-Aug-09 21:55

Thanks for posting but what do you think i should do it using sql server replication

Re: Synchronizing different locations in sql server 2005

Mycroft Holmes14-Aug-09 23:02

Mycroft Holmes

14-Aug-09 23:02

I suggest you hire a good consultant, replication is not for dummies, it is an area fraught with peril for the beginner. I have been using SQL Server since it was Sybase and I would not attempt this without a expert support.

Replication is the way to go so start researching and testing (not on the production servers). I predict it will take you at least 2+ months to get a good handle on replication.

Never underestimate the power of human stupidity
RAH

Re: Synchronizing different locations in sql server 2005

εїзεїзεїз14-Aug-09 23:13

εїзεїзεїз

14-Aug-09 23:13

thanks for the post

well man i am doing this for almost more then 7 years but still in search for better solution

any how thanks for nice post

Re: Synchronizing different locations in sql server 2005

Jerry Hammond15-Aug-09 3:47

Jerry Hammond

15-Aug-09 3:47

softdev_sup wrote:
well man i am doing this for almost more then 7 years but still in search for better solution

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: Synchronizing different locations in sql server 2005

Ashfield16-Aug-09 21:00

Ashfield

16-Aug-09 21:00

softdev_sup wrote:
well man i am doing this for almost more then 7 years but still in search for better solution

Then that maybe suggests that replication is the best solution - after all, thats what it was designed to do, keep databases syncronised.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Help in Query - Oracle

aaraaayen14-Aug-09 8:36

aaraaayen

14-Aug-09 8:36

Hi Friends,

I need help in the following query.

SELECT A.BILL, A.BILLER, A.VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILL

The above query is working fine. and o/p like below:

A3 3A 3
A3 3A 2
A3 3A 1

But now requirement is that, the table A consists VERSION_NO.
For any combination that found for A.BILL=B.BILL AND A.BILLER=B.BILLER
may have different VERSION_NO.

Now I want to fetch only the row which is having max(version_no) for the A.BILL=B.BILL AND A.BILLER=B.BILLER the combination.

I needs the o/p shd be
A3 3A 3

Likewise for all the rows. Pls help me.

Thanks in Advance.

Regards,

Re: Help in Query - Oracle

Mycroft Holmes14-Aug-09 13:41

Mycroft Holmes

14-Aug-09 13:41

I don't know Oracle but in SQL Server I would use Top 1 and order by version_no desc. There will be something similar in Oracle.

Never underestimate the power of human stupidity
RAH

Re: Help in Query - Oracle

N a v a n e e t h14-Aug-09 17:45

N a v a n e e t h

14-Aug-09 17:45

Mycroft Holmes wrote:
There will be something similar in Oracle.

Yes. Oracle uses rownum. Something like,

Select * from table where rownum = 1 order by version_no desc

Navaneeth

How to use google | Ask smart questions

Re: Help in Query - Oracle

aaraaayen16-Aug-09 23:54

aaraaayen

16-Aug-09 23:54

Hi Navaneeth,

Thank you so much.

I understood the rownum=1 will work when i do order by version_no desc.

But in this case, order by is dynamic..that is user-given input. So whatever column user wish to come as order by he ll select that.

Can u help me how in that case i have to use this query.

Thanks lot really!!!

Regards,

Re: Help in Query - Oracle

aaraaayen17-Aug-09 0:00

aaraaayen

17-Aug-09 0:00

Navaneeth,

Thanks.

If I user rownum=1 order by version_no desc, this will give the very first row, but i want the rows with version_no = max(version_no) in every code,unit_id combination. If I have 3 rows with this combination, among the 3 rows i wanted to display the row which is having max(version_no).

Kindly help me. Thanks.

Regards,
Sageetha

Re: Help in Query - Oracle

Johan Hakkesteegt17-Aug-09 2:23

Johan Hakkesteegt

17-Aug-09 2:23

And just using MAX doesn't work?

SELECT A.BILL, A.BILLER, MAX(A.VERSION_NO) AS VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILL

My advice is free, and you may get what you paid for.

SQLServerCE.dll

sohaib_a13-Aug-09 4:46

sohaib_a

13-Aug-09 4:46

I have device running Windows CE 5.0.

I am developing an application for it in C# using a SQL server mobile database.

In order to install the database on the device,I first have to add SQLserverCE.dll.But I don't know why the folder or dll is not available. I tried to install Microsoft SQL Server MObile Developer Tools.but still nothing.

I also reinstalled SQl Server 2005...

considering all the columns in a field

jishbalan13-Aug-09 0:36

jishbalan

13-Aug-09 0:36

Hello friends,

I having two table, one table havin only one fields.

eg : Table A
release
rel1
rel2
rel3

I want to select datas from other table.
table B

release case ID
rel1 a 1
rel1 b 1
rel2 a 2
rel3 c 3

I want to select the cases and ID from table B where release is all the datas present in TAble A. I can build the query to get case and ID. Actually i need the query for another purpose.

I used below query
SELECT cases, ID FROM TAble B WHERE release IN(Select release from TAble B)

this query is very slow for my purpose
The query should link both the table

Please help me to opimise the query

Regards
Jishith

Re: considering all the columns in a field

Ashfield13-Aug-09 1:28

Ashfield

13-Aug-09 1:28

Try this

SELECT cases, ID 
FROM TAbleB b
Join TableA a on a.release = b.release

and check your indices

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

PROBLEM IN STORED PROCEDURE SUBSTRING :((

dotnetcsharpdev13-Aug-09 0:16

dotnetcsharpdev

13-Aug-09 0:16

i have a simple stored procedure   and i want to split postcode in it which could be of length either 7 or 6 and tha second part will always be of length 3. am pasting the sql here. and also the error message. Plz help me resolve this error

<code>

SELECT          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,

                    if Postcode.length = 7
                         begin
                              set Postcode1= substring(Postcode,5,7)
                              set Postcode1= substring(Postcode,1,4)
                         end
                    else
                         begin
                              set Postcode1 = substring(Postcode,4,6)
                              set Postcode1 = substring(Postcode,1,3)

                         end

     FROM            xyz
     WHERE (abc = @def)

     END

</code>

<ERROR>
error:
Msg 102, Level 15, State 1, Procedure XYZ,   LINE 7
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure XYZ, Line LINE 12)
Incorrect syntax near '='.
</ERROR>

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