Viewing A SQL Statment's Affects Before Commiting
Jun 22, 2007Hi all,
I work fo ra company which doesn't have a test database.
Is there any way of viewing the changed to records an SQL statment will make before commiting it?
Thanks
Dave
ADVERTISEMENT
Insert And Delete Statment In One Statment
Jan 18, 2008
Hi all
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
Thank you in advance .....
Regards,
sms
Records Not Commiting - Please Help!
Jul 30, 2007So, I have this SSIS package that basically moves records from one table into another table in the same DB. I€™m running it on the actual server and everything seems to work. The data-flow task lights up yellow and visual studio increments the number of records passed through as if they were running perfectly. However, when I go to look at the data, it€™s not in there! There are no errors reported ad the event log doesn€™t show anything that would lead me to think anything went wrong. Anyone ever run into this issue? I€™m wracking my brain on this one and am completely stumped. Any help would really be appreciated
View 6 Replies View RelatedCommiting A Trigger Under Certain Conditions
Sep 18, 2007 I have a sitemapcache that caches nodes with a sqlcachedependency. Everything is working fine but one thing. Every time you visit a forum, the viewcount is changed, therefore raising the trigger and dropping my cache object. How do i make it so that the trigger is only fired if I update the Title or Description field? ALTER TRIGGER [dbo].[sp_Forums_Topics_AspNet_SqlCacheNotification_Trigger] ON [dbo].[sp_Forums_Topics] FOR INSERT, UPDATE,DELETE AS BEGINSET NOCOUNT ONEXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'sp_Forums_Topics'
END
ADO Write Not Commiting To Database
Sep 6, 2007This seems about the best place for my query however it does cross over a bit ... dont shoot me.
SQL 2000 sp4 on server 2003
dev env VB6
users XP Sp2
Mdac 2.8
I have a legacy app that required some simple ammendment, insert a single row of data into a simple table (Not rocket Science) Sample : insert into maintcon_mach (maintcon_id, mach_id, date) values (123, 456, getdate())
PK on maintcon_id, mach_id so that single relationship exists between maintcon_id and mach_id in the table.
The procedure above is followed by a verification procedure that checks to see if table maintcon_mach has a mach_id 456 against maintcon_id 123, this procedure returns true. So far so good, however if you step through the procedure till disconnecting from the database and then reconnect and do only the verification again, the data is missing.
The following steps to resolve have resulted in no improvement.
1. Verify MDAC in entire project and on users computers all 2.8
2 Remove transactions (just in case) and still the same.
3 Replace SQL insert with ADO (As below) and still the same.. tried this with and without transactions.
4 Tried referecing older MDACs
5 Installed SQLredist on sample machines and still the same.
rs.Open "maintcon_mach", conMach, adOpenForwardOnly, adLockOptimistic
With rs
.AddNew
!maintcon_id = lngmaint
!mach_id = lngMachID
!Date = Format(Now(), "yyyy-mm-dd")
.Update
End With
Is the problem in SQL or MDAC???
Perhaps I've missed something (Obviously have) but have run out of places to look... Any idea's
In advance ...thanx
Commit Not Commiting Inside Stored Procedure
Jun 23, 2006Hi Folks!
I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.
While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.
Here is my sample record in table :tblMsg
SenderRef <space> MsgStatusId <space> MsgType <space>Groupid
1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902
1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space>
and here is my code.
CREATE PROCEDURE msg_ResolveADMBeforeBlim
AS
DECLARE @blimGroupId AS INT
DECLARE @admSenderRef AS VARCHAR(50)
DECLARE admCursor CURSOR FOR
SELECT senderref
FROMtblMsg
WHERE msgstatusid = 18
AND msgtype = 'KBC-RCVD-ADM'
FOR UPDATE
OPEN admCursor
FETCH NEXT FROM admCursor INTO @admSenderRef
WHILE @@FETCH_STATUS = 0
BEGIN
--FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM
SELECT @blimGroupId = GroupID
FROM tblMsg
WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM'
AND SenderRef = @admSenderRef
PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef;
--UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3
BEGIN
UPDATE tblMsg
SET MsgStatusId = 3, GroupId = @blimGroupId
WHERE CURRENT OF admCursor
END
PRINT 'AFTER UPDATE INSIDE WHILE';
END
CLOSE admCursor
DEALLOCATEadmCursor
The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.
Any ideas why this would happen?
Thanks for your help.
Arun
Commiting Transactions/Truncating Transactional Logs/Replication Latency
Feb 22, 2008Hi,
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
Thank you so much in advance!
Sincerely,
Alla
Transaction Not Commiting Using Microsoft SQL Server 2005 JDBC Driver 1.1
Feb 14, 2007I have some code that should execute multiple statements and then commit. I am using the Microsoft SQL Server 2005 JDBC Driver 1.1.
The statements only commit ifI close the connection to the SQL Server 2005 instance. I've looked at the example given at http://msdn2.microsoft.com/en-us/library/ms378931.aspx and it doesn't require closing the connection in order to commit.
Example:
public void doCall(Connection con) {
try {
/*Turn off AutoCommit.*/
con.setAutoCommit(false);
/**Call Two Stored Procedures.*/
Statement stmt = con.preapareCall(/*Java Code*/);
stmt.setObject(/*Java Code*/);
stmt.execute();
Statement stmts = con.preapareCall(/*Java Code*/);
stmt2.setObject(/*Java Code*/);
stmt2.execute();
/**Commit the transaction.*/
con.commit();
}
catch (SQLException ex) { //If some error occurs handle it.
ex.printStackTrace();
try {
con.rollback();
}
catch (SQLException se) {
se.printStackTrace();
}
}
}
My transaction never commits, and when I try to query the associated database tables in Microsoft SQL Server Management Studio 2005 my query hangs.
How AD Affects SQL Server2000/2005
Dec 5, 2007hi
at my work place some of the users migrated to another domain even thought with new domain name they can access SQL Server but user who are part of sysadmin(part of some group) on the box unable to execute a job. it means at operating system level newdomainuser credentials are get resolved that's why users are able to access box but why not able to execute jobs. why sysadmin Privilege not get resolved at SQL Server level very confused............
Stopping SQL Services Affects Users ?
Oct 12, 1999If I stop MSSQLServer service on my SQL7 server using net stop and then restart it seconds later with net start, how will this affect my users ? Will they notice this ? Gets disconnected ?
The reson is to memory recycle my server.
..Harold
Changing XP Password Affects SQL Server?
Apr 27, 2006hi folks,
has anyone experienced this issue?
When one changes the login password for XP, all the SQL Server groups are removed the next time you log into SQL Server 2000.
Is this a bug or a security feature, is there any way around it?
thanks for any help!
Author Of Package Affects Login?
Apr 23, 2007This is a very frustrating problem.
Let me break it down for the reader.
I have an agent service account that works fine - the account that jobs run under.
I know it works fine because this is the same account all my SQL Backup jobs run under - without a hitch.
I also have a valid sql server account that I use for connection string logins when building packages -
that account works fine. I know it works fine because I can login to the SQL Server Manager with this account.
So, I build a package with the sql server account and password for the OLE DB connection manager in a package. All the package does is a simple query on a table and outputs to a flat file.
I create a job, with 'sa' as the owner. in step 1 I select SSIS package, run as SQL Agent Service Account, package source is file system and I point to the package. The package itself uses the sql server login account to execute.
If I run the package it works fine. I close the solution, someone else comes along and opens the solution - THEY CHANGE ABSOLUTELY NOTHING ABOUT THE LOGIN OR SQL AGENT SERVICE ACCOUNT, but apparently just the fact that they opened the solution and looked at the package, breaks the login. If they try to run the package, they get a login failure, whereas I did not. After they close it, it breaks for me as well. If they open the solution, open the connection manager, re-enter the sql login's password, the package works fine for them, until I come along and open the solution, then it is broken again.
This makes no sense to me. Why would an 'author' opening a solution impact the connection manager when ABOLUTELY NOTHING ABOUT THE CONNECTION MANAGER WAS CHANGED?!
Seems like a bug to me. Anyone seen something like this?
Thanks in advance.
randy
SaveCheckpoints=True Affects Recordset
Jun 16, 2006I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:
Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable
adp.Fill(dt, Dts.Variables("CountryTable").Value)
It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.
Note: I have SP1 installed.
SQL7: Order Of Values In IN Clause Affects Results
Dec 14, 1999Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go
returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!
SQL 2012 :: DB Setting That Affects DBCC Checkident (reseed)?
Apr 23, 2014I have encountered an anomaly. The dbcc checkident(reseed) command behaves differently on two SQL servers.
In both cases, I am deleting from (not truncating) data in two tables, due to foreign key constraints. (I am truncating other tables, the issue is not with those tables, only with the deleted-from tables.) On one server, I need to use dbcc checkident(reseed,0) so that when I insert fresh data, it begins with identity key #1. According to MS documentation, that appears to be the correct behavior, when data has been deleted from a table, rather than truncated.
However, on the other server, I need to use dbcc checkident(reseed,1); if I use ...(reseed,0) on that server, it begins inserting data with identity key #0.
This is consistent, repeatable behavior on both servers.
Transactional Replication From SQL 2005 To SQL 2000 Affects Performance Of Database.
Sep 21, 2007
Hello,
We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for
Server A :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2000 SP 2
Server Z :
purpose : used for Reporting
SQL Server version : SQL Server 2000 SP 2
We were doing Transactional replication from Server A to Server B.
Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.
Server B :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2005
I have set up the transactional replication from Server B to Server Z and replication works fine.
However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)
I didnt expected this as our reporting server is still SQL server 2000.
I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)
I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).
I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.
Execution plan now shows "Table Spool" which was not present in previous copy of database.
Almost every query for this database is taking longer time now.
Can someone suggest me what is wrong and what should I need to fix.
Am I going on the right direction?
View Or Function Xxx Is Not Updatable Because The Modification Affects Multiple Base Tables?????
Aug 17, 2007I have read that I need to add an instead of trigger. I have no idea how to do this. I am basicaly trying to update at the moment but will also need to insert later.
From the code in VWD2008 I only get the error when i include this info here: When i press update and these below are included in the update statement then i get the error.
[S_DATE] = @S_DATE, [END[IS_CONFIRMED] = @IS_CONFIRMED, [IS_PAID] = @IS_PAID, [S_Descript] = @S_DESCRIPT[COMPANY] = @COMPANY[MONTH] = @MONTH[ACCOUNT] = @ACCOUNT
Here is view from SQL 2000.
CREATE VIEW dbo.VIEW_TRAINING
AS
SELECT dbo.ADDRESS.EMAIL, dbo.ADDRESS.FIRST_NAME AS [first name], dbo.ADDRESS.LAST_NAME AS [last name], dbo.ADDRESS.STATE,
dbo.ADDRESS.TEL1 AS phone, dbo.CUST.NAME AS Company, dbo.ITEMS.DESCRIPT AS S_Descript, dbo.ADDRESS.JOB_TITLE AS Job_Title,
dbo.TRAINING_SCHEDULE.[MONTH], dbo.TRAINING_SCHEDULE.S_DATE, dbo.TRAINING_SCHEDULE.END_DATE,
dbo.TRAINING_SCHEDULE.IS_CONFIRMED, dbo.TRAINING_SCHEDULE.IS_PAID, dbo.TRAINING_SCHEDULE.CUST_CODE AS Account,
dbo.TRAINING_SCHEDULE.SCHEDULE_ID
FROM dbo.TRAINING_SCHEDULE INNER JOIN
dbo.CUST ON dbo.TRAINING_SCHEDULE.CUST_CODE = dbo.CUST.CUST_CODE RIGHT OUTER JOIN
dbo.X_INVOIC RIGHT OUTER JOIN
dbo.INVOICES ON dbo.X_INVOIC.ORDER_NO = dbo.INVOICES.DOC_NO LEFT OUTER JOIN
dbo.ADDRESS ON dbo.INVOICES.CUST_CODE = dbo.ADDRESS.CUST_CODE LEFT OUTER JOIN
dbo.ITEMS ON dbo.ITEMS.ITEMNO = dbo.X_INVOIC.ITEM_CODE ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODE
WHERE (dbo.X_INVOIC.ITEM_CODE LIKE 'FOT-%') AND (dbo.X_INVOIC.STATUS = 7) AND (dbo.ADDRESS.TYPE IN (4, 5, 6)) AND (dbo.ADDRESS.EMAIL <> '') AND
(dbo.ADDRESS.COUNTRY = 'UNITED STATES') AND (dbo.ITEMS.CATEGORY = 'TRAININGCLASSES') AND
(dbo.TRAINING_SCHEDULE.CUST_CODE = 'steve')
Here is the forms code I am working with: used VWD to generate the forms...I added update...<%@ Page Language="C#" AutoEventWireup="true" CodeFile="training.aspx.cs" Inherits="training" TRACE = TRUE%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:FormView ID="FormView1" runat="server" DataSourceID="dstraining"
DefaultMode="Edit" AllowPaging="True">
<EditItemTemplate>
EMAIL:
<asp:TextBox ID="EMAILTextBox" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
<br />
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel1" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
<br />
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
CommandName="Update" Text="Update" />
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<InsertItemTemplate>
EMAIL:
<asp:TextBox ID="EMAILTextBox" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate>
<ItemTemplate>
EMAIL:
<asp:Label ID="EMAILLabel" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:Label ID="first_nameLabel" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:Label ID="last_nameLabel" runat="server" Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:Label ID="STATELabel" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:Label ID="phoneLabel" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:Label ID="CompanyLabel" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:Label ID="S_DescriptLabel" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:Label ID="Job_TitleLabel" runat="server" Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:Label ID="MONTHLabel" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:Label ID="S_DATELabel" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:Label ID="END_DATELabel" runat="server" Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:Label ID="IS_CONFIRMEDLabel" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:Label ID="IS_PAIDLabel" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:Label ID="AccountLabel" runat="server" Text='<%# Bind("Account") %>' />
<br />
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
<br />
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="dstraining" runat="server"
ConnectionString="<%$ ConnectionStrings:EVEREST_FIBEROPTConnectionString %>"
SelectCommand="SELECT , [first name] AS first_name, [last name] AS last_name, [STATE], [phone], [Company], [S_Descript], [Job_Title] AS Job_Title, [MONTH], [S_DATE], [END_DATE], [IS_CONFIRMED], [IS_PAID], [Account], [SCHEDULE_ID] FROM [VIEW_TRAINING]"
UpdateCommand="UPDATE view_training SET email=@email, [FIRST NAME] = @FIRST_NAME, [LAST NAME] = @LAST_NAME, [STATE] = @STATE, [PHONE] = @PHONE, [JOB_TITLE] = @JOB_TITLE, [COMPANY] = @COMPANY WHERE Account = @Account">
</asp:SqlDataSource>
</form>
</body>
</html>
View Or Function 'MutualFund' Is Not Updatable Because The Modification Affects Multiple Base Tables.
Mar 4, 2008View or function 'MutualFund' is not updatable because the modification affects multiple base tables.
I get the above error when i try to delete rows from a view.
i tried the following code with a table. it works perfectly. i want the same behaviour for a view.
can anyone help.
Delete MutualFund
from MutualFund inner join MutualFund_history
on MutualFund.SecurityID= MutualFund_history.SecurityIDwhere MutualFund.MorningstarCategoryID=MutualFund_history.MorningstarCategoryID and
MutualFund.MorningstarAssetClassID= MutualFund_history.MorningstarAssetClassID and
MutualFund_history.insertdt =(Select MAX(DsegLastUpdated) from Timestamp_History(nolock))
"MutualFund" is a view.
if it were a table, the above works well. but how can i simulate the same effect for a view.
any help will be greatly appreciated.
thanks
Help With Sql Statment?
Jul 11, 2006I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo
View 6 Replies View RelatedSQL Not Statment Help
Jul 11, 2007I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si!
View 19 Replies View RelatedSql Statment
Nov 13, 2007Select @ID = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4 AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
Need Help With Sql Statment
Apr 14, 2008Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named resolvidos with the field Resol (numeric)
How can i select all the records from table 1 who are not in Tabe2 ?
Thank you
mario
IF Statment
May 16, 2002Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!
View 1 Replies View RelatedIf Statment
Apr 2, 2007need help with if statment in a stored procedure
here is what i have and it does not work
Code:
CREATE PROCEDURE Get_ckcompany
@cknum int,
@company varchar
AS
if (@cknum is not null) and (@company is null)
select *
from PMTK_tbl
where Company = @company
else if (@compnay is not null) and (cknum is null)
select *
from PMTK_tbl
where check_Num = @cknum
else
select *
from PMTK_tbl
where Check_Num = @cknum and Company = @company
end if
GO
Need Help With An Sql Statment
May 1, 2008I have a database used for a point of sale system
it has a main table with the total amount of a check - the tip
the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.
The statement i have now is like this
select jc.total + sum(jp.tip) as total from jc innerjoin jp on key
where total <= @max and total >= @min
group by jc.total
but this statement uses total before the tip is added. I am not sure how else to do this any help would be great
SQL Statment
Jun 1, 2006select zsong.song, zsong.trk
from zsong, zfmt
where zfmt.upc='13117' AND zfmt.muzenbr=zsong.muzenbr
That staement works, but if the UPC code is repeated twice it returns the results twice (i want to only read it once)...
how do i make it only return the first result or the last one or whatever (since they all reference the same thing)
Use Statment
Jun 9, 2008Posted - 06/09/2008 : 11:10:47
--------------------------------------------------------------------------------
trying to run the following script.
use 001
select *
from imitmidx_sql
Get incorrect syntax near 001
If I change the use 001 to Data_58 it works fine. Do I need special syntax when the database name is 001??
SQL Statment
Feb 18, 2007Hello All,
I imported a excel file from SSIS and created a table called Lockbox.
To avoid the user from having to change the excel file -it is being imported as is.
I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]
The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID.
The columns are in diffrent order.
And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF'
and convert to a diffrent data type [Site ID] to text.
Is their a sql statment that can do this?
SQL2005
Thanks for your time
Sql Statment
Aug 17, 2007hi,
I Have a following SP running on sql 2005:-
DECLARE @MONTHNO int
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
SELECT (acctno),@CR from bmaster
. The table has a 12 field with cr_1 ... to 12
. the sql can’t understand the @cr the column papers "with No column name'
SQL Statment By Asp.net
Oct 8, 2006is there any chance to use the follwoing script to add new record to the database
"
rs.addnew
rs.("name")="Scot"
rs.update
"
insted of the old fashion " insert into ...."
SELECT Statment
Mar 20, 2007Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows.
Thanks
Steve
A SELECT Statment, Please Help Me
Oct 2, 2007hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS
Optimize SQL Statment
Mar 9, 2008Hello, I have three sql select statments I would like to combine into one. I have created a statment that works but I am not sure if it is a good solution performance wise. Is there a better way to run this query?
Thanks Very Much!!
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate)
Begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate
End
else
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc)
begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc
end
else
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Order By nextupdate