.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQLColumns and SQLColAttributes return different attribute for TIME datatype using SQLNCLI10 driver

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

Hello everyone,

I am using SQL Server 2008 native driver. When I query the attributes of a TIME column in SQL Server 2008, I see that using SQLColumns() API, I get length as 10 whereas with SQLColAttributes() I get the length as 12.

My application stores the column attributes got from SQLColums() call and use that length during a SQLBindCol(). Since SQLColumns() give me 10 as length, when I bind a buffer of 10 bytes, I get a "numeric overflow" error.

Please let me know if there is some setting that I might do so that attributes returned from SQLColums() and SQLColAttributes() are same.



View Complete Post

More Related Resource Links

how to change dynamically [Bbrowsable] attribute for a property at design time c#

hi,   how to change dynamically  [Browsable] attribute for a property  at design time c#

The 'DataType' attribute is not declared bug of MS SQL RS still here

Greetings. I am getting repeated bugs with reporting services that is described here https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=405739&wa=wsignin1.0 In short words, BIDS is saving the report in the wrong format, adding attributes to the tags that dont belong to the schema, and you can no longer open the report with BIDS, you have to carve those attributes out with Notepad, only to find out that next time you're working on it, they come back! This is getting seriously annoying as i virually cannot close my BIDS anymore, each time i want to reopen my project i have to edit it in the notepad!  Threre are words about "microsoft knows about this issue and changed it to "Fixed (External)" but noone knows how to install the fix... Help please.

How to assign datatype at run time to a declared var in a stored proc?

Hi, I want to declare a local var. in sp. such that it would have the same datatype as a column in a specific table Example (forgive the syntax!) Decalre @Address  CustomerTable.Address The above would translate to the following at execution time Declare @Address VARCHAR(200) This feature was in Oracle, is it in SQL Server as well? Thanks. EK

WCF calling webservice - assigning return value taking a long time

0 down vote favorite

I have a wcf service (WCF_A ) which calls another wcf service (WCF_B) (currently I am hosting the WCF_B on my local machine with my credentials – as windows service), WCF_B internally makes a call to a webservice (WS01) that is hosted on the IIS. I have created a test client and call the WCF_A -> WCF_B -> WS01. Just before making the call to (WS01) I start a timer and I stop the timer when the webservice call comes back and the result is assigned to a variable, the flow is as below WCF_B

1)  Debug.WriteLine(“Call to webservice”) 
2)  Starttimer 
3)  Var result = WS01.Function(xxxx) 
4)  Stop

Finding records using time datatype


I need to find rows in a table that have StartTime and EndTime that fall between sent start and end time parameters.  Below is the design of the table I want to search and below it is an example of a SELECT statement.  I need to add to the SELECT another condition (e.g. AND StartTime BETWEEN @StartTime AND @EndTime OR EndTime BETWEEN @StartTime AND @EndTime).  Any help is appreciated.

CREATE TABLE [dbo].[CareScheduleDetail](
 [CareScheduleDetailID] [int] IDENTITY(1,1) NOT NULL,
 [CareScheduleID] [int] NOT NULL,
 [WorkDay] [tinyint] NOT NULL,
 [StartTime] [time](7) NULL,
 [EndTime] [time](7) NULL,
 [SvcType] [varchar](4) NULL,
 [Effective] [date] NULL,
 [Ended] [date] NULL)


SELECT     WorkDay, StartTime, EndTime
FROM         dbo.CareScheduleDetail
WHERE     (Ended IS NULL)

want to display 24 hours time and date with datatype Datetime (Not String) in SSRS


Hi All,

I want to display 24 hours time and date in SSRS & for that I am using Format(now,"MM/dd/yyyy HH:mm:ss") but I am using this expression in Parameter, where I need to define datatype of that parameter as DateTime, not string. For Expression Format(now,"MM/dd/yyyy HH:mm:ss"), If I use DateTime datatype, It is showing error but working with String datatype.

For example:

I have parameter To Date  and value of this parameter should be 10/11/2010 13:09:16 (To Date: 10/11/2010 23:09:16) , not in AP or PM format and datatype of this parameter, want to keep DateTime that I can select value from Calender.

Please suggest me how I can achieve this?. I don't want to do it in stored Procedure side, want to do it in report side.

Thanks Shiven:)

Time Dimension for YTD using assigned Month and Year integers without Date datatype


Hi, most time dimensions are setup using a base Date field in the fact table, and they have plenty of issues for time analysis as it is. However my fact sales records have the time aspect assigned by pre-calculated periods, because depending on various factors, monthly final invoices are all raised on varying days (usually 2nd friday of month but can change). The monthly period is therefore not a straight calendar month. Probably a very common scenario.

So, the invoicing system already assigns the year (ie 2009, 2010, 2011) and monthly period (1, 2, 3 ... 12 with 3 representing march, even though that might represent 13th march to 9th april) and I want to use those as Time dim so we can do YTD, growth-on-prev-year etc.

It looks like its best to setup 2 Dimensions to link to 2 DataColumns/Attributes in the Fact table (say, FYear and FMonth, both integers). That way I can assigned attribute names like March to key column 3. If I combined them into 1 dimension with both fields making up a single key column, would have to either repeat the month names or link it to another Star schema I believe.

I can use the Add Business Intelligence wizard to make the Dimensions into Time ones instead of regular but I'm still not totally sure if this is the best structure/method and once done, how to use the YTD calcs to show in the cube browser (and my MDX knowle

Move up a time hierarchy from current date to return a set of quarters


I have this calculated set to return the previous 3 months.  How do I rewrite to get the last 3 quarters? 



(3,strtomember("[Date].[Month].&[" + Format(Now(),"yyyy-MM") + "-01T00:00:00]").prevmember)


Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

How to programmatically add controls to Windows forms at run time by using Visual C#

Create a Windows Forms Application
Start Visual Studio .NET or Visual Studio 2005 or a later version, and create a new Visual C# Windows Application project named WinControls. Form1 is added to the project by default.
Double-click Form1 to create and view the Form1_Load event procedure.
Add private instance variables to the Form1 class to work with common Windows controls. The Form1 class starts as follows:

.NET 4 Web Application Startup Time

I was chatting with Jonathan Hawkins and some of the folks on the ASP.NET team about performance and Jonathan mentioned the startup time for large ASP.NET applications is improved on .NET 4. There are some improvements in the CLR and in ASP.NET itself that helped. If you have a giant app, you should do some tests.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Essential SQL Server Date, Time and DateTime Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend