Database

24-May-08 23:54

Wrong tool. An sql query will return you the data, not format it for consumption.

You need to manage the result set in your presentation layer rather than attempt to make the sql query do the job.

Never underestimate the power of human stupidity
RAH

Whats wrong in sql2005

AlexeiXX323-May-08 20:01

23-May-08 20:01

Hi, thanks for looking

I am upgrading my app from 2000 to 2005
Some SPs that im using dont return the rows in the expected sort anymore

I have SPs calling views, the sorting is applied in the view and the sp just filters the view
When i was using sql2000 the sorting was working right (as set in the view), but now in sql2005 rows are not returned in the correct sort
Do i have to specify an order by clause in each SP Or is there any setting that i must set??

Thanks in advance

Alexei Rodriguez

Mycroft Holmes25-May-08 0:00

25-May-08 0:00

When adding the order by clause to the view you will get the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.<br />

So I would figure all your sps will require an order by in their select. BTW I would not use top 100% just to get the order by into the view.

Never underestimate the power of human stupidity
RAH

AlexeiXX325-May-08 12:13

25-May-08 12:13

Thanks for your answer

What a huge change
I wonder what other surprises ill find on sql2005 and later in sql2008 Frown | :-(

I cant beleive that even visual studio server explorer added a wrong workaround, something that as i just read was never supposed to be used inside a view

Alexei Rodriguez

AlexeiXX326-May-08 8:43

26-May-08 8:43

I guess i just found the biggest problem with sql2005 Frown | :-(

When generating the scripts, with doesnt care about dependencies and when executing them, you know what happens Frown | :-(

Do you know any way to generate the scripts in the correct order?

Thnans in advance

Alexei Rodriguez

Mycroft Holmes26-May-08 12:27

26-May-08 12:27

I use sql compare (empty database) it does a good job. I have never tried between different versions.

Why not split it logically to table/views/procs then only the procs can be out of sequence.

Never underestimate the power of human stupidity
RAH

AlexeiXX326-May-08 12:43

26-May-08 12:43

Thanks for the reply

How im i supposed to use that?? where is that option?

Alexei Rodriguez

Mycroft Holmes26-May-08 13:54

Any way to attach a timestamp/rowversion column only to selected columns?

26-May-08 13:54

Sorry SQL Compare is a Red-Gate product not part of SQL Server.
clickety[^]

Never underestimate the power of human stupidity
RAH

supercat923-May-08 14:24

supercat9

23-May-08 14:24

I have a database which is used to pass information about certain entities between two programs. Each entity will have a record for each program, containing, in simplified form:

The ID of the entity
The identity (0 or 1) of the program that created the record
Poll Time (used mainly to confirm that both programs are checking the database in timely fashion--not used by the synchronization operations themselves)
Data for the other program
A timestamp/rowversion for that data
A timestamp/rowversion for the other program's data (copied from the other program's record when it's observed)

The first two fields would form a primary key. Each program would check its own timestamp/rowversion against the other programs' to see whether any updates were required.

Two related questions:

-1- What should I use for the timestamp/rowversion? Possibilities I can see:

-a- Using a date/time record for the timestamp would probably work, but it could break if a record gets updated twice quickly. The risk is probably more theoretical than actual, but I'd prefer to avoid something that is structurally not sound.

-b- A 'timestamp' field would seem almost ideal, except that I would want to be able to update the 'other program' timestamp field without bumping my own.

-c- A GUID would work, but GUIDs are bulky and ugly.

-d- In an earlier SQL Server I could have used a bigint and had the update store @@DBTS (a 64-bit integer that was bumped every time a database access occurred). Unfortunately, in newer versions @@DBTS doesn't get bumped unless a row containing a timestamp/rowversion field is updated.

-e- I could have each row contain an ignored timestamp/rowversion field and store @@DBTS in a field I'm actually interested in. That would work, but having the unused field just to force a @@DBTS update seems goofy.

-f- I could use another single-column table as a 'ticket dispenser'. Each time I want a number, do an insert, select SCOPE_IDENTITY(), and empty out the table. That would probably work, but again seems goofy.

What's the right approach?

-2- Is there any good way to find pairs of records, such that the entity ID's are equal, the record types are 0 and 1, and the "My Timestamp" field of the first does not math the "Other Timestamp" field of the second or vice versa, or is the data format described above really not a good design?

Find Instances

mehrdadc4823-May-08 10:42

mehrdadc48

23-May-08 10:42

Hi.
In a system that has SQL Server 2005, how can I know the instance names of the SQL Server?

Best wishes

Re: Find Instances

Alsvha23-May-08 21:01

Alsvha

23-May-08 21:01

If there is only 1 server installed, it has no instances - but only a default instance which does not need to be called with a name.
If there is more, then I think you need to talk to the guys who've set up multiple SQL Server instances.

---------------------------
Blogging about SQL, Technology and many other things

Re: Find Instances

mr.mohsen23-May-08 21:22

mr.mohsen

23-May-08 21:22

goto ths path
control pannel->administrative tools->computer management->
now you have to expand the services and application and in subtree expand the microsoft sql server
just it.
the instance name of your sql server is there.

nobody help you...
you have to help you yourself
and this is success way.

UPDATE JOIN problem

Kevin Brydon23-May-08 3:56

Kevin Brydon

23-May-08 3:56

Hey.

I understand that in sql server 2005 you can write statements to update several tables at once using join. Is that correct? I'm trying to this but keep getting an "Incorrect syntax near 'INNER'" error.

My SQL is

<br />
UPDATE Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID SET Courses.IsDeleted=1, Deliveries.IsDeleted=1, Courses.DeletedOn=@DeletedOn, Deliveries.DeletedOn=@DeletedOn WHERE Courses.CourseID=@CourseID<br />

Any help would be appreciated

Ashfield23-May-08 4:01

Ashfield

23-May-08 4:01

I've never seen this done, as far as I know you can only update one table in an update statment. Your error message suggests that it won't work either.

Bob
Ashfield Consultants Ltd

Kevin Brydon23-May-08 4:10

Kevin Brydon

23-May-08 4:10

Ok. It would be nice if you could do it though. how about DELETE JOIN? I'm sure you can do that

forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2541925&SiteID=1 (its a little slow today)

But for some reason my sql isnt working for that either. ive tried numerous combinations with this being the most sensible i think:

<br />
DELETE FROM Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID WHERE Courses.CourseID = @CourseID

exactly the same error : "Incorrect syntax near 'INNER'"

Alsvha23-May-08 5:51

Alsvha

23-May-08 5:51

You can - as far as I know - only update one base table at the time.

You can however use INNER JOINS for updateable queries - but still only when they affect one table such .

The problem in your syntax is not that you try to update two tables at once, but that you have the wrong syntax for updating using a join.

The easy way would be something like:

UPDATE Courses 
SET Courses.IsDeleted=1, Courses.DeletedOn=@DeletedOn
FROM Cources
INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID 
WHERE Courses.CourseID=@CourseID

And then run it a second time for updates in the Deliveries table.

If you even need to use a JOIN for your update when you can't do both tables at once, of course.

---------------------------
Blogging about SQL, Technology and many other things