Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Can't you have a variable TOP in a select statement?

Hi,
I got a stored procedure like this
CREATE PROCEDURE dbo.readImport
(
@.Start INTEGER,
@.Number INTEGER
)
AS
SELECT TOP @.Number * FROM Import WHERE RowID >= @.Start ORDER BY RowID
GO
However, it doesn't seem to like having an unknown @.Number.
Any ideas?
MortenHi Morten,
If you are using SQL 2k its not possible.
The only thing is to use dynmiac sql for that.
HTH, Jens Suessmeyer.|||Ok, thanks
Morten
On Fri, 11 Nov 2005 09:30:25 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>|||... or SET @.@.ROWCOUNT...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131697825.522144.101160@.g49g2000cwa.googlegroups.com...
> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>

Can't write to SQL Table

I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
situation:
Microsoft SQL Server running Windows 2000 Server. Workstation connecting
via ODBC using SQL Driver.
We have a program on the workstation that pulls information from a single
table. There are over 800 tables in the database. For some reason the
application "scans" the entire database every time it pulls data from the
table we need. This takes a very long time. To try to combat this, we
created a restricted ID that only has access to the table we want. Now
reading from the table much faster.
However, now we can't write to the table. The table name is
"ups_work_table". When we try to write data to it, we get an error
"ups_work_table_trig_ins No data found for row".
So the admin person (as I understand it) made the ID part of a group that
has greater rights. The problem here is that again the ID has access to all
the tables and reading/writing is painfully slow. There has been talk of
groups & roles and how the group settings override the settings of the ID.
So restricting the access of the ID is defeated by giving it greater rights.
My question is: How can we restrict the ID to access a single table and
give that ID read, write, insert, & delete permissions for that table?
Thanks.
Hi
The error "ups_work_table_trig_ins No data found for row" looks like an
error getting raised by some code in the "ups_work_table" insert trigger.
Have a look at that trigger. Possibly, a lookup is beign done on some other
data, and because a specific user is expected by the code, it fails.
Regards
Mike
"Terry Olsen" wrote:

> I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
> situation:
> Microsoft SQL Server running Windows 2000 Server. Workstation connecting
> via ODBC using SQL Driver.
> We have a program on the workstation that pulls information from a single
> table. There are over 800 tables in the database. For some reason the
> application "scans" the entire database every time it pulls data from the
> table we need. This takes a very long time. To try to combat this, we
> created a restricted ID that only has access to the table we want. Now
> reading from the table much faster.
> However, now we can't write to the table. The table name is
> "ups_work_table". When we try to write data to it, we get an error
> "ups_work_table_trig_ins No data found for row".
> So the admin person (as I understand it) made the ID part of a group that
> has greater rights. The problem here is that again the ID has access to all
> the tables and reading/writing is painfully slow. There has been talk of
> groups & roles and how the group settings override the settings of the ID.
> So restricting the access of the ID is defeated by giving it greater rights.
> My question is: How can we restrict the ID to access a single table and
> give that ID read, write, insert, & delete permissions for that table?
> Thanks.
>
>

Can't write to SQL Table

I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
situation:
Microsoft SQL Server running Windows 2000 Server. Workstation connecting
via ODBC using SQL Driver.
We have a program on the workstation that pulls information from a single
table. There are over 800 tables in the database. For some reason the
application "scans" the entire database every time it pulls data from the
table we need. This takes a very long time. To try to combat this, we
created a restricted ID that only has access to the table we want. Now
reading from the table much faster.
However, now we can't write to the table. The table name is
"ups_work_table". When we try to write data to it, we get an error
"ups_work_table_trig_ins No data found for row".
So the admin person (as I understand it) made the ID part of a group that
has greater rights. The problem here is that again the ID has access to all
the tables and reading/writing is painfully slow. There has been talk of
groups & roles and how the group settings override the settings of the ID.
So restricting the access of the ID is defeated by giving it greater rights.
My question is: How can we restrict the ID to access a single table and
give that ID read, write, insert, & delete permissions for that table?
Thanks.Hi
The error "ups_work_table_trig_ins No data found for row" looks like an
error getting raised by some code in the "ups_work_table" insert trigger.
Have a look at that trigger. Possibly, a lookup is beign done on some other
data, and because a specific user is expected by the code, it fails.
Regards
Mike
"Terry Olsen" wrote:

> I'm not the SQL admin, but I'm trying to help fix a problem. Here's the
> situation:
> Microsoft SQL Server running Windows 2000 Server. Workstation connecting
> via ODBC using SQL Driver.
> We have a program on the workstation that pulls information from a single
> table. There are over 800 tables in the database. For some reason the
> application "scans" the entire database every time it pulls data from the
> table we need. This takes a very long time. To try to combat this, we
> created a restricted ID that only has access to the table we want. Now
> reading from the table much faster.
> However, now we can't write to the table. The table name is
> "ups_work_table". When we try to write data to it, we get an error
> "ups_work_table_trig_ins No data found for row".
> So the admin person (as I understand it) made the ID part of a group that
> has greater rights. The problem here is that again the ID has access to a
ll
> the tables and reading/writing is painfully slow. There has been talk of
> groups & roles and how the group settings override the settings of the ID.
> So restricting the access of the ID is defeated by giving it greater right
s.
> My question is: How can we restrict the ID to access a single table and
> give that ID read, write, insert, & delete permissions for that table?
> Thanks.
>
>

