Need to connect to SQL Server on other side of firewall, listening to a different port

Hi,
I want to communicate to SQL Server through my client. Server is listening
via port 16433 instead of defualt 1433.
And this particular server is on the other side of the firewall and it is in
a workgroup. Client Server is in a domain.
I have tried specifying ServerName,PortNo. but it does not work.
Any one know some other way to communicate to this server. Please help.
Thanks
PushkarCan you show us a connection string?
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:OS%23hNh2hFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to communicate to SQL Server through my client. Server is listening
> via port 16433 instead of defualt 1433.
> And this particular server is on the other side of the firewall and it is
in
> a workgroup. Client Server is in a domain.
> I have tried specifying ServerName,PortNo. but it does not work.
> Any one know some other way to communicate to this server. Please help.
> Thanks
> Pushkar
>|||Hi,
I am trying to connect through enterprise browser. And while registering for
the server I am giving server name as
"MyServer,16433"
where 16433 is port on which SQL Server is listening.
Thanks
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233IYy92hFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Can you show us a connection string?
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:OS%23hNh2hFHA.3288@.TK2MSFTNGP09.phx.gbl...
> in
>|||"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:u2to5K3hFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to connect through enterprise browser. And while registering
> for the server I am giving server name as
> "MyServer,16433"
> where 16433 is port on which SQL Server is listening.
Does the name MyServer resolve on your system? Names must be resolved to IP
addresses (or MAC addresses) using some mechanism, NetBIOS broadcasts, DNS,
WINS -- something. Try pinging it. If you have no reason to believe the
name will resolve, you have no expectation of connectivity with SQL Server.
If you have no mechanism for resolving the name, use the IP address of the
SQL box in place of the name.
Also, are you on the inside of the firewall, or the outside? If you are on
the outside, that port must have been opened by the admin of the firewall.
If you are on the inside, the firewall is *most*likely* not a significant
factor (though that is not exclusively true, a capable firewall can be
configured to block traffic in either direction, it just isn't usually done
that way.)
-Mark

> Thanks
> Pushkar
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%233IYy92hFHA.2484@.TK2MSFTNGP15.phx.gbl...
>|||Pushkar
Go to the Client Network Utility and create an alias to SQL Server that is
listening 16433 port
Make sure that TCPIP is at begining of the list.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:u2to5K3hFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to connect through enterprise browser. And while registering
for
> the server I am giving server name as
> "MyServer,16433"
> where 16433 is port on which SQL Server is listening.
> Thanks
> Pushkar
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%233IYy92hFHA.2484@.TK2MSFTNGP15.phx.gbl...
is[vbcol=seagreen]
>|||Hi,
Thanks it works by creating alias.
Thanks
Pushkar
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OfLT1d3hFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Pushkar
> Go to the Client Network Utility and create an alias to SQL Server that is
> listening 16433 port
> Make sure that TCPIP is at begining of the list.
>
> "Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
> news:u2to5K3hFHA.1412@.TK2MSFTNGP09.phx.gbl...
> for
> is
>

Need SQL that will return all records which include a particular v

If I understand you, your table is of OrderItems... One record for each line
item on every order... If that's the case, then the query would look like th
is
Select Distinct OrderID
From OrderItems I
Where Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'A')
And Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'B')
or...
Select Distinct A.OrderID
From OrderItems A
Join OrderItems B
On B.OrderID = A.OrderID
Where A.Item = 'A'
And B.Item = 'B'
"Larry Woods" wrote:

