@@Version still shows Service Pack 1

SQL Server Setup & Upgrade

Index ‹ SQL Server
- Archive
  - Smart Device
  - VB
  - VC++
  - VFP
  - VS Express
  - SQL Server
  - IE
  - Windows Forms
  - Dotnet
  - VS 2008
  - Windows Vista
  - Visual Studio
  - Game
  - Office
  - Visual C#
  - SharePoint
  - Architecture
  - Windows Live
  - ISV

- Previous
  - 1
    - Split - Comma seperaed values You can use PARSENAME with an in-line table provided: You will never have more than 4 entries in your comma delimited list Your data will not contain any PERIOD characters You can still use PARSENAME if you have periods, but you will have to translate them into something else and then translate them back. Use of PARSENAME has the advantage that you are leveraging off a built-in function so it will be fast. The base example is something like this: Code Snippet /* ID DepartmentNames 1 IT,Finance 2 Accounting, Finance, IT */ declare @xample table ( ID integer, DepartmentNames varchar(80) ) insert into @xample select 1, 'IT, Finance' union all select 2, 'Accounting, Finance, IT' --select * from @xample select a.ID, b.theName from @xample a cross apply ( select parseName(replace(DepartmentNames,', ','.'), 5 - seg) as theName from ( select 1 as seg union all select 2 union all select 3 union all select 4 ) p where parseName(replace(DepartmentNames,', ','.'), 5 - seg) is not null ) b /* ID theName ----------- ----------- 1 IT 1 Finance 2 Accounting 2 Finance 2 IT */ The Split code looks like this: Code Snippet select a.ID, b.splitValue from @xample a cross apply ( select cast(ltrim(splitValue) as varchar(20)) as splitValue from dbo.split(departmentNames, ',') ) b Tag: @@Version still shows Service Pack 1
  - 2
    - Cannot Pull table with RDA Hi, I'm using the following code to extablish a rda between my laptop and windows mobile classic 6 emulator to pull down a table named customers from a db named ristobar, having configure an account to access to the database which has db_owner permissions (user id=MobileUser, password=pippo) Anyway I always get the following exception: Source : Microsoft SQL server 2005 Everywhere Edition Native Error: 28627 Message: Failure to open SQL Server with given connect string HResult: 80004005 Below follows IIS 7.0 logfile. The Ip address of my laptop is 192.168.0.199 and its name is SKYWALKER What am I doing wrong Thank you BEholder Public Class Rdademo Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=\SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;" Dim rdaUrl As String = "http://192.168.0.199/ristobardb/sqlcesa30.dll" ' The ip is correct Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf" Private Sub RdaPull() Try If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My_ Documents\Rdadb.sdf") Dim engine As New SqlCeEngine engine.LocalConnectionString = localConnection engine.CreateDatabase() engine.Dispose() Dim rda As SqlCeRemoteDataAccess = Nothing rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection) rda.InternetUrl = rdaUrl rda.LocalConnectionString = localConnection rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOnWithIndexes, "Error Table") rda.Dispose() MessageBox.Show("Done!") Catch ex As System.Data.SqlServerCe.SqlCeException MessageBox.Show(ex.Source) MessageBox.Show(ex.NativeError) MessageBox.Show(ex.Message) MessageBox.Show(Hex(ex.HResult)) End Try End Sub IIS 7.0 Log: Software: Microsoft Internet Information Services 7.0 #Version: 1.0 #Date: 2007-08-29 14:28:54 #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status 2007-08-29 15:21:33 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 2007-08-29 15:21:35 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 2007-08-29 15:21:38 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 2007-08-29 15:21:40 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 2007-08-29 15:21:43 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 2007-08-29 15:21:47 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 2007-08-29 15:21:49 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0 Tag: @@Version still shows Service Pack 1
  - 3
    - How to connect to remote server Is the remote instance a SQL Server Express edition Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: @@Version still shows Service Pack 1
  - 4
    - SQL 2005 Standard on Vista RTM And here's an external blog with the same suggestions: http://blogs.msdn.com/echarran/archive/2006/01/03/509061.aspx Tag: @@Version still shows Service Pack 1
  - 5
    - Performance-Issue with variables thank you! Tag: @@Version still shows Service Pack 1
  - 6
    - cannot connect to server Reporting Services Error Tag: @@Version still shows Service Pack 1
  - 7
    - using encryption via an asp site. how is it done SSL protects the communication with the server; the encryption you experimented with is for protecting the data within the server. For the steps required to view the encrypted data, it is up to you to determine those in your database application - you can have the user manually decrypt or you can have him access the data via views that automatically decrypt. Thanks Laurentiu Tag: @@Version still shows Service Pack 1
  - 8
    - The '[CDbl]' function does not exist. Excellent! That works just fine. Now for the next part of the question: what I'm actually doing is using CDbl() (and, by the way, IsNull()) in a calculated member. Prepending with "VBA!" doesn't work there -- any ideas Thanks again! Keith... P.S. Seems to be an IA64 thing -- read somewhere the code as I have it will work fine in 32 bit environments but not on IA64. Tag: @@Version still shows Service Pack 1
  - 9
    - How to acces report of other system from my system. Can u tell me.How to resolve this prblm Tag: @@Version still shows Service Pack 1
  - 10
    - FTP Connection problem with "The Password was not allowed" error message EncryptSensitiveWithPassword means you have to enter a password in the package properties. You have specified the wrong password there, most likely. For running in BIDS, EncryptSensitiveWithUserKey is just fine. Tag: @@Version still shows Service Pack 1
  - 11
    - Problem with "Transfer SQL Server Objects Task" Hi Michael, I've also experimented with 'copySchema'. No luck so far. Service pack 2 is installed on both servers. I've read the article you're referring to and I just can't believe that the "Transfer... Task" isn't able to perform such a simple thing. Tag: @@Version still shows Service Pack 1
  - 12
    - Problem running SSIS from C# program. What is the error message exactly Can you see the package in the MSDB database when looking in Management Studio Have you read through this http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx Tag: @@Version still shows Service Pack 1
  - 13
    - Schema use in SQL2005 Certainly...Se my Comments below in bold red.... The problem is how the EXEC statement resolves to the default schema. I'd like to know if this is a bug or expected behaviour or can it be resolved /****** Object: StoredProcedure [myschema].[dnn_Forum_StatisticsGet] Script Date: 09/29/2007 08:53:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [myschema . [dnn_Forum_StatisticsGet] / *SCHEMA SPECIFIED is DEFAULT FOR THE USER*/ ( @ModuleID int , @UpdateWindow int = 12 , @TabId int ) AS -- Do we need to update the statistics DECLARE @LastUpdate datetime DECLARE @DateWindow datetime SET @LastUpdate = ISNULL (( SELECT MAX ( CreatedDate ) FROM dnn_Forum_Statistics_Site ), '1/1/1797' ) SET @DateWindow = DATEADD ( hh , - @UpdateWindow , GetDate ()) if ( @LastUpdate < @DateWindow ) BEGIN EXEC dnn_Forum_AA_StatisticsSiteUpdate 0 , 0 , @ModuleID , @TabId / *THIS COMMAND FAILS AS SCHEMA NOT SPECIFIED - SQL2000 this would resolve to the default owner*/ END -- SELECT current statistics SELECT TOP 1 S .*, MostReadSubject = ( SELECT Subject FROM dnn_Forum_Posts ( nolock ) WHERE PostID = S . MostReadThreadID ), MostViewsSubject = ( SELECT Subject FROM dnn_Forum_Posts ( nolock ) WHERE PostID = S . MostViewsThreadID ), MostViewsThreadForumID = ( SELECT ForumID FROM dnn_Forum_Threads ( nolock ) WHERE ThreadID = S . MostViewsThreadID ), MostActiveSubject = ( SELECT Subject FROM dnn_Forum_Posts ( nolock ) WHERE PostID = S . MostActiveThreadID ), MostActiveThreadForumID = ( SELECT ForumID FROM dnn_Forum_Threads ( nolock ) WHERE ThreadID = S . MostActiveThreadID ), MostActiveUserAlias = ( SELECT Alias FROM dnn_Forum_Users ( nolock ) WHERE UserID = S . MostActiveUserID ) FROM dnn_Forum_Statistics_Site S ( nolock ) / *THIS WILL RESOLVE CORRECTLY TO THE DEFAULT SCHEMA*/ WHERE ModuleID = @ModuleID ORDER BY CreatedDate DESC -- SELECT TOP 10 Users SELECT DISTINCT TOP 10 U .*, FUR . Alias , FUR . IsTrusted , FUR . PostCount , FUR . EnableOnlineStatus FROM dnn_Forum_Users FUR ( nolock ), dnn_Users U ( nolock ), dnn_UserPortals UP ( nolock ), dnn_Tabs DT ( nolock ), dnn_TabModules TM ( nolock ), dnn_Modules DM ( nolock ) WHERE FUR . UserID = U . UserID AND U . UserID = UP . UserID AND UP . PortalID = DT . PortalID AND DT . TabID = TM . TabID AND TM . ModuleID = DM . ModuleID AND DM . ModuleID = @ModuleID AND U . UserName <> 'NNTP_NEWSGROUP_USER' AND FUR . PostCount > 0 ORDER BY FUR . PostCount DESC -- SELECT top 10 Moderators SELECT TOP 10 U .*, FUR . Alias , FUR . IsTrusted , FUR . PostCount , M . PostsModerated FROM dnn_Users U ( nolock ), dnn_UserPortals UP ( nolock ), dnn_Forum_Users FUR ( nolock ), dnn_Forum_Moderators M ( nolock ), dnn_Tabs DT ( nolock ), dnn_TabModules TM ( nolock ), dnn_Modules DM ( nolock ) WHERE U . UserID = FUR . UserID AND FUR . UserID = M . UserID AND U . UserID = UP . UserID AND UP . PortalID = DT . PortalID AND DT . TabID = TM . TabID AND TM . TabID = DM . ModuleID AND DM . ModuleID = @ModuleID AND M . PostsModerated > 0 ORDER BY PostsModerated DESC -- SELECT Moderator actions SELECT Description , TotalActions FROM dnn_Forum_ModerationAction ( nolock ) ORDER BY TotalActions DESC Tag: @@Version still shows Service Pack 1
  - 14
    - Granting UPDATE for only certain columns in a table You are going to have to dig deeper. The only scenario that I can think of where an update to another column causes an update to another column causing issues like this is with a trigger with dynamic SQL (a real no-no in almost all cases, but it could exist). The fact that you have appended database names to the ddl makes me curious as to how that *might* cause issue, but I don't even see how anything cross-database could be an issue either. Here is a script that shows what I am meaning: create table test ( column1 int, column2 int ) go create user fred without login go execute as user = 'fred' go update test set column1 = 1 /* Msg 229, Level 14, State 5, Line 1 The UPDATE permission was denied on the object 'test', database 'tempdb', schema 'dbo'. */ go revert go grant update (column1) on test to fred go execute as user = 'fred' go update test set column1 = 1 /* (0 row(s) affected) */ update test set column2 = 1 /* Msg 230, Level 14, State 1, Line 1 The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'. */ go revert go create trigger test$updateColumn1 on test after update as begin exec(' update test set column2 = 2') end go execute as user = 'fred' go update test set column1 = 1 /* Msg 230, Level 14, State 1, Line 2 The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'. */ If you could post a full example like this showing your issue I think that you might find your error, or certainly one of us can help you out. Tag: @@Version still shows Service Pack 1
  - 15
    - Event ID: 17055 I am currently through the same issue. Is there anyway to compact or split up the database (SUS.mdf) Tag: @@Version still shows Service Pack 1
  - 16
    - Trouble with Pivot Task (multiple problems) - Long Jamie, A set of data is the group that will be selected to make the first pivoted row. So if the columns from the view that is queried exist as: Site ID | Pivot ID | Pivot Value | Active Flag 251 | 1001 | Yes | Yes 251 | 1002 | 15 | Yes ... 251 | 2500 | 19 | Yes 333 | 1001 | Yes | No 333 | 1002 | 40 | No ... 333 |2500 | 71 | No The pivot output would be: 251 | Yes | 15 | ... | 19 | Yes 333 | Yes | 40 | ... | 71 | No The Error message for this code is: "Unrecognized pivot key value." However, how could any value be unreconized if 1) the pivot actually works as expected 2) The query from the view is set right now to limit data returned (pivot id's) to those that are known and mapped in the pivot task. If I run the pivot on 1-2 sites all works as expected with no errors. If I run the pivot on 3-4 sites all works as expected WITH redirect error output for all sites If I run the pivot on 6+ sites all fails with no redirect noting how "isErrorOut" is not set. All data for all sites tested has been verified as the same. So there are no unknown pivot keys being returned, no duplicates (except for output, which is another problem). Thanks. Tag: @@Version still shows Service Pack 1
- Next
  - 1
    - Linked server SQL 2005 to 2000 You have a couple of options. Either 'wrap' the server/instance name in square brackets, e.g., SELECT Col1, Col2, etc FROM [machine1\sql2000].dbname.dbo.foobar or, Create an Alias for the linked servername. Tag: @@Version still shows Service Pack 1
  - 2
    - SQL Questions Interview 1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem . Many many reasons. Probably something like (in this order of likelihood) Locks, lots of data, minimal hardware, a waitfor statement in trigger (don't say that if you are being interviewed unless you are feeling really confident!). etc 2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are, id, name, description) id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem See #1, other than the snarky trigger comment for the trivial reasons. 3) How would you change the identity column value or can we change the identity value and how You cannot. You can insert a new row and pick your own identity, but the values are not updateable. 4) How would you use the shared, update, exclusive locks in the sql statement. Look up locking hints and isolation levels. They control how you customize locks and length that locks are being held. Tag: @@Version still shows Service Pack 1
  - 3
    - Line Chart Color Merin, I did see that option. But doing that changes the colors for ALL lines in my chart. I'd like my series to have different colors, if possible. Any ideas Michael Tag: @@Version still shows Service Pack 1
  - 4
    - Im new to using views, and this program they have me working on is using them quite heavily..(cant read from a view) Hey Arnie, thanks for this, I was under the assumption that you couldnt do that either, but had noticed that other calls were doing the same thing just as fine. However! I must be tired, or in a rush, because all the other ones are just using SELECT * FROM [ViewName] which would allow that...DUH! not my best first post, i swear im not that dense! Tag: @@Version still shows Service Pack 1
  - 5
    - Insert Performance Hey, I'm writing an application where I have to perform a lot of inserts/updates on a SQL Server Compact Edt. 3.1 database (up to 50.000 or even 100.000 consecutive inserts). I'm using native C++ and OleDB. I set the command text only once for each table and I increased maximum buffer size (DBPROP_SSCE_MAXBUFFERSIZE). There are also no table indices or foreign keys. The performance is quite well, but not good enough. I took a closer look at the MS Merge Replication and I observed a really good insert performance. Maybe somebody can tell me some ''secrets'' how I can speed up my query performance! best regards Tom Tag: @@Version still shows Service Pack 1
  - 6
    - Cannot see Report Builder in Report Manager I do have the report role assigned to my username. When I connect to the report manager, what username and password should I be entering in the login box Tag: @@Version still shows Service Pack 1
  - 7
    - Update = Delete & Insert? Just to be clear. A TRIGGER treats updates as a delete the old record, insert the new record, to make it easier to manage within the trigger. Internally, it is complicated if it actually deletes it and inserts the record into the same space. Why do you need to know Tag: @@Version still shows Service Pack 1
  - 8
    - odd issue with NOT IN statement It is bcs of your null value on m_orders table.. (Ex) Select * From MYTable Where Id in (1,3,4,null) Will return 1,3,4 Select * From MyTable Where Id not in (1,3,4,null) will not return any value Why The not in (1,3,4,null) = not (Id =1 or Id =3 or Id=4 or Id=null) = id <> 1 and Id <> 3 and Id <> 4 And id <> null = True And True And True And NULL = True And NULL = FALSE ie, ALWAYS FALSE The in (1,3,4) = (Id =1 or Id =3 or Id=4 or Id=null) = True or False Or null = True Or NULL = True So change your query as follow as, update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders Where [order xref] is NOT NULL) Tag: @@Version still shows Service Pack 1
  - 9
    - Looking for Feedback: Native 64-bit Support in SQL Express Mike Wachal - MSFT wrote: What advantages will a native 64-bit Express offer you If for x64 application SQLNCLI provider use shared memory mode I gues it can work only with x64- Express If you could only have either native 64-bit Express or WoW support, which would you choose (Bonus if you tell me why. ) 64-bit Express because we have quite big notification server build as Extended SP and for contacting with 64-bit Application I dont' need to go to other branches in registry. --What user/application scenarios are driving your need for native 64-bit Express previous answer also answer on this question. I have quite complex application based on several application servers and ESP requires x64 arcitecture. Off course I can do workarround but it take some time to do support of WOW32 for installer and changes in ESP logic Tag: @@Version still shows Service Pack 1
  - 10
    - Incorrect processing order for views While this is not a fix for your problem it might help you out for a while. You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround. Martin Tag: @@Version still shows Service Pack 1
  - 11
    - How to insert into a table with a function? Yes, this is really a stored procedure. As you are dynamically selecting the tablename you may want to look at the following topics in Books Online: 1) Cursors - to replace your For Each Loop 2) EXEC or sp_executesql - to dynamically execute a sql string built. You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor: ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables HTH! Tag: @@Version still shows Service Pack 1
  - 12
    - Data disappearing during Run time Unfortuantly I can't find this the only one similar is "FAQ: My data doesn't get saved in Visual Studio" however this isn't the problem, in my program I have a save button wich updates the database, when I click this the data saves to the database but disapears from view, however if I close the application and re open it the data that I'd previously saved is nw there Tag: @@Version still shows Service Pack 1
  - 13
    - Store Membership information using Access .mdb database Hi Caddre Thanks for your reply. I actually do need it in VB. I tried using an online converter,but the converter itself is has errors. I used this converter: http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx and recieved these errors. Conversion was completed, however the following errors were reported: -- line 255 col 65: Out parameter converted to ByRef -- line 1176 col 89: Out parameter converted to ByRef -- line 1299 col 117: Out parameter converted to ByRef -- line 1387 col 115: Out parameter converted to ByRef -- line 1480 col 91: Out parameter converted to ByRef -- line 1491 col 91: Out parameter converted to ByRef -- line 1505 col 59: Out parameter converted to ByRef -- line 1506 col 51: Out parameter converted to ByRef -- line 1507 col 43: Out parameter converted to ByRef -- line 1508 col 52: Out parameter converted to ByRef Are these errors significant I would post the converted code, but it's abit much and I'm not sure if it would be allowed. Tag: @@Version still shows Service Pack 1
  - 14
    - Analysis Services Roles - Dimension Data I'm not sure I'm 100% clear on the problem you are having. Are you saying the dimension members are appropriately restricted but the totals reflect non-visible members For example, if you drill down to Office Code you see just TOR. The total for All Office Code is different than the TOR value If this is what you are trying to address, take a look at the Visual Totals setting in your dimension data security. By setting this value to True, SSAS calculate the totals based on just those members you have access to. Thanks, Bryan Smith Tag: @@Version still shows Service Pack 1
  - 15
    - Why does OPENROWSET not connect? I am able to make OPENROWSET work with a DSN (created a system DSN 'TestDSN'). Ran the following SQL SELECT a .* from OPENROWSET ( 'MSDASQL' , 'DSN=TestDSN' , 'select * from sysservers' ) as a Check for the permissions on the database/table for the login that you are using in the DSN. Also make sure that the SQL Server authentication is configured to run with both SQL Server and Windows Authentication mode (Look for Security in Server Properties). Create the DSN to run with Windows Authentication Hope this helps Tag: @@Version still shows Service Pack 1
  - 16
    - Order By not ordering Month names the way I want Try this (Ordering by the numeric Month order): SELECT ID, ReportDate = datename( month, ReportDate ), Items = max(items) FROM MyTable GROUP BY ID, datename( month, ReportDate ), Month = datepart( month, ReportDate ), ORDER BY ID, Month = datepart( month, ReportDate ) Tag: @@Version still shows Service Pack 1

axel_2005

We just installed the service pack 2 to our server, so I did a select @@version but it's still say Service Pack 1, even though the version number is now 3054. Is that a bug in the service pack

Thanks.

Re: @@Version still shows Service Pack 1

Sam Lester - MSFT

Can you post the entire string returned by SELECT @@VERSION My guess is that you are seeing the Windows OS Service Pack that is also included in the @@VERSION call. SQL Server should return SP2.

Thanks,

Sam Lester (MSFT)

Re: @@Version still shows Service Pack 1

axel_2005

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Re: @@Version still shows Service Pack 1

Sam Lester - MSFT

Yeah, the SP1 in this case refers to your Windows Version:

Windows NT 5.2 (Build 3790: Service Pack 1)

The Service Pack level apparently isn't listed in the @@VERSION as I thought it was. Build 3054 is definitely SP2, but you can also check Add/Remove Programs report or the setup regitry hives to confirm.

Thanks,
Sam Lester (MSFT)

Re: @@Version still shows Service Pack 1

axel_2005

Thanks for your help.