Showing posts with label couple. Show all posts
Showing posts with label couple. Show all posts

Friday, March 23, 2012

Problem with SP3

Hi,
Couple of users got this error after installing (MSDE) SP3.
Does anybody know what this means and what to do?
Invalid column name 'published_in_tran_pub'.
(Source: xxxxxxxxxxxxx (Data source); Error number: 207)
-----The
process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
-----
In use: Merge replication and static filtered publications.
-Katja
This column is related to a new merge agent parameter added with sp3. Can
you check that sp3 has been fully (successfully) installed on distributor,
publisher and subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tuesday, March 20, 2012

Problem with script

We've got a couple of scripts that we routinely run on several SQL
servers, and they usually run fine. But we're trying to run them on one
particular server, and we're running into problems. I'm no expert with
SQL, but two DBA's who I work with have spent hours on this and they're
stumped, so I don't feel so stupid. Maybe someone here can help.

Basically, we've got a .bat file that calls a .sql file. Within the
..bat file, isql is called a couple of times with no problems. Within
the .sql file, there are several lines like:

exec master..xp_cmdshell @.@.cmd

@.@.cmd has previously been defined as a text string that begins with
isql.

Whenever this runs, we get the error:

'isql' is not recognized as an internal or external command,
operable program or batch file.

In other words, it can't find the file isql.exe.

We already made sure that the Windows PATH variable for the user
running the .bat file includes the folder that has isql.exe in it.
Someone said that they once had a problem where that folder had to be
the first folder in the PATH variable in order to recognize isql, so we
even made that change (and logged off and back in, then checked the
path from a DOS prompt to make sure the change took effect).

Anyone know anything else to check that would explain why our sql
script would be unable to find the isql.exe command? Any help would be
greatly appreciated.

--RichardOk, I've got another detail that I was just looking at that seems like
it might be relevant. The initial .bat file uses isql to run the .sql
file. So maybe that has something to do with why the .sql file (run
from within isql) can't access isql? As I said, this works on most
other servers, so it's got to be some sort of security or something.

Would it the .sql file run from within isql use the same user security
as the .bat file that ran it? Or does it have seperate user access?

--Richard|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> Whenever this runs, we get the error:
> 'isql' is not recognized as an internal or external command,
> operable program or batch file.
> In other words, it can't find the file isql.exe.
> We already made sure that the Windows PATH variable for the user
> running the .bat file includes the folder that has isql.exe in it.

That's does count here. What counts is how the PATH for the user
under which SQL Server itself looks like.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> > Whenever this runs, we get the error:
> > 'isql' is not recognized as an internal or external command,
> > operable program or batch file.
> > In other words, it can't find the file isql.exe.
> > We already made sure that the Windows PATH variable for the user
> > running the .bat file includes the folder that has isql.exe in it.
> That's does count here. What counts is how the PATH for the user
> under which SQL Server itself looks like.

That's the part I'm having trouble figuring out, I think. What user's
PATH would it be using when calling isql from within a .sql script that
was run by isql within a .bat file that was kicked off by a user?
Apparently, it's not using the PATH of the user who ran the .bat file.
I just don't know what user's PATH is being used.

As a temporary solution, I copied isql.exe to C:\Windows\System32,
since I know that'll be in the PATH of all users, and that worked.

--Richard|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> That's the part I'm having trouble figuring out, I think. What user's
> PATH would it be using when calling isql from within a .sql script that
> was run by isql within a .bat file that was kicked off by a user?
> Apparently, it's not using the PATH of the user who ran the .bat file.
> I just don't know what user's PATH is being used.

Just like any other Windows process, SQL Server logs into to Windows
with a user and a password. Ot it logs in as LocalSystem.

