MS SQL Server :: DATEADD With 8 Byte Int?

Help With Error 'value Of Type Byte Canot Be Converted To 1 Dimensional Array Of Byte'
hi
i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code.
can anyone help me correct the error and let me know ow to solve it
thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString")
myConnection.Open()
Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id")
cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
dr.Read()
context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error
End Sub

View Replies !

SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,

Any help on this greatly appreciated...

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

Many thanks

View Replies !

Byte[] Value In SQL Query...
Hi people,I have a little problem with this!There are some variables in C# code: int personID = 10;
string personName = "Tom";

System.IO.BinaryReader reader = new System.IO.BinaryReader(FileUploadPersonPhoto.PostedFile.InputStream);

byte[] personPhoto = reader.ReadBytes(FileUploadPersonPhoto.PostedFile.ContentLength); After that, there is a SQL query: string query = "INSERT INTO PersonTable (PersonID, PersonName, PersonPhoto) VALUES ("

+ personID + ", '" + personName + "', " + personPhoto + ")"; In Debug mode, value of the query is "INSERT INTO PersonTable (PersonID, PersonName, PersonPhoto) VALUES (10, 'Tom', System.Byte[])" and it does not work! Is there any prefix or something else that I should put and make it work?Thank you in advance!P.S. Do not want to use sql parameters at this piont!

View Replies !

SQL Binary To C# Byte[]
I have a binary column with length 20 in SQL server table. I store dynamically C# byte[] value range from 0 to 19. So for example, If I store length of 16 and when I try to retrive it back into byte [] in C# it returns whole length 20. When I see in debug it has value from 0 to 15 which I want to use but from 16 to 19 is zero. How can I get just length value which I stored.

I use DataRow to get whole row and from the row object I extract byte [] based on column name.

Thank you in advance....

View Replies !

From Byte[] To A .rdl File
Hi,

Can we regenerate a .rdl file based on the byte[] stream created by Render method?

user sends the report name with parameters to application server and it is application server sends the request to Reporting Server and get the report back (Render method). But how to pass the returned byte[] stream to user and show her/him a report?

View Replies !

CLR UDT Max Byte Size
Hello,

I need some help working with CLR UDTs. I have created two UDTs called trajectory and point. Each trajectory consists of a list of points. Each point consists of three members : lon( type double), lat( type double) and datetime.

I have written my own IBinarySerialize.Write method for the trajectory type which is the following:

Dim maxSize As Integer = 4000
Dim value As String = ""
Dim paddedvalue As String

Dim i As Integer
Dim pt As Point

For i = 0 To point_list.Count - 1

pt = point_list.Item(i)

If i = 0 Then
value = value & pt.X & "|" & pt.Y & "|" & pt.D

Else

value = value & ">" & pt.X & "|" & pt.Y & "|" & pt.D
End If

Next

paddedvalue = value.PadRight(maxSize, ControlChars.NullChar)

For i = 0 To paddedvalue.Length - 1 Step 1
w.Write(paddedvalue(i))
Next

If I try to store 225 points for a trajectory then the following error occurs:

System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.

Why is that happening? The limit size for a UDT is 8000bytes. Each point needs 24Bytes (2*SizeOf(double) + sizeOf(Datetime)).

Please help
ST

View Replies !

Cannot Convet Sql Image To Byte[]
I have followed many examples found on this site, but still get an invalid cast execption when I attempt to run code below. the exception is thrown when I try to convert the sql image to a byte[] in the download section of the code.

//
// sqlUploadImage
//
this.sqlUploadImage.CommandText = "INSERT INTO Image_Table (Description, Type, Lenth, DocName) VALUES (@Description," +
" @Type, @Length, @DocName)";
this.sqlUploadImage.Connection = this.sqlConnection2;
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 50, "Description"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Type", System.Data.SqlDbType.VarChar, 50, "Type"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", System.Data.SqlDbType.Int, 4, "Lenth"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DocName", System.Data.SqlDbType.VarChar, 50, "DocName"));
this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Image", System.Data.SqlDbType.Image, 16, "ImgField"));
this.Button2.Click += new System.EventHandler(this.Button2_Click);
//
// sqlDownLoadImage
//
this.sqlDownLoadImage.CommandText = "SELECT Image_Table.* FROM Image_Table";
this.sqlDownLoadImage.Connection = this.sqlConnection2;

