SQL Express compatibility mode

SQL Server Express

Index ‹ SQL Server

- Previous
  - 1
    - Report Builder Is Report Builder capable of building any complex reports that Visual Studio Report Designer can build What are the limitations of using Report Builder with SQL Server Databases Tag: SQL Express compatibility mode
  - 2
    - 64-Bit SQL Server 2005 (Enterprise), SQL Agent Notifications and Database Mail We have database mail configured on our 64-Bit SQL Server Enterprise 2005 (SP1) and are able to send test messages from the context menu that appears when selecting database mail component. However for some reason when a SQL Agent Job fails, no emails are sent to the 'operator' that has been defined for the job. I have confirmed that we have the operators correctly configured. We also have a small SQL Agent Job that just sends a test email via the sp_send_dbmail command and that works properly. However if I deliberately create a malformed parameter in the sp_send_dbmail command to 'break' it...no notification of a job failure message is sent. I have verified that the service_broker component is enabled...and the status of database mail in the MSDB database is 'started.' We have setup several other 32-bit SQL Server 2005 systems using database mail...and they all work flawlessly. After reviewing existing message posts...I am unsure if there is a problem with database mail on a 64-bit SQL Server box. Some say 'yes' other say 'no'. There are several MSDN notes about the problem...however these appeared prior to the release of SP1 of SQL Server 2005. Thank you for your assistance. ...cordell... Tag: SQL Express compatibility mode
  - 3
    - importing from excel into SQL Server 2005 changing values to NULL I can't figure this one out... I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric. Example: _ITEMNUMBER_ 1-528 K214-5 184PR 45678 As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50). I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there. I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL. Any ideas Tag: SQL Express compatibility mode
  - 4
    - Could not load package because of error 0xC001000A I am trying to run an SSIS Package by using DTRun.exe this way C:\Documents and Settings\Fahad>dtexec /SQL DataDownload /Conn "Data Source =TriMurti;User ID=sa;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate =False;Password=gamba" /U sa /P gamba And the outcome of the command was Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:03:52 AM Could not load package "DataDownload" because of error 0xC001000A. Description: The specified package could not be loaded from the SQL Server database. Source: Started: 9:03:52 AM Finished: 9:03:52 AM Elapsed: 0.25 seconds ----------------------------- Any help in this will sincerely be appreciated. Fahad Tag: SQL Express compatibility mode
  - 5
    - Disabling extended stored procedures for security Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time Or, is the security team being too cautious and we should just tell them to leave these intact Tag: SQL Express compatibility mode
  - 6
    - The SSIS package does not run using SQL Server Agent job Dear All, My SSIS package is "Extract" data from Oracle and Load into SQL Server. The SSIS package does not run when I call the SSIS package from a SQL Server Agent job step I have gone through all the steps in the doc http://support.microsoft.com/kb/918760 , but it still doesn't work. Is it because of connection to "Oracle" database and SQL Server Agent service could not recognise the Authentication information to Oracle Why I am saying this is becuase it works if I change the package to "Extract" data only from SQL Server. Anyone has experience on this problem Many Thanks in Advance John Tag: SQL Express compatibility mode
  - 7
    - smalldatettime declare @ValueDate smalldatetime set @ValueDate = '21/05/2007' select @ValueDate error: The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. Please note, if '05/21/2007' is used then there is no error. How is it possible to get 'dd/mm/yyyy' as above to work please Thanks Tag: SQL Express compatibility mode
  - 8
    - missing borders when SSRS report is rendered in Sharepoint Hi , I am trying to host an SSRS report as a web part in Microsoft Sharepoint 2007. All the elements and data come out well except that a few borders are missing in the rendered report. What could be the possible cause and solution for this. Please suggest. Tag: SQL Express compatibility mode
  - 9
    - Reporting services permission denied on microsoft access linked I have a Microsoft access 2003 database with tables linked to a number of "SharePoint lists" I do this by going to File -> Get External Data -> Link Tables. A new window pop-ups in which I select Files of Type to be Windows SharePoint Services.. Then I select the correct site.. Then select the correct Lists.. Then access correctly links the tables to the SharePoint lists. When I Open reporting services and connect to this access database. "Using the Test Connection" (Jet 4.0) its successful" When i when create a new report and select the new datasource i have just created and in reporting services I write a select all query on one of the tables in access I get this error: An Error occurred while executing query. You do not have the necessary permissions to use the “object. Have your system administrator or the person who created this object establish the appropriate permissions for you. Catastrophic Failure. I have tried this on the super admin machine and still get the same error Any suggestions or help Tag: SQL Express compatibility mode
  - 10
    - IDENTITY_INSERT Issue INSERT INTO dbo.my_table (table_key) VALUES (007) -- Msg 544, Level 16, State 1, Line 1 -- Cannot insert explicit value for identity column in table 'my_table' when IDENTITY_INSERT is set to OFF. SET IDENTITY_INSERT dbo.my_table ON INSERT INTO dbo.my_table(table_key) VALUES (007) SET IDENTITY_INSERT dbo.my_table OFF -- Msg 1088, Level 16, State 11, Line 2 -- Cannot find the object "dbo.my_table" because it does not exist or you do not have permissions. I know for certain that I have read/write permissions on this server. Is this somehow restricted to only the dba or something I appreciate all of this community's help with my basically non-stop flow of questions. You're a huge chunk of help, and it's deeply appreciated!! Jim Work Tag: SQL Express compatibility mode
  - 11
    - SQLIS Checksum Install Problems For detecting delta records, I'm a big fan of SQLIS' checksum transform. I'm having difficulty in it's install on my current machine, however. After the installation and the new transform is added to my DataFlow toolbox... I can't open the UI for the transform to define the checksum. Instead, I get the following error: =================================== Could not load file or assembly 'Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft Visual Studio) ------------------------------ Program Location: at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransformUI.Edit(IWin32Window parentWindow, Variables variables, Connections connections) at Microsoft.DataTransformationServices.Design.DtsComponentDesigner.StartComponentUI(Boolean startGenericUI) Anyone have any suggestions Thanks in advance. Tag: SQL Express compatibility mode
  - 12
    - Count 4 tables I am trying to get a count of rows in 4 tables at once. Output: Table1 Table2 Table3 Table4 12345 143 892626 28428 Or maybe even Table Count Table1 12345 Table2 143 Table3 892626 Table4 28428 Thanks, Cory Tag: SQL Express compatibility mode
  - 13
    - Duplicates Hi! Grateful for some help as a newbie... I have a OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date "... so Column Day in Dim_date contains duplicates. I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim DELETE day from dim_date where day in(Select day from date ... Tag: SQL Express compatibility mode
  - 14
    - Adding Columns and rows in pivot table to Excel 2007 Hi, I am developing Excel pivot table reports pointing to Analysis services Cube (SSAS 2005). I tried to add Columns and rows inside the pivot table but it doesnt allow me to do it. Is there a way to achieve this kind of scenario in Excel 2007 apart from converting pivot table into a normal table and manipulating it. I once read that pivot table is a single object and we cannot manipulate(i.e. add new rows and columns) inside the pivot table. Thanks Kiran Tag: SQL Express compatibility mode
  - 15
    - Error: Incorrect syntax near the keyword 'DECLARE' Can someone tell me why I am getting this error: Msg 156, Level 15, State 1, Procedure fn_FunctionName, Line 11 Incorrect syntax near the keyword 'DECLARE' CREATE FUNCTION fn_FunctionName(@ElementID int) RETURNS TABLE AS RETURN --create a table (o) to hold the outcomes returned by fn_GetDescendantsByClass DECLARE @OUTCOMES TABLE (ID int, PARENT_ID int, CLASS_ID int, TITLE nvarchar(255)) INSERT @OUTCOMES SELECT * FROM fn_GetDescendantsByClass(@ElementID, 302) SELECT DISTINCT sl.JOB_ID, o.ID FROM @OUTCOMES o INNER JOIN (SELECT START_SCENE_ID, DEST_SCENE_ID AS JOB_ID FROM SCENE_LINK WHERE PROPERTY_ID IN (97, 151)) AS sl ON o.ID = sl.START_SCENE_ID GO Thanks very much Tag: SQL Express compatibility mode
  - 16
    - SQL Server Reporting Services and Http Compression We have a wierd situation in our project. We have the following setup - 1. a Production web server cluster with 4 web servers 2. a Production sql reporting server that has reports called from web apps hosted on the production cluster We had an app on Production that was serving Pdf reports (not saved on the server) using he report server. Last week we setup Http Compression on the web servers. From around the same time, the Pdf reports no longer come up. Opening the report just produces a blank IE window. When I hit the same reporting server for the same report using a test web server (thus bypassing the production cluster and the Http Compression), the report comes up fine. Is the Http Compression interfering with the Pdf data served The report is called using an aspx page and .aspx files are included for compression. Appreciate all your help. Tag: SQL Express compatibility mode