To find out how SQL Server, do this on the server machine: In Control
Panel find Administrative Tools. Go there, and find the Services applet.
Open Services, and find the SQL Server service. Double-click it to see
Properties. Log On information is on the second tab.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> > That's the part I'm having trouble figuring out, I think. What user's
> > PATH would it be using when calling isql from within a .sql script that
> > was run by isql within a .bat file that was kicked off by a user?
> > Apparently, it's not using the PATH of the user who ran the .bat file.
> > I just don't know what user's PATH is being used.
> Just like any other Windows process, SQL Server logs into to Windows
> with a user and a password. Ot it logs in as LocalSystem.
> To find out how SQL Server, do this on the server machine: In Control
> Panel find Administrative Tools. Go there, and find the Services applet.
> Open Services, and find the SQL Server service. Double-click it to see
> Properties. Log On information is on the second tab.

Because I know that the .bat file has the security access of the person
who started it, I thought the sql script run by the bat file would, as
well. But apparently, it always has the security access of the SQL
service, which I didn't realize, but it makes perfect sense. Thanks for
the clarification.

I checked, and the SQL service does log on as LocalSystem. So now for
the next obvious question: Since LocalSystem isn't a real user, how do
I change its PATH variable to include the folder that contains isql.exe
and other necessary SQL commands? I've hunted around a little, and I
haven't been able to find anything on changing the settings of
LocalSystem. I only know how to change the PATH of the username that
I'm logged in as.

--Richard|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> I checked, and the SQL service does log on as LocalSystem. So now for
> the next obvious question: Since LocalSystem isn't a real user, how do
> I change its PATH variable to include the folder that contains isql.exe
> and other necessary SQL commands? I've hunted around a little, and I
> haven't been able to find anything on changing the settings of
> LocalSystem. I only know how to change the PATH of the username that
> I'm logged in as.

How to change the path for LocalSystem sounds like a question for
a Windows newsgroup. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

Problem with query on badly designed db

I've asked about this at a couple of other forums, but no one was able to help me so I came to think of this place. I know there are a lot of professionals here and hopefully someone can help me out.

I've been asked to display a list of calendar events from an SQL Server database, but I'm having trouble. It seems to me that who ever designed this database made some mistakes.

I'm trying to list all events from tblEventMain, and below every event, a list of all dates and times that the event occurs. The list should be ordered by the earliest date. Every event can have several dates associated with it, but I want to order the list be the earliest of these dates.

This is what I tried, but I get duplicates no matter what I try since I need to include tblEventTimes in the query to be able to sort the events after the earliest associated date in.SELECT m.id, m.title, m.arrangedBy, m.location, m.price, m.tickets, m.weblink, c.title, t.[date], t.startTime
FROM tblEventMain m
INNER JOIN tblEventTimes t ON m.id = t.eventRef
INNER JOIN tblEventCategories c ON m.categoryRef = c.id
WHERE datediff(dd, GETDATE(), t.[date]) >= 0 AND m.categoryRef <> 25
GROUP BY m.id, m.title, m.arrangedBy, m.location, m.price, m.tickets, m.weblink, c.title, t.[date], t.startTime
ORDER BY t.[date], t.startTimeI've attached an image of the tables so you can see how it looks, but please tell me if anything is unclear.