//upload file
private void Button1_Click(object sender, System.EventArgs e)
{
//Get the filename of the pdf file to be uploaded.
string strFilename = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") +1);

//Get the file type
string strFileType = File1.PostedFile.ContentType;

//Get the file size
int intImageSize = File1.PostedFile.ContentLength;

// Reads the Image
Stream ImageStream = File1.PostedFile.InputStream;

byte[] ImageContent = new byte[intImageSize + 1];
int intStatus = 0;
intStatus = ImageStream.Read(ImageContent, 0, intImageSize);

//Response.Write(strFilename.ToString());
//Response.Write("<br>Image Type: " + strFileType.ToString());
//Response.Write("<br>Image Size: "+intImageSize.ToString());

this.sqlUploadImage.Parameters["@Description"].Value = strFilename;
this.sqlUploadImage.Parameters["@Type"].Value = strFileType;
this.sqlUploadImage.Parameters["@Length"].Value = intImageSize;
this.sqlUploadImage.Parameters["@DocName"].Value = "TestPDF";
this.sqlUploadImage.Parameters["@Image"].Value = ImageContent;

try
{
this.sqlConnection2.Open();
this.sqlUploadImage.ExecuteNonQuery();

this.Label2.Text = "File Uploaded Successfully";
this.Button2.Visible = true;
}
catch(SqlException ex)
{
Response.Write(ex.ToString());
}
finally
{
this.sqlConnection2.Close();
}
}

//download file
private void Button2_Click(object sender, System.EventArgs e)
{
try
{
this.sqlConnection2.Open();
SqlDataReader r = this.sqlDownLoadImage.ExecuteReader(CommandBehavior.CloseConnection);

if(r.Read())
{

Response.ContentType = r["Type"].ToString();
byte[] image = (byte[])r["ImgField"];
Response.BinaryWrite(image);
}
}
catch(SqlException ex)
{
Response.Write(ex.ToString());
}
finally
{
this.sqlConnection2.Close();
}
}

View Replies !

Doube Byte Characters
I want to store some double byte characters in a table. Originally I planed to change all data type of fields which will store double bytes from VARCHAR() to NVARCHAR(). But i just found out we have no problem to pull and store foreign content(double byte characters) in fields with VARCHAR type( the content is showing correct after stored in database)

my question is : if VARCHAR can handle double byte character, what's the point of using NVARCHAR to store unicode character? any advice is appreciated.

Michael

ming.shi@factiva.com

View Replies !

Double-byte In MSSQL
Experts,

i have trouble while insert/update a field which contains double-byte characters (Chinese Traditional).

NO PROBLEM if i m using Enterprise Manager to view/edit the data. They are retrieved properly in the following:
(1) Enterprise Manager
(2) Query Analyzer
(3) Visual Basic
(4) Command prompt isql

EACH of the Chinese words are become a qustion mark '?' if the UPDATE SQL or stored procedure executed in the following:
(2) Query Analyzer
(3) Visual Basic

WHILE (4) Command prompt isql does not have the problem for the same UPDATE SQL and stored procedure.

Do you have any idea?

View Replies !

Ftp Task- 0 Byte File
hi,

i'm using FTP task in my control flow and using *.xml to receive all files at a particular ftp location. Often I see that a 0 byte file is created by the IS package, instead of transferring the file. Is this a known bug? Is there a way to circumvent it? Also, on one of the occasions, the package just hanged for about 60 minutes (while atttempting to receive the file). FTP Connection timeout was set to default (60 sec), I wonder why the FTP task didnt return a timeout error!

Any clues?

regards,

Ali

View Replies !

Create Certificate From Byte[]
Hi,

Just wondering if anyone knows if you can create a certificate from a byte[]. For example, you can create an assembly using CREATE ASSEMBLY FROM 0x...; specifying the hex representation of it - can you do the same with a certificate? This means that you don't need to save the file to disk before loading it into the database.

Cheers,
Adam

View Replies !

Going From Byte() To File Open Of Download
Hello, I'm having users upload documents to my db and storing them as an Image datatype, I can do that without an issue. I'm also able to find that record and return it as a byte(). Now, what's the best practice/scheme to return it to the browser, even FF, and have it prompt the user with the Open or Download thing Dialogue we all know and love.

Thanks in advance.

View Replies !

Storing Byte Array In Sql Server
Hello, I am using .net 1.1 and sql server 2000.

I want to store a byte array, but it seems that only a string is stored there.

Code:

SHA512 sha = new SHA512Managed();

byte[] ReturnedPasswordByte = sha.ComputeHash(ToCryptByte);

MyGenericCommand.Parameters.Add("@Password", ReturnedPasswordByte);

MyConnection.Open();
MyGenericCommand.ExecuteNonQuery();
MyConnection.Close();

If I select the record later, all I get is "System.Byte[]" (as string)

View Replies !

Bulk Insert From Zero Byte File
Ok, hope someone has an idea out there. I have eight fixed width text files that I am importing into SQL 2000 tables in stored procedures called from a vb.net program. Problem is one of the files stands a good chance of being 0 k. As of I now i call one stored proc that then calls one proc for each file. If i try to run it anyhting after the zero byte file fails to import. If i remark it out everything else imports. I check for file.exists on the vb.net and the file passes the test. Any way to handle the 0 byte on the proc side

View Replies !

DTS Package - How To Prevent A 0 Byte File?
I've created a DTS package -- that uses a query to export to a .txt file. My question is -- if the results of this query are zero (no results returned within the package ) -- how can I tell the package not to export a zero byte file. Any thoughts on that? Any help you could give would be greatly appreciated. Thanks!

View Replies !

DTS: Handling Zero Byte Input Files
Good Day,

I have a situation where I need to be able to gracefully handle zero byte text files to be processed by DTS (using ActiveX Script transformations).

I have a job set up in SQL Server that executes several DTS packages, then executes a Transact SQL script to process the data imported by DTS. This all works fine, but there are instances where some of files have no data to be imported. This causes the entire job to fail -- I don't want to alter the job to continue on an error because there is a legimate potential for errors. I do, however, want to be to have the job continue if one of the DTS packages has no data to process. Is there a way (DTS object method, property) to check for the absence of data before the DTS package errors out? Any ideas would be greatly appreciated.

Regards,

Robin Sarac

View Replies !

Derived Column Can't Add 8-byte Integers
I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:

Column Name: BIG_ID

Derived Column: < add as new column >

Expression: (DT_I8)[ID] + 840230000538058

Data Type: eight-byte signed integer  {DT_I8]

The error I get:

The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.

Then I try the expression:

(DT_I8)[ID] + (DT_I8)840230000538058

and

[ID] + 840230000538058

and get the same error.

What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?

Thanks,

Michael

View Replies !

Dateadd
Just want to double check this.To add 30 days to the current date in a stored procedure using SQL Server should be this: DATEADD(day, 30, GETDATE())Right?Thanks,Zath

View Replies !

Dateadd
Hi guys I am trying to convert this to date add because MS SqL and Jet SQL dont exactly speak the same language

=DateSerial(Year([FINALSUIT])4,Month([FINALSUIT]),Day([FINALSUIT]))

I am trying to calcuate the finalsuit date for every 2 years becasue that when employees have to get their gaming license renewed, so I'm trying to add the renewal year by 2 because they have to get it done every two years

View Replies !

Dateadd In RS
Hi

Hope someone can help.

I've got to produce a report that filters information dependent on the purchase date based on various parameters.

The parameters are:

@yearend (Datetime)
@months(int)

the script that I wrote in SQL is

select assetno from assets where purch_date < = dateadd(mm,-@months, @yearend)

but when I try to use this in the dataset it comes up with the following error:

"Application usess a value of the wrong type for the current operation"

Any help would be very much appreciated.

View Replies !

Bug In Dateadd?
when I execute the following:

select dateadd(dd,92710,1/1/1753)

I get 2153-10-31 00:00:00.000

in managed code I get the right answer 11/1/2006. Between 1753 and 2007, there should have been 2007 - 1753 = 254 yrs x approx 365 = 92,710 days. Where is the year 2153 coming from?

View Replies !

Need Help With DATEADD

I am using Derived Column Transformation Task to format my data as I am converting my DTS to SSIS. One of the columns requires to use DATEADD where I add 1 month and substruct one day from the date that I get in the flat file.(note that I always hardcode the day to "01" regardless of what I get in my flat file)

The current script looks like this:

DTSDestination("PeriodEndDate") =
dateadd("d",-1,dateadd("m",1,cdate(mid(cstr(DTSSource("Col004")),1,4) & "-" & mid(cstr(DTSSource("Col004")),5,2) & "-" & "01")))

Does any one know how to convert it so it works in my derived column?

Thanks...

Maria

View Replies !

Sum Iff With DateADD
What is the correct format for getting the last three months of data. in sql its DATEADD(mm-3,getdate()) but for some reason the report doesnt want to take this? what am i doing wrong?

Code Snippet

=IIF(Sum(iif(Fields!Database_Size_Datetime.Value >= DATEADD(mm, - 3, GETDATE()), Cdbl(Fields!MonthlyDBTotal.Value), 0.0))/ 3)

By the way, im trying to get the average over the last three months by doing this.

View Replies !

Dateadd()
If I am running a scheduled job at the first of the month and I want to run an update sequence for the previous month, i was essentially using this sql:

@startdate = dateadd(mm, -1, getdate())
@enddate = dateadd(dd, -1, getdate())

So, will the @enddate represent the last day in the previous month or will that not work?

The Yak Village Idiot

View Replies !

Save An Image To A Byte[] Variable From A Database
i'm trying to read an image file from a database(ms-sql, .mdf) with type image. Anyone have any ideas on how to do this? I have a table adapter created but could not assign it to my byte[] variable. Thanks in advance.

View Replies !

Help: 900 Byte Limit On Stored Procedure Parameter?
Hi,I have a .NET application that I want to save the Config.EXE contentsto my SQL database for remote review/testing. This config file is3700+ bytes long. I created a field in one of my tables with a VARCHAR4800 and then created a stored procedure that receives a parameter(also VARCHAR(4800).However it fails to write anything if the length of the value that Ipass is anything greater than 900. If I pass exactly 900 characters orless - the data is written to the field. If I pass 901 characters Iget nothing.I'm suspicious since it is exactly 900. I seriously doubt it's somelimitation of MS-SQL so I need a nudge in the right direction.Thanks

View Replies !

Breaking Data Into 1500 Byte Chunks
Hi,

I have a text file (5 MB). It appears as a single line in a text editor. But actually it has records of 1500 byte length each.

I want to strip it down to 1500 byte records. So 1500*3500 = 5 MB (approx). The record size is always 1500 bytes.

Does anyone have a script that I can run on this file to achieve this break.

Thanks

View Replies !

Why Can Varchar Datatype Variable Only 4000 Byte?
Why can varchar datatype variable only 4000 byte?
For example:
in a storedprocedure
declare @aa varchar(8000)
......
while
select @aa=@aa+@otherinfo
end
when the length is more than 4000 ,the data in the behind will be lost

View Replies !

DTS - Zerp Byte File Import And DontExecuteTask
Hi, I'm a bit new to DTS but the problem I have encountered relates to importing a text file. On occasion the file is zero bytes which causes an error in DTS. I have added some VBScript to the workflow to check the filesize. If size > 0 then I set Main = DTSStepScriptResult_ExecuteTask otherwise Main = DTSStepScriptResult_DontExecuteTask. This all works fine except that when the file is 0 the dependent process that is waiting for completion/success does not run, presumably because the task was not run (as reqd). I have tried setting the status rather the DontExecuteTask but then the task runs anyway creating the error. Anyonw know how to get this to work or how to get around the problem?

regards
Ken

View Replies !

DTS Adding Byte To Columns During Table Migration
When migrating a table from Informix to SQL Server using DTS services, SQL Server adds an extra byte to the column lengths. Does anyone know why this happens and is there a way to prevent it? I'd like the source and target table to have the same column characteristics.

Thanks.

View Replies !

Trying To Map To Tinyint - Single Byte Unsigned Int Not Working
I have an Excel spreadsheet that I eventually land into my staging table. In between, I'm attempting to get a date code from the Date table. I'm using a Lookup Transformation Editor and mapping the fiscal week of year and fiscal year name. I know the fiscal year name is fine. When I have both the fiscal year name and the fiscal week of year, the package fails on the lookup step. In a data conversion, I convert the fiscal week of year to a single byte unsigned integer. (In the Date table, the fiscal week of year is a tinyint.) I'm not sure what I'm doing wrong?

View Replies !

DATEADD Function?
Hi ALL!anyone can help me resolve this problem.I create SQL sentence and using DATEADD to Update DATETIME FIELDand i need Increase 1 Year and Month is 04 and day is 01.example: origin datetime field : 2007/06/26result after update is : 2008/04/01so by DATEADD(yy,1, datefield)?Have any expression for datefield to set month and day like what i need?Thanks .

View Replies !

DateDiff / DateAdd - Please Help Me:(
hello,
i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive...
i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on
the pictures added in database are active (available to be seen by users) only 1yaer after the date added
I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd
Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo

AS

SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded
FROM Pictures
WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2)
ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me

View Replies !

DATEADD - Add Weekdays Only
Hi, I have a problem with working out some dates. I have a query that has a start date field and a number of days field. I know i can create another field that could provide the return date (DATEADD function) by adding the number of days to the start date. However the problem I have is that i need to discount the weekends from the return date. For example if the start date was a wednesday and the number of days was 3 the datadd sum would give a return date of saturday when in reality it should be monday. I am not sure if i am making sense but if anyone out there has any ideas it would be more than welcome. Andrew

View Replies !

SQL DATEADD Function
I need help in T-SQL.I am using DATEADD function and I want to add 6 months to a date. Butit does not return me the rusults, which I wante.g. SELECT DATEADD(m,'20040630') returns 20041230which is logical correct? But I want it to return end of month (i.e.20041231)Any help in this context will be highly appreciatedAbdul N. Khan

View Replies !

Using DATEADD Function
:eek: Hi,
I'm getting problem while using DATEADD Function.

When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.

View Replies !

Function Dateadd
hi,
can something tell me what function do i need if i want to know what happen the last three hours?
for example: dateadd (hh, -3, getdate())
it seems not working?!!

View Replies !

Dateadd And Variables
Is it possible to use a variable within dateadd? This is what I have: dateadd(mm,+(@variable),field)

View Replies !

DateAdd Function
I programmed the following query to add 10 years to the return of i.fldimmdate.
When I run this all I receive is the date 1/1/1900 for all dates returned in the query. I understand that this means that I'm dividing by zero in integer math, but I have been unable to figure out how to use DateAdd() correctly. Any help is appreciated.

CREATE VIEW iTetDip
AS
SELECT DATEADD(yyyy,10, MAX(i.fldimmdate)) flddate
,d.fldkey
FROM tblimmunizations i
,lovimms l
,tbldemographics d
WHERE l.fldname IN (SELECT l.fldname
FROM lovimms l
WHERE l.fldname LIKE 'Tetanus Diptheria')
AND d.fldkey = i.fldpatientid
AND i.fldimmcodeid = l.fldkey
AND d.fldfmp = '20'
AND d.fldbenstat = 'AD'
AND d.fldrecordstatus = '1'
GROUP BY d.fldkey

View Replies !

DateAdd Function
Hi,

Can someone please tell me why the following won't work:

Code:

SELECT * FROM Validation where CourseDate > DateAdd(wk, -3, GetDate())

I want to select all records where the coursedate is more than 3 weeks old.

Thanks.

View Replies !

DATEDIFF, DATEADD

How was the DATEDIFF function written? I would like to know the logic behind it.

Also what does it mean to say

SELECT DATEDIFF(MONTH, 30, '2004-12-30')

I thought it accepts two dates only. but instead, it took 30 as start date and returned 1259!!

Thanks

View Replies !

DateAdd SQL Query Different In RS?
Heres My SQL to get me 2 hours back of Midnight, two months ago (so 11pm of the prev day)

SELECT DATEADD("hh",-2,DATEADD("m",-2,DATEADD(dd,-(DAY(GetDate())-1),DATEADD(day,DATEDIFF(day, 0, GETDATE()),0))))
So I converted it into RS for my report start date, but it's not running...cant even get preview to load, it detects an error right away.,,just doesnt tell me what it is

=DATEADD("h",-2,DATEADD("m",-2,DATEADD("d",-(DAY(Now())-1),DATEADD("d",DATEDIFF("d", 0, Now()),0)))

I'm pretty sure I have the "h", "m" and "d" syntax right, I tried them all individually and they give me the correct dates...other than changing GetDate() to Now() I dont know what makes this diffenent...is it just too much nesting to Reporting Services?

View Replies !

DATEADD Function
Problem:
I have two nearly identical querys that are running. One of the query's is using the SQL DATEADD function. These query's are running on a SLQ2005 MSDE. There is now service pack four applied to this server.
The question is:
Are there any known issues with the DATEADD function in SQL 2005? This query is used in a report that has been in use for about two years. There were some changes made to the server (BIOS upgrade) including adding this service pack. These changes did not cause any other SQL issues. This query now takes almost five minutes to run. The nearly identical query (the difference is the date is passed in as a parameter rather than using the DATEADD function) takes from one to two seconds. These reports have a history of nearly identical running time.

What is a good way to try and correct the issue or at least to try and isolate the problem? Have there been any known issues with this function?

Thanks for any help!

View Replies !

DATEDIFF And DATEADD
Hi
I'm trying to break down some code to work out how it's working. I've encountered DateDiff and DateAdd and I think this adds a new date of midnight today after reading up on the syntax (still dont fully understand how it works to be honest)

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

However, this is slightly different because where there is a 0 above there is a 13 in this one so I'm wondering what the 13 is actually doing.

SELECT DATEADD(dd, DATEDIFF(dd, 13, GETDATE()), 0)

In this one there's a 21

SELECT DATEADD(dd, DATEDIFF(dd, 21, GETDATE()), 0)

Can someone guide me please?

Thanks
Daniel

View Replies !

Datediff And Dateadd
hi all,

can i have brief explaination of datediff and dateadd function, regarding their arguments and params, definitions.. like the books online definition.. anybody have the books online url?

becoz im curious what this line do in detail(found it in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729 : count age function and it's kewlLLl)

datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)
thanks

~~~Focus on problem, not solution~~~

View Replies !

Using DateAdd And Convert
Here's my sp which doesn't do what I want

I want it to only list records with LastDateIn 10 days prior to today's date.

CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay]
AS

SELECT Convert(nvarchar, LastDateIn,103) AS Expr1, COUNT(Status) AS Expr2
FROM tblFiles
where Convert(datetime, LastDateIn,103) < dateadd(day, -10, getdate())
GROUP BY Convert(nvarchar, LastDateIn,103)
ORDER BY Convert(nvarchar, LastDateIn,103) DESC

GO

View Replies !

Dateadd Confusion
I am trying to find a group of clients that were entered last week. I am getting all the dates to go with the Monday of the week they were entered. i.e. If they were entered on 8-2 or 8-3, they go with 8-1. If they were entered, 8-9, they will be under the 8-8 Monday.

No problem with finding the Mondays using either the nice 'weekcommence' function I found here or using numerous t-sql examples.

One thing I don't understand is- how come I can't use an equal sign in my syntax?

For instance,

SELECT dbo.weekcommence(date_added)
FROM clients
where dbo.weekcommence(date_added) = dateadd(wk, -2, GETDATE() )

won't return any results. If I use a greater than sign, I get this week, 8-8 and last week 8-1. I only want to see the week of 8-1.

Now- look at what I have below: Shouldn't I really be able to see ONLY last week's Mondays with this?

select dateadd(wk, -2, dbo.weekcommence(date_added))from clients

To me- the way this reads is: show me 2 weeks ago, from these dates (first Mondays function).
The results are the last two MONTHS, not weeks. What am I misinterpreting?

I can also use this:

SELECT dbo.weekcommence(date_added) FROM clients
WHERE (dbo.weekcommence(date_added) >= dateadd("d",-7,DATEADD(wk, DATEDIFF(wk,1,getdate()), 0)))

and get the week's of 8-1 and 8-8. But I can't get JUST 8-1 if I take out the greater than sign.

To me- this one reads: Select all my Mondays from the function. Show me Mondays from my function that are = 7 days from this Monday's date.

It seems like it should be straightforward, but I'm obviously missing something big. Any help?

View Replies !

DATEADD Function
I'm using a query that is not retrieving all of the data it should because of the datetime stamp. If I use the following in the where clause:

{table.date} between '4/1/2008' and dateadd(day, 1 '4/30/2008')

that works fine for April but not for other months, where I might get more data from the following month than I should.

Is there a way to add hours, minutes, and seconds (23:59:59), all at the same time, without moving ahead an entire day?

View Replies !

Connecting To Double Byte Progress (MFG-PRO) Databases From SSIS...
HelloWe use MFG-PRO as our ERP system which in turn uses Progressdatabases. In the old version of SQL 2000, using DTS packages, we usedto set the code page via command prompts and execute DTS packageswithin that command prompt to fetch data from our double byte ERPdatabases. In SSIS, we are able to connect and fecth data from NONDouble Byte databases, however, when we set the code page first andexecute via a command prompt the SSIS package, we get the followingerror. We used a ADO.NET connection to connect to our Source ERPSystem. Appreciate any help.ThanksJagannathan SanthanamMicrosoft (R) SQL Server Execute Package UtilityVersion 9.00.1399.06 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started: 8:09:26 AMProgress: 2007-11-28 08:09:26.93Source: Data Flow Task 1Validating: 0% completeEnd ProgressError: 2007-11-28 08:09:27.15Code: 0xC0047062Source: Data Flow Task 1 DataReader Source [1]Description: System.Data.Odbc.OdbcException: ERROR [HYC00][DataDirect][ODBCPROGRESS driver]Optional feature not implemented.ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver'sSQLSetConnectAttr failed.ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature notimplemented..ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver'sSQLSetConnectAttr failed.at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,RetCode retcode)at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnectionconnection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)atSystem.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool,DbConnection owningObject)atSystem.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)atSystem.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionowningConnection)atSystem.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnectionouterConnection, DbConnectionFactory connectionFactory)at System.Data.Odbc.OdbcConnection.Open()atMicrosoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(StringassemblyQualifiedName, String connStr, Object transaction)atMicrosoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object pTransaction)atMicrosoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object transaction)atMicrosoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)End ErrorError: 2007-11-28 08:09:27.16Code: 0xC0047017Source: Data Flow Task 1 DTS.PipelineDescription: component "DataReader Source" (1) failed validationand returnederror code 0x80131937.End ErrorProgress: 2007-11-28 08:09:27.16Source: Data Flow Task 1Validating: 33% completeEnd ProgressError: 2007-11-28 08:09:27.16Code: 0xC004700CSource: Data Flow Task 1 DTS.PipelineDescription: One or more component failed validation.End ErrorError: 2007-11-28 08:09:27.16Code: 0xC0024107Source: Data Flow Task 1Description: There were errors during task validation.End ErrorDTExec: The package execution returned DTSER_FAILURE (1).Started: 8:09:26 AMFinished: 8:09:27 AMElapsed: 0.859 seconds

View Replies !

Connecting To Double Byte Progress (MFG-PRO) Databases From SSIS...

Hello

We use MFG-PRO as our ERP system which in turn uses Progress databases. In the old version of SQL 2000, using DTS packages, we used to set the code page via command prompts and execute DTS packages within that command prompt to fetch data from our double byte ERP databases. In SSIS, we are able to connect and fecth data from NON
Double Byte databases, however, when we set the code page first and execute via a command prompt the SSIS package, we get the following error. We used a ADO.NET connection to connect to our Source ERP System. What shocks me is that this functionality works well in SQL Server 2000 DTS packages but not in the much publicized SSIS packages. The error messages are so cryptic as they used to be in DTS days and do not help a wee bit!

Appreciate any help.

Thanks

Jagannathan Santhanam

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 8:09:26 AM
Progress: 2007-11-28 08:09:26.93
Source: Data Flow Task 1
Validating: 0% complete
End Progress
Error: 2007-11-28 08:09:27.15
Code: 0xC0047062
Source: Data Flow Task 1 DataReader Source [1]
Description: System.Data.Odbc.OdbcException: ERROR [HYC00]
[DataDirect][ODBC
PROGRESS driver]Optional feature not implemented.
ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901
ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver's
SQLSetConnectAttr faile
d.
ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature not
implemented
.
ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901
ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver's
SQLSetConnectAttr faile
d.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
RetCode r
etcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection
connection, Odb
cConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOption
s options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection own
ingObject)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
ow
ningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
ou
terConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireCon
nection(Object pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnection
s(Object transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)
End Error
Error: 2007-11-28 08:09:27.16
Code: 0xC0047017
Source: Data Flow Task 1 DTS.Pipeline
Description: component "DataReader Source" (1) failed validation
and returned
error code 0x80131937.
End Error
Progress: 2007-11-28 08:09:27.16
Source: Data Flow Task 1
Validating: 33% complete
End Progress
Error: 2007-11-28 08:09:27.16
Code: 0xC004700C
Source: Data Flow Task 1 DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2007-11-28 08:09:27.16
Code: 0xC0024107
Source: Data Flow Task 1
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 8:09:26 AM
Finished: 8:09:27 AM
Elapsed: 0.859 seconds

View Replies !