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 sum the time and place at the last registry

Hi,

I have the following problem... In my table I store calling registries...and when a call is transfered to another branch the last brach have to receive all the time...

Só I have the following table

Code Snippet

CREATE TABLE [BILHETES_PROCESSAR] (
[COD_CLIENT] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[DATE_TIME] [datetime] NOT NULL ,
[NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[BRANCH] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO_ORIGINAL] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,-- Tells if is a originate call "2" or received a tranfer "6"
[TRONCO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , -- The port of the router through the call have been made
[TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that is going to receive this call
[ORIGEM_TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that sent the call

[VALOR_TEMPO] [money] NULL ,
[ID_TRANSF] [uniqueidentifier] NULL ,--ID for the branch that originated the call
[ID_REGISTRO] [uniqueidentifier] NOT NULL --ID of the registry
) ON [PRIMARY]
GO

For this table I can have the following data

Code Snippet

1002 2007-07-30 17:52:34.000 01191084426 236 2 2 9 210 NULL 15.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4
1002 2007-07-30 17:53:17.000 01191084426 210 2 6 9 214 236 63.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 D680C38D-C217-461A-8207-9FC367FBB51C
1002 2007-07-30 17:54:30.000 01191084426 214 2 6 9 NULL 210 50.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 24B3073B-F45F-4E87-9796-55F37847473D

So I got this results running this query..

Code Snippet

UPDATE P SET P.TRANSF=P2.BRANCH,
P.ID_TRANSF=
CASE
WHEN P.TIPO_ORIGINAL = '2' THEN P.ID_REGISTRO
WHEN P2.ID_TRANSF IS NOT NULL THEN P2.ID_TRANSF
ELSE '1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'
END
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P.TRANSF IS NULL

UPDATE P2 SET P2.ORIGEM_TRANSF=P.BRANCH, P2.ID_TRANSF=
CASE
WHEN P.ID_TRANSF IS NOT NULL THEN P.ID_TRANSF
ELSE P.ID_REGISTRO
END
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P2.ORIGEM_TRANSF IS NULL

UPDATE P2 SET P2.ID_TRANSF=P.ID_TRANSF
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P2.ID_TRANSF='1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'

This query has only one problem if the user has made a 4o transfer th2 two last registry will have wrong "transf" and "origem_transf" values... but this is not my big problem...

The big problem is that I need to sum all it "valor_tempo" put it on the last registry... that is the one with the code "24B3073B-F45F-4E87-9796-55F37847473D" and then set the "valor_tempo" to 0 to the other registry...

Did you get the ideia ?

Thanks

Hi,

I solved the problem ... But it's taking tooooooooooo much time...like 7 hours to run on all registries...is there a faster way to do that ?

Code Snippet

DECLARE @.REGS_TRANSF as UNIQUEIDENTIFIER
DECLARE @.REG_ATUALIZAR as UNIQUEIDENTIFIER
DECLARE @.VALOR_TEMPO_TOTAL AS MONEY
DECLARE CUR_REGTRANSF CURSOR FOR SELECT ID_TRANSF FROM BILHETES_PROCESSAR WHERE ID_TRANSF IS NOT NULL GROUP BY ID_TRANSF

OPEN CUR_REGTRANSF

FETCH NEXT FROM CUR_REGTRANSF INTO @.REGS_TRANSF
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.REG_ATUALIZAR=(SELECT ID_REGISTRO FROM BILHETES_PROCESSAR WHERE ID_TRANSF=@.REGS_TRANSF AND TRANSFERIDO IS NULL)
SET @.VALOR_TEMPO_TOTAL=(SELECT SUM(VALOR_TEMPO) FROM BILHETES_PROCESSAR WHERE ID_REGISTRO <> @.REG_ATUALIZAR AND ID_TRANSF=@.REGS_TRANSF)
UPDATE BILHETES_PROCESSAR SET VALOR_TEMPO=VALOR_TEMPO+@.VALOR_TEMPO_TOTAL WHERE ID_REGISTRO=@.REG_ATUALIZAR
UPDATE BILHETES_PROCESSAR SET VALOR_TEMPO=0 WHERE ID_REGISTRO <> @.REG_ATUALIZAR
FETCH NEXT FROM CUR_REGTRANSF INTO @.REGS_TRANSF
END

CLOSE CUR_REGTRANSF
DEALLOCATE CUR_REGTRANSF

Here is the code that I used....some fields has diff names...cause we changed...

How to stored a stored procedures results into a table

Hi, How can I store a stored procedure's results(returning dataset) into
a table?

Bob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Bob James" <BJ@.system.com> wrote in message news:41ed4eb1$1_2@.127.0.0.1...
> Hi, How can I store a stored procedure's results(returning dataset) into
> a table?
> Bob
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

insert into dbo.MyTable
exec dbo.MyProc @.parm1 = 'foo'

Where dbo.MyTable already exists and matches the structure (columns and data
types) of the procedure's result set. But there are a number of issues with
this approach:

http://www.sommarskog.se/share_data.html#INSERTEXEC

If you read the whole article you might find one of the other approaches
suits your needs better anyway, so you can avoid those issues.

Simon

How to store/retrieve image/file in sql db

Hi Guys,
I want to employee details in one table along with his photograph and resume
.
How can I achieve this? I'm fresher and I don't have any basic idea about th
is.
Can somebody lead me to a worked out example.
If appropriate how to use WRITETEXT/READTEXT to achieve this task?
thanks in advance.
M.L.SrinivasYou can start here:
http://support.microsoft.com/kb/308042
or
You can use TextCopy.exe in the \bin folder.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
news:9fa72100.0502102147.1a18787@.posting.google.com...
> Hi Guys,
> I want to employee details in one table along with his photograph and
> resume.
> How can I achieve this? I'm fresher and I don't have any basic idea about
> this.
> Can somebody lead me to a worked out example.
> If appropriate how to use WRITETEXT/READTEXT to achieve this task?
> thanks in advance.
> M.L.Srinivas|||I have gone threw this tutorial. But when I retrieve the image from the DB.
The image is only 1B, and I cannot open it. The filename is correct.
Whats wrong here?
-- Kenneth
"oj" wrote:

> You can start here:
> http://support.microsoft.com/kb/308042
> or
> You can use TextCopy.exe in the \bin folder.
> -- OJ: TEXTCOPY example
> -- Loading files into db &
> -- exporting files out to folder
> --
> --
> --TEXTCOPY IN
> --
> --create tb to hold data
> create table tmp(fname varchar(100),img image default '0x0')
> go
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass'
> --specify desired folder
> set @.path='c:\winnt'
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> --insert filenames into tb
> insert tmp(fname)
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> go
> select * from tmp
> go
> --
> --TEXTCOPY OUT
> --
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass,'
> --specify desired output folder
> set @.path='c:\tmp'
> set @.sql='md ' + @.path
> --create output folder
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> exec master..xp_cmdshell @.sql
> go
> drop table tmp
> go
>
> --
> -oj
>
> "M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
> news:9fa72100.0502102147.1a18787@.posting.google.com...
>
>

How to store/display more than one tables/recordset

see the follow code:
can return more than one recordset/table
the datagrid can show more than one table at a time.
1.the problem is how to store more than one table in a dataset at a time.
2.see sql(2),sql(3), the new table 't1' is empty, after exec sql(3) will meet a "System.Data.OleDb.OleDbException",the sql(2) can pass without return recordset, but sql(3) return a null recordset(maybe). What should I do.
thanks.

Public Function m_execsql()
Dim connStr As String
connStr = "User ID=sa;Password=sa;Initial Catalog=Northwind;Provider=""SQLOLEDB.1"";Data Source=MINGLEI"
conn = New System.Data.OleDb.OleDbConnection(connStr)
conn.Open()
Dim sql(3) As String
sql(0) = "select * from products;"
sql(1) = "select * from orders;"
sql(2) = "create table t1;"
sql(3) = "seletc * from t1;"
Dim i As Integer
For i = 0 To sql.GetUpperBound(0) - 1
Try
da = New OleDb.OleDbDataAdapter(sql(i), conn)
Catch e As Exception
MessageBox.Show(e.Message)
End Try
'Try
ds = New DataSet("dsTable")
dt = New DataTable
da.Fill(ds)
'grd.SetDataBinding(ds, i.ToString)
grd.DataSource = dt
'Catch eds As Exception
' MessageBox.Show(eds.Message)
'End T
Next
conn.Close()
conn = Nothing
End Functioniqueen, you can use multiple data adapters to fill one dataset or if your sql statement returned more than one result set.

Here's more from MS KB:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpopulatingdatasetfromdataadapter.asp

You may want post in .Net section too...

How to store Zero length strings, numbers, dates?

I am used to storing Null when a value does not exist. I have been told to
store "zero length strings" in some cases instead. Fine . . .
I do not know how to generate a "zero length string" or a "zero length
number" or anything else "zero length." But I suspect it is simple. Can you
provide a few simple examples?
Thanks
Michaelset columnName = '' (that's 2 single quotes)
for a zero length string.
If the column is a character data type, no problem.
If it's an Int though, the column will return a 0, not necessarily what you
want.
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>I am used to storing Null when a value does not exist. I have been told to
> store "zero length strings" in some cases instead. Fine . . .
> I do not know how to generate a "zero length string" or a "zero length
> number" or anything else "zero length." But I suspect it is simple. Can
> you
> provide a few simple examples?
> Thanks
> Michael
>|||Why are you told to store zero length string? Null is a perfectly good value
especially for cases where the value is unknown.
Anyway, zero length is equivalent to "".
e.g.
create table tb(abc varchar(10) not null)
insert tb values('abc')
insert tb values('')
insert tb values('def')
-oj
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>I am used to storing Null when a value does not exist. I have been told to
> store "zero length strings" in some cases instead. Fine . . .
> I do not know how to generate a "zero length string" or a "zero length
> number" or anything else "zero length." But I suspect it is simple. Can
> you
> provide a few simple examples?
> Thanks
> Michael
>|||I prefer storing Nulls, but that's me. An empty string is typically designat
ed
by two single quotes like so:
Create Table Foo
(
Bar VarChar(10)
)
Insert Foo (Bar) Values('')
There is no such thing as a "zero length number." If the value is a number,
then
it must have a numerical representation or be null. There is nothing in betw
een.
If the value is a string that has a number in it (e.g. '0' as opposed to 0),
then it is obviously the rules above apply.
Thomas
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>I am used to storing Null when a value does not exist. I have been told to
> store "zero length strings" in some cases instead. Fine . . .
> I do not know how to generate a "zero length string" or a "zero length
> number" or anything else "zero length." But I suspect it is simple. Can y
ou
> provide a few simple examples?
> Thanks
> Michael
>|||Another thing:
A zero length string in a date column will also not work as you think.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:%23T4$yKOTFHA.2680@.tk2msftngp13.phx.gbl...
> set columnName = '' (that's 2 single quotes)
> for a zero length string.
> If the column is a character data type, no problem.
> If it's an Int though, the column will return a 0, not necessarily what
> you want.
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>|||Null is a perfectly good value *only* for cases where the value is unknown
:)
The OP said that he was told to store zero-length strings in "some cases
instead." This tells me that in some instances they recognize that the
value is not "unknown", but rather is is "known to be nothing".
Middle names are a perfect example. If you don't know someone's middle
name, you could use NULL to indicate it is unknown. If you know that the
person has *no* middle name, you could use '' to indicate that this person
has no middle name, and that you recognize this fact.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23$IbhMOTFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Why are you told to store zero length string? Null is a perfectly good
> value especially for cases where the value is unknown.
> Anyway, zero length is equivalent to "".
> e.g.
> create table tb(abc varchar(10) not null)
> insert tb values('abc')
> insert tb values('')
> insert tb values('def')
> --
> -oj
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>|||He also says that they want to store zero-length strings in numeric and date
columns.
That one I really don't get.
Even if you could store it, what would a zero-length string mean in these
cases?
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%235Ak%23UOTFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Null is a perfectly good value *only* for cases where the value is unknown
> :)
> The OP said that he was told to store zero-length strings in "some cases
> instead." This tells me that in some instances they recognize that the
> value is not "unknown", but rather is is "known to be nothing".
> Middle names are a perfect example. If you don't know someone's middle
> name, you could use NULL to indicate it is unknown. If you know that the
> person has *no* middle name, you could use '' to indicate that this person
> has no middle name, and that you recognize this fact.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23$IbhMOTFHA.3544@.TK2MSFTNGP12.phx.gbl...
>|||Some people store dates in CHAR columns. Not something I would recommend,
but to each his/her own. The only other way around storing NULLs in numeric
and DATETIME columns would be to define a value that is out of range for
your purposes. For instance "9999-12-31" for an out of range date. Of
course then you introduce the Y10K problem which will have your great,
great, great, great, great (x 100's) grandchildren pulling out their hair
and cursing the day you were born...
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:e$MCzbOTFHA.2424@.TK2MSFTNGP09.phx.gbl...
> He also says that they want to store zero-length strings in numeric and
> date columns.
> That one I really don't get.
> Even if you could store it, what would a zero-length string mean in these
> cases?
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%235Ak%23UOTFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||I would like to thank everyone who responded!
The individual who told me store "zero length strings" feels this "saves
space" and is "more efficient" than storing NULL. I know there are
length-indiocators and Null-indicators stored on the database page but I
don't exactly know their behaviors. My position has always been that storin
g
NULL is way less than 1% of the space normally used by data, so don't worry
about it. Am I overlooking something "material?"
Michael
"oj" wrote:

> Why are you told to store zero length string? Null is a perfectly good val
ue
> especially for cases where the value is unknown.
> Anyway, zero length is equivalent to "".
> e.g.
> create table tb(abc varchar(10) not null)
> insert tb values('abc')
> insert tb values('')
> insert tb values('def')
> --
> -oj
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:60BEB714-6B32-473A-8CD6-9036620F3892@.microsoft.com...
>
>|||zero-length neurons?
"Raymond D'Anjou" wrote:

> He also says that they want to store zero-length strings in numeric and da
te
> columns.
> That one I really don't get.
> Even if you could store it, what would a zero-length string mean in these
> cases?
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%235Ak%23UOTFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
>

How to store very large document data?

Dear all,

I am facing a problem while storing Very large document data to a single image field in SQL Server 2000. It takes very much time for documents in size upto 10 MB. And its not possible to store data more than 10 MB. If I try it gives "Connection Write (Wrapper Write())" message with state 01000 and native 4.

I have used SQLPutData and SQLParamData for this purpose.

Is there any efficient way to do this? My concern is for less time.

Thanks in advance,
AvneeshI need urgent help regarding above problem. Would somebody please send some suggestions ASAP.

Thanks in advnace,
Avneesh

how to store values during dataflow

The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.

How can i get a value that was calculated before? Can i store it in a variable?

Many Thanks!

On the Control Flow add a 'Execute SQL Task' which has something like SELECT MAX(ID) AS MAXID FROM YOURTABLE, store the result into a variable.

Do your inserts, then SELECT * FROM YOURTABLE WHERE ID > YOURVARIABLE

|||

Thanks Paul,

The only way to store this results on a variable is from an 'Execute SQL Task'? can i do the same from either a OLEDB Command or Agregate Transformation?

|||You can use a script component to store values in a variable inside a data flow. You have to write it in the PostExecute method, though, which runs after all the rows pass thorugh it. However, I don't know if that is the best approach, given the scenario you are describing. But, ultimately, it's your call.|||

Thank you very much jwelch, ill try it!

How to store uploaded files in an SQL database?

Hi Friends

Can anyone tell me how to store uploaded files from the user to the SQL database and later give an option of downloading the file probably in the form of a GridView control. How do we write code to allow the user to download the uploaded files. I have written code to upload and store the files into a folder, however, I'm not able to collect data from the folder later and the folder is only accessible offline using the server system which I do not want to happen.

Can anyone help me with this?

Thanks

This should get you what you need:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414

Try using google next time

How to store this string

Hello,
I have string that I need to store in a database, but I don't know exactly
how. These strings could look like this:
A001111105110416352489
I parse the string through a RegEx looking like this:
^([A])(\d{3})([0|1]{3})([0|1])(\w*)
A = Mode, must be A in this case
001 = Component address
111 = 3-button status, can be 000, 001, 010, 100, 101, 110, 011 etc...
0 = Can be 0 or 1
05110416352489 = If above is 1, then this is a barcode, otherwise this isn't
sent.
Currently I store the above values in a field each. But the problem is that
I would like to store each button status separately and there might be
4-buttons, 5-buttons and so on in the future. I don't believe it's a good
idea to store each digit as a new field, but is there another way?
Here's the table:
CREATE TABLE [Actions](
[action_id] [int] IDENTITY(1,1) NOT NULL,
[component_address] [nvarchar](10) NOT NULL,
[time_stamp] [datetime] NOT NULL,
[barcode] [nvarchar](50) NULL,
[action_mode] [nvarchar](1) NOT NULL,
[button_status] [nvarchar](10) NULL,
CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED
(
[action_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Thanks!
Cheers,
JonahI don't really understand what the button status thing represents in the
"real world", so it's hard to say whether storing them seperately is a
good idea or a bad idea. But if you do store the combined status of the
button(s) in a single column, you could have user defined functions to
pull out individual states (and add them to the table as computed
columns, or just use them when you need them).|||Sorry, I noticed that as well when reading my question again...
The 3-button status represents a component in the real-world system with
three buttons on it. When a button is pushed (say the 3rd one), it reports
001. When released 000. If I push the first, keeps it down and press the
third button, it reports 101, and when releasing them both 100 (or 001
depending on what was released first) and finally 000.
Since we track the time for each action on the component, I would sometimes
like to know for how long certain buttons has been pushed. I would also like
to pull statistics for a separate button (say the 2nd one) - how often is it
pushed, what time of the day etc.
I hope this gives some information about the real-world representation.
Cheers,
Jonah|||Are Defined Functions the deal here?
/Jonah
"Jonah Olsson" <jonah@.IHATESPAM.com> skrev i meddelandet
news:uiW%23NEvhGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Sorry, I noticed that as well when reading my question again...
> The 3-button status represents a component in the real-world system with
> three buttons on it. When a button is pushed (say the 3rd one), it reports
> 001. When released 000. If I push the first, keeps it down and press the
> third button, it reports 101, and when releasing them both 100 (or 001
> depending on what was released first) and finally 000.
> Since we track the time for each action on the component, I would
> sometimes like to know for how long certain buttons has been pushed. I
> would also like to pull statistics for a separate button (say the 2nd
> one) - how often is it pushed, what time of the day etc.
> I hope this gives some information about the real-world representation.
> Cheers,
> Jonah
>|||Jonah Olsson (jonah@.IHATESPAM.com) writes:
> Currently I store the above values in a field each. But the problem is
> that I would like to store each button status separately and there might
> be 4-buttons, 5-buttons and so on in the future. I don't believe it's a
> good idea to store each digit as a new field, but is there another way?
> Here's the table:
> CREATE TABLE [Actions](
> [action_id] [int] IDENTITY(1,1) NOT NULL,
> [component_address] [nvarchar](10) NOT NULL,
> [time_stamp] [datetime] NOT NULL,
> [barcode] [nvarchar](50) NULL,
> [action_mode] [nvarchar](1) NOT NULL,
> [button_status] [nvarchar](10) NULL,
> CONSTRAINT [PK_Actions] PRIMARY KEY CLUSTERED
> (
> [action_id] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
The normal way would be to replace button_status with
CREATE TABLE ButtonStatus (
action_id int NOT NULL,
button_no tinyint NOT NULL,
button_status bit NOT NULL,
CONSTRAINT pk_ButtonStatus PRIMARY KEY(action_id, button_no),
CONSTRIANT fk_ButtonStatus_action FOREIGH KEY (action_id)
REFERENCES Atcions(actio_id)
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> The normal way would be to replace button_status with
> CREATE TABLE ButtonStatus (
> action_id int NOT NULL,
> button_no tinyint NOT NULL,
> button_status bit NOT NULL,
> CONSTRAINT pk_ButtonStatus PRIMARY KEY(action_id, button_no),
> CONSTRIANT fk_ButtonStatus_action FOREIGH KEY (action_id)
> REFERENCES Atcions(actio_id)
> )
Ah, good one! So button_no is the button "position" from left to right? 0 1
2?
/Jonah|||Jonah Olsson (jonah@.IHATESPAM.com) writes:
> Ah, good one! So button_no is the button "position" from left to right? 0
> 1 2?
Yes. As for 0-2 vs. 1-3 or right/left vs. left/right, that is up to you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jonah Olsson wrote:

> Currently I store the above values in a field each. But the problem is tha
t
> I would like to store each button status separately and there might be
> 4-buttons, 5-buttons and so on in the future. I don't believe it's a good
> idea to store each digit as a new field, but is there another way?
If you can be reasonably confident that there will never be more than
31 buttons, use an integer, and create defined functions that pull out
individual button statuses from an integer.
This would be a more compact representation than the fully normalized
representation that Erland suggested; his is better in some ways though
-- it would be straightforward to search by button number, for example
("find me a time when button 1 was pressed within 3 seconds of button
2, but button 4 hadn't been pressed in the previous 60 seconds and
button 3 wasn't pressed in the following 120 seconds"). That sort of
query would be pretty impossible *without* full normalization.|||Great! Thank you both.
Cheers,
Jonah

How to store the value of a particular column of result grid displayed by running a stored proce

Hello All,

There is a stored procedure sp_fulltext_service which takes the property to be changed or reset and the value of the property as the parameters.

sp_fulltext_service [ [@.action=] 'action' [ , [ @.value= ] value ] ]

If value is NULL, sp_fulltext_service returns the current settings.

On EXEC sp_fulltext_service 'upgrade_option' I get the following output :

Configuration Name Configuration Value

upgrade_option 4

Is there any way in which I can get the value of the ‘Configuration Value’ in a variable?

The return type of this stored procedure is ‘int’ which signifies success or failure.

Regards,

Rajesh

1. You can insert the output of a stored procedure into a table you create and select from that table.

2.

DECLARE @.return int

SELECT @.return =FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')

SELECT @.return

3. You can right-click a system stored procedure, click modify and see if you can rewrite its code as a select.

How to store the value of a particular column of result grid displ

Hello All,
There is a stored procedure sp_fulltext_service which takes the property to
be changed or reset and the value of the property as the parameters.
sp_fulltext_service [ [@.action=] 'action' [ , [ @.value= ] value ] ]
If value is NULL, sp_fulltext_service returns the current settings.
On EXEC sp_fulltext_service 'upgrade_option' I get the following output :
Configuration Name Configuration Value
upgrade_option 4
Is there any way in which I can get the value of the ‘Configuration Value’
in a variable?
The return type of this stored procedure is ‘int’ which signifies success or
failure.
Regards,
Rajesh
Look at the FULLTEXTSERVICEPROPERTY system function in Books Online.
Are you sure about the "upgrade_option" configuration setting? It's not
documented. Or is it?
ML
http://milambda.blogspot.com/
|||Thanks ML for replying back.
I'm sorry for the wrong option. But let's say i want to get the value of
load_os_resourses property of the fulltext service without using the
FULLTEXTSERVICEPROPERTY function.
Is there any T-SQL way for doing it by executing the sp_fulltext_service SP?
Regards,
Rajesh
"ML" wrote:

> Look at the FULLTEXTSERVICEPROPERTY system function in Books Online.
> Are you sure about the "upgrade_option" configuration setting? It's not
> documented. Or is it?
>
> ML
> --
> http://milambda.blogspot.com/
|||Why can't you use the function?
Look at the system procedure's definition - you can design your own based on
that.
ML
http://milambda.blogspot.com/

How to store the results of a query in a variable.

Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.

Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
Dim dr As DataRow
dr = dv.Table.Rows(0)

session("variable1") = dr("ColumnName1").ToString()
Dim strVar2 as string = dr("ColumnName2").ToString()

etc.

|||

Depending on what you are trying to store, there are several options. First, gather the data in a single variable. This can be a collection, array, some user-defined object or even a dataset.

Once you have the variable, you may do several things with it:

Session - If the resultsets will be different for each user, you may want to use session. Session variables exist for a certain amount of time, then disappear when that time expires. If the session expires, the variable will become NULL (Nothing in VB.NET).

eg: Session["DataSet"] = dataSet;

Cache - If the resultsets will be the same for each user, you may want to use cache. The Page.Cache object can store data for as long as you need it, only refreshing the data after an amount of time has elapsed or if an event triggers the cache to be emptied.

eg: Page.Cache.Add("DataSet", dataSet, cacheDependency,DateTime.Now.AddHours(5));

ViewState - If the resultsets will not be used for long, but need to be used across a postback, you may want to use viewstate. Viewstate is used to maintain state at the page level. That being said, it does not exist on another page, but does exist when you postback to the same page. Use this if you only need the data for a limited amount of time, but you plan on spending awhile on the same page.

eg: ViewState.Add("DataSet", dataSet);

Hope this helps.

How to store the result?

Hi,
If I want to store the result of "sp_spaceused XXX" into a table? What
shall I do?
Thanks a lot!
Michael
CREATE TABLE #foo
(
tablename SYSNAME,
[rows] INT,
reserved VARCHAR(32),
data VARCHAR(32),
index_size VARCHAR(32),
unused VARCHAR(32)
);
INSERT #foo EXEC sp_spaceused 'table_name';
SELECT * FROM #foo;
DROP TABLE #foo;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Michael" <michaelnx@.gmail.com> wrote in message
news:1176318829.693610.312820@.b75g2000hsg.googlegr oups.com...
> Hi,
> If I want to store the result of "sp_spaceused XXX" into a table? What
> shall I do?
> Thanks a lot!
> Michael
>
|||On Apr 11, 4:26 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> CREATE TABLE #foo
> (
> tablename SYSNAME,
> [rows] INT,
> reserved VARCHAR(32),
> data VARCHAR(32),
> index_size VARCHAR(32),
> unused VARCHAR(32)
> );
> INSERT #foo EXEC sp_spaceused 'table_name';
> SELECT * FROM #foo;
> DROP TABLE #foo;
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "Michael" <michae...@.gmail.com> wrote in message
> news:1176318829.693610.312820@.b75g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
Thank you very very much!!!

How to store the result?

Hi,
If I want to store the result of "sp_spaceused XXX" into a table? What
shall I do?
Thanks a lot!
MichaelCREATE TABLE #foo
(
tablename SYSNAME,
[rows] INT,
reserved VARCHAR(32),
data VARCHAR(32),
index_size VARCHAR(32),
unused VARCHAR(32)
);
INSERT #foo EXEC sp_spaceused 'table_name';
SELECT * FROM #foo;
DROP TABLE #foo;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Michael" <michaelnx@.gmail.com> wrote in message
news:1176318829.693610.312820@.b75g2000hsg.googlegroups.com...
> Hi,
> If I want to store the result of "sp_spaceused XXX" into a table? What
> shall I do?
> Thanks a lot!
> Michael
>|||On Apr 11, 4:26 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> CREATE TABLE #foo
> (
> tablename SYSNAME,
> [rows] INT,
> reserved VARCHAR(32),
> data VARCHAR(32),
> index_size VARCHAR(32),
> unused VARCHAR(32)
> );
> INSERT #foo EXEC sp_spaceused 'table_name';
> SELECT * FROM #foo;
> DROP TABLE #foo;
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "Michael" <michae...@.gmail.com> wrote in message
> news:1176318829.693610.312820@.b75g2000hsg.googlegroups.com...
>
>
>
>
> - Show quoted text -
Thank you very very much!!!

How to store the result?

Hi,
If I want to store the result of "sp_spaceused XXX" into a table? What
shall I do?
Thanks a lot!
MichaelCREATE TABLE #foo
(
tablename SYSNAME,
[rows] INT,
reserved VARCHAR(32),
data VARCHAR(32),
index_size VARCHAR(32),
unused VARCHAR(32)
);
INSERT #foo EXEC sp_spaceused 'table_name';
SELECT * FROM #foo;
DROP TABLE #foo;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Michael" <michaelnx@.gmail.com> wrote in message
news:1176318829.693610.312820@.b75g2000hsg.googlegroups.com...
> Hi,
> If I want to store the result of "sp_spaceused XXX" into a table? What
> shall I do?
> Thanks a lot!
> Michael
>|||On Apr 11, 4:26 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> CREATE TABLE #foo
> (
> tablename SYSNAME,
> [rows] INT,
> reserved VARCHAR(32),
> data VARCHAR(32),
> index_size VARCHAR(32),
> unused VARCHAR(32)
> );
> INSERT #foo EXEC sp_spaceused 'table_name';
> SELECT * FROM #foo;
> DROP TABLE #foo;
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "Michael" <michae...@.gmail.com> wrote in message
> news:1176318829.693610.312820@.b75g2000hsg.googlegroups.com...
>
> > Hi,
> > If I want to store the result of "sp_spaceused XXX" into a table? What
> > shall I do?
> > Thanks a lot!
> > Michael- Hide quoted text -
> - Show quoted text -
Thank you very very much!!!

How to Store System Variables in DB?

Hi,

I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @.IntDate and also changed the parameter name but the error was the same.

Please help how to write this System Variable value in to that table.

Regards,
Samy

In this situation I recommend using expressions rather than parameters. This article talks about expressions to store a SQL statement for an OLE DB Source component but this principle can apply to Execute SQL Task as well: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

I know Jamie is a fan of using expressions over parameters, but I disagree, and there are situations where parameters are still better. Security being the big issue with expressions.

Anyway, to get your parameter to work, make sure you have set mappend the parameter on the "Parameter Mapping" page of the Execute SQL Task. Select variable StartTime, direction is Input, Data Type is DATE, and Parameter name is 0.

For OLE-DB connections the parameter name is a zero based incrementing count of the parameters.

The Data Type is DATE, which seems rather confusing, as normally DBTIMESTAMP is the SSIS data type to use, however these are clearly not normal SSIS data types. We just do not have enough different types within SSIS!

|||

Dear Darren,

I did what you mentioned, but the package is not functioning:

SSIS package "HRM_tblParty.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at HRM_tblParty: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "HRM_tblParty.dtsx" finished: Failure.

This is my Query:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)

and I mapped the System::StartTime to Parameter0 (Type: Date and Direction: Input)

Pleae help,
Sassan

|||

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

|||

SamyLahur wrote:

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

The OLE DB Source component can be set by selecting a table, entering a SQL statement, or taking a SQL statement that is stored in a variable. You need to select the 3rd of these options.

-Jamie

|||

The parameter name should be 0, nothing else just the character for zero.

For the expression option you need to specify the variable name correctly-

@.[System::StartTime]

You will also need to convert to date time valeu to a string to do the concatenation-

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= '" + (DT_WSTR, 20 )@.[System::StartTime] + "'"

Note the quick and dirty conversion I have used is open to date format misinterpretation issues DMY, MDY etc. You should break it down and convert to component parts into a string as an unambiguous format. For SQL Server yyyymmdd is unambiguous. Of course using parameter support avoids this issue as it is treated as a date type all the way through.

|||

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

|||Have you read my last post? I appreciate that you may well have missed it due to the exceptionally well designed interface and threading capabilities of these forums.|||

SamyLahur wrote:

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

My first guess would be that you need to cast the date as a string.

There is no expression editor on variable expressions which is (and I'm being polite here) very very BAD. It will appear in SP1 but in the meantime here's a tip for you. Build your expression against the "Description" property of the package. This will enable you to use the expression editor to build your expression and validate it within there. When you have it working, copy and paste the expression into the appropriate place for the variable.

Hope that makes sense.

-Jamie

How to store S.P scripts in a folder?

Hi

I'm using Microsoft SQL server management studio express 2005.

I’ve a Database with some S.Ps.

How to store those S.P scripts in a folder?

Please advice

Thanks

They will all show up in the GUI under Programmability/Stored Procedures, though you can't actually create custom folders for stored procedures in SSMS although its been put forward as a suggestion for SQL2008.

If you want your sprocs well organised and shared with other people, you may want to consider a source control product such as sourcesafe or clearcase.

HTH!

|||

I know that it will show up in GUI under Programmability/S.P

my question is

I need to store the scripts of all those S.Ps in a Folder created on my Desktop

how?

please advise

Thanks

|||

Sorry, i misunderstood your question.

It seems the ability to script out objects in one easy hit has been missed out of SSMS. I would therefore suggest spending some time writing a little SMO script which you can use to script objects to file. Otherwise, its a case of doing each one individually i think.

Do some research into SMO for details on how to program with it.

HTH

|||

Hi vijay,

following the step as below:

1. Open SSMS

2. Login SQL Server

3. Make sure you can see the Database that you wanna Script SP.

4. Expand the node to Programmability \ Stored Procedure , then stay the focus on it.

5. After Step 4 you can see SPs list in the right-side window "Summary".

6.Click the first SP , press "Shift" without release.

7.choose the last one SP.

8.punch the mouse 's Right button ,choose the " Script Stored Procedure as →" "Create to →" to Flie.

9.Choose the folder that you wanna store those SPs and press "Save".

10. done.

Hoping it will help u.

Best Regrades,

Hunt.

|||

1.You can generate a script and save manually to a folder.

Thanks

Databasetimes.net

|||

Thanks Tsai

I followed these steps.

It’s creating one file with all S.P scripts.

Can’t we create a separate file for each & every S.P?

Please advice

Thanks

|||You have to write your own SMO script for this. Its simple; you can do it on your own favorite language(on .NET/C#/VB)

|||

Thanks Mani

I’m very much appreciate if you could provide that SMO script in C#

Thanks

Vijay

|||

hi vijay,

ok,I got your issue.

u don't have to make a SMO.

We can use SSMS DataBase Generate Script Wizard.

1.open SSMS

2.Login SQLServer

3.choose the Databse that you wanna Gen SPs, Stay Focus on the it.

4.With punch mouse Right button → Task → Generate Script

5.Generate Script Wizard will pop up → Choose "Next"

6.make sure Step "Select a DataBase" → that you select at step 3. and Choose "Next"

7.Step "Choose Script Option" → Choose "Next"

if you wanna drop SP before Re-Create SP,u have to Choose "Script Create" = True , "Script Drop" = True

8.Step "Choose Object Types" → Select "Stored Procedure" → Choose "Next"

9.Step "Choose Stored Procedure" → Click "Select All" → Choose "Next"

10.Step "Output Option" → Select " Script to File \ File to generate \ File per Object " → Choose "Next "

11 Step"Script Wizard Summary" → Choose "Finish"

12. Done.

Hoping this can help u.

Best Regrades,

Hunt.

|||

Vijay,

This problem has been fixed in SP2. Earlier to sp2 yes it was not possible to script objects in seperate file. Upgrade the system to SP2 and then try. you will be able to script each object in seperate file

Download sp to from here

http://support.microsoft.com/kb/913089/

Madhu

|||

Thanks a lot Tsai

I got my answer from your nice reply. This is what I'm expecting.

I marked it as Answer.

With Regards

Vijay

How to store S.P scripts in a folder?

Hi

I'm using Microsoft SQL server management studio express 2005.

I’ve a Database with some S.Ps.

How to store those S.P scripts in a folder?

Please advice

Thanks

They will all show up in the GUI under Programmability/Stored Procedures, though you can't actually create custom folders for stored procedures in SSMS although its been put forward as a suggestion for SQL2008.

If you want your sprocs well organised and shared with other people, you may want to consider a source control product such as sourcesafe or clearcase.

HTH!

|||

I know that it will show up in GUI under Programmability/S.P

my question is

I need to store the scripts of all those S.Ps in a Folder created on my Desktop

how?

please advise

Thanks

|||

Sorry, i misunderstood your question.

It seems the ability to script out objects in one easy hit has been missed out of SSMS. I would therefore suggest spending some time writing a little SMO script which you can use to script objects to file. Otherwise, its a case of doing each one individually i think.

Do some research into SMO for details on how to program with it.

HTH

|||

Hi vijay,

following the step as below:

1. Open SSMS

2. Login SQL Server

3. Make sure you can see the Database that you wanna Script SP.

4. Expand the node to Programmability \ Stored Procedure , then stay the focus on it.

5. After Step 4 you can see SPs list in the right-side window "Summary".

6.Click the first SP , press "Shift" without release.

7.choose the last one SP.

8.punch the mouse 's Right button ,choose the " Script Stored Procedure as →" "Create to →" to Flie.

9.Choose the folder that you wanna store those SPs and press "Save".

10. done.

Hoping it will help u.

Best Regrades,

Hunt.

|||

1.You can generate a script and save manually to a folder.

Thanks

Databasetimes.net

|||

Thanks Tsai

I followed these steps.

It’s creating one file with all S.P scripts.

Can’t we create a separate file for each & every S.P?

Please advice

Thanks

|||You have to write your own SMO script for this. Its simple; you can do it on your own favorite language(on .NET/C#/VB)

|||

Thanks Mani

I’m very much appreciate if you could provide that SMO script in C#

Thanks

Vijay

|||

hi vijay,

ok,I got your issue.

u don't have to make a SMO.

We can use SSMS DataBase Generate Script Wizard.

1.open SSMS

2.Login SQLServer

3.choose the Databse that you wanna Gen SPs, Stay Focus on the it.

4.With punch mouse Right button → Task → Generate Script

5.Generate Script Wizard will pop up → Choose "Next"

6.make sure Step "Select a DataBase" → that you select at step 3. and Choose "Next"

7.Step "Choose Script Option" → Choose "Next"

if you wanna drop SP before Re-Create SP,u have to Choose "Script Create" = True , "Script Drop" = True

8.Step "Choose Object Types" → Select "Stored Procedure" → Choose "Next"

9.Step "Choose Stored Procedure" → Click "Select All" → Choose "Next"

10.Step "Output Option" → Select " Script to File \ File to generate \ File per Object " → Choose "Next "

11 Step"Script Wizard Summary" → Choose "Finish"

12. Done.

Hoping this can help u.

Best Regrades,

Hunt.

|||

Vijay,

This problem has been fixed in SP2. Earlier to sp2 yes it was not possible to script objects in seperate file. Upgrade the system to SP2 and then try. you will be able to script each object in seperate file

Download sp to from here

http://support.microsoft.com/kb/913089/

Madhu

|||

Thanks a lot Tsai

I got my answer from your nice reply. This is what I'm expecting.

I marked it as Answer.

With Regards

Vijay

How to store Resume and cover letter into SQL Server?

Hi,

I am trying to create an asp.net web recruiting application for HR, which will give the users the ability to both copy/paste the Resume and cover letter in textbox and upload resume and cover letter, then submit it (which will be saved into SQL Server 2000 table).

I am thinking to save Resume and cover_letter as Image data type columns in SQL Server.

. Can someone give me a direction about how to save the uploaded resume and cover letter to table and if it's the easiest way to do it?

. What to deal with different formats of uploaded resumes? I hope to limit to only Word or HTML

. Since I also give user another option - copy/paste the resume and coverletter into a textboxes. Can I simply save the copy/paste resume and cover letter into text field column? Later, say, if any HR recruiter retrieve the text from database, will it concatonates everything together without line break?

Any ideas is appreciated.

You have two options use NChar 4000 or NVarchar 4000 and limit the size of the uploaded file so you can use ANSI SQL %Like% in your search or use NText and use Full Text index which is an add on to SQL Server dependent on Microsoft Search service with the Microsoft Catalog populated all the time to get search results. Full Text index uses Microsoft proprietry key word search as CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE. Run a search for Full Text index implementation in SQL Server BOL(books online) Hope this helps.|||

You can save the resume in the database in an image field

//Create a Buffer Stream to Hold the uploaded file
string FileName = GetFileName(txtFileUpload.PostedFile.FileName.ToString());
string FileType = txtFileUpload.PostedFile.ContentType.ToString();
int FileSize = int.Parse(txtFileUpload.PostedFile.ContentLength.ToString());
byte[] DocBuffer = new byte[FileSize];
Stream objSream;
objSream = txtFileUpload.PostedFile.InputStream;
objSream.Read(DocBuffer,0,FileSize);
//Pass this buffer as a paramter to a Stored procedure which will insert a new record into ur table
SqlParameter pcvContent = new SqlParameter("@.cvContent",SqlDbType.Image);
pcvContent.Direction = ParameterDirection.Input;
pcvContent.Value = cvContent;
dbCommand.Parameters.Add(pcvContent);
//======================================================================
to retrive the saved file from the database you have to do the following
do your select statment
Response.AddHeader("Content-Disposition","attachment; filename='"+ dr["FileName"].ToString() +"'");
Response.ContentType = dr["FileType"].ToString();
Response.BinaryWrite( (byte[]) dr["CvContent"]);
//=========================================================================
this the first solution.
The secound one is to create a resume builder.

|||Personally, I would consider storing BOTH the raw document in an IMAGEcolumn, and ripping the text of the document and storing that in a TEXTcolumn. This will allow you to set up a full-text index forsearching the resumes, while at the same time making it easy for usersto get the resumes back in whatever format they were submitted in.

|||

hi,

I need to do a keyword search in word and pdf documents. I tried using Index Server linked with SQL Server but its giving me many problems. So i'm planning to rip off the text from the word or pdf docs and store the text in a TEXT field as u said in one of the threads so that i can do a search easily.

Is it possible to read from a word or a pdf file and store it in a string. Sorry about contacting u directly

Thanks in Advance