Sql Stop Logging: code

Showing posts with label code. Show all posts

Friday, March 30, 2012

How to trap xp_cmdshell dtsrun error messages

Hi Here is my store procedure. The below code is not giving error at all when dts fails.
Can any one please help me how to trap xp_cmdshell dtsrun error messages
CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
exec USP_ReadVRUDirectory
set nocount on
DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by filelist
OPEN VRUCFileListCursor
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
WHILE @.@.FETCH_STATUS = 0
BEGIN
truncate table VRUSURVEYComments
set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +' e:\ftproot\vru\VRUComments.txt'
exec master..xp_cmdshell @.cmdstring
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyComments'
SELECT @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
Print @.Error
END
UPDATE VRUSURVEYComments SET [Filename] = @.filename -- where [Filename] is null
update VRUSURVEYComments set cur= 'T'
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyCommentsExport'
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
END
CLOSE VRUCFileListCursor
DEALLOCATE VRUCFileListCursor
Hi
Rather than using xp_cmdshell to rename the file try using something like
the following to change the name of the source file. Using the FSO you can
check errors more easily!
http://www.sqldts.com/default.aspx?200
http://www.sqldts.com/default.aspx?246
You should also check the return value from xp_cmdshell e.g
DECLARE @.cmd sysname, @.var sysname, @.stat int
SET @.var = 'Hello world'
SET @.cmd = 'echo ' + @.var + ' > G:\var_out.txt'
EXEC @.stat = master..xp_cmdshell @.cmd
PRINT '@.stat= ' + CONVERT(varchar,@.stat)
John
"Admin" <admin@.emoneylinks.com> wrote in message
news:12AE78DE-091E-4E43-BEAA-639F1EAA6E4C@.microsoft.com...
> Hi Here is my store procedure. The below code is not giving error at all
when dts fails.
> Can any one please help me how to trap xp_cmdshell dtsrun error messages
> CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
> exec USP_ReadVRUDirectory
> set nocount on
> DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
> DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
> SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by
filelist
> OPEN VRUCFileListCursor
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> truncate table VRUSURVEYComments
> set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +'
e:\ftproot\vru\VRUComments.txt'
> exec master..xp_cmdshell @.cmdstring
> exec master..xp_cmdshell 'dtsrun /S
WPCCP005DS /E /N VRUSurveyComments'
>
> SELECT @.Error = @.@.ERROR
> IF @.Error <> 0
> BEGIN
> Print @.Error
> END
>
> UPDATE VRUSURVEYComments SET [Filename] = @.filename --
where [Filename] is null
> update VRUSURVEYComments set cur= 'T'
> exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N
VRUSurveyCommentsExport'
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> END
> CLOSE VRUCFileListCursor
> DEALLOCATE VRUCFileListCursor
>
>

How to trap the results of constraints in SQL Server 2005from Visual studio C# Code?

Hi all,

Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000.

In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code?

Thanks

Tomy

Your best bet would be to have layered constraints. Your business logic should also make sure that the data being entered is between 1000 and 10000, so that the violation would never reach the database. Database constraints should be left in place for people who like to edit the database directly.

While you can get a violated constraint error from the database, I don't believe it would be phrased properly to display to your users, which would mean lots of parsing in order to make the error presentable and user-friendly. It would be better to use a range validator to prevent the user from entering incorrect information.

As for trapping the error, use Try/Catch blocks

try{}catch (System.Data.SqlClient.SqlException ex){// Handle SQL Exceptions here; // all sql exceptions fall under this exception type, // but the errorcode/errors will be different.}catch (Exception ex){// handle all other errors here.}

|||

Thanks a lot.

Wednesday, March 28, 2012

How to translate oracle decode

Hi,

How to translate oracle Decode without changed code I mean:

I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I

Wrote this function

select dbo.fnDecode( 1 , 2 , 3 )

first parameter always int, and the others parameters could be char, int or float.

I would like to test first value and return second or third value

-> sql_variant for all parameters, ok

but I can use + or -

I can't do this

select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )

If I put cast ok, but I is there another way, overload this call?

With clr doesn't work, because Sql Server doesn't accept function overload calls from C#

Any ideia?

cheers,

Any ideia?

Use explicit cast/convert for both operands?

|||There is no easy way to do what you are trying i.e., replacing Oracle's built-in DECODE function with TSQL/SQLCLR equivalent. The sql_variant approach will work to some extent but you will run into issues when you try to use operators on the value as you experienced. It will require explicit CASTING in lot of cases. And using TSQL UDF will introduce performance problems especially in DMLs if used incorrectly. SQLCLR approach doesn't work because we don't support overloaded methods. Best is to convert uses of DECODE to CASE expression. Note that CASE expression is actually ANSI SQL standard and more portable way to write SQL code. In fact, Oracle supports it from 7.3 or 8i so there is no reason to use DECODE in Oracle too. As stop gap measure, you could write different set of TSQL UDFs (decode, decode1, decode2 etc) each handling different data types. And when you hit performance problems with your queries/DML that uses these UDFs you can convert to CASE expression i.e., inline the code.|||I can't use cast/convert, because I never know what kind of value will be, so I dont know what kind of cast if to char or to int,float ....

How to translate into SSIS?

Hi everyone,

My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:

sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"

rs.execute sql

WHILE NOT RS.EOF

STUFF

ANOTHER SELECT AND ANOTHER LOOP

...

INSERT AND DELETE STUFF

END WHILE

Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.

I don't want to do cursors or something like that with T-SQL.

Thanks in advance for your inputs/help and regards,

Could you use nested FOREACH containers? Store your initial resultset in a variable, then iterate through that, executing the second SQL statement, storing the results in a variable, and using a second FOREACH to iterate that.|||

hi,

using ForEach Loop container on control flow, you mean?

should I use ForEach ADO enumerator, shouldn't?

tia

|||Yes, control flow. Yes, ADO recordset.|||Thank you. I'll test it|||

It's easier than I though. I'll use a Script Task on Control Flow task. Just that task,

I don't need nothing else

|||Could you share the script? Stripped of anything specific to your business, of course.

How to translate into SSIS?

Hi everyone,

My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:

sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"

rs.execute sql

WHILE NOT RS.EOF

STUFF

ANOTHER SELECT AND ANOTHER LOOP

...

INSERT AND DELETE STUFF

END WHILE

Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.

I don't want to do cursors or something like that with T-SQL.

Thanks in advance for your inputs/help and regards,

hi,

using ForEach Loop container on control flow, you mean?

should I use ForEach ADO enumerator, shouldn't?

tia

|||Yes, control flow. Yes, ADO recordset.|||Thank you. I'll test it|||

It's easier than I though. I'll use a Script Task on Control Flow task. Just that task,

I don't need nothing else

|||Could you share the script? Stripped of anything specific to your business, of course.

Monday, March 26, 2012

How to to develope a new PlugIN algorithm

I have a code for Nearest neighbour algorithm, I want to build a datamining algorithm using that code..

I have the following link that includes the source code for a sample plug-in algorithm written in C#.