- Next
  - 1
    - SSRS Report Parameters Hi, I have created two parameters as StartDateTime and EndtDateTime and i am displaying in the Page Header section in the Report as " Report Duration from " & Parameters!DimActivityStartDatetime.Value & " to " & Parameters!DimActivityEndDatetime.Value" It is displaying at the runtime in the report like Report Duration from [DimAct].[StartDateTime].&[2007-03-10TO7:30:00] to [DimAct].[EndDateTime].&[2007-03-10TO7:30:00]. But i need to display only the dates. is is displaying with the dimension name also. Is there any solution to solve this, can anybody help me on this Thanks Dinesh Tag: SQL Express compatibility mode
  - 2
    - MSSQL2005 Analysis Service Distinct Count hi, i am currently trying to build a distinct count on my cube (mssql2005 analysis services). But after i added the discount count on the field i want to and start the processing, the following errors appear. - Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect. - Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database. the count measure works fine. will appreciate any help on this distinct count problem. thanks in advance. - HY Tag: SQL Express compatibility mode
  - 3
    - Maximum Number of Processes Reached I am trying to setup transactional replication between Server A and Server B. There are 265 databases on each server. I am running SQL Server 2005 on Windows Server 2003. The problem comes in at the 201st database. The message in the SQL Server Agent Error Log is : Warning,[398] The job (WSSWPG09-EmpirePaint-WSSWPG06-104) has been queued because the maximum number of working threads (400) are already running. This job will be executed as soon as one of the working thread finishes execution. SQL Server's max worker threads is set to 0. The Distribution, LogReader and T-SQL subsystems have been increased to 200 max_worker_threads. Is there some other setting (maybe a Windows Registry setting) that can be configured to fix this Or have I just hit a physical maximum of the processor Any help is much appreciated. Tag: SQL Express compatibility mode
  - 4
    - Bar Charts - possible to set max widths of columns ? Hi, Is it possible to fix the max width of the columns Sometime the report only generates 1 or 2 columns and it looks a bit odd as they're stretched to the width of the page. Also, when we have a large number of culumns, say 20 odd, the legend text get's real squashed up and un readable, are there any tips/tricks so i can have more flexibility over what's displayed thanks Steve Tag: SQL Express compatibility mode
  - 5
    - Report Builder Query Hi Experts, I have a report model on top of a cube. I have a calculated metrics called Policy Count and different amounts in cube. When i try to create report in report builder with Policy count, amounts and different dimensional attributes it doesnt work. The problem is report builder allows me to select either Policy count in the report or the amount field. Have anyone faced this problem. The same report works in cube browser. Regards' Neeraj Tag: SQL Express compatibility mode
  - 6
    - Trigger / Constraint / ? Hello All I am working on a calendar system where, among other things, we are track resources by event. I.e. here is a conference room and it can only be used by one event at a time. This system also is used by various departments as a calendar for their employees, i.e. sick / vacation / out of office / etc which can be used by more than one person at a time... (it was inherited) My question is if I have a table for the resources (conference room / IT Department Sick Leave / etc) and I have a table for the events (w/ FK to the resources) what will be the best way to make sure that those resources that can't have "double booking" would not allow an insert to the database (while not inhibiting those that can) SQL 2000 table: event eventid int identity pk resourceid int fk comments nvarchar 250 startdate datetime enddate datetime ... table: resource resourceid int identity pk RestrictDoubleBooking bit EmailOnNewEvent bit ... Also, what would be the best practice for emailing an admin on new event insertion for resources that have the flag set Would these best be performed in the application, or on the db side Tag: SQL Express compatibility mode
  - 7
    - tsql for database , size , free space or used space in database hi, i want to know how to find out database size Tag: SQL Express compatibility mode
  - 8
    - SQL DB Cleanup We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed. Tag: SQL Express compatibility mode
  - 9
    - Retriving Deleted record from database Hi friends I have a bit problem here Just I want to get back all deleted record of database How do I perform this task If It is possible then plz help me out Thanks in Advance Khan Tag: SQL Express compatibility mode
  - 10
    - Date part of DateTime I'm trying this simple query in SQL Server 2005: SELECT * FROM tblRecords WHERE InputDate = '1/30/2007' I have two records which have value for InputDate as 1/30/2007 12:34:12 1/30/2007 11:23:32 But the query returns no records due to the time part of the date. How do I pick only the date part for comparison and discard the time part in the InputDate Tag: SQL Express compatibility mode
  - 11
    - Can SSIS save the context of errors at the package level? Lately, I have been experimenting with SSIS and I created a generic custom error logging component that saves all offending data on data flow component failure. However... Instead of re-directing rows at the data flow level and handling/logging the data at that level, is it possible to catch all of this information at the package level and handle/process it there If so how would you do this Thanks! Tony Tag: SQL Express compatibility mode
  - 12
    - Persian Date In SQL Server 2005 Hi all , What is the best solution for reading/writing Persian Date from/into SQL Server 2005 and do calculations on it Note That I connect to sql server database from a .Net App. Any help would be appreciated . Thanks in advance. Tag: SQL Express compatibility mode
  - 13
    - Finding the Month with only Week and Year I'm having trouble figuring out how to do this, but I'm sure there must be a way. I have a table with a column for week number and another for year. I need to find out what month this particular week fell into. I realize that some weeks will cross over two months, so I'm fine with using either the first or last day of the week which could be turned to the day of the year. Any ideas how to get the month when you only have a week number and year to work with Also, I'm not the one who decided to save just a week and a year. I'm just one stuck trying to figure out how to use the data. :) Tag: SQL Express compatibility mode
  - 14
    - cursors vs while loop Hi, We know cursors are evil, use lot of memory, adds up tempdb activity, not scalable, hinders concurrency etc...Say if I replace 10 heavily used cursors in OLTP system with while loops how much do I gain if any and how can I measure that. How can I convince my code review DBA to make this change Does this change help the server Tag: SQL Express compatibility mode
  - 15
    - Error Calling SQLCLR UDF Running SQL Dev Edition on Win2K3 Enterprise Edition. I get the following error. Msg 6522, Level 16, State 2, Line 2 A .NET Framework error occurred during execution of user defined routine or aggregate 'AddressCorrect': System.DllNotFoundException: Unable to load DLL 'D:\CorrectA.dll': Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008) System.DllNotFoundException: at UserDefinedFunctions.CorrectA(String query, String sentlen, StringBuilder errcode, StringBuilder FirmName, StringBuilder urbanization, StringBuilder Dline1, StringBuilder Dline2, StringBuilder LastLine, StringBuilder Stringaddress, StringBuilder DPC, StringBuilder Checkdigit, StringBuilder cityname, StringBuilder stcode, StringBuilder zip, StringBuilder addon, StringBuilder croute, StringBuilder LACS, StringBuilder LOTsequence, StringBuilder LOTcode, StringBuilder PMB, StringBuilder results, StringBuilder strnum, StringBuilder secname, StringBuilder secnum, StringBuilder countyname, StringBuilder countynum) at UserDefinedFunctions.AddressCorrect(String inputAddress) Box has 2GB Ram, with no other processes runing, cant understand why it says out of memory. Appreciate any insights. Thanks, Saptagiri Tag: SQL Express compatibility mode
  - 16
    - TSQL Challenge Im calling all the TSQL gurus to help me with this problem. Tables: Contracts (ContractID,Lastname, Firstname, ContractValue, Date Created Amendments (AmmendmentID, ContractID, Lastname, Firstname, ContractValue, EffectivityDate, DateCreated) Amendments table is related to contracts thru ContractID. Changes to the contract table is made by creating a record to the ammendments table. when this amendment becomes effective it should take effect on the contract. When correcting the contractvalue the user must input the amount to be added or subtracted. For Example: Current Contract: C1, PETRELLI, PITER, 150.00, created on Jan. 01, 2007 Ammendment: A1, C1, PETRELLI, PETER, 20.00, effective on Jan. 20, 2007, created on Jan. 10, 2007 Ammendment: A2, C1, PETRELLI, PETER, 15.00, effective on Mar. 01, 2007, Created on Feb. 01, 2007 The currect Contract: As of Feb.01, 2007: C1, PETRELLI, PETER, 170.00 As of Mar.05, 2007: C1, PETRELLI, PETER, 185.00 The Challenge here is this: How can you make an SQL VIEW that will show the list of Current Contracts and use this view to create a stored procedure (with a parameter @CurrentDate) The SP will show the current Contracts based on the supplied parameter. Example: ShowCurrentContracts(Jan. 05, 2007) Current Contract: C1, PETRELLI, PITER, 150.00 ShowCurrentContracts(Feb. 05, 2007) Current Contract: C1, PETRELLI, PETER, 170.00 ShowCurrentContracts(Jun. 05, 2007) Current Contract: C1, PETRELLI, PETER, 185.00 Tag: SQL Express compatibility mode

tpeugh

I currently use MSDE and now I need to upgrade because we moved to 2005. The problem for me is teh compatibility mode. I need to change it to 80 (2000).

I also need to do it via command line because we have a base command line install for users.

We use it to attach databases and view information.

Is there a way to install SQL Express with a default mode of 80 (SQL 2000).

THanks

Re: SQL Express compatibility mode

Arnie Rowland

I believe that if you 'Upgrade' an MSDE installation, and/or if you attach a SQL 2000 database to SQL Express, the compatibility is automatically set at [80].

If you need a 'tool' to manage your SQL Express installation -rather than be limited to command lines only, you may wish to check out the SQL Server Management Studio -Express version.

SQL Server 2005 Express Edition (Advanced/SSMS)
http://tinyurl.com/yelwr9

SQL Server 2005 SSMS Express Tutorial
http://tinyurl.com/nqsca

Re: SQL Express compatibility mode

Andrea Montanari

hi,

tpeugh wrote:

I currently use MSDE and now I need to upgrade because we moved to 2005. The problem for me is teh compatibility mode. I need to change it to 80 (2000).

as Arnie already pointed out, databases "upgraded" to SQLExpress\SQL Server 2005 version are not directly updated in the compatibility property, which remains 80

I also need to do it via command line because we have a base command line install for users.

We use it to attach databases and view information.

if you need to check/set this property via "command line" tools, you can via SqlCmd.exe and/or oSql.exe, executing the system stored procedure sp_dbcmptlevel ..

Is there a way to install SQL Express with a default mode of 80 (SQL 2000).

no, fortunately

regards