> I have a situation where I have multiple records, let's say ORDERS, and I
> have a record for each line item included in an order. Now, I want to fin
d
> all ORDERS that includes item A and item G, for example. An ORDER could
> include many additional items but the ORDER #'s that I want returns must
> include AT LEAST item A and G.
> How do I do this?
> TIA,
> Larry Woods
>
>Thanks. I'm going with the join for now. But, here is the next question:
The output of the SELECT is a list of OrderID's. Assuming we have
'customerID' in the Order table AND we have 'customerState' in the Customer
table, how do I "expand" the selection to only give me customers from
California (value="CA"), for example?
Customer Table Order Table
orderID <<<< (SELECT
OrderID...etc.)
customerID <<<<<< customerID (foreign key)
customerState
Again, TIA,
Larry Woods
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:02D90644-CC28-4B8E-9AEF-41E6C30EB034@.microsoft.com...
> If I understand you, your table is of OrderItems... One record for each
line
> item on every order... If that's the case, then the query would look like
this
> Select Distinct OrderID
> From OrderItems I
> Where Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'A')
> And Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'B')
> or...
> Select Distinct A.OrderID
> From OrderItems A
> Join OrderItems B
> On B.OrderID = A.OrderID
> Where A.Item = 'A'
> And B.Item = 'B'
>
> "Larry Woods" wrote:
>
I
find|||On Sat, 12 Mar 2005 04:51:46 -0700, Larry Woods wrote:

>Thanks. I'm going with the join for now. But, here is the next question:
>The output of the SELECT is a list of OrderID's. Assuming we have
>'customerID' in the Order table AND we have 'customerState' in the Customer
>table, how do I "expand" the selection to only give me customers from
>California (value="CA"), for example?
>Customer Table Order Table
> orderID <<<< (SELECT
>OrderID...etc.)
> customerID <<<<<< customerID (foreign key)
> customerState
>Again, TIA,
Hi Larry,
SELECT o.OrderID
FROM Orders AS o
INNER JOIN Customers AS c
ON c.CustomerID = o.CustomerID
INNER JOIN (SELECT OrderID
FROM OrderItems
WHERE Item IN ('A', 'B')
GROUP BY OrderID
HAVING COUNT(*) = 2) AS oi
ON oi.OrderID = o.OrderID
WHERE c.CustomerState = 'CA'
(untested - see www.aspfaq.com/5006 for the steps required to get a
tested solution)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Need some help!

Hi All
I want to blow up the database. Say I have 10 records in a particular table
and I want to increase it to 100 records for performance testing purposes. I
s
there a way to do it in SQL Server?
Thank you
Viccu.Viccu
create table #test (id int not null identity(1,1))
declare @.i int
set @.i=1
while @.i<=100
begin
insert #test default values
set @.i=@.i+1
end
select * from #test
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||How about that. In answering your question I grabbed a function that was
posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
microsoft.public.sqlserver.programming there are 12 posts between yours and
his).
Anyway...
--Lets pretend this is your table:
create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime)
go
--and here is your 10 rows of data
insert into #foo (col2, col3) values ('test 1', '1/1/2006')
insert into #foo (col2, col3) values ('test 2', '2/1/2006')
insert into #foo (col2, col3) values ('test 3', '1/3/2006')
insert into #foo (col2, col3) values ('test 4', '1/4/2006')
insert into #foo (col2, col3) values ('test 5', '5/1/2006')
insert into #foo (col2, col3) values ('test 6', '1/6/2006')
insert into #foo (col2, col3) values ('test 7', '1/7/2006')
insert into #foo (col2, col3) values ('test 8', '8/1/2006')
insert into #foo (col2, col3) values ('test 9', '1/9/2006')
insert into #foo (col2, col3) values ('test 10', '10/1/2006')
--Peter Larson's function:
CREATE FUNCTION dbo.fnSeqNumbers
(
@.LowLimit INT,
@.HighLimit INT
)
RETURNS @.Values TABLE
(
Value INT
)
AS
BEGIN
DECLARE @.Temp INT
IF @.LowLimit > @.HighLimit
SELECT @.Temp = @.LowLimit,
@.LowLimit = @.HighLimit,
@.HighLimit = @.Temp
INSERT @.Values VALUES (@.LowLimit)
WHILE @.@.ROWCOUNT > 0
INSERT @.Values
SELECT n.Value + t.Items
FROM @.Values n
CROSS JOIN (
SELECT COUNT(*) Items
FROM @.Values
) t
WHERE n.Value + t.Items <= @.HighLimit
RETURN
END
go
-- showing you the results from the function:
--select * from dbo.fnSeqNumbers(1,10)
--this returns 100 rows
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,10) b
order by a.col2
--...but you already have 10 in your table so lets only insert 90 rows
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
select @.@.rowcount
--look at your "new" data:
select * From #foo
This solution assumes that you have an identity value as your primary key.
If that is not the case in your table but your primary key is a number you
should be able to convert the number to a string, concatinate the value from
the function, and convert it back to an int that could be used as your
primary key. Hopefully this example gives you a few ideas and points you in
a direction where you can figure out a solution.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||Thanks much.
"Uri Dimant" wrote:

> Viccu
> create table #test (id int not null identity(1,1))
> declare @.i int
> set @.i=1
> while @.i<=100
> begin
> insert #test default values
> set @.i=@.i+1
> end
> select * from #test
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||Thank you so much.
"Keith Kratochvil" wrote:

> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||google up "generate test data"|||Thank you.Because of the foreign key constraints,say one of my tables has a
value that should
correspond to the value in the other table. How would I include that
particular value
in the query given by you?
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
"Keith Kratochvil" wrote:

> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||You could hardcode a single value within the select, modify the function to
return an additional column that has "random" FK values passed out of it or
you could just use (re-use) the foreign key data that you already have in
your table (#foo in my example). You probably want to load all the columns
when you insert data...just make sure that you query your FK column from
your base table when joining to the function.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:D11F6AF9-11D2-43FB-AF0D-40726220E012@.microsoft.com...
> Thank you.Because of the foreign key constraints,say one of my tables has
> a
> value that should
> correspond to the value in the other table. How would I include that
> particular value
> in the query given by you?
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2sql

Need some guidance!

I have been trying to rescue my company's helpdesk database because the company moved premesis and we lost that particular server.

It was originally running SQL Server 6.5, and my line manager simply copied the database.DAT files straight out of the MSSQL\Data directory without running a backup through Enterprise Manager.

Does anyone know how i can rescue these files so that we can get our helpdesk up and running again?!

thanks in advance for any help you can provide.I've never done this...but did you try and put the files in the exact location as the old box?

Is the line manager your boss?

If not, smack him/her in the head...

If yes, say what a great job they did to back up the data...

IOs the old box still available?

And how big is the file?|||ok,

Yes i have tried putting them back into the MSSQL\Data directory of an SQL server 6.5 install on a different server, but enterprise manager doesn't recognise them. I've tried creating databases with the said files in question and over-writing them with the originals, but it just makes them suspect, and therefore unusable.

Yes the line manager is my boss, and unfortunately, there's not much i can really say about them, but the "great job" one has sprung to mind.

The files are 77Mb for the main database i want to restore, and the master database is 100Mb.|||As SA you can go into master and manually change the status of the database from Suspect to "AOK". This is not guaranteed to work, but I have done it succesfully on 6.5.

If you got nothing to lose...|||Ok, so he's your boss...buy him a drink...

So you don't have a contact to someone where the old box is?

I remember reading that you have to modify the system tables...

It's not the best solution...
I would try and find someone to help from the old box...is it decommisioned?

Also try doing a search over at sqlteam...I know I saw it over there...|||thanks for that. we don't really have anything to lose, but thankfully we have an old version of the database that works, but it'll put us back to march 2002, and my boss thinks that is unacceptable.

Unfortunately the old box has been formatted and is being used for something else.

have you got the url for sqlteam? it won't hurt to go and have a look, and we might just get a solution to this (we've been working on it for almost a week now).|||Seriously. Try what you did before where you got the suspect database warnings.

Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know...|||http://www.sqlteam.com/default.asp

Good luck...

Formatted the box...sheesh...

Do they have a tape backup?

And if your boss thinks it's unacceptable...you might want to mention that taking backups with his method was un...well not the prefered method...

Where are you from...want me to pay a visit?

I don't have to be nice....

Or better yet...send the hit squad...

The blind dude and rdjabarov should put the fear of god in to him...|||Got the first part done, but as for this part:

Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know..

