Database Discussion Boards

DerekFL9-Jul-08 8:05

9-Jul-08 8:05

You mean something like "violation of index idx_unq_foo cannot insert duplicate key" you want to change that? Its a global you can find by running SELECT * FROM master.dbo.sysmessages WHERE description like '%error%'

The best method to make a customized message is the use a try catch in c# and catch SQLException. customize the message based on the exception type.

Getting the value of a primary key that is an Identity field

LongRange.Shooter9-Jul-08 6:26

LongRange.Shooter

9-Jul-08 6:26

I am creating a new record in the database.
The key is an identity field and the database is set to not allow inserts of assigned values.
I need to return the results of the INSERT to the user.

Is there anything I can do in my SQL that will let me return that key value? Is there something like a @PrimaryKey?

Thanks

Re: Getting the value of a primary key that is an Identity field

leoinfo9-Jul-08 6:56

leoinfo

9-Jul-08 6:56

MySQL

Select last_insert_id()<br />

MSSQL

Select SCOPE_IDENTITY()<br />

Can't find SSIS or SSRS

meeram3959-Jul-08 0:12

meeram395

9-Jul-08 0:12

Hi,

I need to work with SSRS & SSIS. Thinking that this will be available along with Visual Studio .Net 2008, I have installed the same. But to my surprise, I couldn't find SSRS or SSIS under Reporting Services other than Crystal Reports. How can I install the same? Is it available along with VS 2008 or coming seperately? Please provide any help.

Thanks in advance
meeram395

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Re: Can't find SSIS or SSRS

Jerry Hammond9-Jul-08 3:03

Jerry Hammond

9-Jul-08 3:03

You might want to read this: http://technet.microsoft.com/en-us/sqlserver/cc314973.aspx[^]

“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan

help me please

loscalibros9-Jul-08 0:08

loscalibros

9-Jul-08 0:08

project:Visual Studio 2005 ASP.NET with C#

i want to increment my database values with button.
EXAMPLE:
i have sql database in my project.
column.....gold stone place
madde: 10 10 10

when U clicked button

every column inc+10 or +20.

button is in default.aspx page.
which code can i use in button. i did not do it.

Re: help me please [modified]

meeram3959-Jul-08 0:41

meeram395

9-Jul-08 0:41

This is not the right forum to ask this question. Post it in ASP.Net forum

In Asp.Net, just declare a integer variable and then increment it with 1 , like
int i = 0; i++.
In sql server, you can use the Identity columns for increasing automatically. By default it will increment by 1.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

modified on Wednesday, July 9, 2008 6:47 AM

Sending XML as input to Oracle stored procedure

kalyan_vb8-Jul-08 23:48

kalyan_vb

8-Jul-08 23:48

I have the following XML, and I need to copy the Workflow related data i.e Name and Descriptin and Module name in Workflow Master table

Level Number, NumberOfActors in Level Master table,
Rule Action, LevelNumberAfterAction, StateAutoChangeDuration in Rule Master table.

I want to do this in Oracle, and passing this XML as input to stored procedure.

Please let me know how to do this.

<workflow name="" description="" moduename="">
<levels>
<level number="1" numberofactors="1">
<rules>
<rule action="SUBMIT" levelnumberafteraction="2" stateautochangeduration="">
<notification>
<message to="REQUESTER" body="Request has been submitted" subject="CSRM Request Notification" address="">
<message to="OTHERS" body="A new CSRM request has been raised" subject="CSRM Request Notification" address="kalyana_vb@rediffmail.com">

<level number="2" numberofactors="*">
<rules>
<rule action="APPROVE" levelnumberafteraction="3" stateautochangeduration="2">
<notification>
<message to="REQUESTER" body="Request has been approved at level 2" subject="CSRM Request Notification" address="">
<message to="RECEIVER" body="There is a new request pending with you" subject="CSRM Request Notification" address="">

<rule action="REJECT" levelnumberafteraction="1" stateautochangeduration="">
<notification>
<message to="REQUESTER" body="Request has been rejected at level 2" subject="CSRM Request Notification" address="">
<message to="RECEIVER" body="Request has been rejected at level 2" subject="CSRM Request Notification" address="">

default settings SQL Server does not allow remote connections.

Amit Agarrwal8-Jul-08 23:19

Amit Agarrwal

8-Jul-08 23:19

hello all,
I'm getting this error when connecting to my sql server 2005 with asp.net 2.0

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Regards

Amit Agarwal

Re: default settings SQL Server does not allow remote connections.

subai9-Jul-08 2:59

subai

9-Jul-08 2:59

if your database service running and you can use it on local machine that could be wrong connection string(user/pass/server) or sql ports being blocked by firewall on server.

I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe
www.subaitech.blogspot.com

How to find the Table exists in a Data Base

pallaka8-Jul-08 22:16

pallaka

8-Jul-08 22:16

I am having DataBase1
In that i am having many tables.

Table1
table2
table3.....

I want to know weather table2 exists or not.
If not i need to create the table.
So can any one tell me how to find the weather the table i am looking for exists are not

Re: How to find the Table exists in a Data Base

Ashfield8-Jul-08 22:47

Ashfield

8-Jul-08 22:47

