How To Modify Default Setting?
May 16, 2008
Hi,
I have few question:
1)Is it any methot to modify lock_timeout default value? (Default value is -1, if i want to modify default value for example 1, i try [SET lock_timeout 1;] but it only work during that session / connection)
2)Is it any method to modifyTransaction Isolation Level default value? (Default value is READ COMMITTED, if i want to modify default value for example READ UNCOMMITTED, I try [SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;], but it only work during that session / connection)
Thank you!~
View 2 Replies
Mar 14, 2008
Hi All.,
here i try to set default value 0 while creating table., But in my code its not working.,any one please help me to know how to set a default value while creating table.,
here is the sample code.,
CREATE TABLE MODULE_ROLE
(
MODULE_ROLE_IDINT IDENTITY(1,1) NOT NULL,
ADD_FLAGBIT default 0,
UPDATE_FLAGBIT default 0,
BIT default 0,
VIEW_FLAGBIT default 0,
INPUT_USERINT
)
insert into MODULE_ROLE values (null,null,1,null,1)
select * from module_role
----------------------------------------------------------------------
MODULE_ROLE_ID ADD_FLAG UPDATE_FLAG DELETE_FLAG VIEW_FLAG INPUT_USER
-------------- -------- ----------- ----------- --------- -----------
1 NULL NULL 1 NULL 1
BUT I NEED 0 FOR NULL VALUES..,
View 1 Replies
View Related
Nov 28, 2007
I've been reading some forums and I'm not able to get this to work. Basically I'm using an Oracle DB source and trying to import data into SQL server 2005. I guess the best connections to use are OLE DB.
Here are my current connections:
Source: Native OLE DBOracle Provide for OLE DB
Destination: Native OLE DBSQL Native Client
I'm running SP2 of SQL 2005.
Now, the issue is I'm not getting the code page stuff correct. In the data flow I'm just doing a OLE DB Source to SQl Server Destination. I'm not doing any transformations (I'm hoping to avoid doing that).
I'm getting an error:
[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
I'm looking at how to set the DefaultCodePage property in various forums, but nowhere does it say exactly how to do this. I've tried changing the "Extended Properties" and added AlwaysUseDefaultCodePage=TRUE in there, but that doesn't work. I've tried changing the Locale Identifier to 1252 that doesn't work. I've tried a combination of the two, doesn't work. Can somebody tell me two things:
1. what's the best/fastest/industry used method to get data from Oracle into SQL server via SSIS (please include the type of connections).
2. How the hell do you set the DefaultCodePage property. I would love a screenshot on this one as well.
Thanks,
Phil
View 3 Replies
View Related
Oct 23, 2005
Hello,
I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5).
So basically the SysInvNum column for this row should read '200500015'
When I run a basic query using the CAST or CONVERT functions like this:
SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform
OR
SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform
I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself.
I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this:
CREATE FUNCTION GetSysInvNum()
RETURNS varchar
AS
BEGIN
DECLARE @maxcaseid Int
DECLARE @sysinvnum varchar
SELECT @maxcaseid = max (caseid) from caseform
SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid
RETURN @sysinvnum
END
The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2".
Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this?
Thanks in advance
'Wale
View 10 Replies
View Related
Mar 26, 2008
Thought I'd help some folks with rs and dates..
here is some scalar valued functions:
CREATE FUNCTION [dbo].[get_date_only] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0)
END
CREATE FUNCTION [dbo].[get_month_end] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END
CREATE FUNCTION [dbo].[get_month_start] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END
CREATE FUNCTION [dbo].[get_today_end] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
CREATE FUNCTION [dbo].[get_today_noon](@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
CREATE FUNCTION [dbo].[get_today_start] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END
CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
CREATE FUNCTION [dbo].[get_week_end] (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
CREATE FUNCTION [dbo].[get_week_start] (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END
CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END
CREATE FUNCTION [dbo].[get_year_start] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END
CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
Then create a Table-Valued Function like so:
CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
year_end datetime,
tomorrow_noon datetime,
today_noon datetime,
date_only datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_year_end(@date) AS year_end,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,
dbo.get_date_only(@date) AS DateOnly
RETURN
END
Now the RS folks might be thinking but how does this help me as I need a dataset and a dataset can only be based on a Stored Procedure or a direct table. No problem create the following stored procedure:
CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
Now you've got a stored procedure to use as a dataset...Now in reporting services add a new dataset:
Now go to the report parameters section of the report:
Now pick that dataset dsFunctions (or whatever you called it) and then pick any of the value fields from the scalar functions such as:
Now when you run the report it uses the scalars:
If you have questions feel free to ask :).
Hope this helps someone
View 2 Replies
View Related
Jan 26, 2007
Hi all,
Does sombody have experience on dynamically set or change the default value of a report parameter?
Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube
What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.
What I did is:
Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");
ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });
what I got when reveiw the report:
P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.
Any idea and suggestion will be pre-appreciated!
Jone
View 1 Replies
View Related
May 2, 2006
Hi there!
My provider does not yet support SQL Server 2005, they are still on SQL Server 2000. I put together a very basic login page, but it is crashing everytime I access it because of this problem, and sadly I am not advanced enough as yet to know how to remedy it! Can you help?
Here is the error page, some of it; - Any ideas gratefully received!
Russ.
Server Error in '/' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
View 1 Replies
View Related
