Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, January 20, 2012 9:04 PM
Hi All,
I have seen a couple of articles/blogs online on how to check the existence of a file in a folder and its file size but, none of them was working on my SQL Server 2000. Is that a compatability issue? Can someone help me with a simple script which can be executed on SQL Server 2000 too using T-SQL?
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Friday, January 20, 2012 9:24 PM ✅Answered | 1 vote
Xp_fileexist should do the job for you
here is more help http://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/
http://uk.linkedin.com/in/ramjaddu
Friday, January 20, 2012 9:54 PM
Xp_fileexist should do the job for you
here is more help http://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/
Wow! That was awesome. Its working on my machine. Thanks a lot Ram. Can you also tell me how to check the size of a file in a folder using T-SQL?
Regards
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Saturday, January 21, 2012 12:22 PM
Wow! That was awesome. Its working on my machine. Thanks a lot Ram. Can you also tell me how to check the size of a file in a folder using T-SQL?
Next you want to know how paint a wall using a hammer.
Use the right tool for the task. SQL Server is not the place to interact with files. Least of all in SQL 2000. In SQL 2005 and later, it's easier thanks to the CLR, but it does not mean that it's always the best solution.
So what do you really want to do? Why do you know the sizes of these files, and what is the context. That is, what is your application.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, January 21, 2012 1:13 PM
For your reference
http://www.dugger-it.com/articles/xp_fileexist.asp
Saturday, January 21, 2012 4:37 PM
Wow! That was awesome. Its working on my machine. Thanks a lot Ram. Can you also tell me how to check the size of a file in a folder using T-SQL?
Next you want to know how paint a wall using a hammer.
Use the right tool for the task. SQL Server is not the place to interact with files. Least of all in SQL 2000. In SQL 2005 and later, it's easier thanks to the CLR, but it does not mean that it's always the best solution.
So what do you really want to do? Why do you know the sizes of these files, and what is the context. That is, what is your application.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Well I will explain right from the beginning. I need to setup a secure transfer of a log file from the Unix server to a Database server. As a part of that I should create a batch file for copying the commands into the batch file. But, before copying that I need to check whether the batch file is empty? If not I should throw an error email or else should copy the commands into the batch file.
This thread has the complete details of what I am looking for. Please go through.
Please help me with this. I just have SQL Server 2000 which I am supposed to write SPs for all these.
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Saturday, January 21, 2012 4:42 PM
For your reference
I've seen the first link already. However, in the second link someone mentioned that this SP doesn't work with Network drives. If it is so, I do have some network drives to work on. Can you please help me accessing the same with network drives.
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Saturday, January 21, 2012 8:01 PM
Well I will explain right from the beginning. I need to setup a secure transfer of a log file from the Unix server to a Database server. As a part of that I should create a batch file for copying the commands into the batch file. But, before copying that I need to check whether the batch file is empty? If not I should throw an error email or else should copy the commands into the batch file.
You can't do this in SQL Server.
I say it again: you can't do this in SQL Server.
I say it again: you can't do this in SQL Server.
OK, so that is not 100% true. You could write an extended store procedure for the task, but it would completely crazy thing to do, for a number of reasons:
- This feature is deprecated, starting with SQL 2005.
- Writing XPs requires good skills in C++.
- And even a seasoned C/C++ programmer can make an error that causes memory to be overwritten, or a pointer addressing the wrong place. This can cause not only the XP to crash but also bring down SQL Server entirely.
The task you described should be performed outside SQL Server by a program written in C#, Powershell, Perl or why VBscript. Judging from the threads you posted links to, you already have a VBscript from for the task. I guess that runs from DTS. Keeping that script seems to be the simplest solution.
Please help me with this. I just have SQL Server 2000 which I am supposed to write SPs for all these.
I am helping you. I am doing all I can to prevent you from wasting time on implementing somthing which is impossible. While this forum is mainly intended for technical questions, it does not mean that we are no able to give professional advice in general. Why are you "supposed" to write SPs for this? Who is "supposing" you to do this?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, January 21, 2012 9:16 PM
Well I will explain right from the beginning. I need to setup a secure transfer of a log file from the Unix server to a Database server. As a part of that I should create a batch file for copying the commands into the batch file. But, before copying that I need to check whether the batch file is empty? If not I should throw an error email or else should copy the commands into the batch file.
You can't do this in SQL Server.
I say it again: you can't do this in SQL Server.
I say it again: you can't do this in SQL Server.
OK, so that is not 100% true. You could write an extended store procedure for the task, but it would completely crazy thing to do, for a number of reasons:
- This feature is deprecated, starting with SQL 2005.
- Writing XPs requires good skills in C++.
- And even a seasoned C/C++ programmer can make an error that causes memory to be overwritten, or a pointer addressing the wrong place. This can cause not only the XP to crash but also bring down SQL Server entirely.
The task you described should be performed outside SQL Server by a program written in C#, Powershell, Perl or why VBscript. Judging from the threads you posted links to, you already have a VBscript from for the task. I guess that runs from DTS. Keeping that script seems to be the simplest solution.
Please help me with this. I just have SQL Server 2000 which I am supposed to write SPs for all these.
I am helping you. I am doing all I can to prevent you from wasting time on implementing somthing which is impossible. While this forum is mainly intended for technical questions, it does not mean that we are no able to give professional advice in general. Why are you "supposed" to write SPs for this? Who is "supposing" you to do this?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Oh ! So, can't I do that using T-SQL? Yes, I have that in VB Script which should be converted to a SP as we are migrating from SQL Server to Oracle so that it will be easy to handle the same. Otherwise, I couldn't feel such a pain.
As per my work environment, I am supposed to do this. Its not of my own interest :(
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Saturday, January 21, 2012 11:31 PM
Oh ! So, can't I do that using T-SQL?
Correct. Well, run DIR through xp_cmdshell and parse the output. But given:
Yes, I have that in VB Script which should be converted to a SP as we are migrating from SQL Server to Oracle so that it will be easy to handle the same. Otherwise, I couldn't feel such a pain.
That solution will not be portable. You will have to redo solution again when you are on Oracle.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Saturday, January 21, 2012 11:58 PM
Oh ! So, can't I do that using T-SQL?
Correct. Well, run DIR through xp_cmdshell and parse the output. But given:
Yes, I have that in VB Script which should be converted to a SP as we are migrating from SQL Server to Oracle so that it will be easy to handle the same. Otherwise, I couldn't feel such a pain.
That solution will not be portable. You will have to redo solution again when you are on Oracle.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
xp_GetFileDetails is doing the job on my work machine (SQL Server 2000). However, it doesn't on my personal machine (SQL Server 2008 R2).
Here is the screenshot of my work machine:
Now, my question is how to verify the condition in IF condition for the file size by using the result set.
Well, no need to worry about Oracle for now. All they need is get the same functionality of DTS Packages by using T-SQL SPs.
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Sunday, January 22, 2012 12:40 PM
xp_GetFileDetails is doing the job on my work machine (SQL Server 2000). However, it doesn't on my personal machine (SQL Server 2008 R2).
So instead of using a DTS package, which is supported, documented and well-known technology, if outdated, you instead intend to use an undocumented and unsupported extended stored procedure?
If you change plans and decide to migrate to SQL 2008 or SQL 2012, you will still be able to use your DTS packages (well, at least in SQL 2008), and you can convert them to SSIS packages. But the extended procedure isn't in SQL 2008, as you have noticed, so you would have to find a replacement.
I don't know who came up with this idea, but you should tell that person that it was a bad idea, and that the project should be abandoned, before your organisation wastes any more money on this.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Sunday, January 22, 2012 3:01 PM
xp_GetFileDetails is doing the job on my work machine (SQL Server 2000). However, it doesn't on my personal machine (SQL Server 2008 R2).
So instead of using a DTS package, which is supported, documented and well-known technology, if outdated, you instead intend to use an undocumented and unsupported extended stored procedure?
If you change plans and decide to migrate to SQL 2008 or SQL 2012, you will still be able to use your DTS packages (well, at least in SQL 2008), and you can convert them to SSIS packages. But the extended procedure isn't in SQL 2008, as you have noticed, so you would have to find a replacement.
I don't know who came up with this idea, but you should tell that person that it was a bad idea, and that the project should be abandoned, before your organisation wastes any more money on this.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
I do understand that this SP doesn't work on SQL 2008 and its an undocumented one. They couldn't upgrade to SQL Server 2008 or 2012 as they want to migrate to a different kind of RDBMS like Oracle.
For now can you please let me know how can I pull only the file size from the above query in order to check the condition?
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Sunday, January 22, 2012 5:11 PM
I do understand that this SP doesn't work on SQL 2008 and its an undocumented one. They couldn't upgrade to SQL Server 2008 or 2012 as they want to migrate to a different kind of RDBMS like Oracle.
They? Who are they? And why are "they" not "we"?
I don't know if you are employed or a consultant, but in either case, I would suggest that it is your obligation as the technical savvy in the organisation give your strong advice to abandon the project.
For now can you please let me know how can I pull only the file size from the above query in order to check the condition?
No, I refuse to help with something I consider to be a bad idea. I don't know about this extended stored procedure, and I cannot vouch for it. I have very clearly given my advice what you should do.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Sunday, January 22, 2012 6:01 PM
I do understand that this SP doesn't work on SQL 2008 and its an undocumented one. They couldn't upgrade to SQL Server 2008 or 2012 as they want to migrate to a different kind of RDBMS like Oracle.
They? Who are they? And why are "they" not "we"?
I don't know if you are employed or a consultant, but in either case, I would suggest that it is your obligation as the technical savvy in the organisation give your strong advice to abandon the project.
For now can you please let me know how can I pull only the file size from the above query in order to check the condition?
No, I refuse to help with something I consider to be a bad idea. I don't know about this extended stored procedure, and I cannot vouch for it. I have very clearly given my advice what you should do.Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
So, you want me to either upgrade to 2008 or abandon the project? :(
One last question, before I try to do the above, can I create a partition in a Cube using T-SQL and incrementally update all the dimensions of the cube?
Thanks
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Sunday, January 22, 2012 8:53 PM
Well I will explain right from the beginning. I need to setup a secure transfer of a log file from the Unix server to a Database server. As a part of that I should create a batch file for copying the commands into the batch file. But, before copying that I need to check whether the batch file is empty? If not I should throw an error email or else should copy the commands into the batch file.
This has nothing to do with a relational database system (regardless of brand or version).
This question should probably be moved to a C# forum or any other application language forum, because SQL Server is utterly the wrong tool for this job.
You are not trying to write malware to exploit an SQL Injection vulnerability, are you?
Gert-Jan
Sunday, January 22, 2012 9:17 PM
Well I will explain right from the beginning. I need to setup a secure transfer of a log file from the Unix server to a Database server. As a part of that I should create a batch file for copying the commands into the batch file. But, before copying that I need to check whether the batch file is empty? If not I should throw an error email or else should copy the commands into the batch file.
This has nothing to do with a relational database system (regardless of brand or version).
This question should probably be moved to a C# forum or any other application language forum, because SQL Server is utterly the wrong tool for this job.
You are not trying to write malware to exploit an SQL Injection vulnerability, are you?
Gert-Jan
No, I am not. I am currently using ActiveX script for this, and just wanted to do the same using T-SQL.
Regards
Bangaaram
Known is a DROP, Unknown is an OCEAN.
Sunday, January 22, 2012 10:59 PM
So, you want me to either upgrade to 2008 or abandon the project? :(
I don't care whether you stay with SQL Server or migrate to Oracle. It would be highly unprofessional of me to have any such opinions.
But I certainly strongly recommend that you abandon or redefine your project. That is, if you want to replace DTS with something else, that's alright. But it's not alright to put things in the database engine that does not belong there.
One last question, before I try to do the above, can I create a partition in a Cube using T-SQL and incrementally update all the dimensions of the cube?
I have already said that I know nothing about Analysis Server. But when I think of it, I don't think you can do this in SQL 2000. It sounds like you need to run some management commands in AS, and while you can set up a linked server to AS (I believe), in SQL 2000 you can only use a linked server for basic DML. It's a different thing in SQL 2005 and later where you can use EXEC() AT to run a command on a linked server.
But you should ask in an AS forum. Maybe they will tell you the same thing as I have been telling you: T-SQL is the wrong place.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Sunday, January 22, 2012 11:47 PM
So, you want me to either upgrade to 2008 or abandon the project? :(
I don't care whether you stay with SQL Server or migrate to Oracle. It would be highly unprofessional of me to have any such opinions.
But I certainly strongly recommend that you abandon or redefine your project. That is, if you want to replace DTS with something else, that's alright. But it's not alright to put things in the database engine that does not belong there.
One last question, before I try to do the above, can I create a partition in a Cube using T-SQL and incrementally update all the dimensions of the cube?
I have already said that I know nothing about Analysis Server. But when I think of it, I don't think you can do this in SQL 2000. It sounds like you need to run some management commands in AS, and while you can set up a linked server to AS (I believe), in SQL 2000 you can only use a linked server for basic DML. It's a different thing in SQL 2005 and later where you can use EXEC() AT to run a command on a linked server.
But you should ask in an AS forum. Maybe they will tell you the same thing as I have been telling you: T-SQL is the wrong place.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Thanks for your patience and valuable time for me Erland. I really appreciate it.
Cheers !
Bangaaram
Known is a DROP, Unknown is an OCEAN.