SQL Server 2005 SP1 Webchat - March 22nd 9:00 am PST
We will be hosting a public web chat discussion on March 22nd 9:00 – 10:00 am PST about the March CTP of SP1. It is the customers' opportunity to engage with the dev team and provide feedback and ask questions. Strong participation from customers will act as a motivator for stronger participation by the dev team and will increase their interest in having these in the future, so we urge you to join and participate.
For more information and sign up for this web chat go to: http://www.microsoft.com/technet/community/chats/default.mspx#06_0322_TN_SQL.
Error in Report "Memory Consumption" after upgrading with SQL2005 CTP SP1
Opened MS SQL Server Management Studio. Connected to a SQL2005 64bit server with 6GB memory. Choose Summary, Report, Memory Consumption and in the graph for Buffer Pages Distribution (#Pages) there's no graph, but the following error "Unable to retrieve data for this section of the report. Following error occurred. Msg 8114, Level 16, State 1 Error converting data type bigint to int.". The SQL2005 32bit didn't show this error after upgrading with CTP SP1, but it has less memory (2 GB).
Regards
Peter
Reply:
Peter,
I'm moving this thread to the Tools General forum as they'll be best able to help you.
-Jeffrey
------------------------------------
Reply:
Peter
------------------------------------
Welcome and Tips for using this Forum
Welcome to the SQL Express Forum
Hello everyone and welcome to the SQL Express forum. One of the primary goals of the SQL Express MSDN Forum is to offer a gathering place for SQL Express users to share experiences, discuss issues related to SQL Express, ask questions and interact with the folks here at Microsoft. Hopefully everyone on the forum will contribute not only questions, but opinions and answers as well. I'm looking forward to seeing an active community of experts grow up around SQL Express.
This post has information to help you understand what questions to post here, and where to post questions about other technologies as well as some tips to help you find answers to your questions more quickly and how to ask a good question. See you in the group.
Mike Wachal
SQL Express team
Be a good citizen of the Forum
When an answer resolves your problem, mark the thread as Answered. This makes it easier for others to find the solution to this problem when they search for it later. If you find a post particularly helpful, click the link indicating that it was helpful
It seems obvious, but this forum is for discussion and question of the SQL Express family of products. When you want to discuss something that is specific to SQL Express, this is the place to be. An example of something that is specific to SQL Express is User Instances. Since SQL Express uses the same database components as other SQL Editions, there are several common pieces of functionality and there may be better forums elsewhere to ask about those. Don't worry, this is a great place to start if you're using SQL Express, if there is a better batch of experts to answer your question, we'll just move your post to that Forum so those experts can answer. Any alerts you set up will move with the post, so you'll still get notification. Here are a few of the other forums that you might find interesting:
SQL Server Setup & Upgrade – This is where to ask all your setup and upgrade related questions, even for SQL Express. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1)
SQL Server Tools General – This is the best place to ask Management Studio Express questions. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1)
SQL Server Reporting Services – If you're working with RS in Express Advanced, check out this forum. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1)
SQL Server Database Engine – Great forum for general information about engine issues such as performance, FTS, etc. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1)
SQL Server Data Access – If you're connecting to SQL through almost any protocol this is a SQL forum dedicated to you. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1) There is also a .NET Data Access forum that covers similar topics, but with a VS focus. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1)
There are also some general forum categories that will interested you:
Visual Studio Express Editions (http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=35&SiteID=1)
Visual Web Developer and ASP.net (http://forums.asp.net/)
.NET Development (http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=12&SiteID=1)
How to find your answer faster
There is a wealth of information already available to help you answer your questions. Finding an answer via a few quick searches is much quicker than posting a question and waiting for an answer. Here are some great places to start your research:
SQL Server 2005 Books Online:
Search it online at http://msdn2.microsoft.com
Download the full version of the BOL from here
Microsoft Support Knowledge Base:
Search it online at http://support.microsoft.com
Search the SQL Express Team Blog:
The blog is located at http://blogs.msdn.com/sqlexpress
Search the MSDN Forums:
Simple search: http://forums.microsoft.com/MSDN/Search/default.aspx?SiteID=1
Advanced search: http://forums.microsoft.com/MSDN/Search/AdvancedSearch.aspx?SiteID=1
Search other SQL Forums and Web Sites:
MSN Search: http://search.msn.com/
Or use your favorite search engine
Make sure to give all the pertinent information that people will need to answer your question. Questions like "I'm having problems using SQL Express with C#, any ideas?" will likely go unanswered, or at best just result in a request for more information. Here are some ideas of what to include:
· The exact error message if you're getting an error. (The SQL Error Logs can be a rich source of information. See the section on error logs below.)
· Any tools you're using when experiencing the problem. (Management Studio, VS Express product, etc.)
· Any troubleshooting you've already done. (eg. "I've tried connecting to SQL Express with SQLCmd and that fails with the same error.")
· If you're having a coding problem, include a sample of the code that cuases the problem. Make sure to identify the line where the problem occurs if you can.
· Connection strings you're using when you're having connection problems.
· Is everything installed on the same computer or are you working across a network?
Finding the Error Logs
You will often find more information about an error by looking in the Error logs. There are two sets of logs that are interesting:
Setup Error Log default location: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG
SQL Error Log default location: C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\LOG (Note: The # changes depending on the ID number for the installed Instance. This is 1 for the first installation of SQL Server, but if you have mulitple instances, you will need to determine the ID number your working with. See the BOL for more information about Instance ID numbers.)
Bug (?) in SQL2005 x64 SP1 CTP
Server32 (Win2003 Standard 32 bit, SQL2005 Developer 32 bit SP1 CTP, 1gb Memory) and with
Server64 (Win2003 x64 Standard 64 bit, SQL2005 Developer 64 bit SP1 CTP, 6gb Memory).
exec sp_MSforeachtable @command1="SET QUOTED_IDENTIFIER ON DBCC DBREINDEX ('?', '', 90)"
On Server32 the SQL above runs without errors. On Server64 there comes the following error message:
Msg 8621, Level 17, State 1, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.
A 64bit server with 6gb memory has less stack space than a 32bit server with 1gb memory??
Regards
Peter
PS: I know sp_MSforeachtable is not an official stored procedure, but I don't thinkt there's the problem. I got the same error on Server64, when I run the following cursor:
DECLARE tablename CURSOR
READ_ONLY
FOR select [name] from sys.tables where type = 'U'
DECLARE @name varchar(80)
OPEN tablename
FETCH NEXT FROM tablename INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @message varchar(800)
SELECT @message = 'SET QUOTED_IDENTIFIER ON DBCC DBREINDEX (''' + @name + ''', '''', 90)'
EXEC (@message)
END
FETCH NEXT FROM tablename INTO @name
END
CLOSE tablename
DEALLOCATE tablename
GO
Reply:
------------------------------------
Reply:
Thanks
Peter
------------------------------------
Stepping over the line
I'm running both SQL 2000 and SQL 2005 on the same server.
I got this informational message when running a replication job in SQL 2005. It references the SQLAgent of SQL 2000 instance and says it needs to be upgraded to SQL 2005.
Log Windows NT (Application)
Source SQLAgent$My2000Instance
Category Job Engine
Event 1073742027
Computer AMWA-011605
Message
SubSystem Message - Job 'MyJobRunninginSQL2005' (0xF6BA7BF3BB3EAD42BF281CA1CA39D2EE), step 2 - The SQL Server version of the Publisher server needs to be upgraded to 'Microsoft SQL Server 9.0'.
I say "hooey". It is none of its business if one of my instances is SQL 2000.
Reply:
Perhaps this is old news.
Perhaps I didn't read the "Can't do that" notices too closely.
I can't be more explicit, but I am getting solid evidence that one can't run SQL 2000 replication on the same XP server as SQL 2005 replication. I received an error message from a SQL 2005 job that references data that only exists on the SQL 2000 server.
------------------------------------
Reply:
------------------------------------
Reply:
You may have already solved your own problem, but I'm interested in what you're describing here. Could you describe what you're doing in a little more detail? Let me know what OS you're running on the publisher, distributor and subscriber(s). Also describe the type of replication, what version of SQL you're coming from/to, etc.
Buck
------------------------------------
Reply:
I got rid of any possible interference by removing SQL 2000 from the workstation, except for VirtualPC, leaving only SQL 2005 RTM.
However, fyi, I had both SQL 2000 and SQL 2005 replication up. I found that the SQL 2000 (svcpk 4) replication could see the SQL 2005 publications. The error message indicated that the SQL 2005 could see the SQL 2000 publication. All of this is on a single XP server. I'm trying to get a simple merge and tran replication republishing scenario up on 2005. To eliminate any more complexities, I had to simplify my environment. I can't get the tran part working so far. The tran AtoB works, but the tran BtoC fails. I'm commenting on that process in another recent post and not maintaining this post.
------------------------------------
Error connecting Excel (PivotTable) to OLAP services for cube browsing
I have created an excel template by using the pivot table to link to a cube. When i open the template (at user pc) to browse the cube in pivot table, i getting an error message as below:
Connection Failed
SQL State: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server][SQL Server] Login failed for user 'domain\useridA'
I am using olap services 8.0 & sqlserver 2000.
User pc are with the same domain with server. I checked also this useridA is properly created in the cube role. This role contain of abt 6 user id. Others user id is work fine (by using the same excel template) except for this useridA.
Any ideas to solve this issue?
Thanks in advance.
- Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:46 AM
CTP June Dev.Edition:TCP/IP protocol is disabled at default
I find out TCP/IP protocol is disabled at default at CTP June. When you meet with same situation, try enable TCP/IP protocol being enabled on "SQL Server Configration Manager"
It's bug or by design?
Regards
Hajime Gondo
Reply:
-Euan
------------------------------------
Reply:
But I can't accept this design. Bacause developer edition "must" (I think) functionaly works same as enterprise edition. If not, developer must search what is deffernt between Dev. and Ent.(such as me) This causes needless confusion.
------------------------------------
Reply:
-Euan
------------------------------------
Reply:
------------------------------------
Reply:
------------------------------------
Reply:
To install via the command line and turn TCP/IP on, specify the DISABLENETWORKPROTOCOLS command line parameter:
;--------------------------------------------------------------------
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On
; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2
DISABLENETWORKPROTOCOLS=
To configure the TCP/IP after installation, you need to use the Surface Area Configuration tool that Euan mentioned. This blog entry describes it pretty well for SQLExpress - it applies equally well to other skus. ttp://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Hope this helps.
-Jeffrey
------------------------------------
Nsort for SSIS, a highly parallel sort component
Based on the same Nsort technology that has set multiple world records (1 Terabyte in 33 minutes, 40GB in 60 seconds), this SSIS component overcomes the limitations of the built-in sort transformation. Not only does Nsort for SSIS use multiple processors and disks effectively to support for arbitrarily large dataflows, it also allows multiple inputs and outputs to improve parallelism further, removing bottlenecks from the entire dataflow.
Nsort for SSIS is available for X86 and X64.
For better performance than standard SSIS sort transformation, get a free trial now: http://www.ordinal.com/ssis.html
SqlExpress Setup fails if not installed From C:
I have an win2000 Svr SP4 and was getting "sqlncli.msi" not found errors, also the database services module was failing to install.
It turned out the I was attempting to install from my D: drive ( oh the nerve of not using the C drive!) . Steps to fix was
Uninstall all SQL modules from add /remove programs ( No reboot required)
Manually extract as auto extract keeps going to d drive:do a "SQLEXPR.EXE /x:c:\sqlexprtmp" ( notice Use C drive)
Hit c:\sql\exprtmp\setup.exe and sqlexpress all installed no Problem.
Maybe the install documentation http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm could be ammended to show this requirement!
Reply:
Hrm. That's no good. Phil, can you go ahead and log a bug using: http://lab.msdn.microsoft.com/ProductFeedback/
We'll need the log files from %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\Log\Files for the failed installations (or you can just zip them all up). When you log the bug, can you also describe how your system is set up? We'll need that information to help reproduce your issue in-house.
Thanks,
-Jeffrey
------------------------------------
Slow pivoting operations using Excel client
I recently implemented a BI solution for a large organization. Before this, they had an Excel pivot table based on a big query that fed Excel with flat data worth of 70Mbytes. The new solution is based on SSAS and gives an Excel XP pivot client of about two hundred Kbytes.
Some technical details of the cube are: There are 3 dimensions, one of which is time. The other two dims have 2 hierarchies and about 1.5 thousand members at the leaf level. The fact table consists of 6 facts. Each of them splits to two calculated the negative and the positive values based on a CASE statement. The fact table has about 500 thousand rows (for the time being).
The problem is that the users are experiencing a delay of 5 to 8 seconds for each pivoting operation they do, while the old solution gives almost instant pivoting operations. I know that there are a lot of issues to investigate. I also understand that a slower pivoting operation is expected as Excel requests the data from the SSAS server and this is the trade-off you get for the really small Excel file but I was just wondering if I have overlooked something...
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:59 PM
New contest - $10,000 and all it takes to start is an idea...
Just announced, a new contest for SQL Server Express.
Do you like to use technology to build cool and useful stuff? Do you think you could do it with Visual Studio Express and/or SQL Server Express? Would you like $10,000 cash? Well then we have a contest for you! Learn more at www.MadeInExpressContest.com.
Cool stuff.
MJ
Cube Design Question
I need to compare sales quotas versus actual sales performance.
Target quotas exist for dealers, regions and marketing channels. Region and marketing channels are attributes of dealers. Only the top dealers have quotas but all dealers have a region and marketing channel attribute.
Do I create 3 separate small fact tables for each or is there a more efficient solution using the UDM?
TIA
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:58 PM
Intersecting Drillthrough results - an MDX challenge
Hi,
Anyone have any guidelines on how to intersect the results of 2 or more DrillThrough operations?
In other words, say, if I drillthrough on Cell1 and get Factkeys: 1 to 20.
I drillthrough on Cell2, and I get FactKeys: 15 to 30.
Now what I want is to run my MDX Select query, which returns only Cell1 and Cell2. Then I want to do an 'Intersected Drillthrough' operation, which should give me the intersected Factkeys: 15 - 20.
This is the common set of keys from the two drillthrough resultsets (1 - 20) and (15 - 30).
Thanks,
JGP
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:58 PM
Best Practice-working with SQL Express Database
Hi,
I am new to working with Database using .net C#. i will really appreciate if someone can point me to a link where i can find best practices of working with sql express database with my c# web service applications.
preferably i would like to have answers for:
- to have one db or to have one for testing and one real db?
- db security
- use windows authentication or db user authentication
etc.
Reply:
SqlExpress has it's own forum here on the MSDN forums. I'll move the thread over there for more general info on Express best practices.
------------------------------------
Cubesize dependent on number of records?
Experimenting a bit I've constructed two cubes with identical measures, dimensions and aggregation design. The difference is one processes 1 million records and the other one 100 million. The first cube ends up 48,28 MB in size while the other is 4210,30 MB. From where is this difference coming? Considering the number of cells should be equal as they both have same dimensions with same members and each cell have the same measures stored I would've imagined they'd be roughly the same physical size?
Reading the whitepaper about Drillthrough at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp
I found that drillthrough detail information is now stored in the cube. And since the orders of magnitude difference between cubesize and nbr of records are roughly equal I was wondering if this is what's showing up? In that case - is there any way to disable this? Or am I barking up the completely wrong tree?
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:58 PM
Embed a Data Mining model in ASP.NET
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:57 PM
Reporting against MDX Queries
I have a report where each line, and sometimes each cell, contains the results of a different MDX query. These are currently summarized and additional formulas applied using Excel macros and raw text files.
In SQL 2005, using Reporting Services and SSIS, are there any suggestions for improvements to this architecture? Excel formatting is important, as is the ability to expand and collapse nodes from a web page.
I would like some tips or feedback on your experiences with MDX and Reporting Services, to assist me with further ideas on the new architecture.
thanks,
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:57 PM
Dimensions with Startdate / Enddate fields
Hi,
I would like to know what is the best practice to manage Dimensions with StartDate / EndDate fields, in SSAS.
Regards
Ayzan
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:56 PM
Reply:
Well first, is it possible to build dimensions with time dependance ?
Regards
Ayzan
------------------------------------
TechNet Webcast available: Monitoring Replication in SQL Server 2005
On March 3, I gave a webcast about monitoring replication in SQL Server 2005 that includes many demonstrations of Replication Monitor.
Event Description
Products: SQL Server.
Recommended Audience: IT Professional.
Language: English-American
Description:
This webcast provides an overview of the new features of Replication Monitor in Microsoft SQL Server 2005. Along with many of the SQL Server tools, Replication Monitor was redesigned and rewritten for SQL Server 2005. Replication Monitor lets you track end-to-end latency for transactional publications and see a detailed analysis of merge performance. You can also define warning thresholds for replication performance that will generate events and alerts. And, for the first time, Replication Management Objects (RMO) provides a programming interface if you want to write your own monitoring tools.
You can watch a recording of the webcast by clicking:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032290639&Culture=en-US
To view the recording, click the "Register for event" link on the left. (Granted, it doesn't make much sense to "register" for an event in the past, but that's how the site is set up.)
Phillip Garding
Distinct count MDX question
I am trying to get the distinct number of accounts that have subscribed to recieve emails about cruises, have had a past trip between 2004 and 2006, and have flown out of either the Seattle or Houston airports.
My MDX is given below. It is returning the number of accounts but not the number of distinct accounts. Could anyone give me some insite on how to change this query to return distinct accounts?
Thanks. -- Nedra
SELECT
NON EMPTY
CROSSJOIN (
CROSSJOIN (
{[Sub_CruisesDim].[All Sub_CruisesDim]}
, {filter( {[Departure Date].[All Departure Date]}, [Departure Date].[All Departure Date] >= 2004 or [Departure Date].[All Departure Date] <= 2006) }
)
, {
filter ( {[DepartureAirport_Dim].[All DepartureAirport_Dim]}, Not IsEmpty([DepartureAirport_Dim].[All DepartureAirport_Dim].[IAH]) or Not IsEmpty([DepartureAirport_Dim].[All DepartureAirport_Dim].[SEA]))
}
)
ON COLUMNS
FROM
[Account_Fact]
WHERE
([Measures].[Account Id])
- Changed type Ed Price - MSFTMicrosoft employee Sunday, March 15, 2015 10:56 PM
No comments:
Post a Comment