Cant write longer string in the record

I have a field which was varchar before but I changed it to text.

But i can't write in it enough text as i wish. This field is important becouse it holds SQL senences which i parse latter in my application and than i execute it.

it says <Long Text> and i can't enter any more characters.You got more than 8000 bytes of data?

Look up READTEXT WRITETEXT.

Welcome to a whole new world of hurt.

should've stayed with varchar(8000)|||Each "statement" should easily fit into an 8000-character field. Add a table that would organize the statements into batches, then restructure your original table by adding FK from your BatchMaster, and a Statement Sequence field (what statement comes first, second, etc. in each batch) Ability to read/write text fields is good, but when there is no need...there is no need ;)

Cant work out this SQL to return 4 random records

I've got a product table in SQL 2000 that contains say these rows:

ProductID int
ProductName varchar
IsSpecial bit

I want to always return 4 random specials from a query and can do this fine by using:

SELECT TOP 4 ProductID,ProductName
FROM Products WHERE IsSpecial = 1
ORDER BY NEWID()

This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.

What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?

Is this possible?

Thanks

Hello my friend,

The following SQL should work. If you have any questions or problems with it, please let me know.

Kind regards

Scotty

SELECT TOP 4 * FROM
(
SELECT TOP 4 ProductID, ProductName FROM Products WHERE IsSpecial = 1

UNION ALL

SELECT ProductID, ProductName FROM Products WHERE IsSpecial = 0

) myTable

ORDER BY IsSpecial DESC, NewID()

|||

Excellent Scotty, that does exactly what i was looking for!

Cheers.

Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?

I'm writing a stored procedure for a keyword search in a Word or PDF
doc which i've done through Index Server and linked the results to SQL
Server.
Part of my stored proc is shown below in which for a FREETEXT keyword
search i'm using a variable "@.searchstring", which i have to, is not
working.
I know it works with hard text but
Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
END?
Can anyone please guide me how to use a variable in FREETEXT
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM SCOPE(''
"c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
FREETEXT(''@.searchstring'')')Hi,
You need to resort to dynamic SQL, i.e....
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
REMEMBER!!!!!! ====>>>>>>>
To prevent injection make absolutely sure you replace any single quotes with
2 single quotes...
-- this one fails and is subject to injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
exec( 'print ''' + @.searchtext + '''' )
go
-- this one works because prevent injection...
declare @.searchtext varchar(100)
set @.searchtext = 'tony''s injection'
set @.searchtext = REPLACE( @.searchtext, '''', ''' )
exec( 'print ''' + @.searchtext + '''' )
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137755654.791956.239920@.z14g2000cwz.googlegroups.com...
> I'm writing a stored procedure for a keyword search in a Word or PDF
> doc which i've done through Index Server and linked the results to SQL
> Server.
> Part of my stored proc is shown below in which for a FREETEXT keyword
> search i'm using a variable "@.searchstring", which i have to, is not
> working.
> I know it works with hard text but
> Is there any way i can use a Variable in OPENQUERIES or is this my DEAD
> END?
> Can anyone please guide me how to use a variable in FREETEXT
> Thanks in Advance
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM SCOPE(''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE
> FREETEXT(''@.searchstring'')')
>|||Thanks for your help
i tried using above idea and some other examples.
The code shown below is working perfectly in the analyzer. I want to
create a view with the results
Is it possible ?
Thanks in Advance
DECLARE @.searchstring varchar(22)
SET @.searchstring = 'aspnet'
declare @.strSQL varchar(244)
select @.strSQL='select FileName,Path from scope(''''
"c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
char(39)+')'
select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
@.strSQL+ char(39)+ ')'
exec (@.strSQL)
Something like
CREATE VIEW FileSearchResults AS (@.strSQL)
which is not working|||Hi Savvy,
Sorry - you won't be able to create a view for that unless your search
string is hard-coded and never changes.
You could write a stored procedure that accepts the search string as a
parameter.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137764139.455350.325970@.o13g2000cwo.googlegroups.com...
> Thanks for your help
> i tried using above idea and some other examples.
> The code shown below is working perfectly in the analyzer. I want to
> create a view with the results
> Is it possible ?
> Thanks in Advance
>
> DECLARE @.searchstring varchar(22)
> SET @.searchstring = 'aspnet'
> declare @.strSQL varchar(244)
> select @.strSQL='select FileName,Path from scope(''''
> "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains ('
> select @.strSQL=@.strSQL +char(39)+ char(39)+ @.searchstring +char(39)+
> char(39)+')'
> select @.strSQL='select * from openquery(FileSystem,'+ char(39)+
> @.strSQL+ char(39)+ ')'
> exec (@.strSQL)
>
> Something like
> CREATE VIEW FileSearchResults AS (@.strSQL)
> which is not working
>|||Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i
hardcore the @.searchstring with the word which doesn't change.
I just want to use a variable working over there. Can u please help me
in this
Thanks in Advance
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO|||CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
SET @.searchstring = REPLACE( @.searchstring, '''', ''' )
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT('' + @.searchstring + '')'')')
SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
F.PATH AND C.DefaultID=1
GO
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137766282.280852.185740@.g14g2000cwa.googlegroups.com...
> Thank you very much for your help and time Tony Rogerson
> This is my complete stored procedure which is perfectly working when i
> hardcore the @.searchstring with the word which doesn't change.
> I just want to use a variable working over there. Can u please help me
> in this
> Thanks in Advance
> CREATE PROCEDURE SelectIndexServerCVpaths
> (
> @.searchstring varchar(100)
> )
> AS
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'FileSearchResults')
> DROP VIEW FileSearchResults
> EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
> OPENQUERY(FileSystem,''SELECT Directory, FileName,
> DocAuthor, Size, Create, Write, Path FROM
> SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
> FREETEXT(''''@.searchstring'''')'')')
> SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path =
> F.PATH AND C.DefaultID=1
> GO
>|||Thanks for your Great help Tony
I have a strange problem its above code is working in the Query
Analyzer but not working if execute the stored procedure as shown below
i tried but i'm not able to figure out where the problem is
Thanks in Advance
Exec SelectIndexServerCVpaths
@.searchstring = 'aspnet'|||whats the error?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.gmail.com> wrote in message
news:1137770531.573885.223780@.f14g2000cwb.googlegroups.com...
> Thanks for your Great help Tony
> I have a strange problem its above code is working in the Query
> Analyzer but not working if execute the stored procedure as shown below
> i tried but i'm not able to figure out where the problem is
> Thanks in Advance
> Exec SelectIndexServerCVpaths
> @.searchstring = 'aspnet'
>|||I'm sorry Tony
i didn't copy the code properly in my stored procedure this part
exactly FREETEXT('' + @.searchstring + '')'')')
when i copied again
its working perfectly Tony
You dont know how much your help is worth to me
I cant just express in words
I needed to complete project today which i did with your help
Thank you very very very much Tony Rogerson|||I'm really grateful to you Tony
Thanks onceagain

cant view recently added disk

I just added a new disk on a 2003 server box running sql 2000 and i want to
redirect my backups to the new disk (which resides on our EMC), the disk
shows up and i was able to add it as a new resource in cluster manager but it
doesnt show up in sql enterprise manager. Any thoughts? Thanks
You must add the disk as a dependency to the SQL Server.
Add the Resource to the SQL server group. Take the SQL Server offline.
Right click the SQL Server resource (not the group) and choose Properties.
Select the Dependencies tab. Add the new disk resource to the Resource
dependencies list. OK. Bring the entire SQL group online.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ampsman78" <ampsman78@.discussions.microsoft.com> wrote in message
news:29D391B2-28D0-4071-9C01-80DD1C3CA561@.microsoft.com...
> I just added a new disk on a 2003 server box running sql 2000 and i want
to
> redirect my backups to the new disk (which resides on our EMC), the disk
> shows up and i was able to add it as a new resource in cluster manager but
it
> doesnt show up in sql enterprise manager. Any thoughts? Thanks

cant view recently added disk

I just added a new disk on a 2003 server box running sql 2000 and i want to
redirect my backups to the new disk (which resides on our EMC), the disk
shows up and i was able to add it as a new resource in cluster manager but it
doesnt show up in sql enterprise manager. Any thoughts? ThanksYou must add the disk as a dependency to the SQL Server.
Add the Resource to the SQL server group. Take the SQL Server offline.
Right click the SQL Server resource (not the group) and choose Properties.
Select the Dependencies tab. Add the new disk resource to the Resource
dependencies list. OK. Bring the entire SQL group online.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ampsman78" <ampsman78@.discussions.microsoft.com> wrote in message
news:29D391B2-28D0-4071-9C01-80DD1C3CA561@.microsoft.com...
> I just added a new disk on a 2003 server box running sql 2000 and i want
to
> redirect my backups to the new disk (which resides on our EMC), the disk
> shows up and i was able to add it as a new resource in cluster manager but
it
> doesnt show up in sql enterprise manager. Any thoughts? Thanks

cant view recently added disk

I just added a new disk on a 2003 server box running sql 2000 and i want to
redirect my backups to the new disk (which resides on our EMC), the disk
shows up and i was able to add it as a new resource in cluster manager but i
t
doesnt show up in sql enterprise manager. Any thoughts? ThanksYou must add the disk as a dependency to the SQL Server.
Add the Resource to the SQL server group. Take the SQL Server offline.
Right click the SQL Server resource (not the group) and choose Properties.
Select the Dependencies tab. Add the new disk resource to the Resource
dependencies list. OK. Bring the entire SQL group online.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ampsman78" <ampsman78@.discussions.microsoft.com> wrote in message
news:29D391B2-28D0-4071-9C01-80DD1C3CA561@.microsoft.com...
> I just added a new disk on a 2003 server box running sql 2000 and i want
to
> redirect my backups to the new disk (which resides on our EMC), the disk
> shows up and i was able to add it as a new resource in cluster manager but
it
> doesnt show up in sql enterprise manager. Any thoughts? Thanks

