Answer Questions

• Pavan Apuroop creating a SP that add columns into a table

  Hi all, i want to create a stored procedure that will add columns into a table but i have a problem using a parameter in this context, for example: ---- SP ----- -- PARAMS -- declare @TableName nvarchar(30) declare @colName nvarchar(30) declare @colType nvarChar(30) -- Actual Code --- alter table @TableName add @colName @colType it looks as though the paramater cannot be used in this context or i should do something in order to make sense of this code.. please help me :) Z You have to use Dynamic SQL here.. ---- SP ----- -- PARAMS -- declare @TableName nvarchar(30) declare @colName nvarchar(30) declare @colType nvarChar(30) -- Actual Code --- Declare @SQL as Varchar(1000); ...Show All

• Bisjom @@TRANCOUNT - always 0 or 1 ?..

  I am trying to debug our production problem, and I'm stuck... A C# .NET 1.1 method creates a SqlTransaction object on a SqlConnection. [This method is called from an ASP.NET 1.1 web application.] The command invokes a stored procedure on SQL Server 2000 SP4 database. The stored procedure itself is inside a transaction (that is, all batch code is between BEGIN TRANSACTION and COMMIT/ROLLBACK...). DECLARE @a_tran_count INT   SET @a_tran_count = @@TRANCOUNT DECLARE @a_tran_name CHAR(13) SET @a_tran_name = CONVERT(CHAR(13), @a_tran_count)   BEGIN TRANSACTION   DECLARE @a_error INT   <Statements> SET @a_error = @@ERROR IF 0 = @a_error <Statements> ...Show All

• Sarguna disk fragmentation

  I have a Dell PowerEdge 2850 with Windows 2003 server. 2 disk drives with RAID. My databases are fragmented on the drive and I need to defrag the drive at least once a week. My database files are fragmented. Defragger cleans up the files but as soon as I start running processes again, the files fragment and system response time suffers. What can I do to keep the files from fragmenting Question : does file fragmentation really impact the performance of a DB I mean : by construction, the data in files are fragmented, and access to data in DB always require a lot of disk seek. Sequential access through files should be marginal. So, fragmentation of the whole file should not imply a big performance impact. Am I correct ...Show All

• Gadi Sagiv Mail Task in SSIS

  Please help me i am new to ssis ....I am trying to send an email using mail task and I am sure that ISP works fine with smtp. I am entering all the correct information in the task but it takes a long time to run the task and then comes back with a failure error. can someone help me find an easy way to send an email to group of emails...I even tried to send it to 1 email and it is still not working. What error message do you get use outlook client on the same machine (as SQL box) and see if you have the same problem, if the you have the same issue using outlook then this an exchange issue, if outlook can send it OK, then you have the investigate the problem on SQL. you also can set up DatabaseMai ...Show All

• PatrickR SSIS Newbie - ETL question....

  I am tasked with loading the data from one SQL Server 2005 database into another of the same type and schema on the same server. I'd like to use this task to get my feet wet in terms of using SSIS. Can someone suggest a way of doing this Thanks in advance Loopsludge wrote: Yes Jamie. That's correct. I don't want to use the IDs from the source DB because they are typically autogenerated and most likely will not coorespond to the new autogenerated IDs as they get entered, yet I have to maintain the link relationship. Am I making sence No. Because in one breath you say you want the foreign keys coming over (i.e. schema objects) and in the next you say you are interested in data. I think its ...Show All

• BlitzTitan Where is Query Analyzer in SQL Express?

  I know that SQL Express includes a similar tool like Query Analyzer in the SQL Management Tool but I just can't find it. Would anyone kindly point it out please When you open Management Studio Express you can create a New Query a number of different ways. New Query is akin to Query Analyzer. Regards, Mike Wachal SQL Express team ---- Mark the best posts as Answers! thanks for the reply ;-) ...Show All