This is going to sound more than a little stupid, but where do i go to access the above? (i've just been going along blind, because A, im the only one who is free to do it, and B because i don't have any training on server software at all. Not even our IT guy knows how to work with SQL server 6.5, but if you could provide me with a bit of an idiots guide to get to those areas then i would really appreciate it.

Brett, im from outside Aberdeen, but i dont think my boss would take too kindly to having to get someone in to sort this one out. Must be a manager thing...|||That doesn't sound stupid. This is advanced crap.

To set the allow updates option:

Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
Under Server behavior, select or clear the Allow modifications to be made directly to the system catalogs check box.

Now run:
select status, name from sysdatabases
to see that status of all your databases

Then run:
Update sysdatabases set status = [YourDesiredStatus] where name = [YourDatabaseName]

Then RESET THE ALLOW UPDATES OPTION!

Here are the bit values for the status column. Select the options you want, add up their values, and that is [YourDesiredStatus].
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

I'd run backups of any critical data on the server before doing this, just as a matter of principle.

You now know enough to be very dangerous.

Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".|||ok, cheers for that. I just had a thought though. Would the same be possible on a server 2000 install? i.e. re-naming the old .DAT files to the respective file formats and following the above (with the obvious changes due to the software).

If it does then i can put 2000 on the test machine to see if that works out. I seem to remember something about 6.5 databases being incompatible with 2000 for some reason, unless a direct upgrade is carried out...|||You don't know them to well...

I didn't say they'd come in and fix it...mostly berate the guy...

And it's 6.5...I didn't look to close...

I try to forget everything pre 7...

A search...this was a fun thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36375&whichpage=1&SearchTerms=restore%2Cdb%2Cwith%2Cdb%2Cfile

Need to do more digging...|||That doesn't sound stupid. This is advanced crap.

You now know enough to be very dangerous.

Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".

AMEN brother

Your a DBA?...go figure...

And it's BOL|||im nowhere near a DBA (just incase that was intended for me), i just seem to be the try and fix it guy.

Oh, here's another challenge for you. I've just discovered that i need to have it up to service pack 5a, looking through the microsoft site there's only post service pack 5a, not much use there. do you guys know where to find the relevant service packs 'cause im now stumped. (and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")|||and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")

I'm speechless...

What line of business are you in?

I hope it's one where you don't need to worry about data

http://www.microsoft.com/sql/downloads/2000/sp3.asp|||Im not speechless, im pretty fed up with it.

The company i work for is a supplier for power managment companies like the National Grid and a few other ones in other countries. The helpdesk that im trying to fix (which im employed to take the calls for, not fix it) deals with any control system problems that may exist, as well as keeps track of all the hardware that is used in the makeup of the control system (so you can see it's a fairly important thing to get sorted out asap, but it hasn't been the case.)

the problem had been sitting unresolved with a few half-hearted efforts to solve it over the past 8 months, which i was pretty much screaming to get it fixed the whole time. now i got fed up so i started looking into it, and that brings us to where we are today.

Anyway, im going to have to log off now and go home and finish off servicing 4 mountain bikes (finally, something relaxing!), which should take me until friday to do.

I'll post any progress made and/or any other queries up here tomorrow, because i'll probably get stuck real quick.|||You gotta be across the pond somewhere...Ireland perhaps?

And you need the service packs for bot the OS and SQL Server...

Ever hear of slammer?

And if it's out there 8 months now...how are they recording the data now?

Good luck...and have fun with the bikes...

Sounds like you know a good deal about them...

I'm looking to buy a couple...what would be a good one...not too high end, but not junk either...|||If you are still have problems, why don't you just place a call to your company's helpdesk? That person should know all about database servers and system tables and suspect files and ... Oh. Nevermind...

Do NOT try klugeing in a 6.5 database file in place of a 2000 database file and then hand-changing the status code. A general rule of ANY issue resolution situation is to fix ONE THING AT A TIME. Reinstall your 6.5 data base server, bet the old database working, and then upgrade to 2000.|||thanks for the input guys. Brett, i live by Aberdeen, Scotland. If you're looking for a couple of bikes, what kind of riding are you most likely to do?|||How big is aberdeen?

http://www.dbforums.com/showthread.php?p=3701716#post3701716|||Aberdeen has a population of about 265,000 (i think). it covers a fair old area at any rate.|||Did ya give a shout out to David?

And your day's close to being over...isn't it?|||yeah. You'll see my replies in the Yak Corral thread. I wish it was almost over. Its 14:25 here and i don't finish until 16:45. I went and asked a mate of mine if i could do a straight swap for his bike. won't find out until around 7ish, and i really hope he goes for it, even if he is losing out slightly.|||is there any way to use command prompt to create a backup of a database file from a specified location i.e. C:\backup\tempdb.DAT?|||Yes, through the OSQL utility included with SQL Server.|||thanks blindman. I was in the middle of finding a tutorial for using bcp, but i'll try this first to see if it works.|||actually, how do you use it? just had a quick look and it seems to be along the same lines as bcp (i.e. too complicated for me).

just going back to one of your past posts, the database server we are running for the helpdesk is a seperate entity from the company network, and as such doesn't come under normal company IT support. If we were going to raise a call with the IT department, they would be required to take full control of the server and that would mean that they could do as they see fit with it, which my boss says is unacceptable (as usual).

so basically im left with the job of trying to figure out a way to fix the problem (with no training in servers, sql or database management at all, but with this company im not suprised that they expect things to work with no training being given), which is why i ended up here. At least i'll be able to get advice on areas i won't have thought on, or can't because i don't really know what im doing.|||OSQL utility allows you to run SQL statements from the command prompt. You can either enter them line-by-line or call OSQL with a batch file of SQL commands. Your SQL batch file would contain the SQL commands for backing up your database.

BCP is only mildly complicated. It can be a bit quirky to get to work right the first time, but after that it is very fast, simple, and reliable.|||along the same lines as bcp (i.e. too complicated for me).

Huh?

Damn that hurt...

bcp is your freind...get to know her extremely well....

and I use osql very infrequently...you can do it all in sprocs...

I actually justy use osql so I can build a script that I give to my PROD DBA...mostly so they don't screw up a release...|||i guess it'll just take time to learn how to use it.

Got some other bad news today. I was trying to re-allocate resources on my website, and because of the package i have i can't re-allocate any resources at all (im running a php 6.5 website at the moment). So i only have three options: upgrade my package deal (cost somewhere in the region of $100US to do it though), build a brand new html website that won't have the same functionality as the one i have at the moment, or move servers to somewhere where i can re-allocate resources to my hearts content. Is that readysetconnect server any good? 1250Mb with 40Gb transfer is a heck of alot better than my current 100Mb space with 1Gb transfer.|||Here's a nice quick GUI tool for BCP.
http://weblogs.sqlteam.com/davidm/archive/2004/03/31/1151.aspx|||that's brilliant. thanks peter|||A GUI?

Don't use xp_cmdshell....|||Man, you guys are confusing him! OSQL did not exist in 6.5, so the only thing is ISQL there. BCP? He can't even get to his database, how in the world is he gonna use BCP? Monkeying with the status? Oh Codd...Check DISK REFIT/DISK REINIT...Man, I don't even have BOL for 6.5 any more...
Search for Sybase documentation, they had a lot in common, including the commands that I posted.|||You're right. OSQL did not exist. But ISQL did and he should have that. I never did figure out what the difference was...|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.|||Well, I am wading into this thread a little late. Let me see if I have this all straight.

1) There once was a SQL 6.5 box with the database for the helpdesk application on it.
2) In a move, this server was lost.
3) The last known backup of the database is from March.
4) You have the .DAT files from the old SQL 6.5 box, and you boss thinks you can get them attached to the new 6.5 box?

