Database Discussion Boards

Re: list of unmatched countries from multiple tables using sql server

Mycroft Holmes6-Sep-09 20:46

6-Sep-09 20:46

Select * from tCountry where country not  in (Select country from pcountry)

Re: list of unmatched countries from multiple tables using sql server

εїзεїзεїз6-Sep-09 20:47

εїзεїзεїз

6-Sep-09 20:47

i will not talk about Normalization here and will post only regarding your question only
try to use "NOT IN " in query

select ID from T_country where id not in (select ID from P_country)

if you dont have any keys then use the common fields exists in both the tables like in your case you can use Country Name but will not recommend you makes query so slow try to use any numeric fields

and you can also use not equal join

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

Problem to configuring reporting services

hdv2126-Sep-09 4:47

hdv212

6-Sep-09 4:47

Hi i've installed ssrs in my sql server database machine (with xp sp2)
but when i go to this link : http://localhost/reports i got this error :

The report server is not responding. Verify that the report server is running and can be accessed from this computer.

i tried this 2 links, but still i got the same error :

http://technotes.towardsjob.com/sql-server/troubleshooting-ssrs-error-the-report-server-is-not-responding/
http://sanjaysainitech.blogspot.com/2009/03/error-report-server-is-not-responding.html

Note : in second link i don't know how to give permisson to my account in .config files. maybe that's my problem

any help ?

IMPORT DBF File into SQL 2000

Member 46502876-Sep-09 0:33

Member 4650287

6-Sep-09 0:33

Dear all,

I have 3 Dbf files that have to be imported to Sql Server 2000.Master table & dbf files are same fields n data type.Can anyone help me ?

Re: IMPORT DBF File into SQL 2000

εїзεїзεїз6-Sep-09 20:39

εїзεїзεїз

6-Sep-09 20:39

you want to merge them or want to overwrite ?

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

Re: IMPORT DBF File into SQL 2000

Member 46502877-Sep-09 0:31

Member 4650287

7-Sep-09 0:31

thanks for reply.
I just want to merge with old datas.

Re: IMPORT DBF File into SQL 2000

εїзεїзεїз7-Sep-09 6:08

εїзεїзεїз

7-Sep-09 6:08

You can use Import Data Option exists in Sql Server but it will not let you insert data if their are any constrains made on the column of the tables means if their are constrains like no duplicate etc... then it will not let you otherwise it will work

here are some softwares for merging data also

http://wareseeker.com/free-sql-merge-tables/[^]

i made one utility which exports data into an xml file then can import that data into sql server if you can make a utility then it will be another option

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

Trigger not firing in SQL Server 2000

debsoft755-Sep-09 19:29

debsoft75

5-Sep-09 19:29

Hi All,

I have created a Insert Trigger and a Update Trigger on a table in a database.

Now an external application (developed in .NET and I have no source code access) makes an insert successfully in the table.
But the trigger doesn't fire.

When I directly execute a insert script on the table from SQL Query Analyzer, then the trigger fires but when insert is done by the external application (.NET app) the trigger doesn't fire.

Can somebody help me out with this...I'm completely clueless as to why it is behaving this way.

Thanks
Debsoft

Re: Trigger not firing in SQL Server 2000

Abhishek Sur6-Sep-09 0:05

Abhishek Sur

6-Sep-09 0:05

This is weird.. If any insert operation is made trigger gets fired automatically.

Open SQL Server profiler -> and check what exactly is running from inside that application. It must be invoking some insert statement on the database.

Smile | :)

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.

SQL Server FDHOST

mhh1125-Sep-09 5:48

mhh112

5-Sep-09 5:48

MSDN has a very nice description on how to launch FDHOST. However, it does not fit my computer settings. I do as it says-
Start/All Programs/ Microsoft SQL Server 2008/Configuration Tools/SQL Server Configuration Manager

and at that point I don't have SQL Server 2008 Services. Right clicking anything does not give me MSSQLFDLauncher

