Answer Questions

• Ram Shriram Count(1) vs Count(*) in T-SQL?

  Hi,I have a long-standing doubt about the performance of the count statement. I was told by developers in PL/SQL that using count(1) to find the rowcount was much more high-performing than using count(*), as count(*) fetches all the rows into memory before getting the exact count. I want to know whether this holds true for T-SQL as well, or are the two just the same Thanking in anticipation of reply :) . This is actually a failing of PL/SQL. T-SQL recognises that you're talking about count(*) and will consider any row. The same occurs with WHERE EXISTS (SELECT *... So, in T-SQL use count(*) and let the optimiser do its stuff. In PL/SQL, acknowledge that it's not as good (hehe), and use count(1). Rob ...Show All

• QWERTYtech Insert Date values from SQL Server 2005 to ORacle 10g

  I am trying to insert records with a datetime field type from a SQL Server to an Oracle table using SSIS. Any suggestions to get it to work Do you get an error If so, I would use a more generic format like ISO Date to pump the data through SQL Server IS to Oracle. Look for the Convert method in the BOL and the the setting 112. HTH; Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• JavaBoy Retaining Fields for Multiple Models in Mining Model Prediction Tab Design View

  I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX. I have consulted the following without finding an answer: http://msdn2.microsoft.com/en-us/library/ms178445.aspx http://msdn2.microsoft.com/en-us/library/ms175642.aspx http://msdn2.microsoft.com/en-us/library/ms175678.aspx http://msdn2.microsoft.com/en-us/library/ ...Show All

• Learning VB Memory Usage Grows over time

  I am very new to TSQL and the program that follows loops many times over several hours. In task manager I have noticed that the PF usage has grown from 1.08 to 2.05 over several hours and results in a virtual memory shortage error being displayed. I am inclined to believe I am doing something wrong and accumulating some stack space. Can anyone tell me what I am doing wrong PF does drop back to normal when I drop out of SQL. thanks in advance -Soup- use sm declare @test_date as smalldatetime declare @ticker char ( 6 ) declare @buy_date smalldatetime declare @buy_price smallmoney declare @sell_date_5d smalldatetime declare @sell_price_5d smallmoney declare @sell_date_10d smalldatetim ...Show All

• Niehls I need help creating a procedure

  Please I need some body to guide me here. I dont know how to create a store procedure. If the user wants to delete one transaction I need to do the following to be able to delete the transaction. What I'm trying to do here is update the items to the state that they were before the transaction. What I want to do is something like this, I never have made a store procedure this is just an example to make easy to understand what I need to do. CREATE PROCEDURE `videodb`.`CancelTransaction` (transaction_id INT) BEGIN DECLARE sale_types VARCHAR(25); DECLARE sale_ids INT; DECLARE item_ids INT; DECLARE deposit_ids INT; /*Here I need to select the required fields that are needed to do what’s next*/ SELECT rents.id, rents.item_id, rents.type ...Show All

• nagarajan1948 Wrap Matrix after set number of columns

  I have a matrix that can grow in size from 10 to an infinite number of columns. I want to put a max number of columns to show before the matrix goes to the next line. I thought it would be something simple, but it doesn't appear that way. That or I am missing something completely obvious. Any ideas David, Did you find a way to achieve that functionality. I needed the same functionality and i was not able to wrap the matrix. It would be great if you can help me. Thank you, Raj You need to put the matrix inside a list control and add set the grouping expression on the list to =Ceiling(RowNumber(Nothing)/3) where 3 respresents number after which you w ...Show All

• Stéphane Beauchemin Excel Worksheets Become Corrupt -- Bad Metadata?

  One of my main users has had a few Office 2003 SP2 worksheets go corrupt on him and I can't seem to figure out why. For performance reasons, I recommended that he use page filters whenever possible. As of late, he'll send a workbook along that has a few page filters (between 3 and 4) and one of the page filters gets mixed up somehow. For example, let's say he has a Product Line page filter and a Product Name page filter. The Product Line page filter has multiple selections enabled and he will go ahead and select a few product lines and save the file so he can simply refresh the data in the future. Eventually, the Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable, usually resulting in a ...Show All

• LennardF1989 Identity field settings not copied by Import Wizard (2005)

  It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column [ref] [int] IDENTITY ( 1 , 1 ) NOT NULL, When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be [ref] [int] NOT NULL, instead. Is there a way to change this behavior somewhere in the gui When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column. -Eric Did you find any way to copy the identity columns from Sql 2 ...Show All

