In my ASP.Net, for every SqlDataSource instance, I end up duplicating the following ASPX lineConnectionString="<%$ ConnectionStrings:MyDb %> Is-it possible to define a default ConnectionString at a website-wide level? Thanks in advance,Joannèshttp://www.peoplewords.com
I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.
So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.
SELECT measures.turnover ON COLUMNS,
nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS
FROM [Itdev1 Hk]
I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.
Now, how do I get it to default to the last valid member in the list?
Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example: ReportParm1 String [Deal Dim].[Shelf].&[AAM] But, how would i define it with multiple values as a 'default value' ?, for example: ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF] NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space. Any help would be greatly appreciated. thank you.
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
I have 595 default constraints in my database. I can return a list of them using the following:
select * from sys.default_constraints
Is there a way I can return a list of just the ones where NULL is still allowed? I want to update all of the columns with a default value to not allow NULLs.
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change DECLARE@size varchar(5)--The new size of the column DECLARE @TSQL varchar(255)--Contains the code to be executed DECLARE @Object varchar(50)--Holds the name of the table DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10) ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
I have a table that has a date field that can't be null. When a new record is created, if a date is not passed in, I want the default to be the current date. How can I set that? I tried Now, Today, GetDate, etc. Diane
I have a table with the list of employee and 15 column with data type float or money , that represent the number of hours, airfare,gas,parking food, etc....
I have 2 choice: -Put 0 as a default value for each column, like that I do not have to use coalesce when I do SUM for each column. -Leave the default value null but I will have to use coalesce.
In term of performance, what is the best solution?
I have defined 8 varchar columns as not null with default, i.e.
[Namad8] [varchar](40) COLLATE Latin1_General_CI_AS NOT NULL DEFAULT (' '),
I run a script transformation (Flat file source, OLE DB dest) that splits a crlf delimited field an array, and then loads each array element into one of the not null table columns as above. However there may be less than 8 elements present in the input field, which means the remaining columns are not populated.
When I run the data flow task, I get the error "
Error: 0xC020901C at Capture Assessments, OLE DB Destination 1 [1]: There was an error with input column "Namad8" (24) on input "OLE DB Destination Input" (14). The column status returned was: "The value violated the integrity constraints for the column.".
I know I'm trying to insert a null value into the column, but my understanding was that SQL would default the column to spaces. If I define the column to allow nulls, the data flow works fine.
I am using sql as a back-end server. My application requires to insert a default string values when the user do not provide any input or if the inpur is NULL. I have used the following code, but the defaul values are not inserted. I am not getting the desired result.
CREATE TABLE Employees1 (
EmployeeName varchar(50),
State char(3) NOT NULL DEFAULT 'NSW' ,
PostalCode int default 2000,
Country varchar(32) NOT NULL default 'Australia');
how can you alter a table so that you can add a default value whenever the field is NULL? for example, whenever the table is brought up in a query, NULL is replaced with UNKNOWN
I have a Nullable columns. I want the value to be zero if NULL is passed.
1) having a view for every such table, 2) using ISNULL for needed columns in that view 3) using those view for inserts instead of using tables directly.
This way i can reduce the number of places to use ISNULL in all my SPs.I dont want to make it to NOT NULL, as my application impact will be high. And Default constraint does not work here.
Hi,I am using SQL Server 2005 32 bit version. Could any one clarify methe caption database option with some examples? I am quite confusedwith the explanation given in MSDN. Kindly help me.Thanks in Advance.Om Prakash
I have a datatable : Data_Table and a look up table: Lk_table. Myfield that I use in Inner Join is defined in both the data and look table.
So I build my query like this:
SELECT * FROM dbo. Data_Table INNER JOIN
dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield
The pb, sometimes I have myfield still with its default null value in the datatable: Data_Table. So, I end up getting 0 record when I execute the query shown above.
How do I turn that around so that even if myfield in Data_Table is Null, I still get the records from Data_Table. (I don t want a set of records including all possible values from the look up table: Lk_Table)
I have a stored procedure with a SELECT statement, that retrieves 1 row. SELECT name FROM tblNames WHERE nameID = "1" I want all the NULL values in that row to be change in some default values. How do I do this?
What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).
Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?
I have the following problem: I'm connected to flat file source and trying to read data that is later inserted in an MS Access database. Everything wokrs fine instead of one thing - when I have null values in the flat file, I want those NULL values to be inserted in the MS Access db, instead of that what happens is that I actually get the default values for a column type from the flat file and later insert that defalut value. For example if I have a null value in an four-bite-signed-integer column of the flat file, I get 0 as value.
I thouth of solution using a "Derived Column" transformation which can transform the zeros into nulls, but decided to check with you guys if there is a smarter thing to do (for example to edit the flat file source to read the NULLs correctly).
I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).
I used datetime parameter
@daTo (to have the calendar control for selecting date) and check on "Allow Null" value. Here is default Value.
I've also tried to set DefaultValue expression was "=Nothing". Â
IN my query of data set, here is the part I use the parameter @daTo  ... Where (@daTo IS NULL OR TRUNC(@daTo) >= TRUNC(pe.start_time) ....
But when I run the report, I got below error:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
I think it @daTo value actually not null so it try to TRunc the value
I don't know how to set null value default in my report.
I'm trying to have a default or null value in the dropdown list of the parameters on SSRS report. The dataset is bound with the Dynamics-AX 2009 AOT query. In the screen shot below you can see that I need a show All option in the dropdown list.
I have a data transform from a flat-file to a SQL server database. Some of the flat-file fields have NULL values. The SQL table I'm importing into does not allow NULL values in any field, but each field has a Default value specified.
I need to have it so that if a null value comes across in a field using the data transform, it takes the table default on import. I could of sworn I had this working a few days ago, but I get errors now that state I'm violating table constraints. Has anyone done this before?
In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables.
The table may contain column that are NULLable BUT there is NO DEFAULT for them.
If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string?
I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way:
1- set the DEFAULT(NULL) for EVERY column that needs this behaviour
2-set up some Derived Column option in the package to return NULL if the value is missing.
Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns.
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
I have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions. Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this. with CustomGroup( id) as ( Select id from Groups g Inner join GroupStatus s on s.Id = g.Id Where s.Status = €˜Active€™ )
IF @DisplyStatus=€?UserDetail€? begin <!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here. select u.* from users u inner join userGroups ug on ug.UserId = u.Id inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE end else if @DisplyStatus=€?UserSimple€? begin <!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here. select u.FirstName,u.LastName,u.DOB from users u inner join userGroups ug on ug.UserId = u.Id inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE end else if @DisplyStatus=€?UserWithAddress€? begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe