SQL,tables in a DB: system

Showing posts with label system. Show all posts

Tuesday, March 20, 2012

"The system cannot find the file specified" upon login

I recently upgraded my WinXP Pro SP2 PC from SQL Server 2000 to SQL Server 2005. After a subsequent reboot and login, I saw a generic error message:

Error
The system cannot find the file specified.
OK

I didn't think much of it the first time, but it appears each time I reboot.

Sysinternals' Process Explorer identified the error dialog with C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmangr.exe. I just realized today that upon pressing the OK button, SQL Server Service Manager appears in my system tray, with a solid white circle. Upon opening Service Manager, the Services list is empty. The status bar says it is "Not Connected". I figured the entire ..\80 directory had been removed as part of the 2000-to-2005 upgrade; however, the directory and .exe do exist. Is this normal?

Any suggestions on how to proceed?

I think your problem was caused by the SQL 2000 install. I had an old Beta 2 copy of SQL 2000 on my development system, which had long since expired, and I ran into the same problem trying to install SQL 2005 Express. I suspect you have found a solution by now, but in case someone else runs into the same problem just uninstall SQL 2005, uninstall SQL 2000 and then install SQL 2005.

I had to uninstall SQL 2000 manually by following the instructions at the following link.

http://support.microsoft.com/kb/290991/en-us

sql

"The system cannot find the file specified" upon login

I recently upgraded my WinXP Pro SP2 PC from SQL Server 2000 to SQL Server 2005. After a subsequent reboot and login, I saw a generic error message:

Error
The system cannot find the file specified.
OK

I didn't think much of it the first time, but it appears each time I reboot.

Any suggestions on how to proceed?

I had to uninstall SQL 2000 manually by following the instructions at the following link.

http://support.microsoft.com/kb/290991/en-us

Tuesday, March 6, 2012

"File System" vs MSDB packages?

Hello.
I was hoping to learn more about the differences between MSDB and "File System" Integration Services (IS) packages.

First, why do I have the option to choose?

Second, is there a way in T-SQL to query any information on File System packages? With the MSDB packages, I can query msdb.dbo.sysdtspackages90 .

Thanks for any info.

Kirk has a good post on the pros and cons of saving to file or SQL Server here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

There is no way, using T-SQL, to get information about file system packages. SQL Server does not know about such packages. The only slight caveat to this is that the file can be set to log to SQL Server - in which case there will be some info from the package in SQL Server - but SQL Server doesn't "know" about the package as such.

-Jamie|||Kirk has a good discussion

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

What type of info would you like from the package on disk?

Allan

"JFoushee@.discussions.microsoft.com"

wrote in message

news:c8e13f2e-2b59-4cb1-8b6a-5bb9e65b18b4@.discussions.microsoft.com:

> Hello.

> I was hoping to learn more about the differences between MSDB and "File

> System" Integration Services (IS) packages.

> First, why do I have the option to choose?

> Second, is there a way in T-SQL to query any information on File System

> packages? With the MSDB packages, I can query msdb.dbo.sysdtspackages90

> .

> Thanks for any info.|||What type of info would you like from the package on disk?

I was hoping to get directories and/or names of IS packages in the File System.

I think the "root" File System folder is stored as a Registry key.
However, the only place I found it was in HKLM\Software\Microsoft\Windows\CurrentVersion\Installer\UserData\... , which suggests a per-user strategy. This doesn't make sense.|||There is no root package. The packages exist wherever you put them.

-Jamie|||I thoroughly agree there is no "root" package.
However, there is a root folder in Windows Explorer that corresponds to the File System folder in SSMS.
(In my case, C:\Program Files\Microsoft SQL Server\90\DTS\Packages)
This folder name is stored as a value in a Registry key.

When I add a folder or package to either
- C:\Program Files\Microsoft SQL Server\90\DTS\Packages in Windows Explorer
- the "Stored Packages"\"File System" folder in SSMS,
it will appear in the other.

I accept Jamie's answer that I cannot use TSQL to view the "File System" folder contents.

Wasn't sure if there some xp_cmdshell trickery that involves this Registry key, so that it would work for every SQL 2005 configuration. Probably not.

"Current day" MDX

Hi,

is there any "update" on how to do the "current day" as a set in MDX with SQL 2005 based on the actual system date?

If you don't want to base that on the system date is this still the "best practice" to introduce some "flags" in the time dimension to identifiy the actual day, last week, ... Just the way it was introduced with the BI Accelerator tool...

Thanks,

It's an interesting question...

I can't actually remember how SSABI did its current time periods, but from what you say I guess it created sets which filtered on a member property value such as 'Is Current Day'. That should still work, but my feeling is that in AS2005 it might be better to create relative time attributes. So, for example, if you had Year, Quarter and Month attributes you would add Relative Year, Relative Quarter and Relative Month attributes too. These would have members on them such as 'Current Month', 'Current Month - 1', 'Current Month - 2' etc. This would allow users to create much more sophisticated relative time period reports, and at the same time still see the actual dates involved if they crossjoined the actual time period attributes with the relative time period attributes because autoexists would automatically do filter out everything but the correct combinations.

Incidentally, using the NOW() function to find the current date is a bad idea in AS2005 because as far as I can see, server-defined sets are now evaluated at processing time. So for example, add the following set to Adventure Works:

create set currentcube.test as strtoset("{[Date].[Calendar Year].&[" + cstr(cint(mid(cstr(now()),7,4))-2) + "]}");

If you run a query which shows the contents of this set, you should see the CY2004 member returned. However if you change the system date of your machine to be 2005 instead of 2006, then rerun the same query, you'll see that CY2004 is still returned. You need to reprocess the cube ('Process Script Cache' seems to be sufficient) to get CY2003 to appear in your query.

Chris

|||

Chris, I think your suggestion is good. However, it does have some rather nasty processing implications, since you have to reprocess your time dimension each day (if the granularity of your time dimension is date). A "Process Update" is sufficient of course, but this method invalidates (and therefore removes) all aggregations that include attributes from the time dimension. You are therefore forced into doing a "Process Index" on all your measure groups (except those that might not contain the time dimension) to get the aggregations back online.

It puzzles me that named sets should be evaluated at processing time?! As far as I know they are evaluated the first time they are requested by a query in a given session. The set is then cached until the session expires or the set is removed by the DROP SET statement. I have not verified that this is true for server-defined sets, though...

Anyway, using named sets for dynamic time, should in my opinion be one of the best approaches. Using Chris' suggestion allows you to define a single named set "Current Day", which you can base a number of other sets on. For instance, having created the set "Current Day", you can easily create the set "Current Month" by using the Exists function:

CREATE SET CURRENTCUBE.[Current Month] AS Exists([Date].[Calendar].[Month],[Current Day])

... and so on...

If you are using a front-end tool, make sure that it supports the use of named sets. If not, you have to create a calculated member that aggregates across the named set, but this approach has quite a few disadvantages (one of which is that a calculated member does not establish current cube context).

|||

True, the processing overhead could be pretty nasty depending on your cube.

Re sets, you can test out the new behaviour on Adventure Works as follows:

Create the following server-side set

create set currentcube.test as topcount([Date].[Date].[Date].members,10, measures.[internet sales amount]);

|||

Yes, I can see that the two queries return the same set. I don't think the example proves your point, though, since the named set under no circumstances is evaluated in the context of the query, which is why the WHERE clause has no effect on the set of dates returned.

I have also tested the fact that the named set is evaluated at processing time. This is actually not the case. It is evaluated the first time it is requested in a query. This can be verified (following the example in a previous post) by doing a process, changing the system date and then running the query, in which case the returned set will accurately reflect the new system date. Anyway, you are absolutely right that the content of the named set does not change until the cube is reprocessed. I wonder if this behavior can be changed? For the sake of dynamic time, however, it doesn't really matter too much. If only you process a part of your cube database once a day (after midnight), you should be good to go.

|||

You're right about the sets - sorry, my mistake. I think I was getting confused with a slightly different issue which is that the same set can return different results depending on where you put it in the MDX Script. Here's an example in Adventure Works: if you add the following onto the end of your MDX Script -

