I have a source table and Destination table initailly i have loaded some data into destination table through some conditions
Target.col1= Source.col1
AND Target.col2= Source.col2
MERGE INTO dbo.TestDestination AS Target
USING (select col1,
col2,
col3,
[code]...
then data will get filtered. So initial data is wrong data as per conditions so i need to delete initial data and load latest filtered data with these conditions.
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedFor example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
All,
I have a URL Action (defined on cells based on condition specified in MDX) defined in my SSAS 2005 Cube that opens a page in our ASP .Net application.
I use custom MDX statements within SSRS to generate report. Is it possible to expose the above action in a report generated using reporting services ? (in which case I can use the "Jump to URL" feature in SSRS to redirect the user to the appropriate web page) If so, please can you specify how?
Thanks in advance,
Arun
Hi guys,I wanna ask bout the problem with my web application. I'm doing a select a statement from table 1 and and with the query results i got, i need it to store the result on table 2. How will i do this? I need your tips and suggestions.
View 1 Replies View RelatedI have the following query:
SELECT tblArticleCategory.ACategoryID, IsNull(ParentCategory, '') + IsNull( ' > ' + ACategoryName, '') AS Category from tblArticlecategory
RIGHT OUTER JOIN (SELECT ACategoryName as ParentCategory, ACategoryID from tblArticleCategory WHERE AParentID is null AND aActive=1) AS Parent
on parent.ACategoryID = tblArticleCategory.AParentID
WHERE tblArticleCategory.AActive=1ORDER BY Category
This produces an ordered list of the parent categories and subcategories from the table as long as there is a subcateogry directly below one of the parent categories. If there isn't one directly below the parent category, the parent category is dropped from the list. None of the parents with the NULLS back to back are in the list.
6 Arts & Entertainment > Dance2 Arts & Entertainment > Movies13 Computers > E-Learning4 Computers > Hardware14 Computers > Java16 Computers > Link Popularity17 Computers > Microsoft.net15 Computers > RSS5 Computers > Software8 Real Estate > Finance
How do I fix the query so that Automotive, Business, Cancer and Communications are included in the list?
Can any one u explain "ON UPDATE NO ACTION While Creating Table"
View 2 Replies View RelatedHi All,
I have a situation where there are two tables Negotiation and Rubrics. A single negotiation can have N number of Rubrics. Hence it has Primary Key - Foreign Key relationship. Now I am using two different updatable recordset, to make changes from the form.
The problem is with the close button we have in Negotiation Form. For example, a user says create negotiation and fills in d details in the Neg form, and adds several rubric to it (which opens a seperate rubric form). When each rubric is created and comes back to negotiation form, the record is actually updated in the database from the recordset. (Since Rubric is a different recordset).
Now, When i say close from the Negotiation Form. The changes should not be committed or in other words, no changes should be committed, generally its should be rolled back to the original state. Since all the rubric info is already in the database, this is a problem.(hard to track wat all rubrics the negotiation had initially)
I thought of maintaining a transaction in the db, but that wld need a single connection, which is not recommended.
Please suggest us how this can be implemented.
Thanks in advance.
Greetings -
I have a report which contains quite a few parameters for user input. The user wants the parameters arranged in a 'down & over' orientation. This has been done via the parameter order.
The next question is - how do I get the tab action between the parameters changed from the 'over & down' default to the new 'down & over' orientation?
Is this functionality currently supported?
Tks & B/R
I had a situation over the long weekend where the SQL Server was restarted manually and even though all the SQL Server Services were set to start "Automatically" the SQL Server Agent did not come up. Fortunately most of my batch loads do not use the job scheduler but run by way of DOS batch scripts.
When I went to restart the Service I noticed that in the properties for the Services for all the SQL Server services that in the recovery tab that the First Failure, Second Failure and Subsequent failures were all defaulted to "take no action".
My question is what should they be set to "Restart the Service"? I searched MSDN and the web and didn't find anything on this subject. Is there any harm is setting them all to "Restart the Service"? (we have no OLTP databases only OLAPs in this server).
This leads me to wonder why wouldn't they be defaulted to "Restart the Service".
Thanks,
DaveK
Hello
Is it possible to create a trigger with multiple actions?
I would like to create a trigger with INSERT, DELETE, UPDATE funtions but I have not been above to find a clear syntax example
Below I have created a statement of my trigger. Could somebody please confirm if the syntac in this multiple action trigger is ok.multple actions on a single trigger
Is this syntax correct?
CREATE TRIGGER [dbo].[trig_AddDomCatA] ON DomainNames For INSERT, DELETE, UPDATE
AS
INSERT INTO Domain_CatA (DomainName)SELECT DomainName FROM INSERTED
ASDELETE FROM Domain_CatA (DomainName)SELECT DomainName FROM DELETED
ASUPDATE INTO Domain_CatA (DomainName)SELECT DomainName FROM UPDATED
Thanks
Lynn
Hello all,
I've run into a situation which I'm not sure which option to use. I have a web-based app that needs to send an email to a user once an auction has ended (the user being the winning bidder). The backend of the app is SQL 2005. I'm trying to figure out how to get this to work but I'm not sure if I should be using Database Mail or if I should be using a windows service on the web server that pings the database to check if the auction has ended and if the user has been notified. Can anyone shed some light as to which way I should be doing this? Additionally, I'm planning on formatting the style of the mail message so it'll need to be in HTML format and not plain text.
Any help is greatly appreciated.
Regards,
Axel
I want to pull up records where the record begins with a letter. I have
a customer table and I want to pull up all customers whose names
BeginWith whatever letter.I was wondering if Sql had anything along
these lines, like:
"Select * From Customers Where Name Begins With A"
I know that is not a function in Sql, but I was wondering if there was
something like that, or if there is a better way to do this.
Thanks!
Austin W.
Hello
If I have a trigger for INSERT and DELETE, how do I know what action was executed ??
I prefer not to split the trigger to one for each action, because the the procedure for both is almost the same.
Another question, How can I know what records were updated? I need to concatenate the ID/ID's to the SQL in the procedure.
Thanks,
Inon.
I need to define a TRIGGER on a table (temp1) that executes the triggered action for each row of the subject table(temp1) that the triggering SQL modifies. If the triggering SQL operation does not modify any rows, the triggered action is not executed.E.g., If SQL modifies 100 rows, trigger executes 100 times. How can i achieve this type of Trigger in SQL Server .In DB2 i can acheive this by specifying 'FOR EACH ROW' clauseKindly suggest me
View 11 Replies View RelatedIs it possible to use a DMX query as an action in an OLAP cube? So when someone clicked on a product when browsing the cube, it called my DMX query with a product parameter, and the query would return a listing of associated products? I'm not asking how to do the query, I already have that, but just wondering if it's possible to even do that. Thanks!
View 3 Replies View RelatedHi friends
i've a table that has 3 columns that refer to same parent table(foreign keys).am trying to following as part of action.
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_BATCHTO FOREIGN KEY
(
FK_BATCHTO_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_FEESCALER FOREIGN KEY
(
FK_FEESCALER_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE set null
ON DELETE set null
GO
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_SUBDEBT FOREIGN KEY
(
FK_SUBDEBT_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE set null
ON DELETE set null
GO
but it wont let me complaining with following error
"Introducing
FOREIGN KEY constraint 'bl..blahh' on table 'childTable' may cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO
ACTION, or modify other FOREIGN KEY constraints."
what am i missing here
Hi all, i've created a trigger that fires 'INSTEAD OF INSERT, UPDATE, DELETE'.
It work's fine but need to know how i can know when was fired by insert,delete or update event so i can decide the actions to take without creating an individual trigger for each sentence.
Thanks in advance
Hi,
I have a database with 10 tables, and I need one trigger to each table that for insert, update and delete, get the registry that is been modified and insert these data in another table...
How can I do that ?
Thanks
I have a graph and on the graph I have created an action to go to another chart. My first chart has two parameters. When I click the actions points to go to my second chart I have three parameters. Since my first one has 2 and second one have 3, I keep getting an error "invalid parameter" in the first graph. Can this be corrected. Can I use 2 parameters - pass them to the second then select the third parameter?
I have database on SQL Server 2000 set up with a merge publication.This publication is configured with a number of dynamic filters toreduce the amount of data sent to each client. Each client has ananonymous pull subscription. The merge process can be triggered by thewindows sync manager and my application.To improve performance I have created some helper tables to hold themapping between user login and primary keys of selected entities.For the replicated data to be correct the contents of the helper tablesneeds to be up to date.I need to fire off a stored procedure on the publisher beforereplication starts to verify that this data is up to date. I can notsee any documented way of doing this however I have been experimentingwith some unorthodox systems.Firstly has anyone any ideas?I have been considering adding a trigger to some of the tables used bythe Microsoft replication code - yes I know this is very nasty.My problems arise because executing this stored procedure will causesome data to be updated. In updating data we could create a newgeneration in the database. I must therefore run my stored procedurebefore any the Microsoft code makes any generation checks / updates.Anyone done anything similar, Anyone have any better ideas?Any comments would be gratefully received.
View 1 Replies View RelatedI am trying to use a query analyzer to perform a task and I keep getting this message....how do I get around this?
View 1 Replies View RelatedAt Sql Server Live, I saw Sharon Dooley using the debugger in Query Analyzer, but now that I've tried it I can't step an sp, instead, I get....Quote:
SP debugging may not work properly if you log on as 'Local
System account' while SQL Server is configured to run as a
service.
You can open Event Viwer to see details.
Do you wish to continue?
End Quote:
I'm logged in at a W2Kpro box connectig via client tools
to a W2KSVR box via Integrated NT security. SS2K is NOT
installed locally.
Hi
Im in a bit of a pickle due to a large amount of data in a table and the query Im running is taking a very long time, so Id like to get some advice from the guys here to see if it can be done another way.
Ok, so heres the background. I have a table, see desc below, that contains transaction data for a number of users. Each user may have more than one transaction. In each row the SEQUENCENUMBER column is populated with a timestamp for the transaction, so I was using this to get the latest. However there are over 1 million rows in the table and the query below has taken over 12 hours so far to run. Has anyone any ideas on how I can make life a little easier for myself here?
SQL Query
Code:
select user,recipient,sequencenumber from trans_database where user IN (select distinct(user) from trans_database)
order by sequencenumber desc limit 1;
Table Layout
Code:
ID int(10) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references
RECIPIENT varchar(5) latin1_swedish_ci NO select,insert,update,references
DONOR varchar(5) latin1_swedish_ci NO select,insert,update,references
USER varchar(20) latin1_swedish_ci NO select,insert,update,references
UPDATETYPE varchar(10) latin1_swedish_ci NO select,insert,update,references
REPFLAG varchar(1) latin1_swedish_ci NO select,insert,update,references
OPFIELD varchar(20) latin1_swedish_ci NO select,insert,update,references
SEQUENCENUMBER varchar(18) latin1_swedish_ci NO select,insert,update,references
REQUESTDTS varchar(20) latin1_swedish_ci NO select,insert,update,references
PROCESSED varchar(1) latin1_swedish_ci YES (NULL) select,insert,update,references
Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.
Column 1 is holding a true statement that we need to update to false if over 10 mins old...
Dear all,
I am using sql server 2005.
Let me explain the scenario : I am making request system where one request goes to immidiate boss and its immidiate boss till the contry head and even one has right to approve or reject the request. Once its approve from one level it goes to other level but now problem is if some one in the hierarchy does not responds.
My table staructure is
RequestnoRequestempRequestdateSuperierempExpirydatestatus
0001 Em486 21-04-2008 Em604 23-04-2008 Hold
To do the same I am trying to schedule a job which run one sp where I am searching all the superieremp whose status is hold and expirydate is today date. Now problem is when there is multiple record as against each indivual I have to insert the record in the same manner
Please suggest some ideas or any other idea please suggest
Thank you.
hi,I am getting the following error while trying to add a remote server.The user does not have permission to perform this action.The command i used is:exec sp_addlinkedserver 'remote_server_name'What is the solution?Regards,Kalaivanan.
View 1 Replies View RelatedHi,
I'm trying to create a custom report item with the action functionality without sucess, in the Process method I create the Action like this:
Action action = Action;
action.SetDrillthroughAction("myreport");
if (imageMapArea.ActionInfo == null)
imageMapArea.ActionInfo = new ActionInfo();
if (imageMapArea.ActionInfo.Actions == null)
imageMapArea.ActionInfo.Actions = new ActionCollection();
imageMapArea.ActionInfo.Actions.Add(action);
m_ImageMap.Add(imageMapArea);
The Action is the property of the interface:
ICustomReportItem
public Action Action
{
get
{
if (m_action == null)
{
m_action = new Action();
return m_action;
}else
return m_action;
}
}
}
When I click in my custom report item I get theses error message: "The source of the report definition has not been specified"
If I change the line:
action.SetDrillthroughAction("myreport");
for this:
action.SetHyperlinkAction(http://www.google.com/);
Every thing works fine. How can I solve this?
Hello All:
I have a web-based utility that enables users to enter data. I have created several SSIS packages that run on a regular basis that act on the data being collected. While the packages run on a set schedule now I would like to provide users the ability to trigger a package on an as-needed basis as well. The question is how do I do it?
I do not know VB.net, and believe that the web app has been coded using ASP.net. I am looking for some high-level pointers to go about my task. Any links to some articles would also be appreciated.
Thank you!
Hi,
I'm using a SQL Server Analysis Services data source.
I have existing reports that I have to modify.
The report preview and layout functions work until I switch to the data tab.
When I switch to the data tab the DATASET GETS BLOWN AWAY!!!!!!
This does not happen with reports using a SQL Server data source.
I have:
copied a report
loaded the original report and switched to the data tab
saved the report
compared the copy to the ruined versionThe only difference is the datasets section
Some version info:
Microsoft Visual Studio 2005; Version 8.0.50727.762 (SP.050727-7600)
SQL Server Reporting Services; Microsoft SQL Server Reporting Services Designers; Version 9.00.1399.00
Why is this happening?
What can I do to prevent SSRS from ruining these reports?