If you are using SQL Server (you haven't said) then

if not exists (select 1 from sysobjects where xtype = 'U' and name = 'table2')
begin
create table table2.......
end

will do it. There are several other ways too.

Bob
Ashfield Consultants Ltd

Re: How to find the Table exists in a Data Base

TheFM2349-Jul-08 4:46

TheFM234

9-Jul-08 4:46

Another way to do it is (just replace your object names in the carrots):

If Object_Id('<database>.<schema>.<table>') Is Not Null
Begin
Create Table...
End

Which is pretty much the same as posted above, it's just using a system function. It can also work with temp tables:

--Assuming that #table is the temp table name.
If Object_Id('tempdb..#table') Is Not Null
...

Changing between mssql and access through connection string

Y_R8-Jul-08 22:10

Y_R

8-Jul-08 22:10

Hi,

I have a c# (.Net 2) program that connects to access mdb file.
I use strong typed dataset to do this.

Now I have searched the web and found ms sql connection string
I replaced what was generated by the wizard with the new one and
now I can successfully connect to MS SQL server (with the same table).

The problem starts when I try to delete rows from the table.
In access it works great obviously, But with MS SQL I get exception when calling udpate (Wrong syntax near '.').

Is it possible at all to use strong typed datasets to connect to both MS SQL and ACESS. If yes how?

Thanks,
Yaniv

Re: Changing between mssql and access through connection string

Mycroft Holmes9-Jul-08 17:36

Mycroft Holmes

9-Jul-08 17:36

The SQL syntax is different between Access Dead | X|

and SQL server. You are not going to be able to just change connection strings - you will need to duplicate some/all your SQL statements to conform with the TSQL syntax.

Never underestimate the power of human stupidity
RAH

Re: Changing between mssql and access through connection string

Y_R12-Jul-08 21:40

Y_R

12-Jul-08 21:40

Hi,

Thanks for your reply.

I ran the sql query: DELETE FROM table1 WHERE id = 1
on both access and ms sql and it worked fine.

But I don't create the queries manually, It is created by the adapter (I think) of my strong typed database ( so I don't know whats really going there).

Is there a way I can view the query created by it?

Sincerely yours
Y.R.

Re: Changing between mssql and access through connection string

Mycroft Holmes13-Jul-08 12:39

Mycroft Holmes

13-Jul-08 12:39

Y_R wrote:
But I don't create the queries manually

This where you have a problem. You are using the MS built in tools and therefore have only limited knowledge and understanding of the mechanics of the database and the DAL.

I suggest you completely reengineer your application to use a proper data access layer where you have control and understanding of the DAL and database operations.

As for

Y_R wrote:
Is there a way I can view the query created by it?

I have absolutely no idea (but would expect it to be there somewhere) as I have only used the built in stuff enough to identify what a disaster it can be.

BTW this is not a rant against you, everyone has to start somewhere and you have asked intelligent questions and shown a willingness to learn. Do some reasearch into DALs, there are plenty of examples around.

Never underestimate the power of human stupidity
RAH

Re: Changing between mssql and access through connection string

Y_R13-Jul-08 22:53

Y_R

13-Jul-08 22:53

Before we started the project we checked and found strong typed datasets the most fitting solution, Though some people strongly advised against it.

I works fine in a different area of my project (With mysql), So I don't know why people dislike it so much, But I have little experience with databases so I guess you have your reasons.

Anyway, For my specific problem I will do as you suggested and further my research in to the DALs.

Thank you very much you really helped me.

Sincerely yours
Y.R.

How Can I Do This Select Statment

Zeyad Jalil8-Jul-08 20:58

Zeyad Jalil

8-Jul-08 20:58

hi, All

please,, correct this sql statement (select column name1 ,count(column name2) from table name where columnname2='1',column name3 from table name )..

thanks for all person who help me..... Confused | :confused:

Re: How Can I Do This Select Statment

Ashfield8-Jul-08 21:58

Ashfield

8-Jul-08 21:58

zead wrote:
where columnname2='1',column name3

this bit does not make sense.

where columnname2 = '1' AND columnname3 ....

Do I get anything for doing your homework?

Bob
Ashfield Consultants Ltd

Re: How Can I Do This Select Statment

Andy_L_J9-Jul-08 0:39

Andy_L_J

9-Jul-08 0:39

In this exceptional case,....we might point him in the ~~right~~ general direction? Wink | ;)

I don't speak Idiot - please talk slowly and clearly

normalization terms

Pankaj Garg8-Jul-08 19:18

Pankaj Garg

8-Jul-08 19:18

can u explain the meaning of multivalued dependency and functional dependency with an example .

If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!

Re: normalization terms [modified]

leoinfo9-Jul-08 4:08

leoinfo

9-Jul-08 4:08

Functional dependency example: a Human Readable Code (like the hex value CFADE) created based on an Internal ID (850654) of a record (the Internal ID precisely determmine the hex value )
(One)Internal ID -> (One)Human Readable Code

Multivalued dependency example:
(One)Internal ID -> (One)Human Readable Code -> (Multiple)Account Number

You can find some examples here:

http://en.wikipedia.org/wiki/Functional_dependency[^]

http://en.wikipedia.org/wiki/Multivalued_dependency[^]

modified on Wednesday, July 9, 2008 10:57 AM

MS Access Connection String

kadkir8-Jul-08 17:41

kadkir

8-Jul-08 17:41

Hi all,
How do I connect to a MS-access data base which is present in localhost from an application runnig on web server. I want to send data from DB Server (sqlserver 2000) which is on remote machine to Ms-Access DB present in localhost.
After googling I found the following
Open connection to Access database located on a remote server:

"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"

But, here my localhost IP varies depending on the client's system on which the web application is running. So, how do I set the clients' IP value or is there any other way to acheive this.

Thank U all,
Kiran

Re: MS Access Connection String

Jack Brando8-Jul-08 19:19

Jack Brando

8-Jul-08 19:19

try this...

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server_name\db_folder\Your_Database_Name.mdb;User Id=admin;Password=;

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