• Rozee Linked Objects - SSAS 2005

  Can I add the data of several cubes in one main cube May be with linked objects Example: Sales data for company with one headquarter (main factory) and 2 foreign factories. I can build a single cube with all the data and having an entity (or factory) dimension to recognize the data source and define roles on that dimension to grant access to the data. Can I also build 3 cubes and link the 2 foreign factory cubes to the main factory cube The structure of all cubes are almost identical. I wanna just add the content (dimension data and fact data) to the existing main factory cube. The underlying idea is to use the foreign cubes twice. As local cubes at the foreign factories (because of bad internet connection) and as source cubes ...Show All

• JerryCic Data Flow inside For Each loop - error handling

  Hopefully this is an easy question: Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the ...Show All

• J_Dude2003 join several fields to create new field

  What would be the recommendation/approach in creating a seperate field in which joins several differate fields together. I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d. So instead of: a 122 b joe c st I would have: d 122 joe st Thanks! I've worked with the view and it is working well enough to return valid results. I can't use it for the application however. I need to actually store the data in a seperate field. If there are thoughts on how to make this happen, I would appreciate it. The problems I see are: the new field must have spaces - example 122 joe st there may ...Show All

• Keith Lee TimeSeries forecast period

  Using the TimeSeries algorithm, how do I forecast more than one time period ahead I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces Thanks! If you need to build such query using graphic interface, set "Source" to "Prediction Function", set "Field" to "PredictTimeSeries", set "Criteria/Argument" to "Bread, 5" ...Show All

• Jonathan Sampson Unable to install full text search

  After installing SQL Express via the executable provided by MS successfully, I tried to go back and add full text search as well as replication. After going through the steps of add/remove - change ..... and being able to select both features after I hit next it then gives me an error trying to find use source: e:\b6f60166c6128881c04ed9e6c64e75\Setup\sqlrun_sql.msi no big deal if I had the .msi but since it was installed via the .exe I don't have that file. I also did a search on all drives for that file. I also tried just selecting the full text search option. I looked on microsoft.com to see if I could search for the .msi there but no luck. Any advice/ideas would be appreciated! Thanks, Ryan ...Show All

• Ian Vink Increase Tooltip display time?

  Hi All, Is it possible to increase the amount of time a tooltip is displayed within report manager Regards, Jon No I don’t think that this is customizable within the report. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Hi Ki, In the properties box all objects, in the "General" tab is an option for "Tooltip". You can put an expression in here to define a tooltip. Regards, Jon There is a setting for controls naming tooltip which can define any tooltip displayed if you hover over the specified item. HTH, jens Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• Sean Connolly Replication Distributor Job History is shocking!

  Sorry for the odd format but that is what happens when you paste here! This is data from our Job History for a "Replication Distributor" Job Step Type. Here is how I interprete it: The job step started on the 19th. It restarted itself twice on the 20th. Then it restarted itself twice on the 21st. Then it restarted itself 3 times on the 22nd. And finally it restarted itself on the 23rd. Notice the progression of Retries_Attempted. When Retries_Attempted exceeds 10, the step will fail and the service will shut-down, which is what I have seen in the past. I conclude the Advanced Settings of Retry Attempts is not per "Event" but "total tries no matter how many successful retries are encountered." I thought a s ...Show All

• i_dont_care Inconsistent performance from queues

  Hi everyone! I have a very brief question... I have 10 queues in my database and each of them are sent equal number of messages... There are instances where they execute/activate the stored procedures very fast but there are times where they don't, does anyone have an idea why this happens Thank you very much for taking the time to read my post. :) Is it also not the case that the rollback will result in the queue being disabled due to poison message protection. it should really commit if no conversation group is found. Please, your situatation would indicate a designe feature and performance issue with your code on an issue witht eh SB code Hi guys, I've changed my code for RECEIVE into ...Show All

• ThomasA Migrate 2000 -> 2005

  I've got the responsibility over a small SQL server with 8 Database and approx. 100GByte data online. Most database are used and need to be available 24*7*366. Also a number of local data transformation packages are defined and used on a regular basis. Furthermore a number of third party applications rely on this database. My question if it is worth the effort to migrate this to 2005 I know there are all kinds of nice migration wizards but do they really work What if one of the 3rd party apps don't like 2005 Love to hear both good and bad experiences migrating -> 2005 . Thanks Chen, did not know 2000 and 2005 can run side by side but this will greatly increase the possibility of a migration. I ...Show All