IWD4 -- SQL Management Studio server name issue

One thing I've noticed about the SQL Server Management Studio is that while
it allows you to create an alias for the server in the Registered Servers
window. That alias name is not propagated to the Object Browser. It seems
like it should be.
Michael Otey
Please ask this in the private SQL2005 group.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Otey" <mikeo@.teca.com> wrote in message
news:#9xJG2abEHA.3716@.TK2MSFTNGP11.phx.gbl...
> One thing I've noticed about the SQL Server Management Studio is that
while
> it allows you to create an alias for the server in the Registered Servers
> window. That alias name is not propagated to the Object Browser. It seems
> like it should be.
> Michael Otey
>
|||Oops missed. Sorry about that.
"Euan Garden[MS]" <euang@.online.microsoft.com> wrote in message
news:u6T5IEebEHA.2340@.TK2MSFTNGP10.phx.gbl...
> Please ask this in the private SQL2005 group.
> --
> -Euan
> Please reply only to the newsgroup so that others can benefit. When
posting,
> please state the version of SQL Server being used and the error
number/exact
> error message text received, if any.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Michael Otey" <mikeo@.teca.com> wrote in message
> news:#9xJG2abEHA.3716@.TK2MSFTNGP11.phx.gbl...
> while
Servers[vbcol=seagreen]
seems
>

I've problems.

I've 3 problems. First, I've data like this:
Date1 Date2
--
1/1/2005 1/7/2005 {m/d/yyyy}
1/8/2005 1/14/2005
1/15/2005 1/21/2005
If I input date 1/9/2005, so data at record 2 is showed. Because date
1/9/2005 in between 1/8/2005 and 1/14/2005.
If I input date 1/20/2005, so data at record 3 is showed. Because date
1/20/2005 in between 1/15/2005 and 1/21/2005.
and on...
Nah, How syntax SQL to select data above?
Second, I've 2 tables (A & B). I wanna import from table A to B. if Data in
table A exist at table B, so data at table B will be updated. and if Data in
table A not exist at table B, so data at table B will be inserted.
How syntax SQL to do it? Can it only one statement?
Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
its field?
INSERT INTO A (a,b,c,........)
SELECT a,b,c,..........
from B
Or any there other ways to write Syntax SQL (INSERT INTO) so short?> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
DECLARE @.dt SMALLDATETIME
SET @.dt = '20050120'
SELECT Date1, Date2 FROM tablename
WHERE Date1 <= @.dt AND Date2 >= @.dt
Always use YYYYMMDD format when inputting dates. And keep in mind that your
date values are *NOT* stored as the m/d/yyyy format you indicated, unless
they are not DATETIME/SMALLDATETIME.

> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
> in
> table A exist at table B, so data at table B will be updated. and if Data
> in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
No, you will need to run a separate INSERT and UPDATE. Or DELETE the rows
from table B that exist in table A, then insert the whole lot.

> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
YES. It is easy to get this list, open Query Analyzer, hit F8, find the
table, expand it, and click / drag the Columns folder to the query window.

> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
Why do we need a shortcut? If you really like using SELECT *, what do you
do when someone changes the structure of the table?|||Q1:
declare @.d datetime
set @.d = '20050115'
select * from t1 where date1 <= @.d and date2 >= @.d
go
Q2:
You need two statements.
update tb
set tb.c1 = (select c1 from ta where ta.pk = tb.pk), ..., tb.cn = (select cn
from ta where ta.pk = tb.pk)
where exists(select * from ta where ta.pk = tb.pk)
insert into tb
select c1, ..., cn
from ta
where not exists(select * from tb as b where b.pk = ta.pk)
Q3:
Yes
AMB
"Bpk. Adi Wira Kusuma" wrote:

> I've 3 problems. First, I've data like this:
> Date1 Date2
> --
> 1/1/2005 1/7/2005 {m/d/yyyy}
> 1/8/2005 1/14/2005
> 1/15/2005 1/21/2005
> If I input date 1/9/2005, so data at record 2 is showed. Because date
> 1/9/2005 in between 1/8/2005 and 1/14/2005.
> If I input date 1/20/2005, so data at record 3 is showed. Because date
> 1/20/2005 in between 1/15/2005 and 1/21/2005.
> and on...
> Nah, How syntax SQL to select data above?
> Second, I've 2 tables (A & B). I wanna import from table A to B. if Data
in
> table A exist at table B, so data at table B will be updated. and if Data
in
> table A not exist at table B, so data at table B will be inserted.
> How syntax SQL to do it? Can it only one statement?
> Third, I've 2 tables (A & B). where table A have 52 fields and B have 50
> fields. How to write Syntax SQL (INSERT INTO) so short? Must I write each
> its field?
> INSERT INTO A (a,b,c,........)
> SELECT a,b,c,..........
> from B
> Or any there other ways to write Syntax SQL (INSERT INTO) so short?
>
>|||Thanks for your answerings. But I dont undrstand with your statement. You
said that Always use YYYYMMDD format to filter date. Because I always use
mm/dd/yyyy format to filter date. and it can do properly. Example:
SELECT * FROM TB Where BornDate='1/28/2000'
Why can it do? please explain me so detail.
Second, I ask to you. How to delete data at table A that exist at table B.
Usually I write like this:
DELETE FROM TA where NOID in (SELECT NOID FROM TB).
But it can works, if at table A (TA) has 1 field to be primary key. If table
A (TA) has 4 fields to be primary key. How its syntax so good?|||> Thanks for your answerings. But I dont undrstand with your statement. You
> said that Always use YYYYMMDD format to filter date. Because I always use
> mm/dd/yyyy format to filter date. and it can do properly.
Please read both of these in their entirety:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp

I've never seen this before