I have
SQL Server Configuration Manager (local)
SQL Server Services
SQL Server Browser Stopped
SQL Server (SQLEXPRESS) Running
SQL Server Agent (SQLEXPRESS) Stopped
SQL Server Network Configuration
Protocols for SQLEXPRESS
Shared Memory Enabled
Named Pipes Disabled
TCP/IP Disabled
VIA Disabled
SQL Native Client 10.0 Configuration
Client Protocols
Name Order Enabled
Shared Memory 1 Enabled
TCP/IP 2 Enabled
Named Pipes 3 Enabled
VIA Disabled
Aliases
nothing

right clicking
produces the following alternatives:
Open
Refresh
View
Export List
Properties
New Alias

As far as I can see there is no way to start FDHOST from the SQL Server Configuration Manager.

So how do I start FDHOST? Or where do I find MSSQLFDLauncher and how do I start it? MSSQLFDLauncher is the Service Account for Full Text Search.

Mark H

Dynamic Report Generator Framework Design using XML and C# [modified]

RK KL5-Sep-09 3:30

RK KL

5-Sep-09 3:30

Hello experts,

I'm trying to design a framework for generating reports by storing the meta data in an XML.

The following are the sources (in both SQL / Oracle providers).

1) Tables/Views
2) Stored Procedures
3) OLAP Cubes

I'll write a wrapper to sync changes to these Tables/Views/SPs/Cubes so that the XML is up to date.

Tables/Views
============
The user will be able to store subset of the columns and filter criteria (1 or many) and should be able to run the same report at a later stage.

Stored Procedures
=================
Whenever the user wants to run the stored procedure, he/she will supply the parameters and get the output.

OLAP Cubes
==========
Predetermined cubes and/or cubes with specific members as parameters.

I'm thinking the following way using strategy pattern for retrieving data.

1) IReportData: Gets the report data in a dataset/cellset
2) IReportParameters: Gets report parameters (For SPs?)
3) IReportFilters: Report filters / criteria /criteria set
3) IReportSQL: The final executable SQL
4) IReportFormat: Final rendering methodology

What do you guys think about this? Do you have any design pattern (s) that you would like to recommend?

Thanks,

RK.

You can never try. You either do it or you don't.

modified on Saturday, September 5, 2009 3:02 PM

Re: Dynamic Report Generator Framework Design using XML and C#

Mycroft Holmes5-Sep-09 13:08

Mycroft Holmes

5-Sep-09 13:08

You might want to take this to the design forum for a wider response.

Ever since I first heard of XML, way back in the 90s, I figured you should be able to use XSLT to format a report and then chuck an xml data file at it for generic type reports. Never followed up on it as I loathe XML.

Never underestimate the power of human stupidity
RAH

Re: Dynamic Report Generator Framework Design using XML and C#

RK KL5-Sep-09 13:50

RK KL

5-Sep-09 13:50

Thank you! I posted it in the design and architecture forum. I did not know it was there.

You can never try. You either do it or you don't.

Managing Database Updates

Phillip Donegan3-Sep-09 23:19

Phillip Donegan

3-Sep-09 23:19

Hi everyone.

For my current employer I've developed a few systems and deployed them. The deployment of the databases themselves has always been fairly easy. I use migratordotnet to update the schema as I continually develop these systems. That bit works great for me and I've never had any problems.

The problem I am starting to have though is that as the number of releases comes down now to maybe 3 or 4 a year. The schema structures are usually vastly different and this gives me a real headache when I need to migrate the data from a previous version to the latest schema. At the moment to do this I'm writing custom migrations for each release. However the database now has over 60 tables so these migrations are getting very long and more and more bugs are starting to appear.

So my question is how does everyone else tackle this problem? Are there industry standard approaches that I'm not aware of? Are the standard tools that I'm not using that help with this?

Any help / links to docs would be great!

Phil

Re: Managing Database Updates

Johan Hakkesteegt4-Sep-09 3:15

Johan Hakkesteegt

4-Sep-09 3:15

The basic problem with your predicament, is that even if you could find a third party tool to do the migrations for you, you would still always have to do the mapping and possible conversions, which I suppose are the main part of the work anyway.

I think your best bet is to either start designing your database preemptively, or employ an add-and-recycle strategy when writing applications (i.e. as necessary, just only add new tables and/or columns, and as much as possible rename and/or convert existing tables and/or columns). This way you can just leave the existing databases be, and maybe clean them up a little every once in a while when you have time.

