Answer Questions

• chris_dev Text box Expression and Text

  I have an issue where I do the following with a text box "This account has" & "expression" & "in it" The "expression" is a stored procedure that generates a balance The problem I run into is I cant get the expression to show up as currency. I have even tried doing the currency format on the sql side, and it keeps showing the amount as a non currency amount. I have tried applying the the currency type to the text box, and still doesnt have an affect on the amount that shows up. I use the following: ="*Of which" & Format(Sum(Fields!ID.Value, "Footnoteall"),"c") & "is coded as "00 no applicable SIC Code" I than get the follo ...Show All

• Ben Amada How to identify Memory Leak caused by bad written stored procedures?

  Does anyone has experience in monitoring Sql server memory leak I am suspicious of some of the user stored procedures causing the memory leak. Can anyone who has such experience explaine how to find the offened stored procedures Thank you very much for any kind of suggestions and recommendations! Unless you're talking about extended stored procedures, then stored procs themselves don't have memory leaks as such (like normal code), however, they may be doing things inefficiently, or you may have missing table indexes. The best way to start is to use the SQL Profiler tool to monitor stored procedure execution and look at the duration field. If you capture results to a table, you can then select the slowest queries by or ...Show All

• Suresh .M.V How to get records (In this week) only?

  Hi, How to get records from MSSQL database which only recorded from the biging of this week in example my week begin from Sunday.. Thanks Jens K. Suessmeyer, it worked :) Hi, SELECT * FROM SomeTable WHERE DATENAME ( wk , SomeDateColumn ) = DATENAME ( wk , GETDATE ()) AND YEAR ( SomeDateColumn ) = YEAR ( GETDATE ()) HTH; Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Weeknumbers are probably the trickiest part of a date. While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weekn ...Show All

• Pockey Insert to a Linked Server possible via Service Broker?

  I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log: The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.' As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server CREATE PROC sp_mytableS ...Show All

• Dan Cremer Referencing a PK from another database!

  Hi all, I have a recruitment site which is part of a large portal which has other services, so the whole portal has its own database apart from the recruitment's . and there's a couple of other databases. Now I want to authenticate the user once I mean if this user(JobSeeker) had already been logged in from somewhere on the portal modules, I don't want to ask him again to sign in to search jobs or modify his resume. Now, with that said I was wondering can I create a FK say UserID in the resumes table in the (Recruitment) database whose value is the PK of the Users table of the (portal) database . So, is that possible I mean referencing a PK from a table in another database If not, what is the best approac ...Show All

• Greenstrike ReportManager - new "Object reference not set to an instance of an object."-error

  Hi, Im having a bit of a problem with reporting services. First of all, Im running three virtual servers. "MyApp", "ReportServer" and "ReportManager". In order to have a single sign-on experience, I have authentication configured to Forms in all three web.config-files with the same cookie-name and so forth. I also added a MachineKey to all three web.config files to share the authentication for the virtual directories. (and yes, I use the same machineKey for all three apps) To login, a user can enter "/MyApp", "/ReportServer", or "/ReportManager" and then I get redirected to "/MyApp/login.aspx" (as that is the setting in the web.config-files). --------------- ...Show All

