Advanced SQL tutorials in Problem/Answer format

I'd like some challenging practice with SQL; general SQL-92 or MS specific (sql server or access).
There's a million sources on the Web, but most are like reference manuals. I already have MSDN with my VS 6.0 and .Net, as well as help files for 3 different versions of Access. I'm looking for something set up more like assignments or challenges. I found a couple sites like this:
SqlZoo.net and SQLCourse.com

SqlZoo has too many mistakes and vague questions. SQLCourse is pretty good, but no advanced stuff (some multiple Joins, subqueries, calculated fields and such). I like the idea of having the same related tables that you keep solving different problems with. This is more like a real-world situation. I would think that there would be a lot of question/answer tutorials for the MS supplied sample db's (Northwind, Pubs, etc.), but I haven't found any.

These forums are full of good questions posed by people, but they always concern unfamiliar data sources and I have to slog through the posts with my slow dial-up connection. I would rather do one big download and work at the problems offline.
Any suggestions?if you want the same related tables that you keep solving different problems with, your best bet is to search for the more common databases like northwind and sakila, perhaps by throwing several of their tables names into a search

if you want advanced question/answers, i humbly offer my own (the actual articles are on the techtarget.com site)

see http://r937.com/sqlate.cfm|||Thanks. There's a lot of good questions in one spot; I can easily download a bunch of those pages. Since the questions are all at the top of the pages, I can read them without seeing the answers. You helped save me a lot of searching time. If I do find some Northwind-based question tutes, I'll post the link.
Thanks again.|||Ahh, I find I learn more than enough from here :p
Just subscribe to SQL/Access threads that you are interested in.
I've knocked up a test database purely for solving problems on here -
trying to recreate peoples problems and see if I can solve them.

It's often a really good way to learn when you're given an unfamiliar problem, because you try and break it down into a generic solution (which means you can use it again later!).

There's my 2 cents. :cool:

Adobe Reader could not open ...

On a weekly basis I have over 100 subscription reports in pdf format emailed
to clients.
We are using SQL server 2000 and SQL Reporting Services 2000.
Several of the clients are reporting that they are unable to open their
report.
They get "Adobe reader could not open ReportName because it is either not a
supported file type or the file is corrupted... etc"
No custom code involved, all reports are generated in the same manner and
deployed in the same manner.
In some organizations several clients receive the same report and only one
or two of the clients reports a problem.
All these clients are using the same version of Adobe reader.
Is this a reader problem or a Report Server issue?
I am having a problem narrowing it down to the reader or generator!We are having the same issue. It was working for a while and now, user by
user, we are running into the same problem. Does anyone have any suggestions?
"Doug Gifford" wrote:
> On a weekly basis I have over 100 subscription reports in pdf format emailed
> to clients.
> We are using SQL server 2000 and SQL Reporting Services 2000.
> Several of the clients are reporting that they are unable to open their
> report.
> They get "Adobe reader could not open ReportName because it is either not a
> supported file type or the file is corrupted... etc"
> No custom code involved, all reports are generated in the same manner and
> deployed in the same manner.
> In some organizations several clients receive the same report and only one
> or two of the clients reports a problem.
> All these clients are using the same version of Adobe reader.
> Is this a reader problem or a Report Server issue?
> I am having a problem narrowing it down to the reader or generator!
>

ADO Connection port with clustered servers

I am using ADO to connect to clustered SQL Servers, how do I know which port to use?
My current connection string format is:
Provider=sqloledb;Network Library=DBMSSOCN;Data Source=<server ip>,<server port>;Initial Catalog=<my catalog>;/<user>/<pass>
But with clustered servers this will not work as ports are assigned dynamically.
Thanks
If the client machine is using MDAC 2.6 or greater it will figure out the
port number.
MDAC sends a UDP packet to the server to gather this information before
sending the login packet.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||So should I just not include the port number in the connection string?
"Kevin McDonnell [MSFT]" wrote:

> If the client machine is using MDAC 2.6 or greater it will figure out the
> port number.
> MDAC sends a UDP packet to the server to gather this information before
> sending the login packet.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

ADO Connection port with clustered servers

I am using ADO to connect to clustered SQL Servers, how do I know which port
to use?
My current connection string format is:
Provider=sqloledb;Network Library=DBMSSOCN;Data Source=<server ip>,<server p
ort>;Initial Catalog=<my catalog>;/<user>/<pass>
But with clustered servers this will not work as ports are assigned dynamica
lly.
ThanksIf the client machine is using MDAC 2.6 or greater it will figure out the
port number.
MDAC sends a UDP packet to the server to gather this information before
sending the login packet.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||So should I just not include the port number in the connection string?
"Kevin McDonnell [MSFT]" wrote:

> If the client machine is using MDAC 2.6 or greater it will figure out the
> port number.
> MDAC sends a UDP packet to the server to gather this information before
> sending the login packet.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

ADO adDBtimeStamp maps to a DATETIME type in SQL Server

