Firing trigger on a bulk like insert - Database Discussion Boards

Mycroft Holmes4-Mar-09 16:21

4-Mar-09 16:21

The only reaslistic alternative is to use dynamic SQL (sql strings) but that is plain ugly.

Also chaining multiple conditionals can nail a query to the wall. What I often do is use the "required" conditions to get a subset into a temp table and then start deleting records from the temp table or use the conditional filtering on the temp table

Never underestimate the power of human stupidity
RAH

how can i call a function in stored procdeure in sql server 2005

lakshmichawala4-Mar-09 0:30

lakshmichawala

4-Mar-09 0:30

in oracle we can call a function like this in stored procedure

create or replace procdeure getitems
(
item in varchar2
)
begin
item :=getitems(sysdate);
end;

but how the function is called in sql server 2005 in an sp

Re: how can i call a function in stored procdeure in sql server 2005

www.Developerof.NET4-Mar-09 0:37

www.Developerof.NET

4-Mar-09 0:37

lakshmichawala wrote:

but how the function is called in sql server 2005 in an sp

It all depends what your function returns.

If your function returns a scalar value then you can use the function as

select dbo.getitem(itemid) from Items

or if your function returns a table then you can do some thing like this,

select item_code,item_desc from dbo.getitems(getdate())

hope that helps....

When you fail to plan, you are planning to fail.

Re: how can i call a function in stored procdeure in sql server 2005

lakshmichawala4-Mar-09 0:51

lakshmichawala

4-Mar-09 0:51

hi
we can also assing like this na

set @item =dbo.getitems(getdate())

Oracle Question

E_Gold4-Mar-09 0:25

E_Gold

4-Mar-09 0:25

Hi evryone
Is it posible to install Oracle 10G on Windows XP ?

I try to install and the installation stuck in "Ocacle Database Configuration Assistant"

Re: Oracle Question

Eddy Vluggen4-Mar-09 1:19

Eddy Vluggen

4-Mar-09 1:19

E_Gold wrote:
Is it posible to install Oracle 10G on Windows XP ?

Runs fine on my XP system.

E_Gold wrote:
I try to install and the installation stuck in "Ocacle Database Configuration Assistant"

No error message?

I are troll Smile | :)

Re: Oracle Question

M Riaz Bashir4-Mar-09 19:45

M Riaz Bashir

4-Mar-09 19:45

I have also installed without any problem

i think you need to re-install windows xp.

Firing trigger on a bulk like insert

www.Developerof.NET4-Mar-09 0:11

www.Developerof.NET

4-Mar-09 0:11

Hi all,

I have implemented a functionality shown here[^] on codeproject.

This method generates xml from ntext and inserts it into table.

Now i want a trigger on this table which inserts in other table when each row is inserted.But the problem is that when the trigger fires, the inserted table contains all the rows that are inserted in the table (i guess since it was inserted all at once) and hence i m not able to insert it in other table bcas i have to perform some operation on the data also.

Is there a method so that the trigger fires for each row and not all at once????

When you fail to plan, you are planning to fail.

Re: Firing trigger on a bulk like insert

Mycroft Holmes4-Mar-09 16:30

Mycroft Holmes

4-Mar-09 16:30

I have not used openxml so I am assuming a bulk insert is used and the trigger is by passed (I also never, ever use triggers). So why not grab the Details ID, insert the records, get the last ID inserted, call a proc for the detail between. Ugly and subject to other user interference.

OR
Don't use openxml - I am surprised the insert does not fire the insert trigger

Never underestimate the power of human stupidity
RAH

Store 10000 characters

.NET- India 3-Mar-09 20:47

.NET- India

3-Mar-09 20:47

Hello Friends,

I've to store more than 10000 characters in Sql. But when i store more than 8000 it throws an error........ How can i do it

Re: Store 10000 characters

Vimalsoft(Pty) Ltd3-Mar-09 20:58

Vimalsoft(Pty) Ltd

3-Mar-09 20:58

What Datatype are you using to Store those Characters ?

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

Re: Store 10000 characters

.NET- India 3-Mar-09 21:06

.NET- India

