Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
Stew
Create a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
StewCreate a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.sql

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
StewCreate a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

Problem with linking tables

I have an Access database that contains local files and files linked on a SQL server through a DNS connection. I have been using this for years and during that time I have dropped links, added links, changed the DNS server information, etc.

I changed the DNS server information this weekend, deleted all the links, and was ready to start linking the tables with the new DNS information. I click on File >> Get External Data >> Link Tables and am given the traditional file selection dialogue. I go to the bottom (Files of Type) and scroll to the last selection... ODBC Databases().

In the past, after I made that selection, I would then be taken to the ODBC dialogue from my computer to select the connection type and then the actual connection.

Now however, the dialogue immediately stops. It doesn't lock, it just stops and returns me to the Database window from my database as though I wasn't doing anything.

I have reinstalled Office and redone all the updates. Does anyone have any suggestions?

Thanks,

Gary

I just had the same problem today, but after installing new version of Norton Antivirus.

I found the solution on forum from jan 2006 (I'm not that smart)...

Turning off the Office plug-in in Norton Antivirus did indeed work. For those who need specific directions:

1. In Norton Antivirus, select Options from the toolbar, choose Norton Antivirus.
2. On the left select the Miscellaneous option
3. Uncheck the box for the Office Plug-in (scanning MS Office documents)
4. If you had MS Access open during this process, shut it down and restart it.

Hope this helps!

Emilija

|||

Thanks so much, Emilija. You hit it right on the head. I wouldn't have thought of that in 1,000,000 years!

Now all we have to do is make it esaier to find and change things in Norton! Every new edition hides things even more.

Thanks again,

Gary

problem with foreach loop container and excel files

well i am trying to follow the example of msdn help on how to loop through excel files and it doesn't work. the variables have the project scope. what can i do?

What exactly are you trying to do? Can you put the link to the msdn help here please.

Thanks

Sutha

|||

well i have a number of excel files and i want to use them in a loop so as to do calculations and load a table. when i use one file the process is ok, but when i use the steps described in that link

http://msdn2.microsoft.com/en-us/library/ms345182.aspx

i get errors.

thank u in advance

|||

You are not trying to do this on a 64 Bit by any chance?

"You may not be able to connect to as many data sources from a package executing in 64-bit mode as you can from a package executing in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers may not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version." (http://msdn2.microsoft.com/en-us/library/ms141766.aspx)

Thanks

Sutha

|||

no an i get the following message

TITLE: Microsoft Visual Studio

Error at Package3 [Connection manager "SourceConnectionExcel"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Pilote ISAM introuvable.".

Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

|||

Are you getting this error message on "To loop through Excel files by using the Foreach File enumerator" OR "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator".

I worked through the first one (To loop through Excel files by using the Foreach File enumerator) and it worked without any issues.

Thanks

Sutha

|||

i worked with To loop through Excel files by using the Foreach File enumerator

I don't know :)

Thanks

|||

well i tryied with the following way? i read a file with the import/export wizard, i followed the instructions of the link, i loaded the files in a separate table and it worked. the problem is with the excel source in the data flow. am i using the wrong compnent. because i want to read directly the files and not to load them in a table. am i not supposed to use "excel source"?

thnxs

|||

I have a working example that I have done can be download from here.

Uses c:\Temp as source directory for Foreach Loop

Thank

Sutha

|||

and how can i use the script in order to use a data flow task in order to use the data in other transformations?

thnxs

|||

All I have done in that example, is just to get the xls files in c:\temp, assign each file name and print the variable to a message box.

you still have to do data flow task.

Thanks

Sutha

Problem with Execute Process Task

Hi,

I have what should be a very simple process.

I have a FOREACHLOOP container, that is set up to loop through files in a directory:

Enumerator: Foreach File Enumerator

Folder: c:\myfolder

Files: *.txt

Retrieve: Name and extension

Variable mappings: User::file_name, Index 0

So far, so good...

Then, inside the FOREACHLOOP, I have an Execute Process Task, which is simply a .bat file that I am running, and passing the name of the file to:

For example: myBat.bat file_name

In the Execute Process Task, I have:

RequireFullFileName: false

Executable: c:\myFile\myBat.bat

WorkingDirectory: c:\myFile

StandardInputVariable: User::file_name

Everything else is set to the defaults.

However, when I execute my container, I get the following error:

Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "c:\myFile\myBat.bat" " " at "c:\myFile", The process exit code was "1", while the the expected was "0".

Is there anything I am obviously doing wrong here at the package level? When I run the .bat file from the command line, it works fine.

Thanks

Even when I remove the Standard Input Variable, and just put file name as the Argument, I still get an error. This is just running the Execute Process Task, not the loop, for one file only.

|||

Ok, I set the SucessValue = 1, instead of 0, and now it's running ok with the single file name argument.

However, when I change it back to the StandardInputVariable with the User::file_name, it doesn't run the batch file correctly, that is the batch file does not work as expected, which makes me wonder what the User::file_name variable looks like?

I set a breakpoint, but how can I see my watch variable?

Thanks!

|||Type User::file_name into the watch window.|||In the execute process task, if the processes uses command line arguments, as in
some_executable.exe arg1
or
some_batch_file.bat arg2

use the Arguments property.

If the process to execute is properly invoked as below, then it is expecting and will use the standard input file stream, not a command line argument, so use the StandardInputVariable property. Note setting the StandardInputVariable to the "User::file_name" is streaming in the name of the file, NOT the contents of the file (as in the echo "x" case below).

echo "x" | some_executable.exe

If the process expects both command line arguments and standard input, as follows:

echo "x" | some_executable.exe arg1

then use both task properties as well.

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.
Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.
|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:
> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
> > When in em, I try to open a table and return all rows, I get a weird error:
> > "The query cannot be executed because some o fthe files are missing or not
> > registered."
> > Also, I cannot connect to the database with a dataadapter in vs (2003)
> > I can, however, execute an SQL in Query Analyzer.
> > I have reinstalled several times. Some of the Google articles suggest that
> > it might be MDAC. I cannot install MDAC either. It says that the operation
> > is not supported by the OS (xp Pro) The components are already installed.
> > Any input would be greatly appreciated.

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error
:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest tha
t
> it might be MDAC. I cannot install MDAC either. It says that the operatio
n
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installatio
n
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
>

problem with dynamically created RDL files and reports

Hello,

I have a strange problem with a dynamic created RDL resp. dynamic created report.

I have followed the programming tipps on the microsoft site to create programmatically a RDL file and this RDL file is accepted by the reporting server. But when the report is rendered out of that RDL some fields are somehow modified. In one field after the first line (which holds the correct value) all values in the following rows are cut after the fifth character. Other fields contain every now and then strange signs like a caret...

My report items in the RDL e.g. look like this:

- <TableCell>
- <ReportItems>
- <Textbox Name="Details_PBN">
- <Style>
<TextAlign>Left</TextAlign>
<FontFamily>Courier New</FontFamily>
- <BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Value>=Fields!PBN.Value</Value>
</Textbox>
</ReportItems>
</TableCell>

Do I have to do some datatype conversion in my RDL when I create it or how could this be possible?

Any help appreciated!!

I solved it myself. I write the solution down here, perhaps someone else has the same problem.

The data I that is in included in the report is queried from an oracle database. I also created the datasource for the report dynamically and there I used the provider "Oracle". This seemed to cause the problems, because after I changed it to OleDB and uses Provider="MSDAORA.1" as provider in the connection string, it worked like intended.

so... Code on, folks!!!

problem with dtexec

Hi

I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.

Following is the command I use to run my application

Dtexec /FILE "N:\Temp\LoadFirewallData.dtsx" /CONFIGFILE "N:\Temp\LoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[User::RunID]";41

Regards

Meghana

Are you running DTExecUi and DTExec on the same machine, same use? is the DTExec command line exactly the same as that for DTExecUI (look at the Command Line page in DTExecUI if you have not already)?|||

I ran to similar problems. My package has several variables. When I passed them to DTExec, it does not work and it always takes the values I set when I design the package.

Should I design the package differently? No configurations?

Guangming

|||I am using the exact same command line which shows up in dtexecui. It is not working.|||I'm afraid I cannot see why you should get any problems between the two like this, so I assume it is the package. The only other idea I have is to turn on some detailed logging in the package and see what happens prior to it getting stuck.|||

Following is the error which occurs before it gets hung. The variable is of type string. This error occurs on multi proc machine when executed using dtexec. But the same does not happen when we execute it with dtexecui.

The variable "User::stripfilename" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Also, when executed through dtexec command line, it is not consistent enough to show the same error, sometimes it runs through fine and sometimes it errors and hangs till we kill the job from task manager

It seems like couple of other folks are also facing the similar issue

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=222351&SiteID=1

Please let us know if there is any way to resolve this as this is very crucial part of our project.

Regards

Meghana

|||have you tried running the tasks in sequence, rarther than parallel? Have you tried MaxConcurrentExecutables? Seems there is a bug here, but in the meantime one of those should allow you to get going again.|||If it's an issue with variables, and you're not using this in the script, try using

Public Shared Function getVar(ByVal varName As String) As String
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead(varName, vars)
Return vars(varName).Value.ToString()
End Function

rather than referencing the variable in the script properties.

|||

Finally I found the problem:

The way to put the command string is not right after "dtexec /SQL. "

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

problem with dtexec

Hi

I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.

Following is the command I use to run my application

Dtexec /FILE "N:\Temp\LoadFirewallData.dtsx" /CONFIGFILE "N:\Temp\LoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[User::RunID]";41

Regards

Meghana

Are you running DTExecUi and DTExec on the same machine, same use? is the DTExec command line exactly the same as that for DTExecUI (look at the Command Line page in DTExecUI if you have not already)?|||

I ran to similar problems. My package has several variables. When I passed them to DTExec, it does not work and it always takes the values I set when I design the package.

Should I design the package differently? No configurations?

Guangming

|||I am using the exact same command line which shows up in dtexecui. It is not working.|||I'm afraid I cannot see why you should get any problems between the two like this, so I assume it is the package. The only other idea I have is to turn on some detailed logging in the package and see what happens prior to it getting stuck.|||

Following is the error which occurs before it gets hung. The variable is of type string. This error occurs on multi proc machine when executed using dtexec. But the same does not happen when we execute it with dtexecui.

The variable "User::stripfilename" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Also, when executed through dtexec command line, it is not consistent enough to show the same error, sometimes it runs through fine and sometimes it errors and hangs till we kill the job from task manager

It seems like couple of other folks are also facing the similar issue

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=222351&SiteID=1

Please let us know if there is any way to resolve this as this is very crucial part of our project.

Regards

Meghana

|||have you tried running the tasks in sequence, rarther than parallel? Have you tried MaxConcurrentExecutables? Seems there is a bug here, but in the meantime one of those should allow you to get going again.|||If it's an issue with variables, and you're not using this in the script, try using

Public Shared Function getVar(ByVal varName As String) As String
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead(varName, vars)
Return vars(varName).Value.ToString()
End Function

rather than referencing the variable in the script properties.

|||

Finally I found the problem:

The way to put the command string is not right after "dtexec /SQL. "

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

Problem with Date Search "Plz Help Me quickly"

I am trying to search for stored files "for example from date: 15/12/2003 to: 24/6/2006" and when i press search no results appeare the following is the database code:

1 public DataTable searchData(string fileNo,string Title,string dFrom,string dTo,string brief)
2 {
3 string str ="";
4
5 str ="select * from Tb_File where Active = 1 ";
6
7 if (fileNo !="")
8 str +=" and FileNo='" + fileNo +"'";
9 if (Title !="")
10 str +=" and Title like '%" + Title +"%' ";
11 if (brief !="")
12 str +=" and Brief like '%" + brief +"%' ";
13 if (dFrom !="")
14 str +=" and DFrom >= convert(datetime,'" + Convert.ToDateTime(dFrom).ToShortDateString() +"',103) ";
15 if (dTo !="")
16 str +=" and DTo < convert(datetime,'" + Convert.ToDateTime(dTo).ToShortDateString() +"',103) ";
17
18 ole.Open();
19 SqlDataAdapter DA =new SqlDataAdapter(str, ole);
20 DataTable DT =new DataTable();
21 DA.Fill(DT);
22 ole.Close();
23 return DT;
24
25 }

i am using sql 2000, with Visual Studio 2005.

When you use dates in a database, always use the format yyyymmdd as this will eliminate any potential date format issues.

|||

Thanks for your advice, But i have to use this format ==> so do u have a solution ??

ca8msm:

When you use dates in a database, always use the format yyyymmdd as this will eliminate any potential date format issues.

|||

Hi BigSmile,

you can use Profiler to find out what query is executed actually when you run your code. This might give you an idea of where it has gone wrong.

-Kishore

|||

BigSmile:

But i have to use this format

Why?! You want to fix the sql to return the correct records, and it looks like it could potentially be a date formatting problem, so you should use the correct format...

|||

There is probably some ambiguity in the date formats on both ends (e.g. yyddmm and yymmdd).

To avoid this you can write your dates out explicitly as "yyyy-MMM-dd" as in this example: "2007-Oct-20".
This is parsed by SQLServer correctly and avoids all ambiguities.

Writing this out is more code than you are currently writing, but is worth it to ensure 100% accuracy.

I also note you are using string concatenation - you should look at using System.Text.StringBuilder object instead.
Combining many string together produces lots of copies in memory as the string objects are immutable - StringBuilder has only one memory allocation as all appends done are on the same object.

try something like:

System.Text.Stringbuilder sb = new System.Text.StringBuilder(500);
sb.Append("Select * from tb_File where Active =1 ");

if( fileNo != "")
sb.Append(" and FileNo='" + fileNo + "'");

..etc
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...etc.

You could then write the dates out using this stringbuilder:

DateTime fromdate =Convert.ToDateTime(dFrom);
sb.Append( fromdate.Year.ToString() );
sb.Append("-");
sb.Append( fromdate.Month.ToString("MMM") );
sb.Append("-");
sb.Append(" fromDate.Day.ToString() );

|||

You should also read this article onSQL Injection as your site is potentially very insecure.

|||

Thanks, i'll try then mark it as an answer if it apply.

foreachbiscuit:

There is probably some ambiguity in the date formats on both ends (e.g. yyddmm and yymmdd).

To avoid this you can write your dates out explicitly as "yyyy-MMM-dd" as in this example: "2007-Oct-20".
This is parsed by SQLServer correctly and avoids all ambiguities.

Writing this out is more code than you are currently writing, but is worth it to ensure 100% accuracy.

I also note you are using string concatenation - you should look at using System.Text.StringBuilder object instead.
Combining many string together produces lots of copies in memory as the string objects are immutable - StringBuilder has only one memory allocation as all appends done are on the same object.

try something like:

System.Text.Stringbuilder sb = new System.Text.StringBuilder(500);
sb.Append("Select * from tb_File where Active =1 ");

if( fileNo != "")
sb.Append(" and FileNo='" + fileNo + "'");

..etc
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...etc.

You could then write the dates out using this stringbuilder:

DateTime fromdate =Convert.ToDateTime(dFrom);
sb.Append( fromdate.Year.ToString() );
sb.Append("-");
sb.Append( fromdate.Month.ToString("MMM") );
sb.Append("-");
sb.Append(" fromDate.Day.ToString() );

|||

i rewrite it as follows:

public DataTable searchData(string fileNo,string Title,string dFrom,string dTo,string brief) { StringBuilder sb =new StringBuilder(500); sb.Append("select * from Tb_File where Active = 1 ");if (fileNo !="") sb.Append(" and FileNo='" + fileNo +"'");if (Title !="") sb.Append(" and Title like '%" + Title +"%'");if (brief !="") sb.Append(" and Brief like '%" + brief +"%'");if (dFrom !="") { DateTime fromdate = Convert.ToDateTime(dFrom); sb.Append(" and DFrom >= '"); sb.Append( fromdate.Year.ToString() ); sb.Append("-"); sb.Append( fromdate.Month.ToString("MMM") ); sb.Append("-"); sb.Append( fromdate.Day.ToString() ); sb.Append("'"); }if (dTo !="") { DateTime todate = Convert.ToDateTime(dTo); sb.Append(" and DTo < '"); sb.Append(todate.Year.ToString()); sb.Append("-"); sb.Append(todate.Month.ToString("MMM")); sb.Append("-"); sb.Append(todate.Day.ToString()); sb.Append("'"); } ole.Open(); SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole); DataTable DT =new DataTable(); DA.Fill(DT); ole.Close();return DT; }

but there still an error and this is the error message:

Syntax error converting datetime from character string.

System.Data.SqlClient.SqlException was unhandled by user code
  Message="Syntax error converting datetime from character string."
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=16
  LineNumber=1
  Number=241
  Procedure=""
  Server="localhost"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.HasMoreRows()
       at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
       at System.Data.SqlClient.SqlDataReader.Read()
       at System.Data.ProviderBase.DataReaderContainer.Read()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at DataAccess.searchData(String fileNo, String Title, String dFrom, String dTo, String brief) in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\App_Code\DataAccess.cs:line 124
       at FileSearch.bindData() in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\FileSearch.ascx.cs:line 35
       at FileSearch.btnSearch_Click(Object sender, EventArgs e) in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\FileSearch.ascx.cs:line 61
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Plz it is the final part of my project and there is no time, i need your quick replies

|||You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).

|||

Hi BigSmile.
I think I gave you the incorrect code for the writing of the month as I hadn't tested it here.
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole);

...and see what the value of sb.ToString() is.
I imagine it is something like "2007-MMM-10"

I think the correct lines should be:

sb.Append( fromdate.ToString("MMM") );

sb.Append( todate.ToString("MMM") );

|||

Sorry, it doesn't work.. i apply and this is the result:

this is the existing files:

and this is the results:

foreachbiscuit:

Hi BigSmile.
I think I gave you the incorrect code for the writing of the month as I hadn't tested it here.
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole);

...and see what the value of sb.ToString() is.
I imagine it is something like "2007-MMM-10"

I think the correct lines should be:

sb.Append( fromdate.ToString("MMM") );

sb.Append( todate.ToString("MMM") );

|||

ca8msm:

You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).

foreachbiscuit:

To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);

...and see what the value of sb.ToString() is.

Are you actually bothering to listen to anyones replies? Read the two quotes above and act on the advice given.

|||

Thanks alot i think i found the problem

the string builder contains an incorrect string value:

{select * from Tb_File where Active = 1 and Brief like '%03/11/2007%' and DTo < '2007-Mar-9'}

ca8msm:

ca8msm:

You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).

foreachbiscuit:

To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);

...and see what the value of sb.ToString() is.

Are you actually bothering to listen to anyones replies? Read the two quotes above and act on the advice given.

|||

Hi BigSmile,

Looking at your code, the only way this is possible is that you have the parameters coming into the method mixed up.
You are putting the "from date" value in the "brief" string parameter of the searchData method.
This is evident by the fact that the date is not being formatted with the "MMM" format.

(if so, can you also mark this as the answer to the solution! thanks).