How to insert on table with a variable name

Here is the jist of what I want to do, but I know the syntax is wrong...please help

CREATE PROCEDURE mySP
(
@.tablename As varChar(50),
@.input_name As varChar(100)
)
As
INSERT @.tablename (name)
VALUES (@.input_name)

assume the table exists already, and has a 'name' field in it.

thanks guysKatie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

is there a way around using 'exec'?|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

Yea, I just know basics of stored procedures, I'd love to hear how this sort of thing is usually done.

How to insert multiple rows using stored procedure

How to insert multiple rows with using a single stored procedure and favourably as an atomic process?You need to inlude SAVE POINT in your T-SQL code so a rollback or interruption will not take the Transaction back to the beginnning. Like the sample below. Hope this helps.

SAVE TRANSACTION SavepointName
IF @.@.error= some Error
BEGIN
ROLLBACK TRANSACTION SavepointName
COMMIT TRANSACTION
END

Kind regards,
Gift Peddie|||You have a couple of options here:

1) created a delimited key,value pair and parse it in the proc
2) package the values as an xml chunk and use OPENXML to shred the doc and perform the insert

I prefer option 2.

And with regards to atomicity, you wrap 1 or 2 in a BEGIN TRAN, COMMIT or ABORT in the proc.|||Since I never used OPEN XML can you give me a link to a good tutorial how to pass xml from .net code to sql sp..

Thanks|||Have a look at the following article:

Decomposing with OpenXML

how to insert image

Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
RajeshHi Rajesh,

I am presently working on storing and retrieving image on SQL 2000 using VB6.

I would be very much grateful if you could pls send me codes u have that can hlp me.

Thanks a lot

prakash

NB my email is prak_07@.servihoo.com|||Originally posted by rajeshm
Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh

Hi There,

I would also be interested to have this stored procedure.

I would be greatful if you send it to me
Thanks

gunnar.westerberg@.iftech.se|||Rajesh I would also like to have the code and the SP for using the same in my project. please send me a copy also. My mail Id is dineshyadav@.yahoo.com. Thanks in Advance
Regards
Dinesh|||Can you post the Code ?

BTW what are you store in the DB ? the path to the Image ?

Thanks,
Eyal|||Hi Rajesh,
Does your SP supports storing upto 100 MB of data??
Please send me the code ...It would be of great help for me.
I am waiting for a long time for such help. I have my own one ...but it fails for 100 MB. Please send urs.

My E-mail id is avneesh@.newgen.co.in

Thanks in advance and hope u send it ASAP.

Avneesh|||Originally posted by rajeshm
Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh

Hai ...
Please send me your code too. Cause i need it for my next project.
thx

ps : My email desy_g@.hotmail.com|||hello rajesh
I want to store word documents and pictures in SQL server 2000.is it possible with SQL server2000.if please send me your code in
subyphilip@.indiatimes.com|||I have seen similar thread in other forums with no results for getting SP from the originator.

You can use REDADTEXT/WRITETEXT/UPDATETEXT to dealwith text/image datatypes and refer to books online for more information.

Also may check under Umachander's (http://www.umachandar.com/) homepage for the code.|||Please send me the code

My Email-id is mdabidullah@.indiatimes.com

Thanks in advance and hope u send it ASAP.

Abid

Originally posted by rajeshm
Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh|||This looks like a hot thread. I also have a similar problem - inserting into image columns from a script. Im interested in that code too. Cheers!|||I bet you will not get reply/mail from the originator about the code.
As referred you browse thru the Umachander's page and also search under Planet source code (http://www.planet-source-code.com) for similar issue, this will save your time and gives you more resources.|||Thanks for that link. Once i come up with the soln to ma kinda of problem, i will share it with you forum members.|||Appreciate your spirit.|||Originally posted by rajeshm
Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh

I too would be interested in the code. Thanks
victor@.keilman.com|||what's up with this "know-how"?? hasn't anybody tried it at all?

create table tblImages (ImageID int identity(1,1) not null, Contents image null)
go
create procedure sp_AddImage (
@.image image )
as
declare @.ImageID int, @.txtptr varbinary(16)
begin tran
insert tblImages (Contents) values (null)
set @.ImageID = scope_identity()
--this part is to ensure that NULL is written to the image field
update tblImages set Contents = null where ImageID = @.ImageID
select @.txtptr = textptr(Contents)
from tblImages
where ImageID = @.ImageID
writetext tblImages.Contents @.txtptr @.image
if @.@.error != 0 begin
raiserror ('Failed to write new image to the table!', 15, 1)
rollback tran
return (1)
end
commit tran
select RetVal = @.ImageID
return (0)
go
exec sp_AddImage 0x00000fffff|||ms_sql_dba, just think of all the people you have made happy today!

A thousand smilies for you: POWER(POWER( :) :) :) :) :) :) :) :) :) :) , 10), 10)|||ms_sql_dba: I like the code. I have never worked with image/text data before, so I don't know how long it would have taken me to get that insert null trick to recover the identity value. Nice job.|||Please send the code to me

lord_n@.msn.com|||Hi,
I have never worked with images in SQL. Your code works like butter. Beautiful. But I have a question. How would I use it? What would an application pass to me(image, image reference or path to the image?)? How would I give the image back to the application?
Thanks.|||i am interested in this script.
i am happy if you send to me...thanks
my e-mail is hckot@.yahoo.com.hk|||hi,

I am Manoj from INDIA.

I have installed Microsoft SQL SERVER 2000 Desktop Edition.

I want to see all the databases created in SQL SERVER in the VISUAL BASIC. If it were ORACLE we can query by "select * from tab" from VB and populate the listbox with the resulset. But in SQLSERVER the query is not working. Ehat should i do?

Awaiting for ur reply..

-Manoj|||Do you need all the databases or all the tables

select * from tab in Oracle would give you all the tables .. not the databases.

For selecting all tables from a particular database in sql server

select * from sysobjects where type = 'U'|||Originally posted by rajeshm
Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh

Rajesh!
I would like to look at your stored procedure. Can you send it to me I would be very greatful.

papd_sweden@.hotmail.com

Thanks in advance
//Moon|||Dosen't anyody read the entire post ....

Originally posted by ms_sql_dba
what's up with this "know-how"?? hasn't anybody tried it at all?

code:------------------------
create table tblImages (ImageID int identity(1,1) not null, Contents image null)
go
create procedure sp_AddImage (
@.image image )
as
declare @.ImageID int, @.txtptr varbinary(16)
begin tran
insert tblImages (Contents) values (null)
set @.ImageID = scope_identity()
--this part is to ensure that NULL is written to the image field
update tblImages set Contents = null where ImageID = @.ImageID
select @.txtptr = textptr(Contents)
from tblImages
where ImageID = @.ImageID
writetext tblImages.Contents @.txtptr @.image
if @.@.error != 0 begin
raiserror ('Failed to write new image to the table!', 15, 1)
rollback tran
return (1)
end
commit tran
select RetVal = @.ImageID
return (0)
go
exec sp_AddImage 0x00000fffff
------------------------|||Hello, people! This thread is 2 YEARS OLD! And the guy never posted again...

Everything on this thread should be deleted except MS_SQL_DBA's code.|||Where is the moderator when you need him the most :)|||can you send me your proc that store images to ..

does your code do this..

get all rows in a tables.. in a stroc proc..and save them back to a new table and one table is a image field.. ?

beaulieu1@.hotmail.com

thanx... been searching for tree days now..on this

Originally posted by rajeshm
Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh|||Wow! This thread still hasn't been read properly. ;)|||Do you think some ppl are just dumb? or someone is using diff logins just for the fun of it and posting the same request over and over again......|||Hey Patrick ... what does SQL Padawan mean|||Originally posted by Enigma
Hey Patrick ... what does SQL Padawan mean

Dear SQL Apostle,

Have you seen Star Wars? Before you become a Jedi, u'r a Padawan...
:)

This thread is really going out from it original topic....|||Hmm .. Star Wars was never my kinda science fiction ... unbelievable (pun intended) ... Gattaca is more like it ...

So ... when you get your fourth star ..do you become a SQL Jedi Knight ;)

BTW .. this thread was never on topic anyway.|||Kinda already taken...

http://www.sqlteam.com/forums/members.asp|||Gattaca gets four stars from me.

"I never saved anything for the swim back."|||Originally posted by blindman
Gattaca gets four stars from me.

"I never saved anything for the swim back."

My favourite quote too ...|||is this code still available|||Dawn Of The Dead Post|||The code is available halfway through this thread. I believe ms_sql_dba was good enough to put it in here for everyone.

So why can't this thread get sent to the marketplace?|||Send it to the trash bin.|||soz of the dumb question before...didnt actually read the whole thread SOZ...listen what exactly does this bit of code do?

PLEASE HELP i really need to store images on my database....

THANKS|||It's best not to store images in the database, rather, store only a
reference to the image in the database, ie, a text field
containing "c:\wwwroot\images\userphotos\user1\user1.jpg"

If you are having people upload files to your site, you can use asp's
filesystemobject to dynamically create such a file structure.

Follow this http://www.sqlteam.com/item.asp?ItemID=986 link for more information to store images to database.|||I have a question: How can I copy the binary value from tableA.columnA to tableB.columnB? Both of them are image fields. I have tried using updatetext but kept giving the error 'NULL textptr (text, ntext, or image pointer) passed to UpdateText function'. Does you have any samples or code snippets? Thanks!

-------
Originally posted by Satya
I have seen similar thread in other forums with no results for getting SP from the originator.

You can use REDADTEXT/WRITETEXT/UPDATETEXT to dealwith text/image datatypes and refer to books online for more information.

Also may check under Umachander's (http://www.umachandar.com/) homepage for the code.|||Originally posted by Patrick Chua
Do you think some ppl are just dumb? or someone is using diff logins just for the fun of it and posting the same request over and over again...... good questions, hey?!|||Originally posted by stephanie_lim
I have a question: How can I copy the binary value from tableA.columnA to tableB.columnB? Both of them are image fields. I have tried using updatetext but kept giving the error 'NULL textptr (text, ntext, or image pointer) passed to UpdateText function'. Does you have any samples or code snippets? Thanks!

------- you have to update your text/image field to null before retrieving the txtptr.|||why not using 3rd tool to help u.
I suggest u trying borland datapump(an extended tool in delphi install package).
Last ,don't forget u can write a program(vb,delphi,java,etc) to do it by yourself .
It's very easy.|||Originally posted by Satya
It's best not to store images in the database, rather, store only a
reference to the image in the database, ie, a text field
containing "c:\wwwroot\images\userphotos\user1\user1.jpg"

If you are having people upload files to your site, you can use asp's
filesystemobject to dynamically create such a file structure.

Follow this http://www.sqlteam.com/item.asp?ItemID=986 link for more information to store images to database. satya,

you sound like it's been an industry standard to store the actual images as file system objects, rather than in the database. it's actually a matter of trust you have with one over the other. it also depends on the app itself, - surely you don't plan to search for the contents of the file even if it is of type text if it is stored in the database. i successfully implemented storing hard-copy documents in the database that get displayed whenever a hard-copy is requested by the user. i also stored report definitions as well as report outputs into image fields so that they can be viewed using the same format/the same content as when they originally produced. i just don't see a justification for this dogmatic approach. do you?|||ON the basis of performance and managebility I recommended (industry standard as you mentioned) to use the file paths instead of storing them on the database.

