Newby: Administer a database remotely?

I'm working on a website remotely through a VPN and my client uses SQL
Server. There's a requirement for a database and my client uses SQL
Server 2000.

Is it possible to administer this database remotely, just like I do
with MySQL/PHP MyAdmin?

Please elaborate.

Thanks,

Marjorie"marjorie" <marjorie@.popkit.com> wrote in message
news:71f66b4d.0407271230.4c220eb1@.posting.google.c om...
> I'm working on a website remotely through a VPN and my client uses SQL
> Server. There's a requirement for a database and my client uses SQL
> Server 2000.
> Is it possible to administer this database remotely, just like I do
> with MySQL/PHP MyAdmin?
> Please elaborate.
> Thanks,
> Marjorie

I don't know how much administration you need, but if you're developing a
website and need to create / modify a database for it, you can install the
SQL Server client and use the osql command-line utility:

e.g.
c:\> osql -S<server address> -U<user name> -P<password
So I use
osql -Ssqlserver -Usa -Pmypassword.

The flags are case sensitive.
I don't know if this makes sense in the context of virtual private networks
though, as I've never played about with them...

Rowland.|||marjorie@.popkit.com (marjorie) wrote in message news:<71f66b4d.0407271230.4c220eb1@.posting.google.com>...
> I'm working on a website remotely through a VPN and my client uses SQL
> Server. There's a requirement for a database and my client uses SQL
> Server 2000.
> Is it possible to administer this database remotely, just like I do
> with MySQL/PHP MyAdmin?
> Please elaborate.
> Thanks,
> Marjorie

Yes, just install the client tools on your workstation, and make sure
the VPN is configured to allow you to connect to the server. This
article may help:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon

Newby Stupid question

Okay, so this is probably me just being stupid and not understanding.

I have a dimensional database that I populated using SSIS. One fact table has about 78,000 records. When I build a cube on top of the database, I get no results for the measures on the fact table.

What would be a typical reason for something like this occurring?

Thanks
JimProbably you didn't design the Dimension Usage.

NEWBY question - filtering dates

Im not sure how to explain this or if this is the correct forum but here it goes.

I have created a report from a view that gives me court hearing dates on a defendent. When I run the report/view I get two different dates for the person such as, 12/14/2006 and 12/15/2006. How can I get it to show only the later date (12/15/2006)? I pull information from a mainframe that almost always gives me 2 different dates, but we only need the most recent date.

Here is my query for the view