Thanks very much in advance for any help you can give me!First, other than naming a datefield "date", this does not look like a poorly designed database. (I wouldn't have stored Time values separate from Date, but whatever...) I think there may be some confusion in what you are trying to do.

You say you have to include tblEventTimes in the query in order to sort the results, but aren't you actually including it in order to return all the event times associated with an event? I don't see where you are getting duplicate records in you result set, since all your joins are on primary keys. Each row in the result set should be different from any other row, by at least one field.

I'll take a guess at what you really want.

Your current sort order is:
EventDate, EventTime

What you want is:
EarliestOccuringEvent, EventDate, EventTime

Try adding this subquery to your FROM clause:

INNER JOIN (Select EventRef, Min(DateAdd(d, DateDiff(d, '1/1/9000', [date]), StartTime)) as MinDate
From tblEventTimes
Group by EventRef) FirstEvents
on m.id = FirstEvents.EventRef

...and use this as your ORDER BY clause:
ORDER BY FirstEvents.MinDate, t.[date], t.startTime

I assumed that your time values stored only time of day, and not the actual date. You may be able to get away with a simpler MIN function.

blindman|||Originally posted by blindman
You say you have to include tblEventTimes in the query in order to sort the results, but aren't you actually including it in order to return all the event times associated with an event?Well, I guess I gave up on the idea that I could get all the dates and times aswell, in one query. I guess I thought I would have to use another query for every event, to get its dates and times. What I mean is that if I only select the fields I want from tblEventMain, I don't get duplicates, but then I can't sort them after their associated dates and times in tblEventTimes.Originally posted by blindman I don't see where you are getting duplicate records in you result set, since all your joins are on primary keys. Each row in the result set should be different from any other row, by at least one field.Right now I seem to get a duplicate of an event for every associated date. You can see the results here:

http://www2.sodertalje.se/events/list.asp

Search for example after "O N D S K A N" in the page, and you'll see what I mean.

Let's say I have two events

The Movie
2003-10-10 12:30
2003-11-10 12:30
2003-12-10 12:30

The Exhibition
2003-10-10 12:00
2003-12-10 12:30
2003-13-10 12:30

In this case, "Exhibition" and its associated dates and times should be displayed first since its earliest date (even though it's stored in two different columns) is earlier than "The Movie has the earliest date, even though it has several. I hope this gives you a clearer idea of how I'm trying to list the events.Originally posted by blindman What you want is:
EarliestOccuringEvent, EventDate, EventTimeYes, that's exactly what I'm after. I'll try your suggested subquery and get back with the results.|||Since I'm not exactly an SQL wissard, I'm not sure how the query should look after adding your subquery. Here's what it looks like now, and below is a screenshot of the tblEventTimes table so you can see how it looks.SELECT m.id, m.title, m.arrangedBy, m.location, m.price, m.tickets, m.weblink, c.title AS category, t.[date], t.startTime
FROM tblEventMain m
INNER JOIN tblEventTimes t ON m.id = t.eventRef
INNER JOIN tblEventCategories c ON m.categoryRef = c.id
INNER JOIN (SELECT EventRef, MIN(DateAdd(d, DateDiff(d, '1/1/9000', [date]), StartTime)) AS MinDate
FROM tblEventTimes
GROUP BY EventRef) FirstEvents ON m.id = FirstEvents.EventRef
WHERE datediff(dd, GETDATE(), t.[date]) >= 0 AND m.categoryRef <> 25
ORDER BY FirstEvents.MinDate, t.[date], t.startTime

And here's the error message I get: Adding a value to a 'datetime' column caused overflow.|||I forgot the screenie:|||Typo!

"DateDiff(d, '1/1/9000', [date]), StartTime)"

should be:

"DateDiff(d, '1/1/1900', [date]), StartTime)"

blindman|||Ok, I had a chance to test it again today, and I'm sorry to say it didn't work. Check out the test page below where I display the query at the top, and then the results below (m.id and m.title).

http://www2.sodertalje.se/events/test.asp|||On behalf of blindman, I will attempt to answer this:

SELECT m.id,
m.title,
m.arrangedBy,
m.location,
m.price,
m.tickets,
m.weblink,
c.title,
t.[date],
t.startTime
FROM tblEventMain m
INNER JOIN (SELECT [eventRef], MIN([date] + [startTime])AS firsttime FROM [eblEventTimes] GROUP BY [eventRef]) sd ON m.[id] = sd.[eventRef] -- Calculates the first time in an event INNER JOIN tblEventTimes t ON m.id = t.eventRef
AND [date] + [startTime] = sd.firsttime -- Omit this if you want all the times back for an event
INNER JOIN tblEventCategories c ON m.categoryRef = c.id
ORDER BY sd.firsttime -- This ensures that the event with the first time comes first