If your approach are fetching good results without any issues... say no issues with the database consistency then follow it. As far as my experience is concerned I tend to suggest to use file paths rather than storing images directly on the database.:)|||could you be so kind to refer me to a white paper or a publication where this "industry standard" is being discussed? i'd like to at least conceptually imagine the reasoning for this "standard" to even exist.

thanks|||[http://www.sql-server-performance.com/q&a17.asp] for instance.|||even though it's not a white paper or publication, but rather a personal opinion of an author, i'd like to comment on it:

"...storing BLOB objects in SQL Server is cumbersome..." - only if you are not sure how to do it or the method to do it is not the most efficient

"The most commonly accepted way of managing images for a website is not to store the images in the database, but only to store the URL in the database, and to store the images on a file share that has been made a virtual folder of your web server." - please note that (while still being the personal opinion of the author) the paragraph above states that it's "the most commonly accepted". i'd question that because the shops that i worked for and know of accepted the "more cumbursome" way of dealing with images and documents, and that's how they managed to be more successful than the others. you'd ask me what happened to the others? one example is when system administrator decided to reorganize the file structure which resulted in web-site being out of commission until the "more cumbursome" way was introduced and resolved the issue...forever ;)|||I need the code. Thank you.
My email: kim78@.streamyx.com|||Is this thread ever gonna die down ?|||The Code:

Dim ReferTread as this.tread
Dim Understand boolean

Answer=ReferTread.prevpage
if Understand=1
Response.write("You figured it out!")
elseif Understand=0
Response.write("Forget programming")
end if|||OK, ms_sql_dba's code was neat, but there is ADO.Stream object that pretty much does the same.

The alternative to it is the following (please do not ask to repost!!!) which I left unmodified:

-- Ensure QUOTED_IDENTIFIER setting is OFF
set quoted_identifier off
go
-- Drop all participating objects
if object_id('dbo.sp_OA_CreateFile') is not null
drop procedure dbo.sp_OA_CreateFile
go
if object_id('dbo.sp_OA_WriteLineToFile') is not null
drop procedure dbo.sp_OA_WriteLineToFile
go
if object_id('dbo.sp_OA_CloseFile') is not null
drop procedure dbo.sp_OA_CloseFile
go
if object_id('dbo.sp_StoreImage2Table') is not null
drop procedure dbo.sp_StoreImage2Table
go
if object_id('dbo.tblDocumentSubCategories') is not null
drop table dbo.tblDocumentSubCategories
go
if object_id('dbo.tblDocumentCategories') is not null
drop table dbo.tblDocumentCategories
go
if object_id('dbo.tblDocumentTypes') is not null
drop table dbo.tblDocumentTypes
go
if object_id('dbo.tblImages_tmp') is not null
drop table dbo.tblImages_tmp
go
if object_id('dbo.fn_DateFromCharacter') is not null
drop function dbo.fn_DateFromCharacter
go
if object_id('dbo.tblDocuments') is not null
drop table dbo.tblDocuments
go
-- Build the database structure to store binary data
create table dbo.tblDocumentTypes (
TypeID int identity(1,1) not null primary key clustered,
[Description] nvarchar(450) not null )
go
create table dbo.tblDocumentCategories (
CategoryID int identity(1,1) not null primary key clustered,
[Description] nvarchar(450) not null )
go
create table dbo.tblDocumentSubCategories (
SubCategoryID int identity(1,1) not null primary key clustered,
CategoryID int not null,
[Description] nvarchar(450) not null )
go
alter table dbo.tblDocumentSubCategories
add constraint FK_DocumentCategories foreign key (CategoryID)
references dbo.tblDocumentCategories (CategoryID)
go
alter table dbo.tblDocumentCategories
add constraint UC_UniqueDocumentCategory unique ([Description])
go
create table dbo.tblImages_tmp (fImage image null)
go
create table dbo.tblDocuments (
DocumentID int identity(1,1) not null primary key nonclustered,
TypeID int not null,
CategoryID int not null,
SubCategoryID int not null,
DocumentName nvarchar(2000) not null,
[Description] nvarchar(255) not null,
DocumentSize int not null,
AlternateName nchar(12) not null,
CreattionDate datetime not null,
LastModified datetime not null,
LastAccessed datetime not null,
Attributes varchar(10) not null,
DocumentImage image null)
go
create function dbo.fn_DateFromCharacter (
@.DatePart char(8),
@.TimePart char(6) ) returns datetime
as begin
declare @.Date datetime, @.MonthDay char(4), @.Year char(4), @.Month char(2), @.Day char(2)
declare @.Hour char(2), @.Minute char(2), @.Second char(2)
set @.MonthDay = reverse(cast(reverse(@.DatePart) as char(4)))
set @.Month = cast(@.MonthDay as char(2))
set @.Day = reverse(cast(reverse(@.MonthDay) as char(2)))
set @.Year = cast(@.DatePart as char(4))
set @.TimePart = right('000000'+@.TimePart, 6)
set @.Hour = cast(@.TimePart as char(2))
set @.Second = reverse(cast(reverse(@.TimePart) as char(2)))
set @.Minute = cast(reverse(cast(reverse(@.TimePart) as char(4))) as char(2))
return cast(@.Month + '/' + @.Day + '/' + @.Year + ' ' + @.Hour + ':' + @.Minute + ':' + @.Second as datetime)
end
go
create procedure dbo.sp_OA_CreateFile (
@.FileName nvarchar(2000) = null,
@.FileID int output,
@.fs int output)
as
declare @.result int
exec @.result = sp_OACreate 'Scripting.FileSystemObject', @.fs output
if @.result != 0 begin
raiserror ('Failed to create Scripting.FileSystemObject!', 15, 1)
return (1)
end
exec @.result = sp_OAMethod @.fs, 'CreateTextFile', @.FileID output, @.FileName, 1
if @.result != 0 begin
raiserror ('Failed to create/overwrite specified file!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_OA_WriteLineToFile (
@.FileID int,
@.Line varchar(8000) = null)
as
declare @.result int
exec @.result = sp_OAMethod @.FileID, 'WriteLine', null, @.Line
if @.result != 0 begin
raiserror ('Failed to write specified line to file!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_OA_CloseFile (
@.FileID int,
@.fs int )
as
declare @.result int
exec @.result = sp_OADestroy @.FileID
if @.result != 0 begin
raiserror ('Failed to close file!', 15, 1)
return (1)
end
exec @.result = sp_OADestroy @.fs
if @.result != 0 begin
raiserror ('Failed to close Scripting.FileSystemObject!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_StoreImage2Table (
@.FileName nvarchar(4000) = null,
@.FileDescription varchar (255) = null,
@.TypeID int = 0,
@.CategoryID int = 0,
@.SubCategoryID int = 0)
as
set nocount on
declare @.FileSize int,
@.Path nvarchar(4000),
@.FmtFile nvarchar(4000),
@.cmd varchar(8000),
@.DocumentID int,
@.ptr binary(16),
@.ptr_tmp binary(16),
@.fs int,
@.FileID int,
@.error int
if @.FileName is null begin
raiserror ('File name must be specified!', 15, 1)
return (1)
end
if @.FileDescription is null begin
raiserror ('File description must be specified!', 15, 1)
return (1)
end
if charindex('\', @.FileName) = 0 begin
set @.cmd = 'File name <' + upper(@.FileName) + '> is of invalid name!'
raiserror (@.cmd, 15, 1)
return (1)
end
if @.TypeID = 0 begin --Type of document has noot been specified
raiserror ('Document type is not specified!', 15, 1)
return (1)
end
if @.CategoryID = 0 begin --Document category has noot been specified
raiserror ('Document category is not specified!', 15, 1)
return (1)
end
if @.SubCategoryID = 0 begin --Document Sub-category has noot been specified
raiserror ('Document sub-category is not specified!', 15, 1)
return (1)
end
set @.Path = reverse(substring(reverse(@.FileName), charindex('\', reverse(@.FileName), 1), 4000))
set @.FmtFile = @.Path + 'fmt.fmt'
begin tran
create table #FileInfo (
AlternateName nchar(12) null,
FileSize int null,
CreationDate char(8) null,
CreationTime char(6) null,
LastWrittenDate char(8) null,
LastWrittenTime char(6) null,
LastAccessedDate char(8) null,
LastAccessedTime char(6) null,
Attributes varchar(10) null )
create table #output ([output] varchar(8000) null)
commit tran
insert #FileInfo exec master.dbo.xp_getfiledetails @.FileName
select @.FileSize = FileSize from #FileInfo
if @.FileSize is null begin
set @.cmd = 'Specified file <' + upper(@.FileName) + '> does not exist!'
raiserror (@.cmd, 15, 1)
return (1)
end
-- Create format file
exec dbo.sp_OA_CreateFile @.FmtFile, @.FileID output, @.fs output
exec dbo.sp_OA_WriteLineToFile @.FileID, '8.0'
exec dbo.sp_OA_WriteLineToFile @.FileID, '1'
set @.cmd = "1 " +
space(8) + "SQLIMAGE" + space(8) + "0" + space(8) +
cast(@.FileSize as varchar(25)) + space(8) +
char(34) + char(34) + space(8) + "1" + space(8) +
"fImage" + space(8) + char(34) + char(34)
exec dbo.sp_OA_WriteLineToFile @.FileID, @.cmd
exec dbo.sp_OA_CloseFile @.FileID, @.fs
-- Store binary data to temporary location in the database
if exists (select 1 from dbo.tblImages_tmp (nolock)) delete dbo.tblImages_tmp
set @.cmd = "bulk insert " + db_name() + ".dbo.tblImages_tmp from '" + @.FileName + "' " +
"with (formatfile = '" + @.FmtFile + "')"
exec (@.cmd)
set @.cmd = "exec master.dbo.xp_cmdshell 'del " + @.FmtFile + "'"
insert #output exec (@.Cmd)
if not exists (select 1 from dbo.tblImages_tmp) begin
set @.cmd = 'Error occurred while storing file <' + upper(@.FileName) + '> into a temporary table!'
raiserror (@.cmd, 15, 1)
return (1)
end
select @.DocumentID = DocumentID from dbo.tblDocuments d (nolock)
inner join #FileInfo f on d.AlternateName = f.AlternateName and d.DocumentName = @.FileName
-- Insert new tblDocuments record if it doesn't exist
if @.DocumentID is null begin
begin tran
insert dbo.tblDocuments (
TypeID,
CategoryID,
SubCategoryID,
DocumentName ,
[Description] ,
DocumentSize ,
AlternateName ,
CreattionDate ,
LastModified ,
LastAccessed ,
Attributes )
select @.TypeID, @.CategoryID, @.SubCategoryID,
@.FileName, @.FileDescription, @.FileSize, AlternateName,
dbo.fn_DateFromCharacter(CreationDate, CreationTime),
dbo.fn_DateFromCharacter(LastWrittenDate, LastWrittenTime),
dbo.fn_DateFromCharacter(LastAccessedDate, LastAccessedTime),
Attributes from #FileInfo
select @.DocumentID = scope_identity(), @.error = @.@.error
if @.error != 0 begin
raiserror ('Failed to initialize target table!', 15, 1)
rollback tran
return (1)
end
commit tran
end
-- Initialize IMAGE field with NULL
begin tran
update dbo.tblDocuments set DocumentImage = null where DocumentID = @.DocumentID
if @.@.error != 0 begin
raiserror ('Failed to initialize target field!', 15, 1)
rollback tran
return (1)
end
commit tran
-- Use UPDATETEXT to transfer the image from temporary table
select @.ptr = textptr(DocumentImage) from dbo.tblDocuments where DocumentID = @.DocumentID
select @.ptr_tmp = textptr(fImage) from dbo.tblImages_tmp
begin tran
updatetext tblDocuments.DocumentImage @.ptr 0 null tblImages_tmp.fImage @.ptr_tmp
if @.@.error != 0 begin
raiserror ('Failed to store document into target table!', 15, 1)
rollback tran
return (1)
end
-- Clear the temporary table
delete dbo.tblImages_tmp
if @.@.error != 0 begin
raiserror ('Failed to clear temporary table!', 15, 1)
rollback tran
return (1)
end
commit tran
return (0)
go|||Microsoft says it's not wise http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/binarydata.asp
Here is some samplecode to do it

-- 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|||Please read from the link that you posted and look for the authors (wink-wink, it's not Microsoft!)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/professionalactiveserverpages.asp|||I see. I guess I assume if it's on MSDN it at least passed some review. A few reasons I can think of not to do it are DB performance in general, complexity of the code and apps to support it, and not being able to do incremental backups on a DB like you can for file system documents. It's an often debated topic. We have had at least 5 database backed commercial document management systems and none of them stored documents in the DB. Does MS's terraserver store images in the DB? (I don't know, just wondering.)|||I think that's the reason why it's called terra server, there is nothing else in it, just images.

Yukon will still support TEXT/NTEXT and IMAGE datatypes, but will introduce VARCHAR/NVARCHAR(MAX) as well.|||I am also working on saving IMAGE to MSSQL. I am using JAVA as language.

Please send me code at kuldips@.bsharp.com|||I would recommend to re-read the previous posts to eliminate further questions, because all the answers that can be possibly given to the questions...have been posted...|||THIS THREAD IS DEAD!

DEAD, DEAD, DEAD!

IT HAS BEEN DEAD FOR A YEAR. NOBODY IS GOING TO SEND YOU ANY HELPFUL SOFTWARE. NOBODY IS GOING TO GIVE YOU ANY USEFUL ADVICE.

By posting to this thread and expecting any sort of helpful reply, you hearby declare yourself a moron of insufficient intelligence to browse the internet. For your own protection from phishers, overseas pharmaceutical marketers, and deposed Nigerian despots, please disconnect your 56K modem NOW!|||Hi there

I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code

Regards

Rajesh
Plz send the code to me too
thanks
my mail is :
yosif4444@.gmail.com|||yosif4444, you have hereby declared yourself a moron of the highest degree. A poster of insufficient intelligence to browse the internet. Please remove your coffee cup from your CD Drive tray, wipe the white-out off of your screen, unplug your computer, and go outside and play in the driveway.|||This sounds very useful.
Please message me with the code.
Many thanks.
DbaJames.|||This thread collects idiots like flypaper collects flies. DbaJames, you seem to have something stuck to your shoe...|||OK, I'm locking this thread to prevent others from displaying their ignorance.

How to insert data into two tables simulatneously, using Stored Procedure?

Hi all,
I have heard that we must insert into two tables simultaneously when there is a ONE-TO-ONE relationship.

Can anyone tell me how insert into two tables at the same time, using SP?

Thanks

Tomy

tomyseb:

I have heard that we must insert into two tables simultaneously when there is a ONE-TO-ONE relationship.

Not true.

The fact is that you can't insert into 2 tables simultaneously. Commands are executed in a procedural fashion.

Also, it extremely rare to need a one-to-one relationship. A typical example of when it is useful is if you would otherwise be in danger of going over the maximum number of fields limit in Access, and need to break the table up.

sql

How to insert BLOB data Using Store Procedure

Hi,

Can we insert BLOB data using store procedure using Oracle and ASP.Net.

I have inserted BLOB data using insert command, now i want to insert that BLOB via store procedure...

any links/tips will be helpful...

Hi,

Of course, you can use stored procedure to insert BLOB data. Use some data type that takes byte array as parameter, and you can insert into the table. You can just use the INSERT command as the content of the stored procedure.

how to insert a identity key through store procudure?

I want to insert 3 values to sql database through store procudure

-- a store procudure is like this--

ALTER PROCEDURE dbo.FreeExperience

@.identity ,

@.name nvarchar(20),
@.gender int,

AS
begin
Insert into list(cid,name,gender)
values (@.identity,@.name,@.gender)

end
RETURN

------------------

the cid is a identity value . it will +1 automatically when user insert a new data..

if i ingore this column in store procudure it will cause error (becuase this column is not allow null)

please help. thanks

Try

ALTER

PROCEDURE dbo.FreeExperience
@.namenvarchar(20),
@.genderint,
AS
INSERTINTO list(name,gender)values(@.name,@.gender)
RETURN
GO

or

CREATE

PROCEDURE dbo.FreeExperience2
@.identityINT,
@.namenvarchar(20),
@.genderint,
AS
SETIDENTITY_INSERT listON-- Allows Identity value to be supplied
Insertinto list(cid,name,gender)values(@.identity,@.name,@.gender)
SETIDENTITY_INSERT listOFF
RETURN|||

if you cid field is setup as identity in you table (cid int identity(1,1))

insert statement like this should work without any errors

Insert into list(name,gender)
values (@.name,@.gender)

and identity value will be automatically set by SQL server

|||

I still got error message...

exceptiona information: System.Data.Sqlclien.SqlException: procedure or parameters 'FreeExperience' must have a parameters'@.identity', but didn't provide..

p.s FreeExperience is my store procedure name..

|||

If you want SQL Sever to automatically add the Identity value for you, you need to remove the column entirely from the proc, and your INSERT statement. If you want to manually supply the value then you use the SET IDENTITY_INSERT <Table> ON clause, and provide the value you want to be used instead. IF the column is a Primary Key for the table, you also need to make sure the value you are providing does not already exist.

|||

thank you... but I am not sure if I really understand what did you mean

can you please give me a simple example for this ?

this store procudure is for a program which will insert new colum to table

1 column named " cid" in table which is set as a identity column will automatically +1 and insert to cid

|||

Use this stored procedure
alter PROCEDURE dbo.FreeExperience
@.name nvarchar(20),
@.gender int,
AS
Insert into list(name,gender) values (@.name,@.gender)
RETURN

|||

this stored procedure will cause error

because the cid column is not allow to be null...

|||

>>this stored procedure will cause error because the cid column is not allow to be null...

Identity columns are populated automatically by the database so the stored procedure will work!

|||

thank you .. but it really doesn't works..

it continue showing the error message...

can't insert NULL to 'cid，table'list'; not allow Null。INSERT fail..

I checked all column in table list from database, all column allow null except column cid.

the statement will work only if I set the cid column as accept null ..

please help..

|||

Modify ur table by setting the "IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

|||

Modify ur table by setting the "IsIdentity=Yes, IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

How to increment record id in an insert stored procedure

Hello everyone,
I have a problem. I need to insert records from table2 to table1. In
table1 there is a record id which serves as the key of the table. Each time
a record needs to be inserted, a record id is created. This record id is th
e
max(recordid)+1. I wrote the following stored procedure to insert records.
This stored procedure will insert the ten records from table2 into table1.
The only problem is that the recordcount is same for all ten records, instea
d
of incrementing. Should I store the table id in a different table? Any
response will be greatly appreciated.
Begin Transaction
(Select @.recordcount = (max(record)+1) from [dbo].[table1])
Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
field5, field6)
Select @.recordcount, field1, field2, field3, field4, field5, field6 from
[dbo].[table2]
select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
if @.errnum <> 0 GOTO sqlerror
Commit TransactionLook up IDENTITY in Books Online, that does all the work for you.
Jacco Schalkwijk
SQL Server MVP
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:765A9768-316A-4446-82A6-58737F5040CB@.microsoft.com...
> Hello everyone,
> I have a problem. I need to insert records from table2 to table1. In
> table1 there is a record id which serves as the key of the table. Each
> time
> a record needs to be inserted, a record id is created. This record id is
> the
> max(recordid)+1. I wrote the following stored procedure to insert records.
> This stored procedure will insert the ten records from table2 into table1.
> The only problem is that the recordcount is same for all ten records,
> instead
> of incrementing. Should I store the table id in a different table? Any
> response will be greatly appreciated.
> Begin Transaction
> (Select @.recordcount = (max(record)+1) from [dbo].[table1])
> Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
> field5, field6)
> Select @.recordcount, field1, field2, field3, field4, field5, field6 from
> [dbo].[table2]
> select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
> if @.errnum <> 0 GOTO sqlerror
> Commit Transaction
>

How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)

How to include parameter in WHERE statment of a stored procedure?

I have a stored procedure like:

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000)
AS
BEGIN
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable)

