Answer Questions

• Chen Hao automated replication scripting

  We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach. You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx ). Other replica ...Show All

• FredHeida how to limit number of rows showing each page?

  Hi All, i couldn't find how to set up the number of rows displaying on each page i wanna each page display 20 rows. Thanks Nick There's no explicit way of doing it, but it can be achieved by placing your table inside a list control then add a grouping with the folowing expression: = Ceiling(RowNumber(Nothing)/20) Make sure you check the page break at end option. Cool.. I got it.. thank you very much,, Adam ...Show All

• newyuppie Data Conversion Numeric to date

  I have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!) I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect. However I cannot seem to be able to convert and store (in SQL) this column can anyone advise Thanks Blackuke wrote: Hi Jamie It worked great until I put in the historical data for data that was like 61103 (YMMDD) the expression worked great I got 2006/11/03 however when I got data from 2000 it was like this, 110 (M/DD) (eg 2006/01/10) this br ...Show All

• Chris.Stewart howto open tunnel connection

  Hi, I'm connected to a remote server via a ssh tunnel. On the server there is an SQL Server running. The remote SQLServer TCP port 1433 is forwarded to local computer to port 8966. So I have to connect to localhost:8966 instead of remote:1433. In the connect dialog I can specify only protocol but no ip address neither tcp port number How can I specify the localhost:8966 in the SQL Server Management Studio Express as the endpoint to connect to Open SQL Server Configuration manager and set your values under SQL Server Network configurations options. There you can specify the port number to connect to by default. Hope it helps. you havent understood me I dont want to reconfigure the sql server. I'd only want to tell the mana ...Show All

• Spanglishone log_reuse_wait setting

  Hi All I changed a database from replication to non-replicationg, but sys.databases stil shows log_reuse_wait column =6 (REPLICATION). Consequently the log file keeps growing and am not able to shrink it. Any body know how to fix this Thanks K T Hi Greg Using the Man. Studio. First dropeed subscriptions Dropped Publications Then disabled database for publication When I run exec sp_dboption 'dbname', 'published' returns published OFF Thanks K T The DB is in simple mode. I dont see any log running transaction I also did do sp_removedbreplication. dbcc open tran (see below) seems to indicate that some how the db is still marked for replication ! sys.datbases also sh ...Show All

• Horst Klein sqlspec documentation

  HI Guys i got the trail version of sqlspec software 3.1 from elsasoft using this software i can generate document and save in folder on network drives but i cannot open it but here is a tricky i can see console but when i click on tables it says page cannot be displayed what might be the reason or problem .if i save this on my local disk i can open it. any kind of suggestions are appriciated. Hmmm... I'm not familiar with the software you're using. Does it create the chm file that you can't open Thanks, Sam Lester (MSFT) Actually i got rhe following error when i try to open file (.chm) Action canceled Internet Explorer was unable to link to the Web page you requested. The page mi ...Show All

• mabster SQL express database + IIS

  hi all, i have implemented a program, that is divided into two parts, a window form and a website.  they both use the same database(SQL express). (the website is not meant to be published online, but on a local host) Everything works great, when i am in the VS enviroments. but when i publish the website the following happens: if i use the website first and login, thus accessing the database, even if i close my explorer , i can not use the window form (crash when trying to access the database). And if i try it the other way arround, use the window form first, and then try to use the website (while window form is still open only, and has already accessed the database) it crashes also.  In both cases ...Show All

• John Simpson MAI CLR vs T-SQL

  Hi, I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great) It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries Thanks, Bahadir Hi Bhadir, You get some insight from the whitepaper that we published on CLR integration---you can find it here . The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but the ...Show All

• MikeBzz Help Needed in writing a function to help daily nightmare with Debugging.

  I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is. Once I have established which SP is the main problem I need to debug the line of code. What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more.. Now what i thought is to write an SP or Function where I pass : SP name and Parameters that profiler genererates and returns me Declare Statements and Set Statements with parameters filled. EG Profiler Returns Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD' I would call my new SP =PopulateSPParams and cut and paste the profiler's string PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD' this will RETURN THE FOLLOW ...Show All

• Yngwie 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 ... How is this Day columnn populated Is it a surrogate key or is it a date format like 20070214 You say you want to delete the duplicates in the destination. And then insert them again Why not skip the records that are already there Pipo1 How would I actually remove /delete the dupli ...Show All

• tviel WHERE clause

  Hello. I have a database that a client set up that's kind of a mess, but I need to pull records from it. Is it possible to use a WHERE clause with an array The SQL I am getting is really messy, kind of like SELECT Title FROM Master WHERE Cat1 = leg OR comm OR err OR and so on... OR Cat2 = leg OR comm OR err OR and so on... there are multiple categories for each record in the table, with subcategories under main categories. But he only has one record in the table with all of these categories specified. Any help is much appreciated. I tried that and it didn't pull up any records at all. I didn't get any syntax errors, it just didn't work. Here is the code: SelectCommand ="SELECT [Title] FROM ...Show All

• JohnHarding How to sort a table with a parameter

  I have a "Sales shipments" report with several colums. I want a parameter for the user so he choose how to sort. I have two colums in my report I want to sort on: ItemNumber and DeliveryDate. I added a Report Parameter UserSort with two labels (and values): Item (ItemNumer) and Date (DeliveryDate). Then I tried to set the sorting The walktrough doesn`t work. When I set the table sorting on the itemnumber "=Fields!No_.Value" it works, but when I set the parametered value "=Parameters!Sort.Value" it doesn't work. I will try the CASE statement... I tried with the CASE statement but no result. Maybe I didn't use the right code. Fo ...Show All

• mustangBE The media set has 2 media families but only 1 are provided. All members must be provided.

  I tried to restore database using following sql command RESTORE Database RALMYT FROM DISK = 'G:\RALMYT\RALMYT_20060523_3.3.0.0.42_stripped.BAK' but i got this error Msg 3132, Level 16, State 1, Line 1 The media set has 2 media families but only 1 are provided. All members must be provided. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. when i run the RESTORE FILELISTONLY FROM DISK = 'G:\RALMYT\RALMYT_20060523_3.3.0.0.42_stripped.BAK' command i can see these data and log files RALMYT_Data RALMYT_Data_02 RALMYT_Data_03 RALMYT_Data_04 RALMYT_Log RALMYT_Log_1 what would be the error please help me I am having the same problem restorin ...Show All

• Chirag vm how to extract duplicate record using ssis

  ex. from source the file you want to split the record into two, the one with a clean record and the other one with duplicate records Again Thanks for the reply Hi There, I would like to suggest you to pull the data into some temporary table from the flat file first, and then from that table you can separate the data which is clean and duplicate You can write a sql command something like : "select columna,columnb from <tablename> group by columna,columnb having count(*) > 1" .This query will give you the duplicate records. In this way you can load the clean and the duplicate records to different tables by using a conditional split transformation ...Show All

• laurin1 get records after executing a stored procedure

  Hi All, I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters. EXEC [dbo].[ETLloadGROUPS] @countRun = , @startTime = , @endTime = This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped. When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine. Am I missing something here Please Advice Thanks in Advance Tom, Thanks for the quick response. but guess am into a soup here... I have done the following in the ...Show All