3-Mar-09 21:06

varchar

Re: Store 10000 characters

.NET- India 3-Mar-09 21:15

.NET- India

3-Mar-09 21:15

I've done it by using the following command
Create Table Rough2(Name varchar(Max))

Re: Store 10000 characters

Vimalsoft(Pty) Ltd3-Mar-09 21:30

Vimalsoft(Pty) Ltd

3-Mar-09 21:30

Try this

NVARCHAR(MAX)

NTEXTMAX)

Hope it Helps

Re: Store 10000 characters

Eddy Vluggen3-Mar-09 22:42

Eddy Vluggen

3-Mar-09 22:42

Use the text or ntext datatype to store large texts if you don't plan to use them in queries (e.g. where PlaceName = "Amsterdam" is a varchar, but the wikipedia-text on Amsterdam is a text-field)

I are troll Smile | :)

Could not continue scan with NOLOCK due to data movement

Vimalsoft(Pty) Ltd3-Mar-09 20:41

Vimalsoft(Pty) Ltd

3-Mar-09 20:41

Good Morning Friends,

I have a SP that is Supposed to Restore a Database from a Backup. In some Clients this works well but in those particular client its a Problem. The version is SQL 2005.

Here is the Code that fails

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- Restore a backed up database
-- Given: the filename and path where the backedup database file is located and the name of the database to restore it as
-- The entry will be restored and a row placed into oDirect.dbo.tbl_dbref - which keeps track of the databases
-- The users for the database must also be restored!
ALTER PROCEDURE [dbo].[sp_RestoreDatabase] 

	@dbname char(32), 				-- the database name to restore as
	@filename char(64), @path char(256)		-- the location of the backuped up database file	(on the SQL Server)

AS

set nocount on

declare @sql nvarchar(3000)

execute('sp_ClearDatabaseConnections ' + @dbname)

-- Restore the database
select @sql = 			' RESTORE DATABASE ' + ltrim(rtrim( @dbname )) + ' FROM DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @filename )) + ''' ' 
select @sql = ltrim(rtrim(@sql)) + 	'    WITH RECOVERY, '
select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + 'TNGoedit_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , '	-- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + 'TNGoedit_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' '	-- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + ltrim(rtrim(@dbname)) + '_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , '	-- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + ltrim(rtrim(@dbname)) + '_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' '	-- logical file name to physical name
print @sql
execute ( @sql )

-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
	-- Put an entry into oDirect.dbo.tbl_dbRef
	-- execute ( 'sp_DataSet_Save ''' + @xml + ''' ' ) 	

	-- TODO: restore the users

  select 'Restore Successful' [Result]
end
else begin
   select 'Restore Unsuccessful' [Result]
end


/*	-- Example of a restore
RESTORE FILELISTONLY
   FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB' 

RESTORE DATABASE tst
   FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB' 
   WITH RECOVERY,
   MOVE 'LBS_Data' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst.mdf',	-- logical file name to physical name
   MOVE 'LBS_Log' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst_log.ldf'	-- logical file name to physical name
*/

And i get the Error

Exception caught in: ExecuteStoredProc: Could not continue scan with NOLOCK due to data movement. RESTORE could not start database 'Testv3'. RESTORE DATABASE is terminating abnormally. Database 'Testv3' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Thank you.

How to access a table located in different database server.

Spunky Coder3-Mar-09 19:19

Spunky Coder

3-Mar-09 19:19

Hi all,

How can we access a table located in different database server under different database.(In a stored procedure)
I mean can we do some thing like the following in a stored procedure to get the data from the remote table...
{
CONNECT TO THE REMOTE SERVER
SELECT THE TABLE DATA

}

Please let me know how to do this...

"Don't worry if it doesn't work right. If everything did, you'd be out of a job." (Mosher's Law of Software Engineering)

Re: How to access a table located in different database server.

Vimalsoft(Pty) Ltd3-Mar-09 20:47

Vimalsoft(Pty) Ltd

3-Mar-09 20:47

NO NO NO

Lets say i have Table 1 in Server1 and Table2 In Server 2 and want to run a Query based on it. Well you must use a link Server and Call it with the instance of the Link Server like this

SELECT NAME.TABLE1,LASTNAME.TABLE
FROM TABLE 1 --IN THE CURRENT WORKING SERVER
INNER JOIN SERVER2.DATABASE.USER.TABLE2
ON USER_ID.TABLE1 = USER_ID.TABLE2

Hope this Helps

SQL server taking 100% of proccesor

AlexeiXX33-Mar-09 18:40

AlexeiXX3

3-Mar-09 18:40

Hi, im having a little rpblem with sql's performance

The database engine installed fine and had been working fine
Then i installed the client tools and after installing that and restarting pc, the procesor suddenly goes to a 100% for a little while, when i open the task manager, the System process is the one that takes the 100% of the procesor capacity
This had happened before, and i had just unistalled the SSMS and the problem would disappear, but this is weird, i have installed sql server (complete installation) and it works fine (on windows xp)
This is a clean windows installation of windows xp also

Does anyone have had this problem??
Any suggestions

Thanks in advance

Alexei Rodriguez

Get only updated and newly record

Abdul Rahman Hamidy3-Mar-09 17:44

Abdul Rahman Hamidy

3-Mar-09 17:44

Dear All,
i have a database in MSSQL 2005 which i am doing DDL,DML and others from c#.
i am searching for a tool in MSSQL 2005 that it could give only updated and newly inserted records in afile.
one possible i can do is to create publication, but i dont want to go through publication.
is there any method or tool that can provide me such facility.

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: Get only updated and newly record

Riaan Booyzen3-Mar-09 20:23

Riaan Booyzen

3-Mar-09 20:23

Hi, what about having datetime (datetimestamp) columns, one for last_updated and one for created_date?

Re: Get only updated and newly record

Isaac Gordon27-Mar-09 3:06

Isaac Gordon

27-Mar-09 3:06

Hello
U Must Have To use Time Stamp With Help Of time Stamp You Can Find It.

Very basic Prolog question

aab199023-Mar-09 13:34

aab19902

3-Mar-09 13:34

This is my first time coming in contact with GNU Prolog and I cannot find the answer to my problem. I have a very simple program and I have to write a few queries to find some answers.

Since the program is so short i will post the whole thing here...

d(iron, steam).
d(pumps, mines).
d(mines, railroads).
d(iron, railroads).
d(iron, guns).
d(electricity, aluminum).
d(iron, electricity).
d(aluminum, jumbojet).
d(writing, printingpress).
d(printingpress, comicbook).
d(iron, printingpress).
d(aluminum, printingpress).

depends(X,Y) :- d(X,Y).
depends(X, Y) :- d(X, Z), depends(Z, Y).

Here are the questions...

What do you need to invent the comic book?
What do you need to invent electricity?
If you don't have steam engines, what can you not invent?
Given aluminum and electricity, what can you invent?
Which inventions are dependent on iron?

thanks in advance

Trouble understanding following sql

pzn3xq3-Mar-09 9:31

pzn3xq

3-Mar-09 9:31

I'm reviewing the procs in http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon&referringTitle=Home, and have a couple question at the sql statement below:

1) where is the tempdb..sysobjects in sqlserver manager? I see a tempdb under the 'master' db...is that created by default? I don't understand why they have two periods (dot dot) in the tempdb..sysobjects. I do not see a sysobjects table within the tempdb.

2) Can someone explain the '#' in the LIKE '#FileSpaceStats%'. The % is the wildcard, but what is the leading # used for?

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#FileSpaceStats%')
BEGIN
DROP TABLE #FileSpaceStats
END
Thanks

Re: Trouble understanding following sql

Syed Mehroz Alam3-Mar-09 19:24

Syed Mehroz Alam

3-Mar-09 19:24

pzn3xq wrote:
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#FileSpaceStats%')
BEGIN
DROP TABLE #FileSpaceStats
END

# means a temporary table and such tables are stored in the tempdb. So the above query looks for a temp table starting with FileSpaceStats and drops #FileSpaceStats in case it exists.

Hope that helps.

Regards,
Syed Mehroz Alam

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.