END

My problem is, the locationId will be null or length equals 0 some time, how can I make the statement "table.LocationId in (SELECT id FROM tempTable)" included into the WHERE condition dynamically depends on locationId is null or length equals 0?

Is that possible to do it at Database side? Or I have to do it at code side?

Thank you.

Perhaps something like this:

SELECT t.id

FROM Table t

WHERE ( t.id = @.Id
AND ( t.LocationID in (SELECT ID FROM TempTable)

OR t.LocationID IS NULL

)

)
END

|||

Looks like you want dynamic search capabilities based on the parameters passed. You can take a look at the link below for various options:

http://www.sommarskog.se/dyn-search.html

You can do below in your case:

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and @.LocationId is not null and len(@.LocationId) > 0

UNION ALL

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

Query optimizer will use startup expression filter to evaluate the expression containing @.LocationId at run-time. This will result in only one of the UNION ALL branches being executed. You could use this approach. You can also put this query in a inline TVF and use it.

|||

Thank you for your advise.

For your code, my understanding is you list both situations, either parameter has value or not has value, run both and combine the results. Is that correct?

My question is, if @.LocationId is not null, the condition "(@.LocationId is null or len(@.LocationId) = 0)" will be false, and it will AND with condition "table.LocationId in (SELECT id FROM tempTable)", the result will also be false right? Finally, it will AND with "table.id = @.Id", which will be false again. That will make the result set of

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

be null?

Cause I have about 8-9 parameters need to be passed in, follow your code, I guess the stored procedure will be very long, right?

The link you gave provides a coding way to do the if statement to build the query at database.

Thank you.

|||Try the following:

Code Snippet

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000),
@.Parameter2 INT,
@.Parameter3 NUMERIC(18, 2)
AS
SELECT
table.id
FROM
table
WHERE
table.id = @.Id AND
(table.LocationId in (SELECT id FROM tempTable) OR LEN(ISNULL(@.LocationId, '')) = 0) AND
(table.Value2 = @.Parameter2 OR @.Parameter2 IS NULL) AND
(table.Value3 = @.Parameter3 OR @.Parameter3 IS NULL) etc...

If you're checking VARCHARs, NVARCHARs, CHARs, to see if they're either NULL or have length = 0, put an ISNULL around the parameter and compare the length to 0.

For every other parameter you want to check, compare it to the table value and OR it with a check to see if it's null.

Each of those OR'd NULL checks needs to be in brackets with AND clauses otherwise you'll get strange results.

How to import data from a .csv file

I'm a SQL newbie and am wondering what is the correct procedure to
import data from .csv file into a SQL table. I want to electronically
clear checks in MS Dynamics GP and need to update one field in the
checks table to indicate that the check has cleared.
Any help is appreciated.
Best regards,
Frank Hamelly, MCP
NOVA Solutions LLC
Melbourne, FLfhamelly@.cfl.rr.com wrote:
> I'm a SQL newbie and am wondering what is the correct procedure to
> import data from .csv file into a SQL table. I want to electronically
> clear checks in MS Dynamics GP and need to update one field in the
> checks table to indicate that the check has cleared.
> Any help is appreciated.
> Best regards,
> Frank Hamelly, MCP
> NOVA Solutions LLC
> Melbourne, FL
>
Hi Frank,
You can do it either via DTS/SSIS (depending on which SQL server version
you are running) or you can create a linked server in SQL server and
then create a query that can select values from the file and then update
your tables.
Try to look for "importing data" in Books On Line - that should get you
started.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Thank you for the help Steen.
Frank

How to import data from a .csv file

I'm a SQL newbie and am wondering what is the correct procedure to
import data from .csv file into a SQL table. I want to electronically
clear checks in MS Dynamics GP and need to update one field in the
checks table to indicate that the check has cleared.
Any help is appreciated.
Best regards,
Frank Hamelly, MCP
NOVA Solutions LLC
Melbourne, FLfhamelly@.cfl.rr.com wrote:
> I'm a SQL newbie and am wondering what is the correct procedure to
> import data from .csv file into a SQL table. I want to electronically
> clear checks in MS Dynamics GP and need to update one field in the
> checks table to indicate that the check has cleared.
> Any help is appreciated.
> Best regards,
> Frank Hamelly, MCP
> NOVA Solutions LLC
> Melbourne, FL
>
Hi Frank,
You can do it either via DTS/SSIS (depending on which SQL server version
you are running) or you can create a linked server in SQL server and
then create a query that can select values from the file and then update
your tables.
Try to look for "importing data" in Books On Line - that should get you
started.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thank you for the help Steen.
Frank

How to import data from a .csv file

I'm a SQL newbie and am wondering what is the correct procedure to
import data from .csv file into a SQL table. I want to electronically
clear checks in MS Dynamics GP and need to update one field in the
checks table to indicate that the check has cleared.
Any help is appreciated.
Best regards,
Frank Hamelly, MCP
NOVA Solutions LLC
Melbourne, FL
Thank you for the help Steen.
Frank

How to import & export stored procedure in sql query like Table data ?

Dear all,
How to import & export stored procedure in sql query like Table data ?
thanks,
harshad prajapatiHi
You can script the stored procedures using SMO or in SSMS. You should use
version control keep the code in it, then you know that you are scripting a
particular release/revision.
You can also use SSIS to transfer objects (not only stored procedures).
John
"harshad" wrote:
> Dear all,
> How to import & export stored procedure in sql query like Table data ?
> thanks,
> harshad prajapati

How to import & export stored procedure in sql query like Table data ?

Dear all,
How to import & export stored procedure in sql query like Table data ?
thanks,
harshad prajapati
Hi
You can script the stored procedures using SMO or in SSMS. You should use
version control keep the code in it, then you know that you are scripting a
particular release/revision.
You can also use SSIS to transfer objects (not only stored procedures).
John
"harshad" wrote:

> Dear all,
> How to import & export stored procedure in sql query like Table data ?
> thanks,
> harshad prajapati

how to impliment "array" in stored procedure?

i doing an online shop project which have an shoppingcart and it stored database.
and i have the situation like this.
the products have properties such as size, color . and customerscan buy a product with particular size or color. and i havethe shopping cart table structure and data like following
Id(primary key) CartId productId size color quantity
1 1 1 S red 10
2 1 1 S black 2
3 1 1 S blue 3
4 1 1 M red 5
5 1 1 L blue 2
all the data above is i image the customer may inputed. And myproblem is how to use an stored procedure to updata above record when a customer buy the same product which is one of the product fromabove(have same productId, size, color)
and i try to use the following code but it didn't work
<code>
create procedure shoppingcart_add_item
( @.cartId int,
@.productId int,
@.size nvarchar(20),
@.color nvarchar(20),
@.quantity int
)
AS
DECLARE @.countproduct
DECLARE @.oldsize
DECLARE @.oldcolor
select @.countproduct=count(productId) FROM shoppingcart WHERE productId=@.productId AND cartId=@.cartId
select @.oldsize=size,@.oldcolor=color FROM shoppingcart WHERE productId=@.productId
IF @.CountItems > 0 and @.oldsize = @.size and @.oldcolor = @.color
UPDATE
ShoppingCart
SET
Quantity = (@.Quantity + ShoppingCart.Quantity)
WHERE
ProductId = @.ProductId
AND
CartId = @.CartId
ELSE /* New entry for this Cart. Add a new record */
INSERT INTO ShoppingCart
(
CartId,
ProductId,
Quantity,
color,
size
)
VALUES
(
@.CartId,
@.ProductId,
@.Quantity,
@.size,
@.color
)
</CODE>
and the result from this stored procedure is not what i want, what itry to say is can i stored all the size and color in @.oldsize and@.oldcolor array. then loop through the array to get the one iwant??
somebody get any idea? or don't know what i am talking about?
sorry i asked a very stupid quesation, i just think too much, actually the solution is increditable simple, it just need alittle bit SQL can slove this(simple get the Id and update the newrecord depend on this Id)
i don't know why i am so stupid to make things complicated.

How to identify which all SPs are accessing a given table ?

I don't know if I am in the right section of the forum. Please help me with this :

Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database.

Thanks

Prasad P

Hi,

the only reliable method to get the information is to search the Information Schemas for the information, there is an article about sp_depends which sounds like you would get reliable information from it, but you don′t. I didn′t check the behaviour in SQL2k5, but check this article to get deeper information:

http://b.wunder.home.comcast.net/16509.htm

HTH, Jens Suessmeyer.

|||Check out my blog site:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

After having the same problem, I wrote a blog containing a stored procedure which searches stored procedures for a text string using the syscomments system table (which stores the stored proc definitions). If you pass the table name into this procedure, it should get you in the correct direction.

Let me know if that solves your problem, or whether you need more assistance.

HTH|||? No, there's nothing built in. A quick and dirty way of figuring it out is: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TEXT LIKE '%YourTableName%' This does have some issues due to especially large routines and routines that make use of dynamic SQL and concatenate names, but it may give you some indication. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Prasad Peesapati@.discussions..microsoft.com> wrote in message news:bdc2238e-c590-47ff-927a-5660b2f552db@.discussions.microsoft.com... I don't know if I am in the right section of the forum. Please help me with this : Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database. Thanks Prasad P

how to identify stored procedure dependencies?

Hello,
I need to find where an sp is called from another sp or how many sp's a main
sp is calling. Here is something that I tried with no luck. I know that
this sp is calling other sp's or is being called by other sp's. How can I
get a list of sp's related to this'
--this does not return anything for me even though I know there are
dependencies
select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
OBJECT_NAME([depid]) in (select specific_name from
information_schema.routines where routine_type = 'sp_Compare_Add')
order by Proce
Thanks,
Rich1. See what the undocumented procedure master..sp_MSdependencies tells
you. However, this may sometimes miss references.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
3. Run a SQL Profiler event trace.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
> Hello,
> I need to find where an sp is called from another sp or how many sp's a
> main
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Here is a query which will give you the listings for procedure to procedure
dependencies:
select object_name([id]) as [Procedure],
object_name([depid]) as [Depends on Procedure]
from dbo.[sysdepends]
where objectproperty([id], 'IsProcedure') = 1
and objectproperty([depid], 'IsProcedure') = 1
and objectproperty([id], 'IsMSShipped') = 0
Order by [id], [depid]
Note that there is the possibility due to late binding data could be missing
here
HTH-
--Tony
"Rich" wrote:

> Hello,
> I need to find where an sp is called from another sp or how many sp's a ma
in
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Thanks very much.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
This comment did the trick. I located what I needed.
"JT" wrote:

> 1. See what the undocumented procedure master..sp_MSdependencies tells
> you. However, this may sometimes miss references.
> 2. You can query the syscomments table like so:
> select object_name(id) as obj_name from syscomments where text like
> '%usp_mysp%'
> 3. Run a SQL Profiler event trace.
> http://msdn.microsoft.com/library/d...enethowto15.asp
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
>
>|||Hi , you can also do this
CREATE PROC sp_search_code
(
@.SearchStr varchar(100),
@.RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object
name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',
'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) +
'''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @.SearchStr + '%' AND
encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)
ORDER BY 'Object type', 'Object name'
SET @.RowsReturned = @.@.ROWCOUNT
END

How to I run a DTS package from a stored procedure? thank

I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?

Please help me, thanks a lot

Hi,

you have to call it via dtsrun on the command prompt, ousing xp_cmdshell.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens Suessmeyer,

could you be more specific? can you give me a example?

thanks

|||Sure, look the the DTSRUN syntax, you can start the dtsrun either with a GUID naming the package which is stored in SQL Server or by a structured storage file, using the XP_CMDSHELL 'DTSRUN SomePackage' will get you the package run.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Another method would be to use the system OLE automation SPs and use the DTS object model to invoke the package. Please search the web for several examples. Btw, this question is more suited for the SQL Server Integration Services newsgroup so I will move the thread there so someone there can point you to appropriate resources/links.|||

Please read through the article on 'Data Transformation Services (DTS)' @. http://www.databasejournal.com/features/mssql/article.php/1459181
The example in this article demostrates the use of OLE stored procedures and its benefits.

Btw - This forum majorly deals with SQL Server 2005 - Integration Services.

For DTS (SQL Server 2000) related questions post @. http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US

Thanks,
Loonysan

|||I would keep away from using OLE stored procedures - it is a convinient way to do it, but you are running inside SQL Server process. Thus any problems with DTS package may affect the server. Running the package outside using DTSRUN utility is better, as the SQL Server and package run in isolated processes and don't affect each other.

If you mistyped DTS, but was really asking about SSIS (this is SSIS forum after all) - the recommended way is to create Agent Job with the package step, don't assing any schedule to this job, and then start this job from your SQL stored procedure by calling Agent's SP. This provides the isolation as with DTSRUN, but additionally you may specify user context for the SSIS package - so the package does not have to run under the same user as SQL Server.