I saw something today that I had never noticed before. A collegue of mine
and I were troubleshooting a query that was not performing as expected. The
query consisted of a Select from a complex view with a where in subquery.
In all, the query was taking about 30s. The subquery runs quickly when run
by itself. Heck, the Select from the views run quickly by itself.
We looked at the execution plan to try to figure out where we might be able
to add effeciencies. 16% of the total work was being done by the subquery.
This didn't seem to make sense. Then we noticed that the subquery data was
being joined into data stream as the view was being built! I guess I
expected the Optimizer to build the view, run the subquery, and loop through
the data to filter it. The Optimizer instead redefined the view on the fly
by joining in the subquery data. To test it we did a Select Into a temp
table with * from the view and then ran a second query filtering that data
according to the subquery. That test took 6 seconds.
Has anyone seen this type of behaviour before?
ChristianSQL Server has always implemented queries against views by combining
the SELECT that is the view with the SELECT that references the view.
Then it is up to the optimizer to make a good query plan out of that.
There are times the optimizer doesn't choose well, and a query
referencing a complex view such as you describe is certainly a prime
candicate for this problem.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 10:34:42 -0400, "Christian Smith"
<malekai101@.yahoo.com> wrote:

>I saw something today that I had never noticed before. A collegue of mine
>and I were troubleshooting a query that was not performing as expected. Th
e
>query consisted of a Select from a complex view with a where in subquery.
>In all, the query was taking about 30s. The subquery runs quickly when run
>by itself. Heck, the Select from the views run quickly by itself.
>We looked at the execution plan to try to figure out where we might be able
>to add effeciencies. 16% of the total work was being done by the subquery.
>This didn't seem to make sense. Then we noticed that the subquery data was
>being joined into data stream as the view was being built! I guess I
>expected the Optimizer to build the view, run the subquery, and loop throug
h
>the data to filter it. The Optimizer instead redefined the view on the fly
>by joining in the subquery data. To test it we did a Select Into a temp
>table with * from the view and then ran a second query filtering that data
>according to the subquery. That test took 6 seconds.
>Has anyone seen this type of behaviour before?
>Christian

Ive never been good with relationships

I'm having some trouble working out how to query some data. Rather than explain up front, here's some examples of what I want to achieve:

*******************************************************

I've got a structure which looks vaguely like this:

[ANCESTORS]

Grandparent
Parent
Child

If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:

SELECT *
FROM [ANCESTORS]
 (some sortof joins here )
Where Grandparent.Name ='Cybill'

This would return all of the children of 'Cybill' and their children. Now, if I use the following query:

SELECT *
FROM [ANCESTORS]
 (some sortof joins here )
Where Child.Name ='Jean'

This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).

Likewise, if I enter:

SELECT *
FROM [ANCESTORS]
 (some sortof joins here )
Where Parent.Name ='Ron'

Then I would get Ron's parents and also his children.

*******************************************************

So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.

Can anybody see my dilemma here?

Mark-up ASP.net posts here
MarkItUp.com... no. I don't. I've done something like this before with someone, and we didn't go about that method. :(

What's the problem with doing inner joins?|||Because, with Inner JOINs I am relying upon the existance of children to be able to return the grandparents.|||I actually have one table "Ancestors" and also a separate linking table which contains the relationships:

[Ancestors] ( id int, Name varchar )
[Relationships] ( id, fkid )

What I need to be able to do is to write a single query which can span "up to 7" lines of descendency, that is:

Great-Great-Great-Grandparent
Great-Great-Grandparent
Great-Great-Grandparent
Great-Grandparent
Grandparent
Parent
Child

I just have absolutely no idea how to write a single sql query which could filter on one or more levels but ensure referential integrity down the line. In other words, I can specify a child which would trace up the tree in a single line, or, specify a Great-Great-Great-Grandparent which would span out from a single point and would show all the way down to leaf nodes regardless of which level they finished at.|||Can we use a UDF? :-)|||Yes, I'm using SqlServer 2K.

I have finally tuned the stored procedure "program" enough to get my - previously 14 hour (give or take 100 milliseconds) - query down to sub-20 seconds. This is good enough to ship to the client so, I'm going to go with what I have for now.|||20 seconds? I'm still not believing that to be good enough. :) but if it's good enough for the client, it's good enough I guess.

If you're using any temp tables or what not, try indexing them before you apply data to them. I trimmed a procedure that executed in 12 seconds to 2.|||KraGiE, I agree, 20 seconds is a long time to wait, but, it's working so that's a much better position than I had 2 days ago.

I do have # temp tables, about 12 of them actually (more on that later), and my final optimization was to add indexes and optimization hints in the appropriate places, this reduced a 2 minute 20 second query to sub 30 seconds.

As for the 12 temp tables, this, to me was a fair indicator that the schema of the database was wrong to begin with so now, I 'm actually pushing to have the db schema altered too. I'm hoping that this will give me enough of a foothold to be able to achieve the necessary remaining improvements|||Personally, I'm a big fan of int based look up tables. :) Well, in cases like this, I'm fond of them because you can branch them out as far as you want without having just 'add a new field'.

I also think loops are my best friend when used properly.

What I meant by the indexes (if this is what you're doing already, then I'm a redundant moron) is ...

SET NOCOUNT ON
CREATE TABLE #TempTable
(
 tableID int IDENTITY,
 TableField varchar(50)
 SomeOtherVar int
)
GO
CREATE INDEX idx_Temp
 ON #TempTable ( tableID )
GO
INSERT INTO #TempTable
SELECT
 TableID,
 TableField,
 SomeOtherVar
FROM
 RealTable
-- WHERE Your Criteria
GO
-- Do Other Calculations
TRUNCATE TABLE #TempTable
DROP INDEX idx_Temp
DROP TABLE #TempTable
GO
-- Yes Terri, I space my sql in procedures and QA this way normally, and I'm just lazy when I post here. :)

