InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:13:34 GMT20http://www.sqlservercentral.com/Forums/Topic1305859-1292-1.aspxhi, so , if i restore a database from Server1 to Server 2. All the user accounts are still listed in the database on Server 2 and still have access to that database. Is it ok to leave them as is? I belive this is called a orphan account?Whats best practise for them? delete and re-create from the instance level? Leave as is, it does work after all. and i guess keeps logins clean since if you do it for all db then you dont have a big list of users if you have 1000s of logins. Thanks for any advice! Thu, 24 May 2012 07:54:47 GMTn00bDBAhttp://www.sqlservercentral.com/Forums/Topic1303649-1292-1.aspxHello Masters,Is there any check through which we can verify Backup file? I mean to say after any backup how can we check that the backup file is not corrupted and consistent ? Any query or command ?Mon, 21 May 2012 13:13:29 GMTjitendra.padhiyarhttp://www.sqlservercentral.com/Forums/Topic1304848-1292-1.aspxI have found some code that is periodically performed on our databases that is described as 'reindex patch'. It creates a table of tables that meet certain requirements then runs dbcc reindex (tablename) with no_infomsgs on the list. I am interested in converting this to use 'alter index'.I investigated the table list to see what was missed and the main difference is table type 'base table' is delt with but table type 'view' is not.I am new to SQL and strugling to work out why it was set thus so I have this question:Q] Is there any benefit to excluding views from a reindex (apart tasks to perform)?I have been looking at the Brads Sure guide to Maintenance Plans and I can't seem to locate the answer. I am tempted to just replace this code with the maintenance task instead.Thanks in advanceWed, 23 May 2012 05:25:53 GMTDrenlinhttp://www.sqlservercentral.com/Forums/Topic1287529-1292-1.aspxAfter playing in Server management for a hour or so and Google for a while more I decided maybe someone on here may know the answer. so here it goes.I have a LOT of .txt files that i need to bring into a database. right now i have 5 access databases doing it. it isn't hard it is just bringing them in then appending them. that being said there is a difficulty. these files have VERY specific character counts for different data. for example 1-13 is last name, 14-20 is first name...and such. I have access pulling these tables in for each month of the year for all 5 databases. it is time consuming and yet the only way i know how to pull in the data, separate it into its distinct pieces. so is it possible to bring it into SQL and separate it into its distinct pieces?Fri, 20 Apr 2012 17:52:57 GMTslunt01http://www.sqlservercentral.com/Forums/Topic1305839-1292-1.aspxHi, In my report , @Pack should be a multi select but when I try to run the ssrs report if i select 1 or 2 values in the dropdown it runs just fine but when I try to select 3-4 values, the report still shows initial 2 choices. My guess is it is just filtering first 2 dates based on ',' . Dates is varchar field here.Any ideas please?Thanks for your help.[code="sql"]--MY Report dataset for @Dates Paramcreate table #123 (dates varchar(25) null)INSERT INTO #123 (dates)SELECT '1/4/1996'UNION ALLSELECT ' 10/11/1996' UNION ALLSELECT '1/1/2008' UNION ALLSELECT '12/16/2011' UNION ALLSELECT '5/02/2011' union allSELECT '05/10/2011'select * from #123 drop table #123--This is how I filter in my proceduredeclare @Dates varchar(25)select Name, ageWHERE (p.dates IN (SELECT LTRIM(RTRIM(Item)) FROM dbo.fnbreakString(@Dates, ',')) Or @Dates IS NULL)---Function is below:ALTER FUNCTION [dbo].[fnbreakString](@Text varchar(max),@Delimiter varchar(2)= ' ')RETURNS @Strings TABLE( Position int IDENTITY PRIMARY KEY, Item varchar(max))ASBEGIN DECLARE @Index int SET @Index = -1 WHILE (LEN(@Text) > 0) BEGIN SET @Index = CHARINDEX(@Delimiter,@Text) IF (@Index = 0) And (LEN(@Text) > 0) BEGIN INSERT INTO @Strings VALUES (@Text) BREAK END IF (@Index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@Text,@Index - 1)) SET @Text = RIGHT(@Text, (LEN(@Text) - @Index)) END ELSE SET @Text = RIGHT(@Text, (LEN(@Text) - @Index)) END RETURNEND [/code]Thu, 24 May 2012 07:41:43 GMTSQL_pathhttp://www.sqlservercentral.com/Forums/Topic1305244-1292-1.aspxHello,I was wondering and cannot think straight on this, So I came with an idea of getting ideas/views from different people..[b]HARD RULE :[/b] When a Primary Key is defined on a table for a column, by default NULLS are not allowed and Clustered Index is created behind Scenes.[b]MY question :[/b] When an Unique Key and NOT NULL is defined on a column for a table, Does it creates CLUSTERED INDEX behind scenes ? [b]My understanding : [/b] I was reading on Internet and in books too, when a Unique Key is defined on table it creates a NON-CLUSTERED INDEX behind scenes. But does this changes, when a NOT NULL is defined along with Unique Key on a table, as this is logically creating a primary key. And I believe that Sql Server should created a Clustered Index.Please let me know, your valuable comments on this..... Thank youWed, 23 May 2012 12:13:51 GMTquillis131http://www.sqlservercentral.com/Forums/Topic1303698-1292-1.aspxBulk log recovery model minimize the log space. As per my knowledge we uses Bulk recovery model when we are using any bulk operation like BCP. Suppose I want to copy 1 million rows to a table using BCP or using any dml query. Each insert operation will be written to disk, so when 1 million rows will be copy or written to table, obviously the same number of entries written to the log file and thus log file will grow.Than how can we say that Bulk recovery model minimize log space ? Is this model bypasses any transaction operation or any similar operation to minimize log space?Can anyone explain me?ThanksJitendraMon, 21 May 2012 14:02:03 GMTjitendra.padhiyarhttp://www.sqlservercentral.com/Forums/Topic1304322-1292-1.aspxI've got this query close to what I'm looking for. In the same query, I'd like to have 3 extra columns that will put a "1" in those columns that have a charge > 0. For example: If EDCharges is 500, then I'd like to have an EDCharges2 column produce a 1 in that column. Same with NICUCharges and PICU/Peds. Seems not to work when I case an Alias column. Thanks for any help.SELECT P.[encounterkey],p.[VisitID],p.[DivisionCode], SUM(CASE WHEN [departmentnumber] IN(091111,011111,181101,131111) THEN Chargeamount ELSE '' END)AS 'EDCharges', SUM(CASE WHEN [Departmentnumber] IN(013121,093111,133111) THEN Chargeamount ELSE '' END) AS 'NICUCharges', SUM(CASE WHEN [Departmentnumber] IN(013021,013001,093001) THEN Chargeamount ELSE '' END) AS 'PICU/Peds' FROM [database1] as PLEFT OUTER JOIN [database2] as CON P.[EncounterKey]= c.[AccountKey] where dbo.fn_getage(PatientDOB,AdmissionDateTime)<18 and (p.DischargeDateTime > '2011-10-01' and p.DischargeDateTime<'2011-10-31') and p.DivisionCode in ('1','2','6','E') GROUP BY p.encounterkey,p.VisitID, p.DivisionCode order by p.EncounterKeyTue, 22 May 2012 10:54:49 GMTboehnchttp://www.sqlservercentral.com/Forums/Topic1304199-1292-1.aspxHi, I do get all the data in my stored proc but when I run my report ssrs, it skips the rows which have apostrophe in Name column.I rechecked that There is no FILTER on the report. There is Name data set in my report and it has exact same available values as the ones in my stored proc resultany hints would be appreciated.thanks.[code="sql"]create table #123 (NAME varchar(25) null)INSERT INTO #123 (NAME)SELECT 'O''Neill'UNION ALLSELECT 'O''Brian'UNION ALLSELECT 'smith' UNION ALLSELECT 'Brown' select * from #123 drop table #123 [/code]Tue, 22 May 2012 08:29:31 GMTSQL_pathhttp://www.sqlservercentral.com/Forums/Topic1302228-1292-1.aspxi have a select stmt in SP with a where condition from c# front end i am passing the condition param to SP & i waant the result fields of the same in front end i tried but i am geeting error as the paramater is not suppliedthis is my front end codeSqlCommand cmdeper = new SqlCommand("usp_c_bind", con); cmdeper.Parameters.AddWithValue("@key",key); SqlDataReader dreper = cmdeper.ExecuteReader(); if (dreper.HasRows) { while (dreper.Read()) { txtlname.Text = dreper["LNAME"].ToString(); } }SP is @key varchar(50) -- Insert statements for procedure here Select LNAME,LDAY,convert(varchar, ldate, 103) as ldate from CVES where LNAME= @keyThu, 17 May 2012 19:56:32 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1303605-1292-1.aspxHi, I take care of such fields in my column ITEM in my query by making then space(1)then create a ITEM parameter and data set in my report and add extra field as space like below[code="sql"] select space(1) as ITEM where ITEm like ' ' or ITEM is nullunionSelect Item from ITEMS where ITEM is not null[/code]but still my report fails to show "SOME" rows where ITEM has nothing in it..Is it carriage retun issue? Any ideas please?? ThanksMon, 21 May 2012 12:40:50 GMTSQL_pathhttp://www.sqlservercentral.com/Forums/Topic1304046-1292-1.aspxA co-worker is telling me that there is a square character in the field of most records in a table she has. She said that this means the data is encrypted. Is this true? I cannot find this verified anywhere.Tue, 22 May 2012 05:08:50 GMTrummingshttp://www.sqlservercentral.com/Forums/Topic1302840-1292-1.aspxWhere can i get this for the Developer edition? I have tried http://sqlserversamples.codeplex.com/ and the links no longer seem to work or the Sample doesnt install correctly or have a DBFri, 18 May 2012 14:38:09 GMTlee-hardmanhttp://www.sqlservercentral.com/Forums/Topic1303780-1292-1.aspxHi all,my department works on several different projects, all of them on SQL Server 2008 and on the same server.Each project has it's own database, user interface, etc.However, they all share common data: Country, Products, Clients. These tables have been put on another database, called REFERENCE, still on the same server.The problem is about foreign keys, in the projects, that cannot reference the REFERENCE data (FK can't work on different dB).My question is about best practices here, knowing that Projects database should not modify the REFERENCE data: - Should I copy the REFERENCE db into a particular schema of each project and synchronize any modification from the REFERENCE dB to the Projects (what is the best way to synchronise ? Triggers ?) - Should I stop using foreign key in projects (:ermm:) - Any other suggestions ?Thanks !BrunoMon, 21 May 2012 16:10:16 GMTbabaguluhttp://www.sqlservercentral.com/Forums/Topic1303566-1292-1.aspxHello,This is probably simple but not sure why its not working, well its working but duplicating the results, what i am trying to do is take first name from table 1 called customers and compare it to all the first names in table 2 called persons, if there is a match, then insert match finding into a table called match, insert customer first name profile id from table 1 and first name from person table id into match table, this will later be going into a report but for now want to add it to a table. the problem is, i have 5k first names on table 1, and the query below shows Bob on match 5k times... why, i even wrote a query to remove duplicates but never seems to remove it... any one please help, pulling my hair and running out of hair :(declare @firstname nvarchar(150), @lastname nvarchar (200), @company nvarchar(200), @address nvarchar(250),@city nvarchar (250), @state nvarchar (250), @country nvarchar (250), @zip nvarchar (50),@website nvarchar (250), @emailaddress nvarchar (250), @customerid int, @counter int, @max int,@max2person int , @counter2 intselect @max = COUNT(*) from Customersset @counter = 1while (@counter < @max)BEGIN select @firstname = firstname from Customers where customerID = @counter select @maxperson = COUNT(*) from person set @counter2 = 1 while(@counter2 < @maxperson) BEGIN if (exists(select FirstName from Person where ProfileID = @counter2 AND FirstName = @firstname)) BEGIN IF(select COUNT(*) from match where customerid = @counter AND ofacid = @counter2 AND columnid = 1) >2 BEGIN UPDATE Match set customerid = @counter, personid = @counter2, columnid = 1 where customerid = @counter AND personid = @counter2 and columnid = 1 END ELSE BEGIN insert into Match(customerid, personid, columnid) select @counter, @counter2, 1 from Person a set @counter2 = @counter2 + 1 END END ELSE BEGIN set @counter2 = @counter2 + 1 END END set @counter = @counter + 1 ENDMon, 21 May 2012 11:45:05 GMTSiten0308http://www.sqlservercentral.com/Forums/Topic1302422-1292-1.aspxdifference between last(max) and penultimate(max-1) for each group[b][u]I have:[/u][/b]col1 col2a 1a 2a 3b 5b 6b 7c 3c 4c 5max and max-1 refers to the data in column 2 [b][u]I want:[/u][/b]col1 col2a 3-2b 7-6c 5-4ThankyouFri, 18 May 2012 05:59:33 GMTgelu01http://www.sqlservercentral.com/Forums/Topic1303047-1292-1.aspxHi,I am importing data from text file, where the input column value is like"02-APR-12 12.49.24.938000 PM" and the destination table datatype for the column is specified as "datetime".If i insert the value using convert i am getting this below error.Msg 241, Level 16, State 1, Line 2Conversion failed when converting date and/or time from character string.Please help me with this.Thanks.Sat, 19 May 2012 11:22:37 GMTdeep_kkumarhttp://www.sqlservercentral.com/Forums/Topic1302887-1292-1.aspxIs there not an option to enable filestream on the Developer copy of SQL S 2008? I am trying to enable filestream in the SSCM?Fri, 18 May 2012 16:25:11 GMTlee-hardmanhttp://www.sqlservercentral.com/Forums/Topic1300779-1292-1.aspxcurrently i have multiple sql statements for different table executed in the front end(asp.net)the querys are if exist(select.........)update..............elseinsert(...............)from the front end its working finei am creating a single SP for the same , when calling it in the frontend how can i pass the multiple values for different tables ? & execute itor do i need to create different SPs for different tables?i alo need to add in begin trans & end trans so that either all teh queries gets executed or noneTue, 15 May 2012 23:47:28 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1302759-1292-1.aspxHello every one, here I need your help in deciding which model is a good practice and easy to maintain: [b]MODEL 01: [/b]name email A B C Daa john@test.com 0 0 1 1bb rick@test.com 0 1 0 1cc sally@test.com 0 1 1 0dd aha@test.com 1 1 0 0 I have a table with columns A-D which are of the BIT data type. Each column represents a different scenario. To keep track of whether or not a particular type of email needs to be sent to the person (their row) 0 - indicates email sent and 1 - indicates, email needs to be sent out. As emails (different scenario's) are added to the system this model requires a new column to be added and widens the table. I’m wondering if the email type (A-D) could be stored in a list column like outline below: [b]MODEL 02: [/b] name email Xaa john@test.com 'A,B,C,D'bb rick@test.com 'B,D'cc sally@test.com 'C,D"dd aha@test.com 'A,B' In the above model, after the email is sent out for scenario A, it should dis appear from 'A,B,C,D' indication emails yet to be sent for B,C and D. Is there a construct in SQL Server to handles columns of a list data type? Would performance suffer when querying this table with the “LIKE” command? Is there another way to query this column, like a ListFind() function? Would I have to handle all maintenance to ensure that the list is always well formed from the application code? Should I just stick with the original model and deal with all of the columns per email? I’d rather not create another table in this case. Fri, 18 May 2012 12:39:41 GMTonlygoodoneshttp://www.sqlservercentral.com/Forums/Topic1301782-1292-1.aspxI've been asked to run a package on a sql server - its Enterprise Edition (64-bit), SQL Server 2008. The compatibility level of the database is SQL Server 2005. The package was provided by a consultant off site. I copied the package on to the server - placing it in the directory where it could be seen by SSMS integration services. My experience with SSIS is limited.A few questions: 1. Copying to a directory where it can be seen by SSMS integration services - is there a better method to employ to run the package? 2. This is Enterpise Edtion 64 bit - is there a requirement to run this from a command prompt as opposed to running through the "Execution Package Utility"? I believe in 2005 - running through the UI was 32 bit vs running command prompt you can convert to 64 bit. Is my concern here justified? Am I confusing this with something else? Any comments / URLs provided would be appreciated. Thanks.Thu, 17 May 2012 07:56:57 GMTjralston88http://www.sqlservercentral.com/Forums/Topic1301942-1292-1.aspxTrying to "case" into the same column 'Department'. I'm obviously a Newbie as this is producing two "Department" columns. Tried one Case Statement with some "Or" but it wasn't working. Any help is appreciated.SELECT [EncounterKey] ,[MedRecNo] ,[AccountKey] ,[DischargeDateTime] ,[DateOfService] ,[PrimaryInsPlan] ,[DivisionDesc] ,[InpatientOutpatientInd] ,[PatientType] ,[DepartmentNumber] ,[EDFlag] ,[ChargeAmount] case when (departmentnumber in (091111,011111)) then 'Emergency' else '' end as Department, case when (departmentnumber in (013121)) then 'NICU' else '' end as Department From [database1] where [departmentnumber] in (091111,011111,013121)Thu, 17 May 2012 10:30:25 GMTboehnchttp://www.sqlservercentral.com/Forums/Topic1271578-1292-1.aspxIf I have the following:1 Mike Blue2 Mike Red3 Joe Green4 Joe Black5 Joe RedAnd I want1 Mike Blue Red2 Joe Green Black RedWhat would be the easiest way to manipulate the data being mindful that i am really working with an XML file that I have imported into memory:<Automobile> <Car name=Corvette> <Properties color=Red ID=12345> </Properties <PropertiesGroup color=Green> </Properties> </Car></Automobile>So When I select the data from memory I get:Corvette 12345 RedCorvette 12345 GreenBut I want:Corvette 12345 Red GreenThanks.Fri, 23 Mar 2012 06:48:59 GMTgrecci1077http://www.sqlservercentral.com/Forums/Topic1301531-1292-1.aspxI am building my first on-line store by following an example in a book.Problem is that the book example relies on stored procedures on the database which are called from the php files and my clients server does not allow stored procedures.The simple procedures are easy to replace with code, but I've got stuck on a procedure that prepares and executes a statement.The code in the php file that calls the statement is$sql = 'CALL catalog_get_products_in_category(:category_id, :short_product_description_length,:products_per_page, :start_item)';and the stored procedure isPREPARE statement FROM"SELECT p.product_id, p.name,IF(LENGTH(p.description) <= ?,p.description,CONCAT(LEFT(p.description, ?),'...')) AS description,p.price, p.discounted_price, p.thumbnailFROM product pINNER JOIN product_category pcON p.product_id = pc.product_idWHERE pc.category_id = ?ORDER BY p.display DESCLIMIT ?, ?";-- Define query parametersSET @p1 = inShortProductDescriptionLength; SET @p2 = inShortProductDescriptionLength; SET @p3 = inCategoryId;SET @p4 = inStartItem; SET @p5 = inProductsPerPage; -- Execute the statementEXECUTE statement USING @p1, @p2, @p3, @p4, @p5;Can anyone help me please?Thu, 17 May 2012 01:41:35 GMTsimon 99522http://www.sqlservercentral.com/Forums/Topic1301699-1292-1.aspxI have a need to validate that the handful of SQL accounts we allow are being used by non-Windows clients (Windows users/app should all be using AD groups/accounts) as part of a pseudo compliance task.I've found what I assume are the typical places to look for connection, session, users, & login info so I could probably do something based on hostname or IP address but was wondering if there's any information "intrinsic" to the connection that would provide or imply the client's OS platform, tool used in connection (e.g. mgmt studio), or "library" used to compile the code making the connection (e.g. JDBC).Ideas, thoughts, newbie jokes?Thu, 17 May 2012 06:33:54 GMTspencer.campeahttp://www.sqlservercentral.com/Forums/Topic1301495-1292-1.aspxHi, I am in the process of writing a stored procedure that is expected to return thousands of record. Can you please suggest whether the results returned as a list is better in performance or an XML being returned is better? Will we run into any performance issue because db is trying to generate a big xml response?The result currently looks like following in a list[code="plain"]custId prodId ------- -------1 3451 1231 7891 6782 1232 456[/code]XML of following structure is the one I have in mind.[code="plain"]<group> <customers> <custId> 1 </custId> <prods> <prodId> 345 </prodId> <prodId> 123 </prodId> <prodId> 789 </prodId> <prodId> 678 </prodId> </prods> </customers> <customers> <custId> 2 </custId> <prods> <prodId> 123 </prodId> <prodId> 456 </prodId> </prods> </customers></group>[/code]Please help me decide.Thanks,RoshanWed, 16 May 2012 23:49:49 GMTroshan.kurianhttp://www.sqlservercentral.com/Forums/Topic1300898-1292-1.aspxiam using if in SP if (@lused = '') begin @lused = 0 @balleave = Convert.ToInt32(@lused) end slq statement s followsbut i get error incorrect syntax near @lused = 0Wed, 16 May 2012 04:14:22 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1301421-1292-1.aspxHello,I have two tables. Table 1 has patient.ID and patient.lastname. Patient.ID is the unique identifier: each patient is assigned a unique patient.ID. Table 2 has patient.id, test.date, and test.value. Each patient can have more than one test.date and test.value. I want only the first test.date for each unique patient.ID and combine the test information with patient.lastname in the query results. I understand JOIN statements but I am stuck on how to select only the first test.date.I'd truly appreciate any advice!Thanks,DanielleWed, 16 May 2012 16:38:26 GMTdmboyce1http://www.sqlservercentral.com/Forums/Topic1301158-1292-1.aspxI have two tables:[code="sql"]t1 (dow smallint)t2 (custId int, dow smallint, data1 varchar(128), data2 float)[/code]I then insert 7 records into t1, one for each day of the week (1-7).Now, I want to query these tables so that I will always get 7 records from my SELECT statement. Here is the query:[code="sql"]declare @CUSTID intselect @CUSTID = 1select b.custId, a.dow, b.data1, b.data2from t2 b left outer join t1 a on a.dow = b.dowwhere b.custId = @CUSTID[/code]When I have no data in t2, I still want to get 7 rows of NULLS. This query, however, returns nothing. Is there a way I can do this?Wed, 16 May 2012 09:43:52 GMTjames.jensen1350http://www.sqlservercentral.com/Forums/Topic1300777-1292-1.aspxworking with web based application i have a main table with 1000s of recordsuser A logs in & selects few records from main tablesuppose user B also logs in & select another set of records which may or may not contain the records from user Amy query is in a multiuser enviorment when working with data from multiple users creating a temp table or a session table when in user A or B can store their own set of data & work wthwht wuld be the best approch this case.i tried creating a #temptable but the problem is i am not able to use it with reporting servicesTue, 15 May 2012 23:43:48 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1300561-1292-1.aspxHello Masters,During upgradation, all the user database will be upgraded to new version, but what will happen to system databases and Scheduled tasks and jobs? How would we upgrade them to new version? Are they automatically upgraded ?Tue, 15 May 2012 13:42:21 GMTjitendra.padhiyarhttp://www.sqlservercentral.com/Forums/Topic1300548-1292-1.aspxI need to pull info as follows:I have a single table called Agencies and I am trying to pull these columns from it:Select AgencyID, AgencyName,AgencyType, Count of all docs for this AgencyID whereisOpenAllegation= 1, Count of all docs for this AgencyID where WorkType field = "Agreed-upon Procedures"This table contains these fields:AgencyIDWorkTypeGradeReportDueDateReportReceivedDateOtherOtherReasonisAllegationPeriodFinishDateSelection of documents based on these OR conditions:1. Grade = 'D' and PeriodFinishDate within last 3 yearsOR2. ReportReceivedDate > ReportDueDate for 3 consecutive years (use the year portion of ReportDueDate)OR3. Other= -1 and OtherReason="High Profile" and ReportDueDate within last 3 yearsTue, 15 May 2012 13:29:20 GMTpgmr1998http://www.sqlservercentral.com/Forums/Topic1300800-1292-1.aspxHello,I am starting to try and get into learning SSIS and would appreciate some pointers please.I would be grateful if the hierarchy of the following could be explained and how they relate to one another:* Projects* Packages* SolutionsI've seen a few SSIS tutorials, but none of them seem to really start at the beginning as far as I can see and don't explain the relationship between the above or how they should be used.Initially, for the sake of simplicity I have created two SSIS projects. One for each flat file I'm importing into an SQL table. By keeping each of these in a separate project it seems to make thing simpler. However, if I were to want to pull in several flat files into various tables for a given purpose or application, should I place all these packages into a single project?Any guidance or advice welcome.RegardsSteveWed, 16 May 2012 01:11:13 GMTraotorhttp://www.sqlservercentral.com/Forums/Topic1298804-1292-1.aspxNot sure if this is where to ask this or not,In Report Manager, is there a way to search all report Subscriptions by creator?Fri, 11 May 2012 11:47:02 GMTjaredgrywacheskihttp://www.sqlservercentral.com/Forums/Topic1298803-1292-1.aspxI'm converting an Access db to SQL and one of the queries converts the first letter of each word to uppercase using the StrConv function in access. I found that there isn't a corresponding function in SQL. Below is the query which is simple just need to know what to use to make this happenUPDATE Attribute_Data SET Attribute_Data.AttLabel = StrConv([AttLabel],3)WHERE (((Attribute_Data.AttributeNum) Like '%mfg%'))Thanks,NewbieFri, 11 May 2012 11:44:45 GMTtburk 5368http://www.sqlservercentral.com/Forums/Topic1300042-1292-1.aspxI have a secondary log ship database which is always in restoring mode. All the logshipping are running without problems but I have no idea why the database is always on restoring. I have tried re-creating the logshipping and this did not help. All other logshipping database does not have this problem.Any assistance is appreciated.Mon, 14 May 2012 23:34:51 GMTlpsqlctrlhttp://www.sqlservercentral.com/Forums/Topic1296189-1292-1.aspxHi all-I know this is a newbie question but...I am trying to generate a dynamic case statement where each case is related to a look-up table row.I have the following code(that I got help with on this forum) but it is using hard coded case elements. ;WITH EmpGroups AS ( SELECT CASE WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Full-time' then 'Full-time' WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Part-time' then 'Part-time' WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Retired' then 'Retired' WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Student' then 'Student' WHEN test.EmploymentStatus.EmploymentStatusDesc = 'Unemployeed' then 'Unemployeed' ELSE 'Other' END AS EmpGroup FROM test.EmploymentStatus inner join test.employment on test.employment.employmentstatusID = test.employmentstatus.employmentstatusID inner join test.volunteer on test.volunteer.partyid = test.employment.PartyID) In this example the EmploymentStatus.EmploymentStatusDesc field may be one of many employment descriptions. Is it possible to to have the Full-time, Part-time etc dynamically populated with results from a query. eg Select EmploymentStatus.EmploymentStatusDesc from EmploymentStatus?As always thanks for your help.AndyMon, 07 May 2012 20:07:54 GMTandy 56206http://www.sqlservercentral.com/Forums/Topic1298569-1292-1.aspxI want to give a user access to a database. Via SSMS I expand the database, expand securityand right click users. Selecting new user, I click the Login Name and then browse the objects only to see the user is not there. What do I need to do?URLs / comments are appreciated. Thanks.Fri, 11 May 2012 07:12:09 GMTjralston88http://www.sqlservercentral.com/Forums/Topic1298315-1292-1.aspxI have an Access db that an employee created that had some VBA code in it that I need to convert to SQL. Not familiar with VBA and new to SQL. What the code does is replaces ASCII charcters with spaces.Here is a snippet of the code.Function Dead(TextIN As String, Optional NonPrints As Boolean) As StringDim Str As StringStr = Trim(TextIN)If NonPrints ThenDim x As Long' remove all non-printable charactersWhile InStr(Str, vbCrLf) > 0Str = Replace(Str, vbCrLf, " ")WendFor x = 126 To 160While InStr(Str, Chr(x)) > 0Str = Replace(Str, Chr(x), "")WendNext xEnd IfWhile InStr(Str, String(2, " ")) > 0Str = Replace(Str, String(2, " "), " ")WendDead = StrEnd FunctionSo not sure how to look for ASCII characters in SQL and how to do the loop.Any help to get me in the right direction would be appreciated.Thu, 10 May 2012 20:29:05 GMTtburk 5368http://www.sqlservercentral.com/Forums/Topic1299627-1292-1.aspxI read an article that stated that the .mdf and .ldf should be on different disks. Each time I create a new database, I make sure to adhere to this policy. Occassionally, software vendors need to create databases from their software... this takes the location in the Model. I read that it was a Best Practice not to change the location in the Model for the .ldf location. Can anyone verify or deny this? I cannot find the article now to show my Manager.Thank you in advance!CharlieMon, 14 May 2012 09:05:04 GMTrummings