Admittedly, I may not have understood the full depth of your plight.

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

Re: Managing Database Updates

Phillip Donegan4-Sep-09 3:30

Phillip Donegan

4-Sep-09 3:30

No I think you've hit the nail on the head. I've asked a few friends who work in different businesses what they do and they say the same that basically i have to write a migration or only add to the schema (which I hadn't thought of as an approach so thanks for that).

Oh well I'll struggle with these queries then.

Thanks
Phil

Re: Managing Database Updates

Johan Hakkesteegt4-Sep-09 3:41

Johan Hakkesteegt

4-Sep-09 3:41

This may or may not be of use to you, but for example our SAP system uses a control database.

In other words, you could make one database that just contains information about the schemas of your other application databases. This can help you keep better track of what different columns and formats, etc, you have, it can help you make development charts, upgrade / migration plans, and you can use it to run database checks (see if an application database has the correct schema, and such), and you can use it to build and store migration queries (or templates) there.
Another nice advantage is, if you ever have to build multiple language GUIs (english and spanish for example), you can use it to store common translations for columns and controls.

Good luck with it.

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

Re: Managing Database Updates

Mycroft Holmes5-Sep-09 13:18

Mycroft Holmes

5-Sep-09 13:18

Phillip Donegan wrote:
maybe 3 or 4 a year

I would think if you are deploying a major schema change (vastly different) every 3-4 months then you must have an extremely dynamic business model or somebody got the design wrong, and continues to get it wrong.

I usually work on single project contracts and have almost always used the migrate method, a PITA but then the business does not expect to repeat this, ever. However I am currently in a position where I am using the incrementing schema as the changes are fairly minor over a long period. The problem I find is the business does not necesarily keep up with the changes, be prepared for, why is this info not being up dated anymore type questions.

Never underestimate the power of human stupidity
RAH

Re: Managing Database Updates

Phillip Donegan6-Sep-09 22:19

Phillip Donegan

6-Sep-09 22:19

You know how it is, a request for a change or enhancement comes in, that requires some new tables. Do this 5 or 6 times and you spot a better way of doing it so decide to change the schema, then your old data is screwed to you have to write a monster migration!!

Re: Managing Database Updates

Mycroft Holmes6-Sep-09 22:33

Mycroft Holmes

6-Sep-09 22:33

I now find I have quite a few "attribute" tables in my schemas. Customer now wants 2nd mobile - add a phone attribute linked to the customer table.

I have one app that is ALL attributes, it tends to get nasty when trying to retrieve data, monstrous number of joins. Which goes to prove you can go too far down that path.

help writing query

michaelgr13-Sep-09 22:21

michaelgr1

3-Sep-09 22:21

Hello,

I write a query (combine of 2 queries using UNION):

query1
UNION
query2

I want the result of query 2 (there is only one result in query2) will be placed in the last row of the results of the big query.
I mean the results of query1 will be in the first rows and the result of the query2 will be in the last row.
How can i do it?

Re: help writing query

Blue_Boy3-Sep-09 22:41

Blue_Boy

3-Sep-09 22:41

Example

select * from table1 <br />
union<br />
select * from table2

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Re: help writing query

michaelgr13-Sep-09 23:36

michaelgr1

3-Sep-09 23:36

OK,
I know that.
But the problem is that the results are mixed.
I want the results of the second query will be the last (in the last rows of the result big table)

Union set theory

David Mujica4-Sep-09 3:02

David Mujica

4-Sep-09 3:02

Remember that SQL unions are based on set theory, so if value A is from table 1 and value A is also from table 2 you would only get one value A is your result set. By introducing the sort indicator that "Ashfield" suggests, you would get 2 values of A.

You would get 1,A and 2,A.

I'm not sure if this would impact your algorithm, but I just wanted to make you aware of it.

Cheers,
David

Re: help writing query

Ashfield4-Sep-09 1:17

Ashfield

4-Sep-09 1:17

The easiest way I know of is to introduce a sorting column

select '1' as seq,* from table1
union
select '2' as seq from table2
order by seq

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

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