|||Yeh, that's pretty much what I'm doing already re: the indexes ( although who am I to call you a redundant moron :P ) except that I create the index only after population and, therefore also set the fill factor to 100% - but, for the purposes of an illustration, yes, that's pretty much what I do.|||awesome. In some cases, I'll put the index in before I start populating if the data's going to be enormous. It sets a bit faster from my experience.

I've installed SQL express, now what?

This may be a stupid question, but I've installed SQL Express and I don't know what to do next. I went to my program files/Microsft SQL... and the only folder present is a configuration folder.

Next I went to my control panel/admin tools/ODBC and succussfully created a user data source. (With the help of the forum!)

Obviously, I've never used SQL before, but I've read a couple of books and other stuff online, and I want to teach myself. So I decided to install this. Basically, I'm looking for answers on how to access the database. I know it's installed, it's in the Add/Remove programs list. I just don't know how to connect/access it.

Sorry for the dumb question.

Stacey

I think I figured it out. You have to access the DB using another program, and connect using the ODBC that I previously created. Am I correct?

I thought it was an independent DB, meaning I could directly access it. Thanks!

|||

Hi

You'll need to download SQL Management Studio Express (here). It will allow you to create databases, run queries etc. That way you won't have to work "in the dark".

HTH

|||

Thank you so much for helping me. Now I just have to figure out how to use it, and what to do!!!

Stacey

|||Update - Thank you so much Andre! I am off and running thanks to you. I figured out how to use it, and now I'm learning SQL thanks to you!!

I've got the June CTP Sql 2005 but SSIS doesn't work

"Beta period is over".

I wonder if I'll be able to call any SSIS programatically. Database Engines works fine, as usual.

I meant from BIDS I can't open any SSIS|||

Have you uninstalled the CTP and installed RTM?

What error are you getting?

sql

I've got a SQL database and I need to see what the data it looks like

Is there a program like Access that can open a SQL database and display the information?

You can link the tables and view data using Access.

You can also install the SQL Client tools and use Enterprise Manager or Management Studio (depending on version).

|||Hi,

If you have Access in place which supports ADP projects you can establish a connection to the database and will be able to view almost all user defined objects in the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

I've broken my SQL Server 2K - Please help

HI,
Having spent some time playing around with replication across the net, I
decided to start from scratch with some hints and tips on other sites BUT I
started to remove the replication in the wrong order and it now wont allow me
to disable replication.
The message I get is;
error 3724 cannot drop procedure dbo.sp_selxxxx because its being used for
replication
Any ideas?
Thanks
Pls try using sp_removedbreplication to get rid of any remaining system
objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
I put that SP into the Analyzer and the same message still appears!
What now!
Thanks
TIM
"Paul Ibison" wrote:

> Pls try using sp_removedbreplication to get rid of any remaining system
> objects.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hi Paul,
Dont worry, have sorted it now.
Thanks for that.
TIM
Also just one other question.....
When I set up the distibutor/publisher on the main server I then setup a
subscriber PUSH to my other server. Do I need to setup a PULL/PUSH on the
subscriber as well or just that one well alone.
Thanks
""confused"" wrote:
[vbcol=seagreen]
> Hi Paul,
> I put that SP into the Analyzer and the same message still appears!
> What now!
> Thanks
> TIM
> "Paul Ibison" wrote:
|||Did you get the same message when running the proc or after running it and
when trying to delete the proc? Presumably the proc is in the same database?
Anyway, please try dropping them from Query analyser directly:
drop procedure dbo.[sp_sel_EE1F8F95DC214CA5ED44BBA96EA645E7_pal]
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

I've been told that SQL Server replication is buggy and unreliable

Hi, I've recently been told that SQL Server replication is buggy and unreliable. I find this very hard to believe. I'm working with a subcontractor in another state that is hosting a large DB for us. I want to replicate a read-only copy of the DB over
the internetl to our local site. They also tell me that it is difficult to recover when there is a problem with replication. I again find this hard to believe. The worst case it to re-snapshot.
I've been working with SQL Server since 2002, but not replication. I've found SQL Server to be very reliable and nice to work with.
I would greatly appreciate honest feedback.
Thanks very much,
Griff
Griff,
I've used it for 4 years and found it to be robust. I teach replication to
financial staff in London and it is widely used by insurance companies and
banks, which might make you feel more confident about its usefulness.
To be honest, almost always what I initially thought was a limitation in the
technology actually turned out to be me using the wrong implementation, or
not being aware of existing workarounds.
For someone getting started, BOL (and Hilary's upcoming books) should
provide the foundation needed to make correct choices. For troubleshooting
I'd advise anyone starting out to implement replication in as many different
ways as possible, see when you've broken it and then research this newsgroup
thoroughly.
HTH,
Paul Ibison
Also, I keep a scratchpad of errors/solutions I've seen, or read about and
tested at www.replicationanswers.com.
|||Griff,
I've used it for 4 years and found it to be robust. I teach replication to
financial staff in London and it is widely used by insurance companies and
banks, which might make you feel more confident about its usefulness.
To be honest, almost always what I initially thought was a limitation in the
technology actually turned out to be me using the wrong implementation, or
not being aware of existing workarounds.
For someone getting started, BOL (and Hilary's upcoming books) should
provide the foundation needed to make correct choices. For troubleshooting
I'd advise anyone starting out to implement replication in as many different
ways as possible, see when you've broken it and then research this newsgroup
thoroughly.
HTH,
Paul Ibison
Also, I keep a scratchpad of errors/solutions I've seen, or read about and
tested at www.replicationanswers.com.
|||Replication is not buggy per se.
the problem with replication is that it is depenedent on often reliable
links. Sometimes a LAN connection which is good enough for day to day use,
will turn out to be too unreliable or unstable for replication.
Replication is resilient to many errors, but not a poor connection.
Its akin to someone taking a gravel road and complaining about the car.
Another problem with replication is that it is a complex product that few
dba's really understand well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eI8AqChXEHA.384@.TK2MSFTNGP10.phx.gbl...
> Griff,
> I've used it for 4 years and found it to be robust. I teach replication to
> financial staff in London and it is widely used by insurance companies and
> banks, which might make you feel more confident about its usefulness.
> To be honest, almost always what I initially thought was a limitation in
the
> technology actually turned out to be me using the wrong implementation, or
> not being aware of existing workarounds.
> For someone getting started, BOL (and Hilary's upcoming books) should
> provide the foundation needed to make correct choices. For troubleshooting
> I'd advise anyone starting out to implement replication in as many
different
> ways as possible, see when you've broken it and then research this
newsgroup
> thoroughly.
> HTH,
> Paul Ibison
> Also, I keep a scratchpad of errors/solutions I've seen, or read about and
> tested at www.replicationanswers.com.
>
|||Replication is not buggy per se.
the problem with replication is that it is depenedent on often reliable
links. Sometimes a LAN connection which is good enough for day to day use,
will turn out to be too unreliable or unstable for replication.
Replication is resilient to many errors, but not a poor connection.
Its akin to someone taking a gravel road and complaining about the car.
Another problem with replication is that it is a complex product that few
dba's really understand well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eI8AqChXEHA.384@.TK2MSFTNGP10.phx.gbl...
> Griff,
> I've used it for 4 years and found it to be robust. I teach replication to
> financial staff in London and it is widely used by insurance companies and
> banks, which might make you feel more confident about its usefulness.
> To be honest, almost always what I initially thought was a limitation in
the
> technology actually turned out to be me using the wrong implementation, or
> not being aware of existing workarounds.
> For someone getting started, BOL (and Hilary's upcoming books) should
> provide the foundation needed to make correct choices. For troubleshooting
> I'd advise anyone starting out to implement replication in as many
different
> ways as possible, see when you've broken it and then research this
newsgroup
> thoroughly.
> HTH,
> Paul Ibison
> Also, I keep a scratchpad of errors/solutions I've seen, or read about and
> tested at www.replicationanswers.com.
>

I've been hacked....

I'm a novice with SQL Server (using SQL 7.0). It appears I've been hacked a
nd I'm considering nuking the whole network and starting over (it's only a h
alf-dozen machines). I would like to upgrade to SQL 2000.
Recently we lost our connection to SQL (we get a network error even on the m
achine the data is on). I've done regular backups, but I don't know how to
restore the backup to a different machine. I know you can do it using detac
h-attach, but I didn't know
we were going to lose the server, so I never "detached" the database. I tri
ed to restore a database to a different machine once before and I remember i
t wouldn't let me do it.
Does anyone have any suggestions? I could try installing 2000 on the machin
e in question, but I'm afraid I might lose the data permanently that way.
Thanks for your help.
Reed SprungFirst off...what errors are you getting when you try to restore the
database?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Reed" <rsprung@.solutionsom.com> wrote in message
news:429DCDE6-22FB-4663-9975-C6FE5FB0484E@.microsoft.com...
> I'm a novice with SQL Server (using SQL 7.0). It appears I've been hacked
and I'm considering nuking the whole network and starting over (it's only a
half-dozen machines). I would like to upgrade to SQL 2000.
> Recently we lost our connection to SQL (we get a network error even on the
machine the data is on). I've done regular backups, but I don't know how to
restore the backup to a different machine. I know you can do it using
detach-attach, but I didn't know we were going to lose the server, so I
never "detached" the database. I tried to restore a database to a different
machine once before and I remember it wouldn't let me do it.
> Does anyone have any suggestions? I could try installing 2000 on the
machine in question, but I'm afraid I might lose the data permanently that
way.
> Thanks for your help.
> Reed Sprung|||I haven't tried. I don't have another machine running SQL right now (I will
soon). I just remember that I couldn't do it before (this was probably a c
ouple of years ago).
Are you telling me that I should be able to restore to a different machine?
I have the backup files and I have the original data files to work with if
needed.
Should I install 7.0 on another machine to try to recover the data, or shoul
d I just install a new copy of 2000 and try it?
Thanks for your reply.
Reed|||I personally would re-install 7.0, restore the backup (yes, you can do it if
the backup file is good), and then follow the Microsoft recommendations to
pgrade to SQL Server 2000.
I restore from machine to different machine all the time, especially when I
need fresh data from a cliet on my development lab boxen.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Reed" <rsprung@.solutionsom.com> wrote in message
news:D927B945-5826-474A-8B4A-9E0B4739ACAB@.microsoft.com...
> I haven't tried. I don't have another machine running SQL right now (I
will soon). I just remember that I couldn't do it before (this was probably
a couple of years ago).
> Are you telling me that I should be able to restore to a different
machine? I have the backup files and I have the original data files to work
with if needed.
> Should I install 7.0 on another machine to try to recover the data, or
should I just install a new copy of 2000 and try it?
> Thanks for your reply.
> Reed

I've been hacked....

I'm a novice with SQL Server (using SQL 7.0). It appears I've been hacked and I'm considering nuking the whole network and starting over (it's only a half-dozen machines). I would like to upgrade to SQL 2000.
Recently we lost our connection to SQL (we get a network error even on the machine the data is on). I've done regular backups, but I don't know how to restore the backup to a different machine. I know you can do it using detach-attach, but I didn't know
we were going to lose the server, so I never "detached" the database. I tried to restore a database to a different machine once before and I remember it wouldn't let me do it.
Does anyone have any suggestions? I could try installing 2000 on the machine in question, but I'm afraid I might lose the data permanently that way.
Thanks for your help.
Reed Sprung
First off...what errors are you getting when you try to restore the
database?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Reed" <rsprung@.solutionsom.com> wrote in message
news:429DCDE6-22FB-4663-9975-C6FE5FB0484E@.microsoft.com...
> I'm a novice with SQL Server (using SQL 7.0). It appears I've been hacked
and I'm considering nuking the whole network and starting over (it's only a
half-dozen machines). I would like to upgrade to SQL 2000.
> Recently we lost our connection to SQL (we get a network error even on the
machine the data is on). I've done regular backups, but I don't know how to
restore the backup to a different machine. I know you can do it using
detach-attach, but I didn't know we were going to lose the server, so I
never "detached" the database. I tried to restore a database to a different
machine once before and I remember it wouldn't let me do it.
> Does anyone have any suggestions? I could try installing 2000 on the
machine in question, but I'm afraid I might lose the data permanently that
way.
> Thanks for your help.
> Reed Sprung
|||I haven't tried. I don't have another machine running SQL right now (I will soon). I just remember that I couldn't do it before (this was probably a couple of years ago).
Are you telling me that I should be able to restore to a different machine? I have the backup files and I have the original data files to work with if needed.
Should I install 7.0 on another machine to try to recover the data, or should I just install a new copy of 2000 and try it?
Thanks for your reply.
Reed
|||I personally would re-install 7.0, restore the backup (yes, you can do it if
the backup file is good), and then follow the Microsoft recommendations to
pgrade to SQL Server 2000.
I restore from machine to different machine all the time, especially when I
need fresh data from a cliet on my development lab boxen.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Reed" <rsprung@.solutionsom.com> wrote in message
news:D927B945-5826-474A-8B4A-9E0B4739ACAB@.microsoft.com...
> I haven't tried. I don't have another machine running SQL right now (I
will soon). I just remember that I couldn't do it before (this was probably
a couple of years ago).
> Are you telling me that I should be able to restore to a different
machine? I have the backup files and I have the original data files to work
with if needed.
> Should I install 7.0 on another machine to try to recover the data, or
should I just install a new copy of 2000 and try it?
> Thanks for your reply.
> Reed

Ive been given a query analizer for MSDE are there others

Greetings,

I've been sent an alternative "Query Analizer" thingy to play with and it seems to do the job - but it ain't my area.

I've posted the tool in a free public place for a short time:
http://www.adoanywhere.com/members/yeohray/7C1_SQLTool.zip

But, for the benefit of people reding this from the archives, please follow
the registerable author link [ http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1&TPN=1 ]

Apart from needing documentation (we all know about that old scenario) it seems good.

It didn't waste my time (and I wouldn't waste yours I hope). Could I have your comments please ?

Also, I'm looking for other tools that do the same as this one - so I can compare functionality. Even without try the freebie, any links please ?

TIAyou've "been sent"? by whom? where's it from? did you virus-check it?