SELECT dbo.CASES.CaseID, dbo.CASENUMBERS.CaseNumber AS Arrest_Number, dbo.CASENUMBERTYPES.CaseNumTypeCode AS Arrest_NumType,
CASENUMBERS_1.CaseNumber AS JN_Number, CASENUMBERTYPES_1.CaseNumTypeCode AS JN_NumType, dbo.ROLETYPES.RoleTypeDesc,
dbo.ACTORS.SIDNum, dbo.ACTORLOCATIONS.ActorLocDesc, dbo.CHARGENUMBER.ChargeDescription, dbo.DEFCHARGES.OffenseDate,
dbo.fnNameReverse(dbo.ACTORNAMES.LastName, dbo.ACTORNAMES.FirstName, dbo.ACTORNAMES.MiddleName, dbo.ACTORNAMES.Suffix)
AS NameRev, dbo.EVENTS.ev_StartDate AS PreHearingSetDate, dbo.OFFENSELEVELS.OffenseLevelDesc, dbo.CASES.UnitID
FROM dbo.CASES INNER JOIN
dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN
dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN
dbo.CASENUMBERS AS CASENUMBERS_1 ON dbo.CASES.CaseID = CASENUMBERS_1.CaseID INNER JOIN
dbo.CASENUMBERTYPES AS CASENUMBERTYPES_1 ON CASENUMBERS_1.CaseNumTypeID = CASENUMBERTYPES_1.CaseNumTypeID INNER JOIN
dbo.CASEPARTIES ON dbo.CASES.CaseID = dbo.CASEPARTIES.CaseID INNER JOIN
dbo.ROLETYPES ON dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID AND
dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID INNER JOIN
dbo.ACTORNAMES ON dbo.CASEPARTIES.ActorNameID = dbo.ACTORNAMES.ActorNameID INNER JOIN
dbo.ACTORS ON dbo.ACTORNAMES.ActorID = dbo.ACTORS.ActorID INNER JOIN
dbo.ACTORLOCATIONS ON dbo.ACTORS.ActorLocationID = dbo.ACTORLOCATIONS.ActorLocationID INNER JOIN
dbo.DEFCHARGESUMMARY ON dbo.CASES.CaseID = dbo.DEFCHARGESUMMARY.CasesCaseID INNER JOIN
dbo.DEFCHARGES ON dbo.DEFCHARGESUMMARY.DefChargeID = dbo.DEFCHARGES.DefChargeID INNER JOIN
dbo.CHARGENUMBER ON dbo.DEFCHARGES.ChargeNumberID = dbo.CHARGENUMBER.ChargeNumberID INNER JOIN
dbo.COURTS ON dbo.CASES.CaseCourtID = dbo.COURTS.CourtID INNER JOIN
dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID INNER JOIN
dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN
dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID
WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND
(dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND (dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET') AND
(dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))

In the 'PRE-HEARING SET' table is where I receive the two dates. Is there a way to filter out the first date? I just want to show the most recent date.

Hi,

as you do not SELECT anything from the events table what about:

--dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN

dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID

WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND

(dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND

(dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))

AND EXISTS(SELECT * FROM dbo.EVENTTYPES WHERE EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID AND dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Newby JOIN question

I'm attempting to use a JOIN for the first time and I'm getting about 94,000 rows returned when I should only be getting about 270. Something must not be unique (possible DSN) but I can't figure out why the join is not working. I know it must be difficult to determine the problem without actually seeing the tables but could somebody with a lot of SQL experience spot any glaring problems with this query? Thanks!

SELECT DS.DSNName AS DSN,
S.ServerName AS WebServer,
DBs.DBName,
A.ServerName AS SQLServer
FROM DSNs DS,
Servers S,
DBs,
DSNs LEFT OUTER JOIN Servers A
ON DSNs.SQLServerID = S.ServerID
WHERE DBs.Status = 1
AND DS.WebserverID = S.ServerID
AND DBs.DBID = DS.DBID
ORDER BY DSN

Hi reformatted, I got this:

SELECT DS.DSNName AS DSN,
S.ServerName AS WebServer,
DBs.DBName,
A.ServerName AS SQLServer
FROM DSNs DS,
INNER JOIN Servers S,
ON DS.WebserverID = S.ServerID
INNER JOIN DBs
ON DBs.DBID = DS.DBID
INNER JOIN DSNs
ON DSNs.SQLServerID = S.ServerID
LEFT OUTER JOIN Servers A
ON DSNs.SQLServerID = A.ServerID --I guess this was wrong as it was pointing to the S.Server not the A.Server, see if this helps.
WHERE DBs.Status = 1
AND DS.WebserverID = S.ServerID
AND DBs.DBID = DS.DBID
ORDER BY DSN

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your reply. I removed a couple unnecessary commas and the query runs but does not pull back any data. I will play around with it. Please let me know if you have any additional suggestions.|||If I am not sure about a query, I always do a step.by-step approach, starting with the base query and adding more joins one by one controlling the expected results after each step. This is much easier than doing the whole query and then guessing by the results which JOIN was not the right one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Newby DTS Question

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

Thanks

Mikemike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.
>
I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1
>
How would I go about performing such clauses using DTS?

Hi Mike,

Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:

SELECT my_columns
FROM dbo.My_Table
WHERE id ?

The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.

You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.

HTH,
-Tom.|||Thanks for your help Tom,worked a treat.

Thomas R. Hummel wrote:

Quote:

Originally Posted by

mike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

>
Hi Mike,
>
Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:
>
SELECT my_columns
FROM dbo.My_Table
WHERE id ?
>
The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.
>
You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.
>
HTH,
-Tom.

newby - AUTOINCREMENT problem.

Hi,

I'm enclosed a snippet of test code which highlights my problem. The Stored
procedure insertValue should insert text into the parent, then insert other
text into the child table but the 2 tables should auto increment in sync
(i.e. so that they both end up with the same id numbers). I've tried taking
the auto increment out of the child table but then I don't know how to get
the right parent id into the child table.

Any advice appreciated - this is my first database, so I'm just in the
learning process really. Code follows:

CREATE TABLE Parent
(id INTEGER DEFAULT AUTOINCREMENT,
parenttext VARCHAR(16),
PRIMARY KEY (id))!

CREATE TABLE Child
(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
childtext VARCHAR(16),
FOREIGN KEY (childid) REFERENCES Parent(id),
PRIMARY KEY (childID))!

CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
BEGIN
insert into parent (parenttext) values (p);
insert into child (childtext) values (c);
END!

call insertValues('from parent', 'from child')!
select * from parent, child where parent.id = child.childid!In message <436cefc6$1_3@.mk-nntp-2.news.uk.tiscali.com>, Mary Walker
<123@.123.com> writes
>Hi,
>I'm enclosed a snippet of test code which highlights my problem. The Stored
>procedure insertValue should insert text into the parent, then insert other
>text into the child table but the 2 tables should auto increment in sync
>(i.e. so that they both end up with the same id numbers). I've tried taking
>the auto increment out of the child table but then I don't know how to get
>the right parent id into the child table.
>Any advice appreciated - this is my first database, so I'm just in the
>learning process really. Code follows:

OK. The first piece of advice is don't rely on the autonumber to return
any particular value. There are ways that the values in the two tables
could get out of sync and it would be a PITA to fix.

There are two options that I could suggest.

One is that you create the parent record and then take the autonumber
value from there and explicitly insert it into the child table. Don't
use an autonumber in child table, use a simple integer instead. This is
the way I recommend that you do it.

The second method has limited applicability but can make things easier.
First redesign your child table with an integer key. Then create a query
that links the two tables. You can now update the query instead of
updating the two tables separately.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.|||>> this is my first database, so I'm just in the learning process really. <<

Get a book on RDBMS and learn why an auto-increment can NEVER be a
relational key. Learn why a table name should be a collective or
plural name (unless there is only one row in the table). Learn way
"id" is too vague to be data element name. Learn why camelCase is a
bitch to read --hint: where does your eye jump when you see an
Uppercase letter?

I know you want to have a "Magical Universal Key" that will solve all
your design problems, without having to really think or learn anything.
What is your **real key** in your **real** problem?

Going to a Newsgroup to get what usually takes a few YEARS of college
and experience does not work.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131243682.809956.301200@.g49g2000cwa.googlegr oups.com...
>>> this is my first database, so I'm just in the learning process really.
>>> <<
> Get a book on RDBMS and learn why an auto-increment can NEVER be a
> relational key. Learn why a table name should be a collective or
> plural name (unless there is only one row in the table). Learn way
> "id" is too vague to be data element name. Learn why camelCase is a
> bitch to read --hint: where does your eye jump when you see an
> Uppercase letter?
> I know you want to have a "Magical Universal Key" that will solve all
> your design problems, without having to really think or learn anything.
> What is your **real key** in your **real** problem?
> Going to a Newsgroup to get what usually takes a few YEARS of college
> and experience does not work.

LOL. Thanks very much you for reply. I have actually learnt a lot by
simply reading your reply - but I'll buy the book anyway :-)|||In your book 'sql for smarties' you have an example with a table named
'Warehouse' - if you where to follow your own naming standards then it
should be called 'Warehouses'.

'id' is fine within the context of the table, if the column belongs to the
table 'Child' then its obviously Child.id.

What key would you use for a message board? Would you still key it on
subject and posting date or like the rest of us and how NNTP works, create a
guid with the domain?

The auto-number (IDENTITY property) is usually used as a artificial (or
surrogate) key, there is no such thing as a relational key - I think you
mean 'natural key', see: http://en.wikipedia.org/wiki/Natural_key.

Quoting from that aritcle...

"
The main disadvantage of choosing a natural key is that it may need to
change if your business requirements change. For example, if you have chosen
CustomerNumber as the primary key for a customer, and, subsequently,
CustomerNumber becomes alphanumeric instead of numeric, then as well as
changing the type of the column, you will need to make changes to all other
tables where CustomerNumber is used as a foreign key.

Retrieved from "http://en.wikipedia.org/wiki/Natural_key"

"

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131243682.809956.301200@.g49g2000cwa.googlegr oups.com...
>>> this is my first database, so I'm just in the learning process really.
>>> <<
> Get a book on RDBMS and learn why an auto-increment can NEVER be a
> relational key. Learn why a table name should be a collective or
> plural name (unless there is only one row in the table). Learn way
> "id" is too vague to be data element name. Learn why camelCase is a
> bitch to read --hint: where does your eye jump when you see an
> Uppercase letter?
> I know you want to have a "Magical Universal Key" that will solve all
> your design problems, without having to really think or learn anything.
> What is your **real key** in your **real** problem?
> Going to a Newsgroup to get what usually takes a few YEARS of college
> and experience does not work.|||Hi Mary,

The SCOPE_IDENTITY() will return the last inserted IDENTITY value for that
connection.

insert into parent (parenttext) values (p);
print scope_identity() -- gives the id from parent

insert into child (childtext) values (c);
print scope_identity() -- gives id from child

Does that help?

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Mary Walker" <123@.123.com> wrote in message
news:436cefc6$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Hi,
> I'm enclosed a snippet of test code which highlights my problem. The
> Stored procedure insertValue should insert text into the parent, then
> insert other text into the child table but the 2 tables should auto
> increment in sync (i.e. so that they both end up with the same id
> numbers). I've tried taking the auto increment out of the child table but
> then I don't know how to get the right parent id into the child table.
> Any advice appreciated - this is my first database, so I'm just in the
> learning process really. Code follows:
> CREATE TABLE Parent
> (id INTEGER DEFAULT AUTOINCREMENT,
> parenttext VARCHAR(16),
> PRIMARY KEY (id))!
> CREATE TABLE Child
> (childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
> childtext VARCHAR(16),
> FOREIGN KEY (childid) REFERENCES Parent(id),
> PRIMARY KEY (childID))!
> CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
> BEGIN
> insert into parent (parenttext) values (p);
> insert into child (childtext) values (c);
> END!
> call insertValues('from parent', 'from child')!
> select * from parent, child where parent.id = child.childid!