(managed plug-in framework that's available for download here: )http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en#DMAPI.

But i am confused on where to insert my algorithm logic?

Hiya, see this sample plug-in algorithm download below. You should get the ideas for that. Hope it helps.

http://sqlserverdatamining.com/DMCommunity/_Downloads/2384.aspx

Regards,

|||

The managed plug-in framework comes with a step-by-step tutorial as well as with a CHM (help) file which should help.

The download pointed by Helen will give you more insight on how the plug-in architecture actually works (the managed plug-in is a wrapper on top of the COM architecture).

The step-by-step tutorial coming with the managed plug-in also links to a few other architecture overview articles.

Briefly, assuming you will start from the completed managed plug-in sample, you should:

- override the Algorithm's InsertCases method and the case processor's ProcessCase method to perform the processing

- override the Algorithm's Predict method to perform predictions

sql

Wednesday, March 7, 2012

how to store output of exec stmt in another variable?

I am trying to store the output/result of exec statement in another variable like @.b.

Code Snippet

declare @.a varchar(10)

declare @.b int

set @.a='2 * 3';

EXEC ('select ' + @.a)

in the above sample I need to store 6 in @.b, how?

Please advice

Thanks

You could use sp_executesql like:

declare @.res int /*?*/;

declare @.sql nvarchar(max);

set @.sql = N'SELECT @.res = ' + @.a; -- Protect against SQL injection by using QUOTENAME as appropriate etc...

exec sp_executesql @.sql, N'@.res int OUTPUT', @.res = @.res OUTPUT;

But why do you want to do this? What are you trying to achieve? In SQL Server 2005, you could store the formula in a table and evaluate using CLR logic. This will actually perform better for complex calculations and will not suffer from SQL Injection issues. Otherwise, you should try to avoid dynamic SQL as far as possible. It is easy to code but hard to get it right in terms of security, performance and manageability. The risks are too much if you miss anything.

|||

Thanks a lot Chandar, for your nice reply

I got my answer from your query

The reason why I want this one is

I calculated the product of some values in a Table's column using COALESCE & EXEC Statements. But I am unable to store that result in a variable.

But now I got it using exec sp_executesql etc…..

Once again Thanks

|||You don't need to use dynamic SQL to get the product of values. You can do that with simple expressions using SUM and LOG. Search in this forum for my posts on this topic - I have some links that point to sample code that you can use.

Friday, February 24, 2012

How to store HTML code in SQL server table

Hello,
I'm creating something like a web site builder in ASP.Net, and I need to
store an HTML code in the SQL server table. If my customer just enters an
HTML code, it is stored in the SQL server table like this: <table
cellSpacing="0" cellPadding="0" width="760"
border="0">, instead of <table cellSpacing="0" cellPadding="0"
width="760" border="0">
This significantly increases the size of the stored code.
Are there any conversion functions or something like this to avoid it? Where
this conversion should be done - in ASP.Net or SQL server? How do I convert
it back to create an HTML page?
Please note that I must use the Unicode data type - ntext, nchar etc, since
I'm using some Cyrillic characters.
I would appreciate your advice very much.
Thank you,
--
Peter AfoninPeter,
ntext in SQL Server will store Html markup just fine. You are encoding
your html before storing it in the database. Find out where you are
encoding the markup and change that code. Debug your code and make sure
that you are indeed sending pure html to the database. SQL Server will not
automatically encode html, so your code is doing it somewhere.
Best regards,
Jeffrey Palermo
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>|||What is the code you use to store the string you get in the SQL database?
"Peter Afonin" <pva@.speakeasy.net> a écrit dans le message de
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>
>|||As the other poster states, it is not SQL that is doing the encoding, it is
something else...
Also, if the html pages will be less than 4000 unicode characters, you may
prefer to strore the data in a varchar column.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Afonin" <pva@.speakeasy.net> wrote in message
news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm creating something like a web site builder in ASP.Net, and I need to
> store an HTML code in the SQL server table. If my customer just enters an
> HTML code, it is stored in the SQL server table like this: <table
> cellSpacing="0" cellPadding="0" width="760"
> border="0">, instead of <table cellSpacing="0"
cellPadding="0"
> width="760" border="0">
> This significantly increases the size of the stored code.
> Are there any conversion functions or something like this to avoid it?
Where
> this conversion should be done - in ASP.Net or SQL server? How do I
convert
> it back to create an HTML page?
> Please note that I must use the Unicode data type - ntext, nchar etc,
since
> I'm using some Cyrillic characters.
> I would appreciate your advice very much.
> Thank you,
> --
> Peter Afonin
>|||Thank you everyone for your suggestions.
So far when I use ntext it works OK, then I use the Literal control and
Server.Decode to write the data in ASP.Net.
Peter
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I'm creating something like a web site builder in ASP.Net, and I need to
> > store an HTML code in the SQL server table. If my customer just enters
an
> > HTML code, it is stored in the SQL server table like this: <table
> > cellSpacing="0" cellPadding="0"
width="760"
> > border="0">, instead of <table cellSpacing="0"
> cellPadding="0"
> > width="760" border="0">
> >
> > This significantly increases the size of the stored code.
> >
> > Are there any conversion functions or something like this to avoid it?
> Where
> > this conversion should be done - in ASP.Net or SQL server? How do I
> convert
> > it back to create an HTML page?
> >
> > Please note that I must use the Unicode data type - ntext, nchar etc,
> since
> > I'm using some Cyrillic characters.
> >
> > I would appreciate your advice very much.
> >
> > Thank you,
> >
> > --
> > Peter Afonin
> >
> >
>|||Wayne,
Maybe it's just a typo, but varchar cannot be properly used with unicode
and has a maximum length of 8000 characters. nvarchar has a limit of 4000
characters and should be used for all applications for maintenance purposes
(since most applications will have to support unicode in the future).
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> As the other poster states, it is not SQL that is doing the encoding, it
is
> something else...
> Also, if the html pages will be less than 4000 unicode characters, you may
> prefer to strore the data in a varchar column.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter Afonin" <pva@.speakeasy.net> wrote in message
> news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I'm creating something like a web site builder in ASP.Net, and I need to
> > store an HTML code in the SQL server table. If my customer just enters
an
> > HTML code, it is stored in the SQL server table like this: <table
> > cellSpacing="0" cellPadding="0"
width="760"
> > border="0">, instead of <table cellSpacing="0"
> cellPadding="0"
> > width="760" border="0">
> >
> > This significantly increases the size of the stored code.
> >
> > Are there any conversion functions or something like this to avoid it?
> Where
> > this conversion should be done - in ASP.Net or SQL server? How do I
> convert
> > it back to create an HTML page?
> >
> > Please note that I must use the Unicode data type - ntext, nchar etc,
> since
> > I'm using some Cyrillic characters.
> >
> > I would appreciate your advice very much.
> >
> > Thank you,
> >
> > --
> > Peter Afonin
> >
> >
>|||Peter, I'm glad you have it working now.
--
Best regards,
Jeffrey Palermo
Blog: http://dotnetjunkies.com/weblog/jpalermo
"Peter Afonin" <peter@.gudzon.net> wrote in message
news:OAMi7Mk1EHA.1124@.tk2msftngp13.phx.gbl...
> Thank you everyone for your suggestions.
> So far when I use ntext it works OK, then I use the Literal control and
> Server.Decode to write the data in ASP.Net.
> Peter
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eL$4fPh1EHA.4004@.tk2msftngp13.phx.gbl...
> > As the other poster states, it is not SQL that is doing the encoding, it
> is
> > something else...
> >
> > Also, if the html pages will be less than 4000 unicode characters, you
may
> > prefer to strore the data in a varchar column.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Peter Afonin" <pva@.speakeasy.net> wrote in message
> > news:eyXuQOa1EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > Hello,
> > >
> > > I'm creating something like a web site builder in ASP.Net, and I need
to
> > > store an HTML code in the SQL server table. If my customer just enters
> an
> > > HTML code, it is stored in the SQL server table like this: <table
> > > cellSpacing="0" cellPadding="0"
> width="760"
> > > border="0">, instead of <table cellSpacing="0"
> > cellPadding="0"
> > > width="760" border="0">
> > >
> > > This significantly increases the size of the stored code.
> > >
> > > Are there any conversion functions or something like this to avoid it?
> > Where
> > > this conversion should be done - in ASP.Net or SQL server? How do I
> > convert
> > > it back to create an HTML page?
> > >
> > > Please note that I must use the Unicode data type - ntext, nchar etc,
> > since
> > > I'm using some Cyrillic characters.
> > >
> > > I would appreciate your advice very much.
> > >
> > > Thank you,
> > >
> > > --
> > > Peter Afonin
> > >
> > >
> >
> >
>

How to store duration in database?

Hi all,

I want to find working duration between two datetimes in c#.
i'm using following code...

DateTime starttime = Convert.ToDateTime(Session["StartTime"]);
DateTime endtime = DateTime.Now;

TimeSpan duration = endtime - starttime;
DateTime period = new DateTime(duration.Ticks);

i want to store this duration in database through stored procedure, i've give datetime datatype to duration but it is giving error in conversion of TimeSpan to DateTime..

Please help... Thanks

You don't need to store this information in your database. You can retrieve this value by using DateDiff between two dates in SELECT statement, for example in seconds.

Datediff(second,startdate, enddate) as duration

Here is a link to how to get the duration and format it afterwards.

http://forums.asp.net/t/1185449.aspx

How to store .wav or .mp3 file in SQL Server 2000

Hi,

I have a problem to store .wav and .mp3 file to store in SQL Server
2000.

How can I store this?

Can any one give me code regarding this. I have little bit idea. But
I need some technical help in Code.

Thanks in advance,

PratikHere's some code to get you started:

CREATE TABLE MyMp3s
(
Mp3Image image
)
GO

CREATE PROC InsertMp3Image
@.Mp3Image image
AS
INSERT INTO MyMp3s VALUES(@.Mp3Image)
GO

// C# code snippet

Int32 m_MaxFileSize = 10240000;

SqlCommand storedProcedure =
new SqlCommand("InsertMp3Image" , SqlConnection);
storedProcedure.CommandTimeout = 30;
storedProcedure.CommandType = CommandType.StoredProcedure;

System.IO.BinaryReader binaryReader =
new BinaryReader(
new FileStream (@."C:\Mp3\MyMp3.mp3",
FileMode.Open,
FileAccess.Read));

byte[] buffer = binaryReader.ReadBytes(m_MaxFileSize);

binaryReader.Close();

storedProcedure.Parameters.Add( "@.Mp3Image", buffer );

storedProcedure.ExecuteNonQuery();

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Pratik Kothari" <pratik.kothari@.dadomain.com> wrote in message
news:d025254b.0311210330.1fcb8ef3@.posting.google.c om...
> Hi,
> I have a problem to store .wav and .mp3 file to store in SQL Server
> 2000.
> How can I store this?
> Can any one give me code regarding this. I have little bit idea. But
> I need some technical help in Code.
> Thanks in advance,
> Pratik|||pratik.kothari@.dadomain.com (Pratik Kothari) wrote in message news:<d025254b.0311210330.1fcb8ef3@.posting.google.com>...
> Hi,
> I have a problem to store .wav and .mp3 file to store in SQL Server
> 2000.
> How can I store this?
> Can any one give me code regarding this. I have little bit idea. But
> I need some technical help in Code.
> Thanks in advance,
> Pratik

You sure you want to? Why not store the file on the hard drive and
name of the file in the database?|||>>>>>
You sure you want to? Why not store the file on the hard drive and
name of the file in the database?
<<<<<

While this approach is much easier to implement, there is a host of
security and data integrity issues that vitiates its use.

1) What if someone deletes the file? You end up with a dangling
reference.

2) What if someone, innocently or otherwise, overwrites the file? Bad
voodoo.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanx Dan,

This is that what i want.

Pratik

Sunday, February 19, 2012

How to stop SP ?

My aims is ..i want the SP do nothing . even some one call the SP
in vb.net , I can use EXIT sub or exit fucntion , so. does any code i can
put to make sp do nothing (or comment all the code instead ')
Thanks
private function...
exit function
..my code
end privateHi
RETURN is what you are looking for.
CREATE PROCEDURE ABC
AS
--The SP will exit on the next line, reporting success
RETURN 0
--Other code goes here.
GO
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:#USWIilIFHA.1304@.TK2MSFTNGP10.phx.gbl...
> My aims is ..i want the SP do nothing . even some one call the SP
> in vb.net , I can use EXIT sub or exit fucntion , so. does any code i can
> put to make sp do nothing (or comment all the code instead ')
> Thanks
> private function...
> exit function
> ..my code
> end private
>

How to stop running DTS package if my view return zero record?

Hi everyone,

I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.

So.. I need help from you guys, cause I don't know How.

This is my DTS description.

My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.

Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.

BUT How? Can someone please help? Thanks

Kelvin,

This is an SSIS forum (not DTS).

Sorry.

|||Not sure, and I second Rafael's comment, but can you put in a row counter against the view and test it's result?|||

I did, but I don't know how to put it into a package.?

Please Help......

|||

Kelvin Y wrote:
I did, but I don't know how to put it into a package.?
Please Help......

If you are using SSIS, and not DTS, you can use an Execute SQL task in the control flow to query the view and capture the row count. (select count(*) from view) Then in the control flow you can test the variable that was previously populated with the Execute SQL task for a condition of != 0, and if so, continue processing.|||

OK. here's another idea....slightly different from Phil's. Give it a shot. (using SSIS again)

The SQL task has a property "ForceExecutionResult" that you can configure using exprssions. Get the count from the view exactly like Phil explained above but instead of using a conditional split, use the value to set the exprssion to Success or Failure.

You wont be able to log an approiate error message though.

P.S: For some reason the use of the word "e x p r e s s i o n" is causing my post to be truncated. So, had to use "exprssion"

|||

Thanks everyone, but I can't find the "ForceExecutionResult" property.

Can you tell me where is it?

Thanks

|||

If you dont have experience with exprssions, I'd suggest using Phil's approach.

But to answer your question, you can find the property under the "misc" category about 6th in the list.

|||Thanks everyone, it works now. Thanks for helping me out.|||

Kelvin Y wrote:
Thanks everyone, it works now. Thanks for helping me out.

Please mark this thread as answered.

How to stop a SSIS package execution from code?

Hi everyone,

After a Execute method I would need to stop a package but I don't know why:

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

I have a Events class (EventSSIS) which implements IDTSEvents and have an OnQueryCancel event but without parameters, such so:

Function OnQueryCancel() AsBooleanImplements IDTSEvents.OnQueryCancel

ReturnFalse

EndFunction

Let me know how to pass a boolean parameter because of I can't overloaded OnQueryCancel method

TIA-

I'm sorry, issue is solved.

happy coding,

How to start with crystal reports? Basic code needed.

Hi,
I am completely new to crystal reports. So, I need basic information to start with. I have created a simple report using Crystal Report XI. Now I would like to print it using VB 6 by clicking on a cmdPrint. Could somebody help me to get start?
Sweetie.goto FAQ section of www.vbcity.com and find CR articles|||goto FAQ section of www.vbcity.com and find CR articles

Thanks, Madhi.

Sweetie.

Friday, March 30, 2012

How to trap xp_cmdshell dtsrun error messages

How to trap the results of constraints in SQL Server 2005from Visual studio C# Code?

Wednesday, March 28, 2012

How to translate oracle decode

How to translate into SSIS?

How to translate into SSIS?

Monday, March 26, 2012

How to to develope a new PlugIN algorithm

Wednesday, March 7, 2012

how to store output of exec stmt in another variable?

Friday, February 24, 2012

How to store HTML code in SQL server table

How to store HTML code in SQL server table

How to store HTML code in SQL server table

How to store duration in database?

How to store .wav or .mp3 file in SQL Server 2000

Sunday, February 19, 2012

How to stop SP ?

How to stop running DTS package if my view return zero record?

How to stop a SSIS package execution from code?

How to start with crystal reports? Basic code needed.

Sql Stop Logging

Blog Archive

About Me