I have a smalldatetime in SQL table, and I want to get this datetime
in the format yyyy-mm-dd hh:mm, but acorss ADO, I got yyyy-m-d
format. This is not I will. One way I thought is converting the
smalldatetime to a string. And this's not a elegant way. Because in
the case, I have to change many sql sentences to fix the problem.
Any suggestion? Thanks in advance.You are correct that date/time formatting should be done in application code
rather than SQL Server so this isn't really a SQL Server question. What
language are you using?
In VbScript, you could do something like:
MyDateTimeString = _
DatePart("yyyy", myDateTime) & "-" & _
Right("0" & DatePart("m", myDateTime), 2) & "-" & _
Right("0" & DatePart("d", myDateTime), 2) & _
" " & _
Right("0" & DatePart("h", myDateTime), 2) & ":" & _
Right("0" & DatePart("n", myDateTime), 2)
In a C#:
MyDateTimeString = myDateTime.Format("yyyy-MM-dd HH:mm");
In a VB.Net:
MyDateTimeString = myDateTime.Format("yyyy-MM-dd HH:mm")
--
Hope this helps.
Dan Guzman
SQL Server MVP
<1983ddd@.gmail.com> wrote in message
news:1184992038.598841.62590@.x35g2000prf.googlegroups.com...
>I have a smalldatetime in SQL table, and I want to get this datetime
> in the format yyyy-mm-dd hh:mm, but acorss ADO, I got yyyy-m-d
> format. This is not I will. One way I thought is converting the
> smalldatetime to a string. And this's not a elegant way. Because in
> the case, I have to change many sql sentences to fix the problem.
> Any suggestion? Thanks in advance.
>

ADO adDBtimeStamp maps to a DATETIME type in SQL Server

I have a smalldatetime in SQL table, and I want to get this datetime
in the format yyyy-mm-dd hh:mm, but acorss ADO, I got yyyy-m-d
format. This is not I will. One way I thought is converting the
smalldatetime to a string. And this's not a elegant way. Because in
the case, I have to change many sql sentences to fix the problem.
Any suggestion? Thanks in advance.
You are correct that date/time formatting should be done in application code
rather than SQL Server so this isn't really a SQL Server question. What
language are you using?
In VbScript, you could do something like:
MyDateTimeString = _
DatePart("yyyy", myDateTime) & "-" & _
Right("0" & DatePart("m", myDateTime), 2) & "-" & _
Right("0" & DatePart("d", myDateTime), 2) & _
" " & _
Right("0" & DatePart("h", myDateTime), 2) & ":" & _
Right("0" & DatePart("n", myDateTime), 2)
In a C#:
MyDateTimeString = myDateTime.Format("yyyy-MM-dd HH:mm");
In a VB.Net:
MyDateTimeString = myDateTime.Format("yyyy-MM-dd HH:mm")
Hope this helps.
Dan Guzman
SQL Server MVP
<1983ddd@.gmail.com> wrote in message
news:1184992038.598841.62590@.x35g2000prf.googlegro ups.com...
>I have a smalldatetime in SQL table, and I want to get this datetime
> in the format yyyy-mm-dd hh:mm, but acorss ADO, I got yyyy-m-d
> format. This is not I will. One way I thought is converting the
> smalldatetime to a string. And this's not a elegant way. Because in
> the case, I have to change many sql sentences to fix the problem.
> Any suggestion? Thanks in advance.
>

ADO adDBtimeStamp maps to a DATETIME type in SQL Server

I have a smalldatetime in SQL table, and I want to get this datetime
in the format yyyy-mm-dd hh:mm, but acorss ADO, I got yyyy-m-d
format. This is not I will. One way I thought is converting the
smalldatetime to a string. And this's not a elegant way. Because in
the case, I have to change many sql sentences to fix the problem.
Any suggestion? Thanks in advance.You are correct that date/time formatting should be done in application code
rather than SQL Server so this isn't really a SQL Server question. What
language are you using?
In VbScript, you could do something like:
MyDateTimeString = _
DatePart("yyyy", myDateTime) & "-" & _
Right("0" & DatePart("m", myDateTime), 2) & "-" & _
Right("0" & DatePart("d", myDateTime), 2) & _
" " & _
Right("0" & DatePart("h", myDateTime), 2) & ":" & _
Right("0" & DatePart("n", myDateTime), 2)
In a C#:
MyDateTimeString = myDateTime.Format("yyyy-MM-dd HH:mm");
In a VB.Net:
MyDateTimeString = myDateTime.Format("yyyy-MM-dd HH:mm")
Hope this helps.
Dan Guzman
SQL Server MVP
<1983ddd@.gmail.com> wrote in message
news:1184992038.598841.62590@.x35g2000prf.googlegroups.com...
>I have a smalldatetime in SQL table, and I want to get this datetime
> in the format yyyy-mm-dd hh:mm, but acorss ADO, I got yyyy-m-d
> format. This is not I will. One way I thought is converting the
> smalldatetime to a string. And this's not a elegant way. Because in
> the case, I have to change many sql sentences to fix the problem.
> Any suggestion? Thanks in advance.
>