• dindelus Management Studio Express breaks database connectivity

  Hi, I'm using SQL Express to run DotNetNuke on my own Windows 2003 server. DotNetNuke(DNN) copies a database on to the server from it's installation files and during the installtion process it fills the database with starter data. That process works fine. But the minute I attach to the database in SQL Server Management Studio Express (MSE), my web app loses connectivity to the database. When browsing to the default.aspx, I see the following message: Error Installing DotNetNuke Current Assembly Version: 04.03.05 ERROR: Could not connect to database specified in connectionString for SqlDataProvider __________________________________________________________________ (don't be fooled by the error message, Dot Net Nuke was fu ...Show All

• Ahmed Shahin Derived Column

  I have two columns made up of 4 digits numbers eg col1 1234, col2 9876 I want to create a derived column so i get a eight digit column, eg col3 12349876 I cannot seem to get the expression right, I seem to be always adding the value which i do not want, can someone help me out with the expression Thanks Robbie test agauin Rafael Salas wrote: Please mark the thread as answered... thanks This is done how Rafael Salas wrote: I may be wrong...but that expression will keep leading zeros for col2 only...what if you want to keep the leading zeros of col1 You'll lose them. That's why I lik ...Show All

• Nigel36 SSL To SQL Server 2000 From VB6 : Error

  Hi All, I am trying to connect to SQL Server 2000 database from my VB6 code. The SQL Server is having the setting ON to force all incoming connections to use SSL. It is using a valid certificate with the same name as FQDN of the server and has been provided by the security department in my organization. I have got my client code on my desktop. I have installed the root CA certificate for the SSL certificate on DB server in my desktop's Trusted Root Certificates. When I try to access the database from my .NET 2.0 code, it works fine. The .NET code gives an error if I remove the CA certificate from my desktop. This behaviour is as expected. I am using Encrypt=True setting in my code here although I dont think it is necessary when se ...Show All

• AlucardHellSing Capturing Previous Value

  When updating the value of a field in a table, is it possible to capture -- either for insertion in another table or as a variable -- the previous value of the field Thus, if a field value is 'Value1' and a user updates it to 'Value2' is there some way to hold 'Value1' as a variable Lynn Trapp Hi Adam, Thanks for that example. It looks rather promising. I have the odd situation of needing to keeping 2 tables in separate systems in synch. Thus, if a user changes a value in one of the tables (and they only have access to a couple of fields), then I need to be able to programatically identify and update the corresponding record in the other table (thus the need for the original value in the first ...Show All

• Feng26 Calculated fields on olap pivottable in excel 2007

  Is there a way to add calculated fields in excel 2007 to a pivot table, connected to a SSAS cube Meaning that is calculated on the fly automatically while drilling up/down... I do not think so. The formulas button is disabled. Formulas have to be server side MDX calculated members. Same in Excel 2003 You will also note some other diffences like Grouping may require security access to cube metadata in order to work. Philippe This is quite a disadvantage! Hopefully the kpi wizard and the set selector of ProClarity will be integrated into excel! ...Show All

• Petknep Database.EnumObjectPermissions throws InvalidCastException

  Hi, I am trying to run the EnumObjectPermissions method on the pubs database in SQL Server 2000. When it tries to enumerate the permissions for the public role it throws a InvalidCastException saying that you cannot cast a System.DBNull to System.String I know that its not my code so I am unsure what to do to fix this issue. The code runs fine against other databases. James I opened a case on this issue and it has been logged as a bug. The fix may be included in SQL 2005 SP2 or may be released as a hotfix I'm able to reproduce the InvalidCastException error on any SQL Server 2000 database by granting a "CREATE" database level permissions. By default t ...Show All

• Amos Soma How do I move my database?

  I am using VWD 2005 Express, with SQL 2005 Express. My web hosting company supports ASP 2.0 and SQL Server 2005, but have put SQL on a database server, which is separate from the application server. I successfully published a "Hello world" test application, but my database application failed after displaying the logon page, presumably when it first tried to connect to the database. VWD has created my database within the application folder ( app \App_Data\ASPNETDB.MDF) and created the configuration string in web.config: - < add name = " ConnectionString " connectionString = " Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True " pr ...Show All