create set currentcube.test1 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

(measures.[internet sales amount], [Customer].[Education].&[Bachelors])=0;

create set currentcube.test2 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

Then run queries showing the contents of test1 and test2, you can see that they return different results - test2 accurately reflects the change made by the assignment. This of course isn't inconsistent with the set being evaluated the first time it's queried (presumably the results are then stored in the 'script cache' I was processing), just that it's correctly evaluated in the context of the script.

Anyway, I've had an idea on how to have a relative time dimension of the type I've described without incurring any of the processing penalties. What might work (and I need to test this) would be to create a separate Relative Time dimension then add it to your cube with *no* relationship to any measure group; you could then use MDX Script assignments to map the members on it to the equivalent members on the real time dimension. Definitely worth investigating...

Chris

|||Good thinking! I would be very interested in knowing how you accomplish this, as I have already tried to implement your suggestion (without success).

|||

Here's a 'proof of concept' version:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348468&SiteID=1

I'd be interested to hear if anyone actually tries this, and if they have any suggestions for improvements.

Chris

|||That link appears to point back to this thread, but I would be extremely interested in seeing your proof of concept code. I didn't know it was possible to dynamically link dimensions at run time, but that would solve for me a number of very difficult issues I am struggling with.|||

I have seen two other ways of implementing the current day.

Never have members in the time dimension after the current date. Today it is Aug 22 so never let at time member enter the dimension after this date. With this solution you can look for the last time member in MDX by lastchild.lastchild and so on. In this case it is the ETL process and SSIS that manage the time dimension.

Another solution is to look for a measure in the cube that you know will reflect the current day or the day before the current day. Actual Sales is a good candidate if budget sales is entered for the full year in advance. In this case you can use Tail with Filter, in MDX, to look for the last non empty member. If this set works you can add Lag(MDX) och Lead(MDX) to your first named set, together with Lead(MDX).

If you buy "Fast track to MDX", Second edition, you can read a discussion about these methods and a third one, recursion.

Regards

Thomas Ivarsson

|||

I use this technique as it supports dates beyond today.

In ETL, I select the min and max trx date keys (a bit more involved if multiple fact tables which I have) from the fact table and store them in a one row table called DIM_DAY_RANGE. Then use the following where clause for your DAY_DIM view

create view v_active_day_dim as
select * from
FROM dbo.DAY_DIM
WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

The same technique applies to active products and customers and other dimensions. Makes the cube smaller and only used dimension keys are diplayed.

|||

Perhaps it's good to know some background...

I need this information ("today") because I need to build up reports which show "todays" Orders, Revenue or whatever. The users should do nothing but open the report, so I need something like a set to use instead of a fixed day or something what a user has to select.

Using the approach to "just offer what you have" is OK in many cases, however what do you do if you not only have actual sales but also your planned sales? Then you have to be able to show all days (or months) of the current year or also the next year... So this will not help you much...

|||

Quote:"what do you do if you not only have actual sales but also your planned sales". Do you have a version dimension like Actual, Budget, Planned, Forecast ? Or is this in the measures like, ActualSales, BudgetSales, Planned Sales, ForecastSales?

/Thomas

|||

Have a look here: http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

Regards

Thomas Ivarsson

|||

Thomas,

that's basically what Chris posted before... I used that for my solution as well because I'll have daily reprocesses of the cube... But it's a nice page with quite some useful stuff...

"Current day" MDX

Hi,

is there any "update" on how to do the "current day" as a set in MDX with SQL 2005 based on the actual system date?

Thanks,

It's an interesting question...

create set currentcube.test as strtoset("{[Date].[Calendar Year].&[" + cstr(cint(mid(cstr(now()),7,4))-2) + "]}");

Chris

|||

CREATE SET CURRENTCUBE.[Current Month] AS Exists([Date].[Calendar].[Month],[Current Day])

... and so on...

|||

True, the processing overhead could be pretty nasty depending on your cube.

Re sets, you can test out the new behaviour on Adventure Works as follows:

Create the following server-side set

create set currentcube.test as topcount([Date].[Date].[Date].members,10, measures.[internet sales amount]);

|||

create set currentcube.test1 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

(measures.[internet sales amount], [Customer].[Education].&[Bachelors])=0;

create set currentcube.test2 as order([Customer].[Education].[Education].members, measures.[internet sales amount], bdesc);

Chris

|||Good thinking! I would be very interested in knowing how you accomplish this, as I have already tried to implement your suggestion (without success).

|||

Here's a 'proof of concept' version:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348468&SiteID=1

I'd be interested to hear if anyone actually tries this, and if they have any suggestions for improvements.

Chris

I have seen two other ways of implementing the current day.

If you buy "Fast track to MDX", Second edition, you can read a discussion about these methods and a third one, recursion.

Regards

Thomas Ivarsson

|||

I use this technique as it supports dates beyond today.

create view v_active_day_dim as
select * from
FROM dbo.DAY_DIM
WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

The same technique applies to active products and customers and other dimensions. Makes the cube smaller and only used dimension keys are diplayed.

|||

Perhaps it's good to know some background...

|||

/Thomas

|||

Have a look here: http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

Regards

Thomas Ivarsson

|||

Thomas,

that's basically what Chris posted before... I used that for my solution as well because I'll have daily reprocesses of the cube... But it's a nice page with quite some useful stuff...

Sunday, February 19, 2012

"Additional information: System error" message

Hello,
I needed to look at some .BCP files, so I thought I'd create a small tool for that.
I decided to create a temporary DB, attach empty mdf, ldf files with predefined structure and then insert the BCP files, so I can view the data as DB tables .

Here is the procedure I follow:
1) I drop the existing (if exists) DB ("Drop DBName") - to be prepared for the next set of .BCP files.
2) Then I create a new DB using sp_attach_db,
3) Then I populate the db via "Bulk Insert".

1st time this works just fine, but when I try to execute step 1 (drop DB) after step 3 I get a message that there is still open connection and the DB can't be dropped.

Here I can do two different things:
1) Find the SPID of the open connection to my DB and KILL the process or
2) Instead of killing the process, set the DB to "SINGLE_USER WITH ROLLBACK IMMEDIATE" and then drop the DB.

In both cases I was able to Drop the DB and attach the new one, however the Bulk Insert (via ExecuteNonQuery() ) for some reason closes the current connection and brakes with the "System Error" message.

I simulated this procedure on Query Analyser and on SQL Server Studio Express, but everything worked just fine there.

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

To make it short, here is the SQL Query procedure (which worked, though):

USE Master
if exists
(select name
from master.dbo.sysdatabases
where has_dbaccess(name)=1 and name='MyDB')
BEGIN
Drop database MyDB
END
exec sp_who2

-- Before the next command I have to manually restore the .ndf, .ldf files, deleted by the "Drop DB" command

sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'D:\CurrSchema\DB_Patient.ndf',
@.filename2 = N'D:\CurrSchema\DB_Definitions.mdf',
@.filename3 = N'D:\CurrSchema\DB_Log.ldf',
@.filename4 = N'D:\CurrSchema\DB_Data.ndf'
exec sp_who2

-- WAITFOR DELAY '00:00:04'
-- need to wait manually here. Waitfor won't work with "USE DB"

USE MyDB
BULK INSERT CAL
FROM '\\srv\D$\CurrFiles\CAL.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)

BULK INSERT CAP
FROM '\\srv\CurrFiles\CAP.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)
exec sp_who2

Has anybody seen something like this?

Thank you.

Haven't seen it personally. However, I suggest you insert a 'go' between each command to seperate them into batches. Also, be sure to change the database to 'master' for your connection before sending/executing 'drop db'.|||

Yes, Thank you - you were right. I did not need the 'GO', but the change to 'master' was the key. I should have been more careful when using somebody else's code - no matter how encapsulated, it's never context-free - in my case I had to add context switching with 'USE master'.

George.

|||

Hi... you mentioned you had the below source sample...

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

Can you please post or sent to billbbellevue@.hotmail.com

Kind regards,

Bill Bezick