Hmm. I think I would have to plead ignorance on this one, and have your boss show the "poor ignorant lowly wage drone" just how to do it. After he fails, you can have a private laugh in the pub ;-).

There might be a way of doing it, but I can not think of what it may be. I have not touoched SQL 6.5 in ages (3 years), now. If I remember right, there was a column in the master database that was related to the last transaction ID in each database. If that is right (and that is a big "if"), I think reattaching this database could be impossible. As Blindman pointed out, it is certainly darned difficult.

Without the database being attached to a live SQL Server, You will not be able to run anything against it (isql and bcp included). All I can think of is what you have already tried (create new database with the same name, shut down, switch files, pray).

As I remember detaching and reattaching databases was a big deal when SQL 7.0 came out. I did not see the big deal at the time, and thought it was just another Microsoft marketing blitz fixating on minor details that have almost no application. Wait... I still think that. Shows you how often I use detaching databases, eh? The point is, it is not supported in SQL 6.5

If you do go humbly before your boss to have him show you how it is done, be sure to let us know what he says when he confronsts the inevitable. I think we can all guess what Microsoft Support's answer to all of this will be.|||...Again, DISK REFIT/DISK REINIT should do the drick ONLY if you have all .DAT files. I hope you're not missing log devices or have an incomplete set of .DAT files...|||rdabarov probably has it. I do not remember DISK REFIT, but DISK REINIT rings a bell. Be sure you have a safe copy of the .dat files, though. These commands may make a few changes to the files.|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.

Yeah, but I don't remember any functional difference. Was there?|||Beyond OSQL having a few extra/changed switches, there was not a whole lot of difference that I could see. I could have been Microsoft trying to phase out DB Library. Why they went to ODBC, I am not entirely sure. Looks like OSQL could be used with an ODBC DSN, so technically, you could try to query Oracle with it. Not sure why you would want to, though.

Judging by the SQL 2005 download, it looks like ISQL is dead, OSQL is deprecated, and SQLCMD is the new tool for scripting. Again, just a few changes in the switches from OSQL. Probably a lot under the covers, but I don't see it, yet.

EDIT: Oh, and BCP is still there. How much of Brett's stuff would break, if they changed BCP to SQLCOPY? ;-)|||Bite your tonque...the horror, the sheer horror....|||We need a topic change. Or move it to Brett's place (everybody should know by now where it is ;))|||Does Brett's place serve Margaritas? I've been working with Oracle all week, so I could use a pitcher myself.|||I have all the .DAT files, but they are not backup files of the databases. My boss has only just realised that he should have made a backup of everything through the enterprise manager, but instead just copied them directly from the MSSQL\Data directory.

But here's the latest on what's been going on:

We have set up the box we were using to run server 2000 (windows and sql) with the march 2002 backup we know works. sounds straight forward enough.

My boss has put another box together and has set it up the same as we had it before (windows server 2000 and sql 6.5), but he wants to try and see if he can get it working.

Im overjoyed that he finally took it in hand and decided to try this for himself because he knows alot more about sql server and all the rest too. I keep getting asked about what i did and how i did it, but i'll mention this and see if he knows about it. (rdjabarov, if you could give me a procedure on how to do the DISK REFIT/DISK REINIT then that would be great)

but utill then im stuck trying to get the rest of the software working, which due to fact it is installed on different machines, will have to be re-license and god only knows how much that'll cost. Once that's done, i'll have to try and bring the information up to date manually, or at least until we rescue those .DAT files.|||ok, here's the latest update:

I got the AHDTEST database that i had been trying to rescue working to a degree in 6.5, but i got 81 pages (A4 pages) of errors because the master database has not been restored. I have a few queries about this because the .DAT files were made on a server called BK_MAN5_INV, and the name of this server is XW4000, so there might be a problem with the DISK REFIT/DISK REINIT anyway wouldn't there? My train of thought leads me down to the fact that the servers have different names and different configurations, thus unless the REFIT/INIT commands altered these settings to be the same as the target machine (in this case XW4000) then that would be fine, but im not so sure about that.

Does anyone know if it actually does this?|||So what database have you restored? I didn't mention it (got busy here for a change ;)) but when installing a fresh 6.5 instance you needed to make sure that code page, language, and sort order are the same as the original. Is this the case?|||no one here knows what the code page, language and sort order settings were due to this being an inheritence case. all we could do is assume they used system defaults. Database wise, i got the AHDTEST50 database in without it going suspect, but got a shed load of errors due to the fact that the master database, and thus all the other databases too, haven't been put in. So as a result im looking at restoring the system databases as well as the user databases. I just wish there was a way to put them all in at the same time (through MS Query or something like it).sql

Need MAX from Each Group

Hello,

I have a table called 'tblCLTestScores' that holds student test scores
for one particular test. Students take this same test three times on
different dates, so the table contains three or fewer records for each
student. The records for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.

I need a query that will, for every student, extract the record with
the MAX 'TestDate'.

The fields that I need to query from 'tblCLTestScores' are as
follows:

TS. StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
TS.BookID,
TS.TestDate,
TS.SOLevel

I've tried a couple of things using MAX(TS.TestDate), but I always get
the maximum TestDate for all of the records, and not the maximum
TestDate for each student.

How do I set up the query to ge the MAX TestDate for each student?

Thank you for your help!

CSDunnHi

It is not clear how you differentiate testA from testB Assuming you have a
TestId try something like:

SELECT
TS.StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
TS.BookID,
TS.TestDate,
TS.SOLevel
FROM tblCLTestscores TS JOIN
( SELECT StudentID, TestId, MAX(TestDate) AS TestDate
FROM tblCLTestscores
GROUP BY StudentID, TestId ) DT ON TS.StudentID = DT.StudentID AND TS.TestId
= DT.TestId
AND TS.TestDate =DT.TestDate

John

"CSDunn" <cdunn@.valverde.edu> wrote in message
news:807dbff7.0406071022.28823376@.posting.google.c om...
> Hello,
> I have a table called 'tblCLTestScores' that holds student test scores
> for one particular test. Students take this same test three times on
> different dates, so the table contains three or fewer records for each
> student. The records for any given student are made distinct by the ID
> number of the test booklet, and the date on which they took the test.
> I need a query that will, for every student, extract the record with
> the MAX 'TestDate'.
> The fields that I need to query from 'tblCLTestScores' are as
> follows:
> TS. StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
> TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
> TS.BookID,
> TS.TestDate,
> TS.SOLevel
> I've tried a couple of things using MAX(TS.TestDate), but I always get
> the maximum TestDate for all of the records, and not the maximum
> TestDate for each student.
> How do I set up the query to ge the MAX TestDate for each student?
> Thank you for your help!
> CSDunn|||>> I have a table called 'tblCLTestScores' ... <<

Take off that silly, redundant "tbl-" prefix that you added in
violation of ISO-11179 Standards.

>> .. that holds student test scores for one particular test. Students
take this same test three times on different dates, so the table
contains three or fewer records [sic] for each student. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. They are totally different
concepts.

>> The records [sic] for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.
<<

A great natural key!

>> I need a query that will, for every student, extract the record
with the MAX 'TestDate'. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You ave us a bunch of qualified names without so much
as a table.

>> How do I set up the query to ge the MAX TestDate for each student?
<<

SELECT S1.student_id, ...
FROM CLTestScores AS S1
WHERE test_date
= (SELECT MAX(S2.test_date)
FROM CLTestScores AS S2
WHERE S1.student_id = S2.student_id);|||>> I have a table called 'tblCLTestScores' ... <<

Take off that silly, redundant "tbl-" prefix that you added in
violation of ISO-11179 Standards.

>> .. that holds student test scores for one particular test. Students
take this same test three times on different dates, so the table
contains three or fewer records [sic] for each student. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. They are totally different
concepts.

>> The records [sic] for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.
<<

A great natural key! Verifiable, clean, etc. But please post DDL, so
that people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.
You ave us a bunch of qualified names without so much as a table.

>> How do I set up the query to get the MAX(test_date) for each
student? <<

SELECT S1.student_id, ...
FROM CLTestScores AS S1
WHERE test_date
= (SELECT MAX(S2.test_date)
FROM CLTestScores AS S2
WHERE S1.student_id = S2.student_id);