• dgracey Log files must be named *_log.ldf?

  Hello, I've been having problems with SQL Express when my database files are named basename.mdf and basename.ldf (e.g. on using the Access upsizing wizard). At points that I haven't fully identified yet (I'm using VB Express clients), a second log file named basename_log.mdf gets created and SQL Express gets confused and compalins the files are corrupted. Can anyone explain what is happening TIA, Bill Hello Jens. My problem is not with the ldf extension but with a second transaction log file getting created. As an example: 1) Create a new Access database named myDatabase.mdb (containing a single table.) 2) Use the Upsizing wizard to create a new SQL datab ...Show All

• Troy Lundin Copying a Datbase between two different servers

  Hello Everyone, I am new to SQl Server 2005. I have created a database on one machine and I tried copying it on to another machine where SQL Server 2005 is installed. Both machines have SQL Server 2005 installed. When I tried copying using copy database wizard its asking for the destination server. I have given the name of the destination server but its giving me error msg saying tht it cant connect. How to chk whether my destination server is online If it is not how to make it online Any help would be appreciated. Srividya. Remote connections are disabled by default for SQL Server. YOu will have to enable them first. For more information see my screencast on my site which covers th ...Show All

• Bharat Gadhia I have same datasource name for all sites, but each datasource points to a different DB

  I have multiple reportprojects on server each report project belongs to a different client. the problem i am having is i am using the same name datasource in all different projects but they all poitn to different databases on sql server. when i am deploying the report project that time it is not uploading the datasource info, on the report server, since all datasources will bemaintained in the same folder on reportserver, thast why it seems it is not uploading the datasource info at all, since it already found the same datasource name on reportserver. I am maintaining the same datasource names thorugh put all report projects, is it possible to keep the datasource info in the project folder instead of datasource folder on reportser ...Show All

• Peter paterson SQL's america date format conflict with australian date format

  Hi I am trying get my VB6 application to insert a record into a table (SQL Express) which has a datetime column but it would not process if the data format is differ to *American Date format*. The date() function in VB returns 15/11/2006 which is in Australian Date format (DD/MM/YYYY) according to my setting in "Reginal and Lanuage Option-> Locale 0> English (Australia)" setting. I get the following error: Msg 242, Level 16, State 3, Server KIT\SQLEXPRESS, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. My computer's locale is set to English (Australia) and I expect the datetime format would follow what is set ...Show All

• dustinto Non-browsable custom properties

  Does anyone happen to know if it is possible to set a custom data flow component property to be non-browsable I have a number of custom component properties, and would prefer that they only be updateable through my custom UI as opposed to via the property grid on the SSIS designer, thanks Sure, use the [Browsable(false)] attribute on the property. Kirk Haselden Author "SQL Server Integration Services" This would apply to a task only. It doesn't work for a pipeline component, since properties are not defined as regular properties on a class, so unfortunately cannot be attributed as normal properties are. I cannot see a way to apply such attributes/settings to custom com ...Show All

• Kanagal Raj Calc Measure - aggregation behaviour differs between cube and mdx

  Howdy, I have a calculated measure for a division that is calculated something like this (simplified): MEMBER [Measures].Grade AS [Measures].Metal / [Measures].Tons This measure is persisted in the cube. Normally, the measure correctly aggregates (ie shows Sum(Metal) / Sum(Tons)), but when I put it in MDX as follows, it incorrectly shows Sum(Metal / Tons): WITH MEMBER [Date].Calendar.YTD AS SUM (YTD( [Date].[Calendar].[Date].&[2006-03-26T00:00:00] ) SELECT [Date].Calendar.YTD ON COLUMNS, [Measures].Grade ON ROWS FROM [TheCube] I assumed that this behaviour was because I was aggregating the measure via the SUM() and YTD() functions, but when I take this measure out of the cube and place it in ...Show All

• barkingdog Set Foreign Key Checks off in an insert script

  I would like to insert data (via script) in tables without any foreign key checks. In MySQL the corresponding command is: SET FOREIGN_KEY_CHECKS = 0; [insert queries] Is there any equivalent in Transact-SQL respective command of SQL Server 2005 database engine Best regards, Wolfang Dear Adam, thank you very much for the provided solution. I am looking for a way to not alter all table with the 'alter table ... nocheck contraint' and afterwards to set back to check constraint table by table. In MySQL you can set the foreign key checks off for a specific script without alter each table, e.g.: --Create Table script use test; create table x (id int not null primary key); create table ...Show All

• loonysan Setting width of parameter text box?

  Is there a way to set the width of the parameter text boxes in the Report Server UI As luck would have it, I need it to be just a few characters wider. Thank you, Dick Campbell You might find this helpful: http://msdn2.microsoft.com/en-us/library/ms345247.aspx It's not a slam-dunk, but hopefully you can use this to gain some control. That was my problem too once up on a time, however, I couldn't find any way to edit the parameters propery, since they are created automatically by SSRS. --Amde Thanks Mike, That's correct, I am referring to the parameters at the top of the screen when you run the reports. It l ...Show All

• bluestar Microsoft.ReportingServices.Designer

  Hello all, After reinstalling several times the MSSQL 2005 express, and the SP1 when I'm trying to create a project with the BIDS I've got an error: Could not load file or assembly Microsoft.ReportingServices.Designer Version=9.0.242.0 .. or one of its dependencies. System cannot find the file. But it exists!!!What can I do Many thx! Reporting services installs in C drive but you can install VS2005 in D drive but the .NET framework, Enterprise library and other development components also require C drive. Hope this helps. I installed Visual Studio 2005 on my D:\ drive and indicated that Reporting Services should be installed on D:\ as well except the installer places some of the files on the C:\ drive anyway. I expect ...Show All