okay, i tried it (yes, i took a chance, i'm stupid that way)

it actually works nicely -- a damn site better at submitting queries to MSDE than the way i was doing it up till now (via a pass-through query in access)

it's cute, too|||Good point about the security!

I'll address that issue with the author, virus scanners here should have picked them up though.

I got it from a chap I was assisting with some Delphi knowledge.

Thanks for checking it out, I didn't understand all of it myself.|||so, this isn't yours, then? i mean, it isn't adoanywhere's? don't they have something similar? how come you posted it on their forum? do you work for adoanywhere? do they mind you mentioning what appears to be a competing product? just what does their product do, anyway? and where did you say you got this little Delphi app, in case it screws somethying up on my machine and i feel like visiting the said chap at his place of residence with a clue-by-four?

:D|||Ther author was having problems with bugs in Delphi and retrieval of stats & plans through ADO. With adoanywhere forum help he eventually got it resolved and posted the tool for us to play with. I've used it witout problems since it was uploaded but I'll make it clearer in future posts and on the forum that all these third party apps are at downloaders risk.

I work for adoanwyhere - I wrote adoanywhere, and it's no problem adding other tools to forum if you know of any. Glad to 'ave 'em.

Mike (adoanywhere)|||okay, thanks for the clarification

by the way, does your product allow me to run queries against MSDE too?

i should mention i haven't a clue what ADO is (and no particular desire to find out)|||Yeah, you can query MSDE with adoanywhere.

ADO is an interface (bit like ODBC), so you can query databases that have and ADO "driver" - called provider in ado lingo.

Most databses have an ADO provider. Problem is though that ADO has loads of properties are that may or may not be available at any give time in your application.

Adoanywhere lets you fiddle with the ado settings. So although you can query data with it, it does more complex stuff at its heart.

I generally use adoanywhere for querying & checking ado properties - then switch to proprietry tools (like query analizer and Enterprise manager) for database management stuff. Horses for courses as they say.

All the best, Mike.sql

I've been curious out XQuery

XPath is pretty powerful. What is it that XQuery can
do that I can't already do with XPath? What makes it
so crucial that we have it?
Enlighten me...
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Tony Lavinio" <stylusstudio@.stylusstudio.com> wrote in message
news:Ntcce.11489$f6.6526@.fe04.lga...
> Dear SQL Server users on microsoft.public.sqlserver.xml,
> Microsoft recently announced that they are dropping XQuery from
> their next release of the .NET Framework, 2.0 (Whidbey). Since
> Microsoft ships .NET Framework only every 3 or so years, cutting
> XQuery from Whidbey means that the next opportunity for XQuery
> to find its way into .NET Framework won't be until around 2009,
> and even then it's far from a sure thing. That's why it's so
> important that the community of XQuery developers makes XQuery a
> priority for Microsoft.
> Stylus Studio believes that XQuery's has broader applicability
> then just an XML querying language but is also an important
> mid-tier data integration and Web service enabling technology,
> and therefore should not be dropped from the Microsoft .NET 2.0
> Framework. Many Microsoft MVP's agree and Stylus Studio is
> asking for any developer who shares this vision and to sign the
> petition online at: http://www.stylusstudio.com/xqueryforall/
> --
> Sincerely,
> Tony Lavinio
> Stylus Studio Principal Software Architect
> http://www.stylusstudio.com/
Hi Robbe,
Comparing XPath with XQuery can't be done in the scope of a message.
There is fare amount of material on XPath2/XQuery (starting from
w3c.org)
Here a quick highlight:
- XQuery is based on XPath2.
- The type system is more flexible (new built-in type sequence, XSD
types).
- The built-in function library has been vastly improved.
- XQuery expresses grouping/ordering in a very intuitive way
(for/where/order by).
Ivan
|||Comparing XQuery vs XPath 2.0:
XQuery 1.0 = XPath 2.0 - (sibling, ancestor and namespace axes) + element
construction + variable binding + order by + user-defined functions + Prolog
(namespace binding etc.)
I expect that 60% of the XQuery 1.0 queries written will also be valid XPath
2.0 queries (disregarding the namespace bindings in the XQuery prolog).
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23kTfxQrTFHA.2680@.tk2msftngp13.phx.gbl...
> XPath is pretty powerful. What is it that XQuery can
> do that I can't already do with XPath? What makes it
> so crucial that we have it?
> Enlighten me...
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.masterado.net/home/listings.aspx
>
> "Tony Lavinio" <stylusstudio@.stylusstudio.com> wrote in message
> news:Ntcce.11489$f6.6526@.fe04.lga...
>
|||What is a user-defined function for XQuery? Can you elaborate?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23i$WOo%23TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Comparing XQuery vs XPath 2.0:
> XQuery 1.0 = XPath 2.0 - (sibling, ancestor and namespace axes) + element
> construction + variable binding + order by + user-defined functions +
> Prolog (namespace binding etc.)
> I expect that 60% of the XQuery 1.0 queries written will also be valid
> XPath 2.0 queries (disregarding the namespace bindings in the XQuery
> prolog).
> Best regards
> Michael
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23kTfxQrTFHA.2680@.tk2msftngp13.phx.gbl...
>
|||XQuery allows users to define XQuery functions in the prolog (and even
provides the option for function library modules). These functions can
either be external, or written using XQuery.
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%233O6sKEUFHA.736@.TK2MSFTNGP10.phx.gbl...
> What is a user-defined function for XQuery? Can you elaborate?
> --
> 2004 and 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.masterado.net/home/listings.aspx
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23i$WOo%23TFHA.1796@.TK2MSFTNGP15.phx.gbl...
>
|||There's a fairly basic tutorial at W3Schools that serves as a good
beginner's intro:
http://www.w3schools.com/xquery/default.asp
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%233O6sKEUFHA.736@.TK2MSFTNGP10.phx.gbl...
What is a user-defined function for XQuery? Can you elaborate?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23i$WOo%23TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Comparing XQuery vs XPath 2.0:
> XQuery 1.0 = XPath 2.0 - (sibling, ancestor and namespace axes) + element
> construction + variable binding + order by + user-defined functions +
> Prolog (namespace binding etc.)
> I expect that 60% of the XQuery 1.0 queries written will also be valid
> XPath 2.0 queries (disregarding the namespace bindings in the XQuery
> prolog).
> Best regards
> Michael
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23kTfxQrTFHA.2680@.tk2msftngp13.phx.gbl...
>

I've been curious out XQuery

XPath is pretty powerful. What is it that XQuery can
do that I can't already do with XPath? What makes it
so crucial that we have it?
Enlighten me...
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Tony Lavinio" <stylusstudio@.stylusstudio.com> wrote in message
news:Ntcce.11489$f6.6526@.fe04.lga...
> Dear SQL Server users on microsoft.public.sqlserver.xml,
> Microsoft recently announced that they are dropping XQuery from
> their next release of the .NET Framework, 2.0 (Whidbey). Since
> Microsoft ships .NET Framework only every 3 or so years, cutting
> XQuery from Whidbey means that the next opportunity for XQuery
> to find its way into .NET Framework won't be until around 2009,
> and even then it's far from a sure thing. That's why it's so
> important that the community of XQuery developers makes XQuery a
> priority for Microsoft.
> Stylus Studio believes that XQuery's has broader applicability
> then just an XML querying language but is also an important
> mid-tier data integration and Web service enabling technology,
> and therefore should not be dropped from the Microsoft .NET 2.0
> Framework. Many Microsoft MVP's agree and Stylus Studio is
> asking for any developer who shares this vision and to sign the
> petition online at: http://www.stylusstudio.com/xqueryforall/
> --
> Sincerely,
> Tony Lavinio
> Stylus Studio Principal Software Architect
> http://www.stylusstudio.com/Hi Robbe,
Comparing XPath with XQuery can't be done in the scope of a message.
There is fare amount of material on XPath2/XQuery (starting from
w3c.org)
Here a quick highlight:
- XQuery is based on XPath2.
- The type system is more flexible (new built-in type sequence, XSD
types).
- The built-in function library has been vastly improved.
- XQuery expresses grouping/ordering in a very intuitive way
(for/where/order by).
Ivan|||Comparing XQuery vs XPath 2.0:
XQuery 1.0 = XPath 2.0 - (sibling, ancestor and namespace axes) + element
construction + variable binding + order by + user-defined functions + Prolog
(namespace binding etc.)
I expect that 60% of the XQuery 1.0 queries written will also be valid XPath
2.0 queries (disregarding the namespace bindings in the XQuery prolog).
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23kTfxQrTFHA.2680@.tk2msftngp13.phx.gbl...
> XPath is pretty powerful. What is it that XQuery can
> do that I can't already do with XPath? What makes it
> so crucial that we have it?
> Enlighten me...
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.masterado.net/home/listings.aspx
>
> "Tony Lavinio" <stylusstudio@.stylusstudio.com> wrote in message
> news:Ntcce.11489$f6.6526@.fe04.lga...
>|||What is a user-defined function for XQuery? Can you elaborate?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23i$WOo%23TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Comparing XQuery vs XPath 2.0:
> XQuery 1.0 = XPath 2.0 - (sibling, ancestor and namespace axes) + element
> construction + variable binding + order by + user-defined functions +
> Prolog (namespace binding etc.)
> I expect that 60% of the XQuery 1.0 queries written will also be valid
> XPath 2.0 queries (disregarding the namespace bindings in the XQuery
> prolog).
> Best regards
> Michael
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23kTfxQrTFHA.2680@.tk2msftngp13.phx.gbl...
>|||XQuery allows users to define XQuery functions in the prolog (and even
provides the option for function library modules). These functions can
either be external, or written using XQuery.
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%233O6sKEUFHA.736@.TK2MSFTNGP10.phx.gbl...
> What is a user-defined function for XQuery? Can you elaborate?
> --
> 2004 and 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.masterado.net/home/listings.aspx
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23i$WOo%23TFHA.1796@.TK2MSFTNGP15.phx.gbl...
>|||There's a fairly basic tutorial at W3Schools that serves as a good
beginner's intro:
http://www.w3schools.com/xquery/default.asp
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%233O6sKEUFHA.736@.TK2MSFTNGP10.phx.gbl...
What is a user-defined function for XQuery? Can you elaborate?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23i$WOo%23TFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Comparing XQuery vs XPath 2.0:
> XQuery 1.0 = XPath 2.0 - (sibling, ancestor and namespace axes) + element
> construction + variable binding + order by + user-defined functions +
> Prolog (namespace binding etc.)
> I expect that 60% of the XQuery 1.0 queries written will also be valid
> XPath 2.0 queries (disregarding the namespace bindings in the XQuery
> prolog).
> Best regards
> Michael
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23kTfxQrTFHA.2680@.tk2msftngp13.phx.gbl...
>

I'VE 3 QUESTIONS

FIRST, If I make a view like this:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\";Extended properties=DBase III')...dav
So If it is called from Client, Does Data to be read from client's D:\ or
server's D:\?
If Data is still read from server, how can data be read from client?
Second, At Ms Access, I ever make query like this.
SELECT noid, FIRST(Fddate) AS fdate
from TB1
GROUP BY noid
I wanna make like it in SQL Server 2000. Can I do it?
Third, I've data like it
field1 field2
--
a1 3
a1 4
a1 23
b1 35
b1 30
b1 31
I wanna delete records, but first record of group (field1) is not deleted.
How syntax SQL to do it?> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?

> If Data is still read from server, how can data be read from client?
Its read from the server, if you want to read it from the client you have
to put the data on a network share that the server can reach and open it.

> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
With no background information thatll be just a guess to, but you can use
semething like MIN()

> How syntax SQL to do it?
Delete
From SomeTable ST
Where field2 NOT IN
(Select TOP 1 field2 From sometable Where ST2.field1 = ST.field1 order by
field2)
HTH, Jens Suessmeyer.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eH9MZy2jFHA.3448@.TK2MSFTNGP12.phx.gbl...
> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>|||> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
It is read from the server. To read it from the client, try using the UNC
name of the shared folder in the "data source".

> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
Use MIN or MAX aggregate functions.

> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
delete t1
where exists(select * from t1 as a where a.field1 = t1.field1 and a.field2 <
t1.field2)
--or
delete t1
where field2 > (select min(a.field2) from t1 as a where a.field1 = t1.field1
)
AMB
"Bpk. Adi Wira Kusuma" wrote:

> FIRST, If I make a view like this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="D:\";Extended properties=DBase III')...dav
> So If it is called from Client, Does Data to be read from client's D:\ or
> server's D:\?
> If Data is still read from server, how can data be read from client?
> Second, At Ms Access, I ever make query like this.
> SELECT noid, FIRST(Fddate) AS fdate
> from TB1
> GROUP BY noid
> I wanna make like it in SQL Server 2000. Can I do it?
> Third, I've data like it
> field1 field2
> --
> a1 3
> a1 4
> a1 23
> b1 35
> b1 30
> b1 31
> I wanna delete records, but first record of group (field1) is not deleted.
> How syntax SQL to do it?
>
>

its been a while since Ive used sql....

...and I was using mySql b4, but on ms sql server when you create a table whats the syntax for specifying a field that auto increments for each new record added?

'm trying the following:

1> create table maillist(id autoincrement(), email varchar)
2> go
Msg 170, Level 15, State 1, Server ****, Line 1
Line 1: Incorrect syntax near ')'.
1>IDENTITY is the keyword used in SQL Server

CREATE TABLE [dbo].[Debug] (
[DebugID] [int] IDENTITY (1, 1) NOT NULL ,
[DateEntered] [datetime] NULL ,
[Message] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

|||i take it

dbo = the db name

debug = the table

dbugid = my field name

int = type

what is: on [primary]?|||what does the (1,1) signify?|||The seed value and incremental step for the identity column. (e.g starts from 1 and increases 1 at a time 1,2,3,4,5...)|||ahhh

I getcha :)|||dbo is the owner of the table (dbo is best).
Debug is the table.
debugid is the field name.

Primary is the file group (you can normally ignore it, this is script generated by the Enterprise Manager).

issues with replication on a cluster?

OK please forgive me. I dont normally cross post, or ask the same question
twice, but Im in quite a pickle here.
Ive been using replication in a non clustered environment for years now. Ive
never seen extreme slowness with the initial snapshot of replication like Im
seeing currently. And to top it off my boxes are the fastest Ive ever used.
Dual 3.0 Zeons.
4 gigs Ram.
1 Gigabit network.
1 way, Transactional, Continuous, Remote Distributor (on the Subscriber), 25
gig database.
Im getting better snapshot performance on two old, 512 mb ram development
boxes. Could this be a Clustering issue?
SQL2K SP3
TIA, ChrisR
I recall having something similar happen on one of our cluster servers.
IIRC, we stopped the snapshot and restarted it, and got better performance.
I'll have to confirm this with the other DBA who was working on this
problem, but won't be able to do this till Tuesday.
"ChrisR" <bla@.noemail.com> wrote in message
news:%230IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
> Ive
> never seen extreme slowness with the initial snapshot of replication like
> Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
> used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
> 25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Hi
Never seen that, but I have seen general performance drop when moving to a
cluster as the SAN was not as optimal as everyone thought.
The initial snapshot is very IO intensive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <bla@.noemail.com> wrote in message
news:#0IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
Ive
> never seen extreme slowness with the initial snapshot of replication like
Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>

issues with replication on a cluster?

OK please forgive me. I dont normally cross post, or ask the same question
twice, but Im in quite a pickle here.
Ive been using replication in a non clustered environment for years now. Ive
never seen extreme slowness with the initial snapshot of replication like Im
seeing currently. And to top it off my boxes are the fastest Ive ever used.
Dual 3.0 Zeons.
4 gigs Ram.
1 Gigabit network.
1 way, Transactional, Continuous, Remote Distributor (on the Subscriber), 25
gig database.
Im getting better snapshot performance on two old, 512 mb ram development
boxes. Could this be a Clustering issue?
SQL2K SP3
TIA, ChrisR
I recall having something similar happen on one of our cluster servers.
IIRC, we stopped the snapshot and restarted it, and got better performance.
I'll have to confirm this with the other DBA who was working on this
problem, but won't be able to do this till Tuesday.
"ChrisR" <bla@.noemail.com> wrote in message
news:%230IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
> Ive
> never seen extreme slowness with the initial snapshot of replication like
> Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
> used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
> 25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Hi
Never seen that, but I have seen general performance drop when moving to a
cluster as the SAN was not as optimal as everyone thought.
The initial snapshot is very IO intensive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <bla@.noemail.com> wrote in message
news:#0IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
Ive
> never seen extreme slowness with the initial snapshot of replication like
Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>

Issues

Hi,
I've installed SQL Express as the default 'SQLExpress' install (windows
authentication) and while I can connect fine to my created databases from VS
Web Edition 2005 (using the test server) I cannot get access to the databases
from IIS http://localhost/test.aspx for example. I've worked through various
issues to get to this point and I now just want to use a connection string
i.e. string strConn = "Initial Catalog=Pandora; Integrated Security=True;
Database=Pandora; Server=WireslessBliss;"; to connect to my database and
while it doesn't initially throw an error as soon as I run the
objConn.Open(); line:
SqlConnection objConn = new SqlConnection(strConn);
SqlCommand objCommand = new SqlCommand("SELECT * FROM CatInfo2;", objConn);
objConn.Open();
(using the namespaces <%@. Import Namespace="System.Data"%>
<%@. Import Namespace="System.Data.SqlClient"%> )
it says this...Exception Details: System.Data.SqlClient.SqlException:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. And points to the objConn.Open();
line.
I am really frustrated by this as all I am trying to do is connect so I can
do a little hand coding without VS to get more of a feel for whats going on
with dotNet. I am just starting SQL Express as a move up from Access
Can someone PLEASE help!!
Thanks in advance.
Alex
Try this one:
Dim cn As ADODB.Connection
Set cn = New Connection
cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"AttachDBFileName=" & App.Path & "\BDatos.mdf;Data
Source=servidor\sqlexpress"
73
"Alex" <Alex@.discussions.microsoft.com> escribi en el mensaje
news:40FA2342-7D46-44B4-A18A-3626B4C06541@.microsoft.com...
> Hi,
> I've installed SQL Express as the default 'SQLExpress' install (windows
> authentication) and while I can connect fine to my created databases from
> VS
> Web Edition 2005 (using the test server) I cannot get access to the
> databases
> from IIS http://localhost/test.aspx for example. I've worked through
> various
> issues to get to this point and I now just want to use a connection string
> i.e. string strConn = "Initial Catalog=Pandora; Integrated Security=True;
> Database=Pandora; Server=WireslessBliss;"; to connect to my database and
> while it doesn't initially throw an error as soon as I run the
> objConn.Open(); line:
> SqlConnection objConn = new SqlConnection(strConn);
> SqlCommand objCommand = new SqlCommand("SELECT * FROM CatInfo2;",
> objConn);
> objConn.Open();
> (using the namespaces <%@. Import Namespace="System.Data"%>
> <%@. Import Namespace="System.Data.SqlClient"%> )
> it says this...Exception Details: System.Data.SqlClient.SqlException:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding. And points to the
> objConn.Open();
> line.
> I am really frustrated by this as all I am trying to do is connect so I
> can
> do a little hand coding without VS to get more of a feel for whats going
> on
> with dotNet. I am just starting SQL Express as a move up from Access
> Can someone PLEASE help!!
> Thanks in advance.
> Alex

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615
> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odb c_view'));
select * from sys.sql_modules where object_id =
object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) =
'spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron
|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>
|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>
|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO