Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server


SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.





SQL Server 2005 Query/trigger/function (whatever It Is That I Need)


Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario

 table 1 = stock table

 table 2 = Purchase order table

 in table 2 there are line items that have ordered quantities for parts that we have ordered

 in table 1 under each part number is a field for "quantity on order"

I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1

 

quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match

so for part number 516 i have this

 

table 2

poNumber             partNumber                 quantity

1                             516                             1

2                             516                             12

3                             516                             4

 

table 1

partNumber            inStock              onOrder

516                          0                        17(this is what i am trying to figure out how to compute)

 

any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.




View Complete Forum Thread with Replies
Related Forum Messages:
SQL Server 2005 Query With Date Function
I wrote a function and a SQL to get the  3 columns Date,Total Orders
& Amount, for dates between Date Started and Date Completed if I
pass different Dates in the SQL I get the correct result but if I pass
same dates then I don't get the result I am looking for .For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns.But I give same dates for  Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.Some how I could not trace what could be the error in my SQL /Function.I appreciate if I could get some work around for this.Thanks!  Function:create function dbo.CreateDateList(@start datetime, @end datetime)returns @t table ( [date] datetime )asbegin if @start is null or @end is null return if @start > @end return set @start = convert(datetime, convert(varchar(10), @start, 120), 120) set @end = convert(datetime, convert(varchar(10), @end, 120), 120) while @start < @end begin insert into @t ( [date] ) values (@start) set @start = dateadd(day, 1, @start) end returnend ---------SELECT qUERY---------- SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101) Union SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID Order by Date   

View Replies !
Calling VB.NET Function From Trigger In SQL 2005
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running.
 

I need help on how can I capture insert trigger event VS 2003 console application?

View Replies !
ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View Replies !
SQL Server Trigger Query
I have a trigger written below to update another table when one table is updated.
Basically it works when i update row after row from enterprise manager However when i use a script to update 10 or 20 rows then this trigger does not work. Can any one offer any suggestions on where this issue could be ?
 

DECLARE @iCount INT
DECLARE @OldValue VARCHAR(255)
DECLARE @NEWVALUE VARCHAR(255)
DECLARE @EQNAME VARCHAR(255)
DECLARE @EQTYPE VARCHAR(255)
DECLARE @TEST VARCHAR(255)
SET @NEWVALUE = 'INIT'
IF (UPDATE(VALUE))
BEGIN
 SELECT @OldValue = deleted.value from deleted
 SELECT @NEWVALUE =  inserted.VALUE,@EQNAME  =  inserted.EQ_NAME  FROM inserted where inserted.attr = 'AreaCfg'
    IF (@@ROWCOUNT =0)
    BEGIN
  RETURN
    END
    IF ((@NEWVALUE = '') OR  (@NEWVALUE is NULL))
    BEGIN
        DELETE   [TABLE-1] WHERE ENTITYID = @EQNAME
        RETURN
    END
 IF ((@NEWVALUE != @OldValue))
 BEGIN
  SELECT @iCount = Count(*) FROM  [TABLE-1]   WHERE ENTITYID = @EQNAME
  IF (@iCount = 0)
  BEGIN
   -- No records are available so insert
   SELECT @EQTYPE = Typ  FROM IP_EQ  WHERE EQ_NAME = @EQNAME                                          
   INSERT INTO   [TABLE-1] (AlarmGroupId,  EntityID, EntityType, OwnerId)  VALUES (@NEWVALUE,@EQNAME,@EQTYPE,'Production Tracker')
  END
  IF (@iCount > 0)
  BEGIN
   -- No records are available so insert
   SELECT @EQTYPE = Typ  FROM IP_EQ  WHERE EQ_NAME = @EQNAME
   UPDATE  [TABLE-1] SET ALARMGROUPID = @NEWVALUE WHERE ENTITYID = @EQNAME
  END
 END
END

View Replies !
SQL Server 2005, SQL Server Mobile, SQL Server Management Studio. Unsupported HTTP Function Call
Hi All,

View Replies !
Running Query In Linked Server Through Trigger
i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error: [OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.any suggestions will be appreciated.

View Replies !
SQL 2005 Server &&amp; CLR Trigger...
Hello,
 
   I'm having problems with a CLR Trigger trying to get it to work with SQL Server... and i believe it is because permissions... basicly i want to make my trigger get the name of a newly created database and then look for
a table and then see if is empty... then add some rows... thats all...
 
I have set my databases as Trustworthy and also i have enabled the "clr enabled" parameter...
i mark my project as "Safe" in the Project properties... and it still doesn't work... do i need to
sign my project..? if so.. how?
 
i'm clueless...
 
Thank You.!
 



Code Block
'Table_1' table
- Unable to create table. 
A .NET Framework error occurred during execution of user-defined routine or aggregate "Trigger1":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
System.Security.HostProtectionException:
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
   at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
   at Triggers.Trigger1()
.
The statement has been terminated.

 
 

View Replies !
Need Help With Trigger Sql Server 2005
I am trying to create this trigger

CREATE TRIGGER User_Insert ON aspnet_users AFTER UPDATE AS
IF UPDATE (UserId)
insert into users (aspnet_user_id, username)
select au.userid, au.username
from aspnet_users au
where au.userid != users.aspnet_user_id;

Whenever the aspnet_users table has a new user added I want the Userid and username fields from that table inserted into the
the aspnet_user_id and username fileds of the users table.

when creating the trigger I get the following error..

Msg 4104, Level 16, State 1, Procedure User_Insert, Line 3
The multi-part identifier "users.aspnet_user_id" could not be bound.

View Replies !
Trigger And Update() Function!
In the SQL 7.0 Documentation is indicated that the UPDATE(<fieldname>), if
used in a isert/update trigger, it return TRUE or FALSE based on presence of <field> in the SET List, if the trigger was fired by an UPDATE operation, or in the list of field to fill, if the trigger was fired by an INSERT operation.

Below there is a piece of code that demostrate the false documentation
assertion in the case of a trigger fired by an INSERT operation, infact even
if i have specified only the FIELD1 in INSERT Statement the trigger Print in the screen that i have touched FIELD2 too.

Now the SP1 don't solve this BUG, even if i had read some month ago (before
that SP1 was out) that in SP1 this will be fixed!

Anyone Can Help me to solve this problem, indicating me where i can post the
problem to sensitize the Microsoft's Service Pack Factory ?

Thank in Advance

PS:Sorry for my Bad English .... correct me please if some part are not
readable!

------[Cut Here]-------------------

CREATE TABLE TEST (FIELD1 INTEGER , FIELD2 INTEGER )
GO

CREATE TRIGGER TriggerIU_Test
ON TEST
FOR INSERT, UPDATE
AS

IF UPDATE(FIELD1) PRINT 'Field 1 Touched'

IF UPDATE(FIELD2) PRINT 'Field 2 Touched'
GO

print 'InsertIng '
INSERT INTO TEST (FIELD1) VALUES (2)

print 'Updating '
UPDATE TEST set FIELD1 = 1

GO
DROP TABLE TEST
------[Cut Here]-------------------

View Replies !
Calling VB.NET Function From SQL Trigger
 
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running.
 

I need help on how can I capture insert trigger event VS 2003 console application?

View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly.
I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.
 I have two SQL Server instances installed.
 1- UserLT (this is sql 2000)2- UserLTSQL2005 (this is SQL 2005 named instance)
But when i try to add a database to my VS website project I get the following error:
Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925
I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)
and changed the "SQLEXPRESS" to "USERLTSQL2005".
But I still get the same error message. Any ideas how i can resolve this issue?

View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly
hello,

i've installed SQL server 2005 express and Visual web developper 2005 express.

when i whant to create a database in VWD the following error occures:

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251

i searched the internet but can't find the solution, i already reinstalled my complete workstation but the problem stays.

please help!

View Replies !
How Can I Debug A Trigger In SQL Server 2005?
Hello,I have a form in a web application. When I submit the form it inserts the record into the SQL Server 2005 database. I have an insert trigger. My question is how can i debug the trigger? the trigger then calls a CLR based stored procedure, again how can i debug this CLR stored procedure which gets called by the trigger. Thanks...kindly advice. 

View Replies !
Does SQL Server 2005 Exsit The Trigger ?
When the database starts,I want to startup a trigger to do something,how can I do ?
I know there are two kinds of trigger in SQL Server 2005 ,one is DML trigger and the another is DDL trigger
Could you tell me ,Is there a trigger for database level in SQL Server 2005 ?
I know there is this kind of trigger in oracle .

View Replies !
SQL Server 2005: TRIGGER AFTER INSERT
Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/

View Replies !
SQL SERVER 2005 INSTED OF Trigger
Hi.

View Replies !
(SQL Server 2005) Instead Of Delete Trigger
Hi,

I simply need a trigger to delete some datasets from a view but have some trouble writing an adequate trigger. Here is my attempt:

Use myDB;
Go
CREATE TRIGGER IO_Trig_Del_myView ON myView
INSTEAD OF Delete
AS
BEGIN
SET NOCOUNT ON
-- Check for dataset, if present delete.
IF (EXISTS (SELECT Z.[myPk]
FROM myTable t, deleted
WHERE t.[myPk] = deleted.[myPk]))
Delete From myTable
Where myTable.[myPk] = deleted.[myPk]...

This causes the following failure:

Msg 4104, Level 16, State 1, Procedure IO_Trig_Del_myView, Line 11
The multi-part identifier "deleted.myPK" could not be bound.

Can somebody explain the reason to me? myPk is part of the View I created. Since I do have three tables in myView so I get that message three times, once per table. 

View Replies !
Sql Server 2005 &#043; Row Level Trigger
hi
actually i have a temporay table wich has four columns
say col_1,col_2,col_3,col_4.
so firstly i wud bulk insert from a text file wich mite contain thousands of rows.
then from this temp table col_1 and col_2 shd go to master_a.wich has an identiy column as primary key.
say id,col_1, col_2.

i will have another table detail_b where in there is a foreign key to the table master_a for id.
so it will have f_id, col_3,col_4.

so i am riting a trigger on master_a. so whenver row is inserted in master_a. coresponding id and col 3 col4 shd be inserted into detail_b.

For this in oracle we have row level trigger. where in for each insertion in master a .. correspoding trigger will fire but in sql 2000..
but i wud like to implemtn row level trigger..
can u hlep me out..

View Replies !
Sql Server 2005 &#043; Row Level Trigger
hi
i want to know if row level trigger works with sql 2005.
because in 2000.. only statement level trigger was used.

Thks in advance.

View Replies !
Dont Think Discretization Function Is Necessary On The Analysis Service Server In SQL Server 2005
Hi, all here,

I am just wondering about the Discretization function available on analysis service server (which can not actually discretize data into user-defined expressions) . Isnt it redundant ? I mean since users can discretize their data  more meaningfully based on their own expressions in database engine of SQL Server 2005.

Thanks a lot.

View Replies !
VS2005 Error: Connection To SQL Server Files (*.mdf) Requires SQL Server Express 2005 To Function Properly.
 

Good Evening All,
 
I've serached this forum and Google'd for a resolution to this issue, to no avail.  Here's the scenario:
I'm running VS 2005 on Windows Media Center laptop and need to create ASP.net membership for my web application built using VB.  I have SQL Server Developer installed with instance name MSSQLSERVER.  Previously, I uninstalled SQL Express and installed Developer edition and can now open and utilize Management Studio.
 
Now, when I try to create a new SQL Server database in my solution's App_Data directory, I receive the above error.  I already changed instance name in VS2005 per Tools-Options-Database Tools-Data Connections to MSSQLSERVER. 
 
Could someone provide me with a list of procedures to ensure proper setup of VS2005 with SQL Server Developer Edition?
 
Thanks much for your help.

View Replies !
Split Function - Sql Server 2005
In my table, column1 having a comma separated values.I want to display in rowwise.
for example:
column1
aa,ss,ff
 
output should be
aa
ss
ff
 
Pls. help me..I want to do thr query.

View Replies !
Count Function: SQL Server 2005
is it possible to do a count on the same column but under different circumstances posting the results in two different result columns?
 

View Replies !
Sql Server 2005 GETDATE() Function
I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector.

When I use the SQL Mangment Studio Express to execute this query:

SELECT GETDATE()
it returns 2008-04-22 16:25:03.690 which is OK !!

I want to get that same value using a Java program so I do that query but when I display it it shows as:

2008-04-22  and there is no time there !
I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM
So maybe the time isn´t there ?

This is the code i am using:

            Statement st = con.createStatement();
            String sql = "SELECT GETDATE()";
            ResultSet result1 = st.executeQuery(sql);

result1.next();

            Date today = result1.getDate(1);


Any help would be appreciated.

View Replies !
Trigger On Table-valued Function?
Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.

View Replies !
Simple Trigger For SQL Server Express 2005
I've this tableCREATE TABLE [dbo].[Attivita]( [IDAttivita] [int] IDENTITY(1,1) NOT NULL, [IDOwner] [int] NULL, [IDAttivitaStato] [varchar](1) COLLATE Latin1_General_CI_AS NULL, [IDAttivitaTipo] [varchar](2) COLLATE Latin1_General_CI_AS NULL, [IDAnagrafica] [int] NULL, [Data] [datetime] NULL CONSTRAINT [DF_Attivita_Data] DEFAULT (getdate()), [Descrizione] [varchar](max) COLLATE Latin1_General_CI_AS NULL, [Privato] [bit] NULL, [LastUpdate] [datetime] NULL CONSTRAINT [DF_Attivita_LastUpdate] DEFAULT (getdate()), CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED ( [IDAttivita] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]How Can I create a trigger for modify the IDAttivitàStato field in specific situaion: if IDAttivitaTipo ==  OK or  == NOIDAttivitaStato must be set to CPlease note that I can write in the IDAttivitaStato field (during my code operation).... but I would like also automatic update if the IDAttivitaTipo is OK or NO Can you help me for create this with a trigger?Thanks 

View Replies !
Possible Trigger Problem SQL Server 2005 Express
 Hey guys,

I have a problem regarding (if it’s the
right way to go about it) triggers in SQL Server 2005 Express. I have already finished
my application but wanted to add this in. My system is a shipping system which
allows users to add data about ships, orders etc. In order for a order to be “put
on� a ship I have created a form which first allows users to select what order
and what ship and then creates a new entry on a table called OrdersonShip. What
I would like my code to do is first check that there is space on the ship (Each
ship has a maxCargo and a currentCargo) and then when the new entry on the
OrdersonShip table is produced I would like the numberofitems field on the
order to be associated with the ship to be added to the currentCargo on the
ship table. How would I go about this?

 Many
thanks for any help you can give me.

Peter

 

View Replies !
SQL Server 2005 Trigger Or Stored Procedure
I need to create either a trigger or stored procedure in SQL server 2005(hopefully someone can tell me).. Here is what I need to happen: I have a table with orders that are generated from a website. After the transaction is completed, I need have the record that was just created also copy to another table. There is a field called flag and the values in this field are either 1 or 2. Imediatly after the transaction occurs, I need the records where flag = 1 to copy to this other table. How would I go about doing this?

View Replies !
Design Question, Trigger Or SP [SQL Server 2005]
 

[SQL Server 2005]

 

In our system we keep track on all changes that are made by the users.

This means that posts is never deleted, only marked as not "active".

Each table has a SP who takes care of the INSERT/UPDATE.

This SP also calls another SP, which actually does the "inactive" marking of the post.

The question is:

Would it be better (or worse) to have a TRIGGER taking care of this instead?

Occasionally there are data inserted at the "exact" same time (different input sources).
Sometimes there can be a quite heavy workload on the INSERT.

 

/HÃ¥kan

View Replies !
How To Run User Defined Function On Sql Server 2005
Dear all
I wants to run sql server user defined function when linked two server.
 
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
 
Regards
Jerminxxx

View Replies !
EMERGENCY:rollback Function For Sql Server 2005?
is there a roll back function for sql server 2005 that would change data back to what it was before the execution of last update  query?

View Replies !
Does SQL Server 2005 Has Similar Function Like Mysql_fetch_row ?
I just began to use SQL Server 2005 as database programming and
found out that I have to translate mysql_fetch_row into SQL Server 2005
but I cannot find some related functions/api, and I was wondering
Does SQL Server 2005 has similar function like mysql_fetch_row ?
Or if not, any advice how I can program to acheive similar functions ?
thank you in advance.

View Replies !
SP5 Problem For Insert Trigger Update Function
In recent testing, I have found that the trigger function UPDATE under SQL Server 6.5 SP5 behaves differently then SP4. For insert, the update function returns true on items that have not been explicitly inserted.

Is this a bug? Is this a new feature change? Where can I get more information on a bug list for SP5?

View Replies !
Does A Function Trigger A Profiler SP:Complete Event?
I have a stored procedure that uses a returned table form a function as one of its primary datasources.  When I run the query in Analyzer with a trace on Stored Procedure event SP:Complete I get multiple Complete events.  This leads me to beleive that the function completing triggers the event but I am looking for outside confirmation.

We are having problems with traffic on one network segment and the network admin is pointing to these events and saying that my ap is making multiple calls to the procedure, thus causing the traffic problem..

View Replies !
Maximum Stored Procedure, Function, Trigger, Or Vi
HI ALL,
I AM USING SQL SERVER 2005.
I HAVE RETURN A RECURSIVE FUNCTION TO FIND OUT WHETHER THE NEXT DATE DOES NOT FALL WITHIN HOLIDAYS
BUT I AM GETING THIS ERROR
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

THE CODE I USED IS


alter FUNCTION [dbo].[GetNextDay](@dt datetime , @empcode varchar(50) )
RETURNS datetime
AS
BEGIN
DECLARE @zoneid VARCHAR(50)
declare @lvlflag varchar(50)
declare @utdt DATETIME
DECLARE @RETDT DATETIME
DECLARE @COMPDT DATETIME


Select @lvlflag= b.ulm_user_field_flag from bbraun_emis.dbo.emp_reference a join bbraun_emis.dbo.user_lvl_master b on b.ulm_user_lvl_id = a.ER_USER_LVL and a.er_emp_code = @empcode
SELECT @zoneid = ZONECODE FROM bbraun_emis.dbo.VWREGIONLINK WHERE CITYCODE IN (SELECT DISTINCT HM_CITY_CODE FROM bbraun_emis.dbo.HOSP_MASTER WHERE HM_HOSP_CODE IN (SELECT HER_HOSP_CODE FROM bbraun_emis.dbo.HOSP_EMP_REL WHERE HER_EMP_CODE in(@EMPCODE)))
select @compdt = holiday_date from oriffconnect.dbo.holiday_master where zone_code = @zoneid and field_staff = @lvlflag and holiday_date = @dt

if(@@ROWCOUNT = 0)
begin

Select @utdt = DATEADD(dd,1,@utdt)
SeT @utdt = ([dbo].[GetNextDay](@utdt , @empcode))
end
IF(@@ROWCOUNT <> 0)
begin
set @utdt = @dt
end
Select @RETDT = @utdt
RETURN @RETDT

END




PLEASE HELP

View Replies !
Deploying CLR Trigger To SQL-Server 2005 -- Permissions Required.
I've written a C# trigger on Developer_Box, using VS 2005Professional.I need to deploy it on Test_Server, running SQLServer 2005.It compile fine, and the references to the appropriate database andtable are correct.First attempt:You do not have CREATE Trigger permission.Solution - Walk over to Test_Server, open management studio and givemyself CREATE Trigger permission.Second Attempt:"Cannot create the trigger 'MyTrigger', because you do not havepermission."What it doesn't tell me is what permission is missing.I could run this as admin, but I'm going to need to deploy it onProduction_Server eventually, so I want to request the minimalpermission set possible. (And I won't get admin on Production)compatibility mode is 90;CLR is enabled via sp_configure (and RECONFIGURE was run)

View Replies !
SQL Server 2005 Trigger Fires Per Statement Or Per Record
Hi

  In SQL Server 2005, does TRIGGER for DML fire per statement or per Record ?

  If both, can we define the way it should fires.

thanks / ramanuj

View Replies !
How Can A Trigger Act Asynchronously To The Event That Fired It In Sql Server 2005?
if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place.  We are more concerned with the original event being committed than the actions that follow via the trigger.     

View Replies !
How To Determine, Inside A Function, If A Linked-server-query Returned Results
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL

(CAT_ASA is the linked-server's name)

Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!

View Replies !
How To Create Assembly Function Using Dll Files In SQL Server 2005???
Hiiiiiiii all
 
I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration
 
I have tried like this
 

CREATE ASSEMBLY abc
FROM 'C:abc.dll'

WITH PERMISSION_SET = SAFE
 
but it gives me
incorrect syntax error 
so plzzzzz anyone help me wht to do in my probbbbbbbbb???????
 
Pratik Kansara

View Replies !
How To Read CSV File In SQL Server 2005 Using OpenRowSet Function
Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

 

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

View Replies !
Question About Querying Xml Returned By Eventdata() Function In Ddl Trigger
Hi All,
I wanted to query the xml returned by the eventdata() function in a ddl
trigger to view it in result set.
 
I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for
ddl_database_level_events
as
--select
eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @data xml
select @data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post
Time'
from
@data.nodes('/EVENT_INSTANCE')
as EventsTable(Col)
go
 
 
Thank you in advance,
Bishoy

View Replies !
Crear Archivo De Texto Desde Trigger En SQL SERVER 2005
 

Necesito crear un archivo .txt con cierto texto cada vez que se ejecuta el trigger. Quisiera saber como hacerlo.
Gracias

View Replies !
SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record
Hello,
 
I am trying to figure out how to use the select maximum command in SQL Server 2005.  I have already created a database and I have it populate it with multiple fields and multiple records.  I Would like to create a new column or field which contains the maximum value from four of the fields.  I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column. 
 
Any help you can provide will be greatly appreciated!
 
Thank you,
Nathan

View Replies !
ODBC Link From Access To SQL Server 2005 Stored Function
If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?

I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.

I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.

What am I missing?

Suggestions?

View Replies !
Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?

View Replies !
Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32
Hi,

I face this error when i try to run my store procedure.
The sample of store procedure as following:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON

DECLARE @COUNTER INT
SET @COUNTER = 0

DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END

OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)

SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END

CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF


anyone can help me identify this error?


Thanks


Regards,
Jojomay

View Replies !
Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32)
Hello,

I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


Please let me know what should be the reason?
Thanks,
Ravi

View Replies !
Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded
Hi all,

I have writen a Function which call's the same function it self. I'm getting the error as below.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.

CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

View Replies !
Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded (limit 32).
I have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?

View Replies !

Copyright © 2005-08 www.BigResource.com, All rights reserved