Answer Questions

• Joel Martinez Maintenance Plan Question

  Hi There I am not sure which forum would eb the right place for a Maintenance plan question, so i am just posting it here. I have noticed that the maintenance plans i have created are not dynamic, by that i mean that for each step, integrity check, backup etc, i have always selected the option all user databases. However it seems that when new user databases are created these are not part of the maintenance plan. Is this correct, do i have to create a new maintenance plan or go back and reconfigure the exisiting one evertime a new user database is created Or am i mising something here P.S i gatehr this from looking at the TSQL from the Maintenance plan afetr creating new databases i see the TSQL still only references the databa ...Show All

• Rizzlers List a city only once

  Hi...I want one listbox showing cities but I dont want to list a city more than one time.... I know that DISTINCT maybe could work...But I dont get it to work correctly.... The code: < asp : ListBox ID ="ListBox1" runat ="server" AutoPostBack ="True" DataSourceID ="DataSource1" DataTextField ="City" DataValueField ="City"></ asp : ListBox > < asp : SqlDataSource ID ="DataSource1" runat ="server" ConnectionString =" <%$ ConnectionStrings:ConnectionString %> " SelectCommand ="SELECT DISTINCT [City] FROM [Location]"> </ asp : SqlDataSource > I got the message: The text data type cannot be selected as DISTINCT because it is not comparable &nbs ...Show All

• Javier Luna Built-in data types in SQL Express: best practices?

  Greetings, I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes Why so many Also, what is the "text" ...Show All

• Peto_SVK Granting UPDATE for only certain columns in a table

  I have tried using the SQL statement shown below to grant UPDATE permissions for a single column in a single table to a user with db_datareader privileges. grant update (col_1 ) on trs . dbo.table_1 to calc When I then run a SQL script that has an UPDATE for col_1 on trs.dbo.table_1, I get an error message Msg 230, Level 14, State 1, Line 2100 UPDATE permission denied on column 'col_2' of object 'table_1', database 'TRS', schema 'dbo'. Why is the error message referring to "col_2" when my SQL statement is trying to update "col_1" When I performed the "grant" I did it with an account that has db_owner, db_securityadmin, and db_ddladmin privileges. This worked in SQL Server 20 ...Show All

• dez66 Converting to Windows Authentication? Suggestions, please

  I am presently using MS SQL Server 2000; I have an authentication problem for which I do not have a good solution. Here are the main issues related to the problem: Standards mandate we change to use Windows authentication We have an old fat client application that presently works using SQL authentication We have ad hoc and report users that are members of a domain group that has read only privileges in the database The database is a union of 8 distinct databases that all run on the same server There are nearly 1000 stored procedure with a couple hundred of them using references to one of the other 8 databases – a couple hundred cross database procedures All update and record maintenance is performed within the c ...Show All

• Julian&amp;#237;n Incorrect syntax near ')' SQL 2000

  Hi I keep receiving the error message Incorrect syntax near ')' whilst trying to save a stored procedure from within visual studio, I get the same error from within enterprise manager. The procedure is incomplete but valid, any idea appreciated. The procedure follows: CREATE PROCEDURE dbo.ProcessComment @source AS VARCHAR(50) = NULL AS SET NOCOUNT ON DECLARE @sourceID AS INT DECLARE @counter AS INT DECLARE @Sources TABLE ( sourceid AS INT, lastconversation AS DATETIME ) SET @sourceID = -1 SET @counter = -1 /*We need to see if this is a new or existing source*/ IF NOT @source = NULL BEGIN INSERT @Sources SELECT sourceID, lastconversation FROM sources WHERE name = @source ORDER BY lastconv ...Show All

• Xaid Migrate SQL Server from 64Bit platform to 32Bit

  Hi, All: We have to migrate several 64Bit SQL Servers to 32Bit platform. Due to the 64Bit source systems are all production system, the downtime is too limited. So we can only get the DB files via Detach way. Among the source systems, both SQL2000 and SQL2005 exist. My question are: Is it possible to migrate the DB from 64Bit to 32Bit via Detach/Attach If ok, how to do that Any info will be highly appreciated! Thanks! Best Regards, Kevin See the response to the same question here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1066278&SiteID=1 ...Show All

• bessermt Creating an Expression to Modify a Date Field

  In my Derived Column Transformation Editor I have something like this: DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule]) where [Schedule] is a database timestamp field from a OLEDB Datasource. I want to produce a string something like: "DD/MM/YYYY" using the expression above, I get something really wierd like "1905-07-21 00:00:00" Help much appreciated! Perfect! Thanks! Hey Jhon, DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date d ...Show All

• Musafir backup/restore strategy help

  Hello. I have only ever been required to take a full back up of my main prod database every night. Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour. I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought: Full backup @ 7pm Backup tran log every hour after that starting @ 7am (as there are no changes overnight) How does that sound also when the tran log is backed up, is it truncated Or do I need to shrink it Basically I need to know what to do so it doesn't get too big! ...Show All

• Badhris How to implement OR clause in MDX Queries

  Hi How to implement OR clause in MDX Queries so that i can write a query with OR condition on two dimensions. If possible please post some example. Regards; Rakesh   Hi Mosha   Thanks for your post on the forum. On SP1 the MDX script gives me the error “#Error Arbitrary shape is not allowed when its elements cross a reference dimension”. Whereas on SP2 it displays the result. So; I think they have made the required changes for referenced dimension or M2M relationships. But I still believe that there is some issue with this kind of query and cube design. The result of the query   SELECT [Measures].[Fact Loan Count] on 0 FROM [SANDBOX] WHERE { &n ...Show All

• djshades2004 SQL SP1 build 2047 installation error

  Hello, I am having problems installing the SP1 for SQL server 2005 on a W2K3 R2 server. It appears that the Database Services and Analysis Services are not being updated. I would appreciate any help with this if anyone has seen or had experience with this issue. Thanks, the basic log file is listed below. Redist9_Hotfix_KB913090_3.log 09/17/2006 19:15:53.408 ================================================================================ 09/17/2006 19:15:53.408 Hotfix package launched 09/17/2006 19:16:57.719 Attempting to install instance: SQL Server Native Client 09/17/2006 19:16:57.735 Attempting to install target: HOSPSQL 09/17/2006 19:16:57.735 Attempting to install file: sqlncli.msi 09/17/2006 19:16:57.782 Attempting to ...Show All

• kapeed Restore a Database with Differential

  Hello All, I'm new to this forum after read a few question error it given me little bit more understanding. But here's my problem I was create a database implement some tables than make a 'Full Backup' and input information on the table before make a 'Differential Backup'. I don't want restore a full database and just want to recovery specific table were deleted or removed. Please help........ Message show error: Restore failed for Server 'SALES18'. (Microsoft.SqlServer.Smo) Additional information: System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. Thank you very much. A differential backup, though onl ...Show All

• yabansu removing duplicate entries in SQL Field

  Hi All, Below is a snippet of MS SQL inside some VB that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense. Below is the SQL and the results from the returned page. PS I tried to use distinct but with no luck I want j ...Show All

• mstcrow5429 Is SQL 2005 memory limitation <= OS memory limitation?

  If I install SQL 2005 Standard on Windows 2003 Standard, is SQL limited to 4 gigs of physical RAM I'm planning a new system that will run SQL 2005 Standard edition on a Windows 2003 Standard platform. The spec calls for 8 GB of RAM. My experience would lead me to suspect I need to install Windows 2003 Enterprise to take advantage of all the installed memory. vielen dank. Seems obvious, but needed to ask. You can’t get around the OS limitation. HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• h1 Shared Memory Provider, error: 0 - No process is on the other end of the pipe

  If you connect to a server and open a query in SQL Managment studio (2005) and looses connection and then regain the connection, when you try to run the query it gives me a "TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host."   This happens against both 2005 and 2000 servers.   If I re-run the query it works.   However, this happens in our VB.NET app as well for clients with Wireless connections.   So, something has changed in Framework 2.0 regarding this, does anyone know how to tell the client to not throw this error and just try connecting the server instead which will work   If I remember correctly, the same procedure should work for RTM bits. Is it not work ...Show All