Database Discussion Boards

Pete O'Hanlon5-Nov-07 0:01

5-Nov-07 0:01

So, isn't an automated approach a better choice then? Remove the danger that changes are missed between the dev and test environments.

Deja View - the feeling that you've seen this post before.

Re: Database Testing Concurrency

Brady Kelly5-Nov-07 0:04

Brady Kelly

5-Nov-07 0:04

It is and will be, but for this release an untested, automated approach seems a bit risky. I would like to move toward that though, using November to verify the the automated tasks before a December and future releases.

MY NEW BLOG

Re: Database Testing Concurrency

SimulationofSai5-Nov-07 0:08

SimulationofSai

5-Nov-07 0:08

I wouldn't worry too much about it in a testing/development environment like what's being intended here. It's not production and whatever error that comes out of using DDL triggers are traceable and rectifiable.

SG
Cause is effect concealed. Effect is cause revealed.

Re: Database Testing Concurrency

Pete O'Hanlon5-Nov-07 0:22

Pete O'Hanlon

5-Nov-07 0:22

I always worry whenever I hear people comment that consistency between dev and testing isn't too big a deal. The issue you have here is that you introduce something else into your testing process that doesn't need to be there. Why is this an issue?

Well, you have a development environment, which may or may not be different to a testing environment, which may or may not be different to your production environment. How do you ensure that what you did in the development phase makes it into the production phase?

Testing should replicate the production environment as much as is practicable, and should really be done up front. It shouldn't need manual intervention to ensure that "script A is run and then script B and then script C, and oh yes, we've got to run script D as well - mustn't forget that because it recreates the views that only get hit once a week, but are absolutely vital".

Deja View - the feeling that you've seen this post before.

Re: Database Testing Concurrency

SimulationofSai5-Nov-07 19:48

SimulationofSai

5-Nov-07 19:48

I agree with your point that the test env and the production env must be as similar as possible. But I can still write "intelligent" triggers that check for db existence, schema mismatches etc beforehand.
Our products go through QA in a lot of separate teams and it's impossible to replicate the production environment in all the QA setups. We neither have ownership of the test beds nor can we dictate anything. We just put in our own mechanism that gives us better insight that what the blunt test results which the QA gives us.

Triggers are not a bad thing to have. It's just plain stupid to have them in an OLTP table. But in a situation where it's fired only rarely, they can reduce a lot of code needed for a workaround.

SG
Cause is effect concealed. Effect is cause revealed.

SQL Database Constraints

Vimalsoft(Pty) Ltd4-Nov-07 22:37

Vimalsoft(Pty) Ltd

4-Nov-07 22:37

hi All

Am migrating from SQL2000 to SQl2005. the database had been migrated. i would like to know if i migrate, the Constraint in my table like "Primarykey", does it get shipped on the migration Process or they get lost? must i start create a Primary key for each table?

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: SQL Database Constraints

SimulationofSai5-Nov-07 19:50

SimulationofSai

5-Nov-07 19:50

If you've used the setup migration wizard, everything usually works fine. But in case you used DTS/SSIS, the PK's may not have been replicated and you need to add constraints manually.

SG
Cause is effect concealed. Effect is cause revealed.

To get son and all grandsons

VB.Net Developer4-Nov-07 22:07

VB.Net Developer

4-Nov-07 22:07

parent son
A B
A C
A D
D E
D F
D G
G I
G J
i blank
j blank
b blank
c balnk

I need a query to get son and grandson, grandgrand sons (till Entire generation) …. Of ‘A’…..

Re: To get son and all grandsons

Pete O'Hanlon4-Nov-07 22:30

Pete O'Hanlon

4-Nov-07 22:30

VB.Net Developer wrote:
I need a query to get son and grandson, grandgrand sons (till Entire generation) …. Of ‘A’…..

Very good. What have you tried so far? Plus, what database is this query in.

We don't do homework for you, and we don't do your thinking for you. We do help you if you've tried something and can't get it to work.

Hint - if you are using SQL Server, take a look at Common Table Expressions (CTEs) to accomplish this.

Deja View - the feeling that you've seen this post before.

Re: To get son and all grandsons

Grampa Simpson6-Nov-07 2:49

Grampa Simpson

6-Nov-07 2:49

Trying to be a bit more helpful

But the solution has to do with CTEs, at least if your using SQL Server as this is not standard SQL:

<br />
with tmp(parent, son) as (<br />
select <br />
   tab.parent, tab.son from tab<br />
where <br />
    tab.parent='A'<br />
<br />
union all<br />
<br />
select <br />
   tmp.super, tab.son<br />
from <br />
   tmp join tab on tmp.son=tab.parent<br />

Beware of loops! In that case you need to introduce a recursion counter or a condition that restricts son<>'A'.

Inserting Leading Zero for datepart(MM and datepart(dd

Support1234-Nov-07 20:33

Support123

4-Nov-07 20:33

Hi all,

I Have the following statement...

SELECT convert(varchar,convert(varchar,datepart(yyyy,GetDate())) + case Len(datepart(MM, GetDate())) when 2 then convert(varchar,datepart(MM,GetDate()),101) when 1 then '0' + convert(varchar,datepart(MM,GetDate()),101) end + case Len(datepart(MM, GetDate())) when 2 then convert(varchar,datepart(dd, GetDate())) when 1 then '0' + convert(varchar,datepart(dd, GetDate())) end) AS [TodayDate]

Now what i planned this to do is add the leading zero in front of the day and month figure if the length is 1 to make the length 2... like this...
20071105 (notice the zero in front of the 5)

BUT it is still showing it like this...
2007115

How do i go about getting the zero in front of the 5 ??? My output needs to look like YYYYMMDD

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: Inserting Leading Zero for datepart(MM and datepart(dd

Support1234-Nov-07 23:28

Support123

4-Nov-07 23:28

Never Mind....

i have made this a bigger problem then it actually is... SORRY about that...

The easiest way to do this is:

CONVERT(CHAR(8), GetDate(), 112)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: Inserting Leading Zero for datepart(MM and datepart(dd

r a j u u4-Nov-07 23:45

r a j u u

4-Nov-07 23:45

better you can use like...
CONVERT(CHAR(8), GetDate(), 112)

/*101-12/17/2006
102-2006.12.17
103-17/12/2006
104-17.12.2006
105-17-12-2007
106-17 Dec 2006
107-Dec 17, 2006
108-00:00:00
109-Dec 17 2006 12:00:00
110-12-17-2006
111-2006/12/17
112-20061217
113-17 Dec 2006 00:00:00
114-00:00:00:000*/

Cast into Numeric

banker_kiran4-Nov-07 19:56

banker_kiran

4-Nov-07 19:56

Hi,
Problem with converting varchar into Numeric

select DEFINITION_DETAIL_DATA.SMID, Cast(Data_Value as Numeric)as Val from DEFINITION_DETAIL_DATA
inner join DEFINITION_MST on DEFINITION_MST.SMID = DEFINITION_DETAIL_DATA.SMID Where DEFINITION_MST.MPID = 176
and Convert(Numeric,Data_Value) > 10000

Value without where condition shows like.

1 2500
2 3500
3 5000
4 10000
5 15000
6 20000

but when i add Convert(Numeric,Data_Value) > 10000 in query then its give me
an error.
Error converting data type varchar to numeric.

kiran banker

Re: Cast into Numeric

r a j u u4-Nov-07 20:24

r a j u u

4-Nov-07 20:24

plz try this

select DEFINITION_DETAIL_DATA.SMID, Cast(Data_Value as Numeric)as Val from DEFINITION_DETAIL_DATA
inner join DEFINITION_MST on DEFINITION_MST.SMID = DEFINITION_DETAIL_DATA.SMID Where DEFINITION_MST.MPID = 176
and val > 10000

Re: Cast into Numeric

banker_kiran4-Nov-07 20:27

banker_kiran

4-Nov-07 20:27

giving an error
invalid column namd

kiran banker

Re: Cast into Numeric

r a j u u4-Nov-07 20:50

r a j u u

4-Nov-07 20:50

select DEFINITION_DETAIL_DATA.SMID, Cast(Data_Value as Numeric)as Val from DEFINITION_DETAIL_DATA
inner join DEFINITION_MST on DEFINITION_MST.SMID = DEFINITION_DETAIL_DATA.SMID Where DEFINITION_MST.MPID = 176
and Cast(Data_Value as Numeric)as Val > 10000

Re: Cast into Numeric

banker_kiran4-Nov-07 23:14

banker_kiran

4-Nov-07 23:14

still no working

but i write like this

select DEFINITION_DETAIL_DATA.SMID, Convert(Decimal,Data_Value) as Val from DEFINITION_DETAIL_DATA
inner join DEFINITION_MST on DEFINITION_MST.SMID = DEFINITION_DETAIL_DATA.SMID Where DEFINITION_MST.MPID = 176 AND DEFINITION_DETAIL_DATA.SMID = 204
and Convert(Decimal,Data_Value) > 1

then it works

but i want like this

select DEFINITION_DETAIL_DATA.SMID, Convert(Decimal,Data_Value) as Val from DEFINITION_DETAIL_DATA
inner join DEFINITION_MST on DEFINITION_MST.SMID = DEFINITION_DETAIL_DATA.SMID Where DEFINITION_MST.MPID = 176
and Convert(Decimal,Data_Value) > 1

kiran banker

Re: Cast into Numeric

Muhammad Shahid Farooq5-Nov-07 3:48

Muhammad Shahid Farooq

5-Nov-07 3:48

Your query is absolutely right. But “Data_Value” column contains invalid data, so this error occurs. Invalid data means column contains the following data:
-Null
-Empty
-Special Characters
-Spaces
Please check the data if contain invalid data then remove & run query, your required result obtained.

Re: Cast into Numeric

banker_kiran5-Nov-07 18:12

banker_kiran

5-Nov-07 18:12

it contains following data.
SMID Data_Value
1 2500
2 3500
3 5000
4 10000
5 15000
6 20000

kiran banker

Databse ambiguity

sindhutiwari4-Nov-07 19:34

sindhutiwari

4-Nov-07 19:34

HI,
I am doing a project (.net) in which i have added a folder named data and added a ms access database mdb file to it ..........
So when ever i am running my project a copy of data folder is getting stored in the
debug folder and the updates and manipulations which i am doing are getting reflected in the database which is present in the debug folder
and at the time or retrieving data is coming from the data folder which i created
So please tell me a way such that the mdb file must be at a single place
regards
sindhu tiwari

its me sid

Deploying report in Sql Server Reporting Services

ankprasanna4-Nov-07 18:07

ankprasanna

4-Nov-07 18:07

I designed a report in Sql Server 2005 express edition with windows authentication mode and build it. In building no errors. While deploying report it is asking for username & password.
I deployed manually by using URL http://localhost/reportserver
report was deployed but in report viewer in asp.net webform, it is giving error message that Page not found

Prasanna

Problem with my connection

sudany_zool4-Nov-07 16:13

sudany_zool

4-Nov-07 16:13

need some help here some error appears when i am tryin to run my software
the error is:
"An error has occured while establishing a connection to the server when connection to SQL Server 2005 this failure maybe caused by the fact that under the default setting SQL does not allow remote connections.(provider:Named Pipes provider, error 40-could not open a connection to SQL server)".

Re: Problem with my connection

John Gathogo4-Nov-07 18:21

John Gathogo

4-Nov-07 18:21

Use SQL Server Service Area Configuration Manager to allow remote connections. Also allow both TCP/IP and Named Pipes protocols. That should solve your problem.

Help Help Help

Ahmad Adnan3-Nov-07 23:13

Ahmad Adnan

3-Nov-07 23:13

help plz
where do i find Authonication mode in sql server 2005
coz my configuration is set to windows Authonication and i want to change it into mixed

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