Can't view merge agent properties (trying again)

In Enterprise Manager (EM), if you go to: [Replication Monitor -> Agents ->
Merge Agents] you'll see a list of Merge Agents to the right.
I'd like to be able to select one of the items, right-click, and select
"Agent Properties".
When I do this, I get a dialog box that appears. The title of the dialog is
"Connect to SQL Server". It's looking for "Connection Information" and it
wants a Login name and Password.
Well, my SQL Server uses the sa user. So, I tried the sa user and it's
password. Nope!
The SQL Server Agent runs under a windows account. I tried that ID and it's
password. Nope!
The other strange thing about this is that my friend doesn't have this
problem on his laptop/SQL Server. He can view the agent properties no
problem. One difference we can detect is that his SQL Server uses a Windows
Account and mine uses sa. Not sure if that makes a difference.
I also went to [Security -> Logins] and added a new login and gave him every
available permission I could find (using the SQL Server Login Properties
Tabbed Dialg). I tried logging on with this guy. Nope!
So, sa doesn't work. The SQL Server Agent windows ID doesn't work. The new
Login guy didn't work. Ummm. What exactly is it looking for? But for some
reason, if I enter the ID and password I get the same MsgBox:
SQL Server Enterprise Manager
A connection could not be established to XXXX [3023].
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration
properties (by right-clicking on the XXXX [3023] node) and try again.
Thanks in advance,
William Campbell
Hi.
Maybe I mis-read the msdn web page, but I am reading this (about managed
newsgroups):
a.. Unlimited on-line technical support - keep your PSS incidents
a.. A commitment to respond to your post within two business days
The thing is, this hasn't happened. Did I incorrectly enter my post
somehow? It's been more than 2 business days.
Thanks,
William Campbell
"MSDN-Managed" <NO_SPAM> wrote in message
news:9DB1BD5B-54AA-4CA9-BD40-48084F121F8A@.microsoft.com...
> In Enterprise Manager (EM), if you go to: [Replication Monitor ->
Agents ->
> Merge Agents] you'll see a list of Merge Agents to the right.
> I'd like to be able to select one of the items, right-click, and select
> "Agent Properties".
> When I do this, I get a dialog box that appears. The title of the dialog
is
> "Connect to SQL Server". It's looking for "Connection Information" and it
> wants a Login name and Password.
> Well, my SQL Server uses the sa user. So, I tried the sa user and it's
> password. Nope!
> The SQL Server Agent runs under a windows account. I tried that ID and
it's
> password. Nope!
> The other strange thing about this is that my friend doesn't have this
> problem on his laptop/SQL Server. He can view the agent properties no
> problem. One difference we can detect is that his SQL Server uses a
Windows
> Account and mine uses sa. Not sure if that makes a difference.
> I also went to [Security -> Logins] and added a new login and gave him
every
> available permission I could find (using the SQL Server Login Properties
> Tabbed Dialg). I tried logging on with this guy. Nope!
> So, sa doesn't work. The SQL Server Agent windows ID doesn't work. The
new
> Login guy didn't work. Ummm. What exactly is it looking for? But for
some
> reason, if I enter the ID and password I get the same MsgBox:
> --
> SQL Server Enterprise Manager
> --
> A connection could not be established to XXXX [3023].
> Reason: SQL Server does not exist or access denied.
> ConnectionOpen (Connect())..
> Please verify SQL Server is running and check your SQL Server registration
> properties (by right-clicking on the XXXX [3023] node) and try again.
> Thanks in advance,
> William Campbell
>
|||The problem appears to be that you're not using a posting alias that you've
registered with MSDN. You can start that process using the Register link on
this page: http://msdn.microsoft.com/newsgroups/managed/. The MSDN team that
monitors this newsgroup looking for those posts is using a tool that points
out the posts from Managed Customers and your posting address isn't
appearing as one.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
news:OWjeHj%232EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi.
> Maybe I mis-read the msdn web page, but I am reading this (about managed
> newsgroups):
> a.. Unlimited on-line technical support - keep your PSS incidents
> a.. A commitment to respond to your post within two business days
> The thing is, this hasn't happened. Did I incorrectly enter my post
> somehow? It's been more than 2 business days.
> Thanks,
> William Campbell
> "MSDN-Managed" <NO_SPAM> wrote in message
> news:9DB1BD5B-54AA-4CA9-BD40-48084F121F8A@.microsoft.com...
> Agents ->
> is
> it's
> Windows
> every
> new
> some
>
|||Hmm. But I did. The "MSDN-Managed" newgroup post (the original, not the
one with my name) is using the ID that is registered with our Universal
subscription.
I went to the link you mentioned (a few days ago), made sure that I gave our
ID an alias so that our email address didn't appear (thus, the alias
MSDN-Managed and the "NO_SPAM" email address). And submitted the post
through the web interface from the same link you displayed (you sign-in
through that passport account to the web interface).
That's why I'm confused. I went through all the steps. The one from
"MSDN-Managed" (not "William Campbell") is using our subscription id.
William Campbell
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:uRWmYw%232EHA.2788@.TK2MSFTNGP15.phx.gbl...
> The problem appears to be that you're not using a posting alias that
you've
> registered with MSDN. You can start that process using the Register link
on
> this page: http://msdn.microsoft.com/newsgroups/managed/. The MSDN team
that
> monitors this newsgroup looking for those posts is using a tool that
points
> out the posts from Managed Customers and your posting address isn't
> appearing as one.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Please reply to the newsgroups only, thanks.
> "William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
> news:OWjeHj%232EHA.2540@.TK2MSFTNGP09.phx.gbl...
dialog[vbcol=seagreen]
Properties[vbcol=seagreen]
The
>
|||Hmm, I'll have a chat with the manager of that team then, as our internal
tool clearly isn't picking it up, and get somebody to look at your post.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
news:ud31bc$2EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hmm. But I did. The "MSDN-Managed" newgroup post (the original, not the
> one with my name) is using the ID that is registered with our Universal
> subscription.
> I went to the link you mentioned (a few days ago), made sure that I gave
> our
> ID an alias so that our email address didn't appear (thus, the alias
> MSDN-Managed and the "NO_SPAM" email address). And submitted the post
> through the web interface from the same link you displayed (you sign-in
> through that passport account to the web interface).
> That's why I'm confused. I went through all the steps. The one from
> "MSDN-Managed" (not "William Campbell") is using our subscription id.
> William Campbell
> "Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
> news:uRWmYw%232EHA.2788@.TK2MSFTNGP15.phx.gbl...
> you've
> on
> that
> points
> rights.
> dialog
> Properties
> The
>
|||Is there a timeframe for this? It's been a few days (and 8 days since my
initial post under this "Msdn-Managed" ID).
Are you saying that this ID that I'm posting under right now ... doesn't
show up as someone with a Universal account? Currently I'm at the web
interface. I'm signed in. Next to the Sign In/Sign Out button is another
smaller button that you can edit a profile. I looked there and didn't see
anything that I could "check" or fill in that I didn't already.
This is the ID that we have associated with our Univeral account. So, I'm
confused. If it's not showing up as valid - can we take care of the issue so
that it does show up as valid? Because I'm not sure what else I can do here
- and It's been weeks since my initial post.
Thanks,
William Campbell
"Stephen Dybing [MSFT]" wrote:

> Hmm, I'll have a chat with the manager of that team then, as our internal
> tool clearly isn't picking it up, and get somebody to look at your post.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Please reply to the newsgroups only, thanks.
> "William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
> news:ud31bc$2EHA.3616@.TK2MSFTNGP11.phx.gbl...
>
>
|||Have you sent email to ngmsdnfb@.microsoft.com about this yet? The managers
of that team are on that alias and will get back to you fairly quickly. I'm
going to forward this along to them, but you might as well send them mail
yourself as well.
It doesn't look to me like you've followed the directions to set up a valid
posting address that can be recognized by our system and until you do so, I
believe that you're going to have this problem. Here are instructions for
registering:
1. Use the passport associated with your MSDN subscription to login at
http://msdn.microsoft.com/subscriptions/.
2. On the What's Hot tab, click the <here> link at the end of the first
paragraph of the "Unlimited Support" section, which takes you to the
registration page.
3. Pick a nickname and domain. For example, you could use johndoe for the
Nickname and @.nospam.nospam as the domain. Click the [Submit] button and it
should register johndoe@.nospam.nospam as your no-spam alias. That alias may
or may not be available. We are experiencing an intermittent problem with
this page where a link goes down and rejects all submissions. If the alias
you select gets rejected, please try waiting 10-15 minutes before trying
again.
When you successfully register we attempt to popup a page with more
information. It explains how to configure your profile, among other things.
If you have popups blocked, the link is:
http://msdn.microsoft.com/subscripti...gednewsgroups/
Once you've successfully registered your account, you'll need to start
posting using is, not the <NO_SPAM> address you used for this post.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"MSDN-Managed" <NO_SPAM> wrote in message
news:33FC6C3D-AF7D-4DAA-8E04-1C2E7944EC39@.microsoft.com...
> Is there a timeframe for this? It's been a few days (and 8 days since my
> initial post under this "Msdn-Managed" ID).
> Are you saying that this ID that I'm posting under right now ... doesn't
> show up as someone with a Universal account? Currently I'm at the web
> interface. I'm signed in. Next to the Sign In/Sign Out button is another
> smaller button that you can edit a profile. I looked there and didn't see
> anything that I could "check" or fill in that I didn't already.
> This is the ID that we have associated with our Univeral account. So, I'm
> confused. If it's not showing up as valid - can we take care of the issue
so
> that it does show up as valid? Because I'm not sure what else I can do
here[vbcol=seagreen]
> - and It's been weeks since my initial post.
> Thanks,
> William Campbell
> "Stephen Dybing [MSFT]" wrote:
internal[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
Universal[vbcol=seagreen]
gave[vbcol=seagreen]
sign-in[vbcol=seagreen]
link[vbcol=seagreen]
team[vbcol=seagreen]
post[vbcol=seagreen]
Information"[vbcol=seagreen]
ID[vbcol=seagreen]
this[vbcol=seagreen]
properties no[vbcol=seagreen]
a[vbcol=seagreen]
him[vbcol=seagreen]
work.[vbcol=seagreen]
But[vbcol=seagreen]
again.[vbcol=seagreen]
|||William, would you please send me your email address at
stephd@.microsoft.com? I've been chatting with one of the managers of the
Managed Newsgroup support team and he'd like to talk to you directly. If
you'll send me your email address, I'll have him contact you.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:uhBxoGT4EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Have you sent email to ngmsdnfb@.microsoft.com about this yet? The managers
> of that team are on that alias and will get back to you fairly quickly.
I'm
> going to forward this along to them, but you might as well send them mail
> yourself as well.
> It doesn't look to me like you've followed the directions to set up a
valid
> posting address that can be recognized by our system and until you do so,
I
> believe that you're going to have this problem. Here are instructions for
> registering:
> 1. Use the passport associated with your MSDN subscription to login at
> http://msdn.microsoft.com/subscriptions/.
> 2. On the What's Hot tab, click the <here> link at the end of the first
> paragraph of the "Unlimited Support" section, which takes you to the
> registration page.
> 3. Pick a nickname and domain. For example, you could use johndoe for the
> Nickname and @.nospam.nospam as the domain. Click the [Submit] button and
it
> should register johndoe@.nospam.nospam as your no-spam alias. That alias
may
> or may not be available. We are experiencing an intermittent problem with
> this page where a link goes down and rejects all submissions. If the alias
> you select gets rejected, please try waiting 10-15 minutes before trying
> again.
> When you successfully register we attempt to popup a page with more
> information. It explains how to configure your profile, among other
things.
> If you have popups blocked, the link is:
> http://msdn.microsoft.com/subscripti...gednewsgroups/
> Once you've successfully registered your account, you'll need to start
> posting using is, not the <NO_SPAM> address you used for this post.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Please reply to the newsgroups only, thanks.
> "MSDN-Managed" <NO_SPAM> wrote in message
> news:33FC6C3D-AF7D-4DAA-8E04-1C2E7944EC39@.microsoft.com...
my[vbcol=seagreen]
another[vbcol=seagreen]
see[vbcol=seagreen]
I'm[vbcol=seagreen]
issue[vbcol=seagreen]
> so
> here
> internal
post.[vbcol=seagreen]
> rights.
not[vbcol=seagreen]
> the
> Universal
> gave
post[vbcol=seagreen]
> sign-in
from[vbcol=seagreen]
id.[vbcol=seagreen]
message[vbcol=seagreen]
that[vbcol=seagreen]
> link
> team
that[vbcol=seagreen]
> post
Monitor ->[vbcol=seagreen]
the[vbcol=seagreen]
> Information"
and[vbcol=seagreen]
> ID
have[vbcol=seagreen]
> this
> properties no
uses[vbcol=seagreen]
> a
gave
> him
> work.
> But
> again.
>
|||Test - did this work? Hopefully it picks up my post now.
|||I still don't think you're following the directions. Your posting address
appears to be set to "NO_SPAM" and that is not one of the valid choices from
the registration page that I have pointed out a couple of times, there's no
domain listed. I'm including the directions again below. Please choose a
nickname and enter it in the nickname box (and it would be a very good idea
to make it more unique than "NO_SPAM" and then pick one of the domains from
the choose a domain drop-down list box. Then after as it's registered,
you'll need to use that full email address as your posting address. This
isn't a free service so we need to make it unique enough to ensure that
you're the only one using that alias.
1. Use the passport associated with your MSDN subscription to login at
http://msdn.microsoft.com/subscriptions/.
2. On the What's Hot tab, click the <here> link at the end of the first
paragraph of the "Unlimited Support" section, which takes you to the
registration page.
3. Pick a nickname and domain. For example, you could use johndoe for the
Nickname and @.nospam.nospam as the domain. Click the [Submit] button and it
should register johndoe@.nospam.nospam as your no-spam alias. That alias may
or may not be available. We are experiencing an intermittent problem with
this page where a link goes down and rejects all submissions. If the alias
you select gets rejected, please try waiting 10-15 minutes before trying
again.
When you successfully register we attempt to popup a page with more
information. It explains how to configure your profile, among other things.
If you have popups blocked, the link is:
http://msdn.microsoft.com/subscripti...gednewsgroups/
Once you've successfully registered your account, you'll need to start
posting using it, not the <NO_SPAM> address you used again for this post.
Mitch should be following up with you today via email.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"MSDN-Managed" <NO_SPAM> wrote in message
news:E42F00CD-59D8-493F-B94A-05737B1F8082@.microsoft.com...
> Test - did this work? Hopefully it picks up my post now.

Can't view Locks / Process ID

Hi,
We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.
Has anyone out there experienced this? Or does anyone know the
solution?
Thanks,
MarkHave you tried reinstalling the Client tools on Affected boxes. Also if you
could start the profiler trace in the background and check if its running
any queries.
HTH
Vishal|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if yo
u
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if yo
u
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark

Can't view Locks / Process ID

Hi,
We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.
Has anyone out there experienced this? Or does anyone know the
solution?
Thanks,
MarkHave you tried reinstalling the Client tools on Affected boxes. Also if you
could start the profiler trace in the background and check if its running
any queries.
HTH
Vishal|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if you
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if you
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark

Cant view Locks / Process ID

Hi,

We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.

Has anyone out there experienced this? Or does anyone know the
solution?

Thanks,
Mark(mfanny@.gmail.com) writes:
> We have SQL 7 running on Windows 2000 Server. For some reason we are
> unable to view Locks / Process ID from workstations running Windows XP
> SP2 with Enterprise Manager. Nothing shows up in the window on the
> right. All it's says at the top of the window is "There are no items to
> show in this view" If we use Enterprise Manager on the server, we can
> view the Locks / Process ID. We can view both, Process Info and Locks /
> Object from the workstations and server, just not the Locks / Process
> ID. We used to, but I don't know what changed. I'm thinking it might
> have to do with upgrading to XP SP2, or a Windows update, but I'm not
> sure.

I find it a bit difficult to believe that Windows XP SP2 would matter,
at least when it comes to the communication with SQL Server. But maybe
some interaction with the MMC has broken.

If you use any Profiler, can you detect wether Locks / ProcessID generate
any calls to SQL Server?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can't use VS 2003 for reports now that VS 2005 and SQL 2005 are installed?

I've been using Visual Studio 2003 successfully for several months to
develop a web-based reporting solution. I've also had SQL Server 2000
installed, including the evaluation version of Reporting Services. I
just recently installed Visual Studio 2005 (but left Visual Studio 2003
installed as well) and updated to SQL Server 2005, including the new
version of Reporting Services. I made a copy of one of the report
projects from my VS 2003 solution and I can successfully open it in VS
2005 and convert the reports to the new RS 2005 format. However, when
I try to open my original solution (not the converted copy) in VS 2003,
the non-report projects open fine, but I get the follow message as it
tries to open each of the report projects: "The application for
C:\MyReportProject.rptproj is not installed. Make sure the application
for the project type (.rptproj) is installed." After that, the word
"unavailable" appears next to each of the report projects in Solution
Explorer, but the non-report projects are fine.
Any ideas?I am having the same issue.
--
John
"Lee" wrote:
> I've been using Visual Studio 2003 successfully for several months to
> develop a web-based reporting solution. I've also had SQL Server 2000
> installed, including the evaluation version of Reporting Services. I
> just recently installed Visual Studio 2005 (but left Visual Studio 2003
> installed as well) and updated to SQL Server 2005, including the new
> version of Reporting Services. I made a copy of one of the report
> projects from my VS 2003 solution and I can successfully open it in VS
> 2005 and convert the reports to the new RS 2005 format. However, when
> I try to open my original solution (not the converted copy) in VS 2003,
> the non-report projects open fine, but I get the follow message as it
> tries to open each of the report projects: "The application for
> C:\MyReportProject.rptproj is not installed. Make sure the application
> for the project type (.rptproj) is installed." After that, the word
> "unavailable" appears next to each of the report projects in Solution
> Explorer, but the non-report projects are fine.
> Any ideas?
>|||Dear All,
solution :
--
install the Client Components from SQL SERVER 2005.
SQL SERVER 2005 -> Client Components -> Business Intelligence
with regards,
Murali
"John K" wrote:
> I am having the same issue.
> --
> John
>
> "Lee" wrote:
> > I've been using Visual Studio 2003 successfully for several months to
> > develop a web-based reporting solution. I've also had SQL Server 2000
> > installed, including the evaluation version of Reporting Services. I
> > just recently installed Visual Studio 2005 (but left Visual Studio 2003
> > installed as well) and updated to SQL Server 2005, including the new
> > version of Reporting Services. I made a copy of one of the report
> > projects from my VS 2003 solution and I can successfully open it in VS
> > 2005 and convert the reports to the new RS 2005 format. However, when
> > I try to open my original solution (not the converted copy) in VS 2003,
> > the non-report projects open fine, but I get the follow message as it
> > tries to open each of the report projects: "The application for
> > C:\MyReportProject.rptproj is not installed. Make sure the application
> > for the project type (.rptproj) is installed." After that, the word
> > "unavailable" appears next to each of the report projects in Solution
> > Explorer, but the non-report projects are fine.
> >
> > Any ideas?
> >
> >|||That will install 2005 components...
What I need back is the 2003 projects templates that work will SQL 2000 !
Has anybody managed to fix this issue ?
> install the Client Components from SQL SERVER 2005.
> SQL SERVER 2005 -> Client Components -> Business Intelligence
> with regards,
> Murali
>
> "John K" wrote:
> > I am having the same issue.
> >
> > --
> > John
> >
> >
> > "Lee" wrote:
> >
> > > I've been using Visual Studio 2003 successfully for several months to
> > > develop a web-based reporting solution. I've also had SQL Server 2000
> > > installed, including the evaluation version of Reporting Services. I
> > > just recently installed Visual Studio 2005 (but left Visual Studio 2003
> > > installed as well) and updated to SQL Server 2005, including the new
> > > version of Reporting Services. I made a copy of one of the report
> > > projects from my VS 2003 solution and I can successfully open it in VS
> > > 2005 and convert the reports to the new RS 2005 format. However, when
> > > I try to open my original solution (not the converted copy) in VS 2003,
> > > the non-report projects open fine, but I get the follow message as it
> > > tries to open each of the report projects: "The application for
> > > C:\MyReportProject.rptproj is not installed. Make sure the application
> > > for the project type (.rptproj) is installed." After that, the word
> > > "unavailable" appears next to each of the report projects in Solution
> > > Explorer, but the non-report projects are fine.
> > >
> > > Any ideas?
> > >
> > >|||Did you try re-installing the RS 2000 report designer?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mikey" <mikey@.discussions.microsoft.com> wrote in message
news:C4164015-D773-40D7-BABB-4E0AE343CC1E@.microsoft.com...
> That will install 2005 components...
> What I need back is the 2003 projects templates that work will SQL 2000 !
> Has anybody managed to fix this issue ?
>
>> install the Client Components from SQL SERVER 2005.
>> SQL SERVER 2005 -> Client Components -> Business Intelligence
>> with regards,
>> Murali
>>
>> "John K" wrote:
>> > I am having the same issue.
>> >
>> > --
>> > John
>> >
>> >
>> > "Lee" wrote:
>> >
>> > > I've been using Visual Studio 2003 successfully for several months to
>> > > develop a web-based reporting solution. I've also had SQL Server
>> > > 2000
>> > > installed, including the evaluation version of Reporting Services. I
>> > > just recently installed Visual Studio 2005 (but left Visual Studio
>> > > 2003
>> > > installed as well) and updated to SQL Server 2005, including the new
>> > > version of Reporting Services. I made a copy of one of the report
>> > > projects from my VS 2003 solution and I can successfully open it in
>> > > VS
>> > > 2005 and convert the reports to the new RS 2005 format. However,
>> > > when
>> > > I try to open my original solution (not the converted copy) in VS
>> > > 2003,
>> > > the non-report projects open fine, but I get the follow message as it
>> > > tries to open each of the report projects: "The application for
>> > > C:\MyReportProject.rptproj is not installed. Make sure the
>> > > application
>> > > for the project type (.rptproj) is installed." After that, the word
>> > > "unavailable" appears next to each of the report projects in Solution
>> > > Explorer, but the non-report projects are fine.
>> > >
>> > > Any ideas?
>> > >
>> > >|||Hi Bruce,
I did this morning.. and it has worked by installing the client designer of
SQL 2000 reporting back onto my machine.
Thanks for the reply. Just hadn't had chance to post my solution back.

Can't use Virtual PC for VS 2005 Pro with SQL Server Express

I am unable to successfully complete an install of Visual Studio 2005 with SQL Server 2005 Express on a Virtual PC 2004 image, which seems to be a pretty reasonable expectation. The installation completes successfully all of the way through in a Windows XP Pro SP2 image, but at the end the installation program informs you that SQL Server Express has failed to install, with no other explanation. No setup log files give any information as to why the installation failed. Then I see in the fine print for SQL Server Express downloads that "There is no support in this release for Virtual Server, Virtual PC". (http://download.microsoft.com/download/c/c/6/cc66b572-a402-4c6a-8233-b9c7d05840c7/RequirementsSQLEXP2005.htm)
Is this indeed the case that we essentially cannot run the full VS 2005 installation in Virtual PC? Is there no workaround for this problem?
Express is certainly supported on Virtual PC and Virtual Server. It's hard to say for sure at this late date but I'm going to blame a bug in VS that was fixed by RTM.

Can't use Virtual PC for VS 2005 Pro with SQL Server Express

I am unable to successfully complete an install of Visual Studio 2005 with SQL Server 2005 Express on a Virtual PC 2004 image, which seems to be a pretty reasonable expectation. The installation completes successfully all of the way through in a Windows XP Pro SP2 image, but at the end the installation program informs you that SQL Server Express has failed to install, with no other explanation. No setup log files give any information as to why the installation failed. Then I see in the fine print for SQL Server Express downloads that "There is no support in this release for Virtual Server, Virtual PC". (http://download.microsoft.com/download/c/c/6/cc66b572-a402-4c6a-8233-b9c7d05840c7/RequirementsSQLEXP2005.htm)
Is this indeed the case that we essentially cannot run the full VS 2005 installation in Virtual PC? Is there no workaround for this problem?
Express is certainly supported on Virtual PC and Virtual Server. It's hard to say for sure at this late date but I'm going to blame a bug in VS that was fixed by RTM.

Can't use variables in SQL-Task

Hi,

I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:

1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123

b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @.NewParameterName"

d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."

2. a) see 1.a)

b) see 1.b)

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"

d) Running this results in following error: "Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."

Any ideas?

Regards,

Jan

You cannot use a parameter to substitue in the name of a table. They are typically used as arguments in WHERE clause predicates.

If you want to dynamically set the table name then use an expression. This explains how: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Thanks a lot for that link, now my package is working fine :-).

Best Regards,

Jan Wagner

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Index
Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
| |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Filter(WHERESTARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IB
The first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB
|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB