Get Tables Involved In Operation
Jun 17, 2014Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.
View 2 Replies
ADVERTISEMENT
How To Get Tables Involved In Constraint
Jan 18, 2006Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you
View 2 Replies View RelatedUpdate Query When Joined Tables Are Involved
Sep 30, 2013I have a query written that filters on joined table data. The SELECT looks like this:
SELECT *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
SQL2000: Disable Trigger Not Allowed In Tables That Involved In Publication.
Apr 10, 2007We have setup a replication in SQL2000:
We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command
Alter table 'tbl' disable trigger 'abc' errored out. The error message said:
''Cannot alter the table 'tbl' because it is being published for replication."
I've digged more into this and found although it's not allowed to disable a triggers,
the SQLServer do allow delete the trigger and recreate them.
Is there any way to disable the trigger directly?
Thanks in advance,
Don
BTW:
I've used the following sql directly, however the trigger still fires.
UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@table_name)
The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.
DB Design :: Summing From Two Tables Then Performing Operation With 2 Sums
Jun 17, 2015I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this
accnt acc1167
ven1 amnt1 ven job# amnt
1167 100 1167 1 200
1152 50 1167 2 300
1167 110 1167 3 100
1167 300 1167 4 200
1252 1050 1167 5 200
1167 210 1167 6 150
1167 1150
1167 130
2113 800
1167 550
1167 1200
I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.
for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:
acc1167
ven job# amnt
1167 1 61.4
1167 2 92.1
1167 3 30.7
1167 4 61.4
1167 5 61.4
1167 6 46.05
i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:
UPDATE acc1167
sum1 = sum amnt1 where ven1 = '1167'
from accnt
sum2 = sum amnt
from accnt
SET amnt = sum2/sum1*amnt
FROM acc1167
SQL 2012 :: Current Operation Cancelled Because Another Operation In Transaction Failed
Nov 20, 2013I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.
OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.
Restoring A Db That Was Involved In Replication
Mar 24, 2004Hi all
I have restored a db from a live environment to my dev envorinment. This db on live is a replication publisher.
My dev environment has no replication, but when I try to modify the schema in the db I restored I get the following error
"Server: Msg 4932, Level 16, State 1, Line 441
ALTER TABLE DROP COLUMN failed because 'url_web1' is currently replicated."
Does anyone know what I have to do to restore the database on my de box so it 'forgets' its live replication settings?
On my dev server I have no items in the replication folder in Enterprise Manager but it seems replication details are stored somewhere, perhaps in the backup itself?
Matt
Views With Parameters Involved
Apr 2, 2008Hi,
I have done a stored procedure but happend to know that people calling my database cannot access procedures only views. But my select query requires to parameters (two dates) in order to get the correct data.
How can I solve the problem?
Restoring The Database Involved In Replication!
Dec 5, 2003Do we need to disable replication to do a restore? Any help on this?
View 1 Replies View RelatedEffective Query When There Is Lot Of Rows Involved
Dec 17, 2014i have 3 tables having refrential integrity on id on column on each table
TABLEA contains 5 million rows
TABLEB contains 50 thousand rows
TABLEC contails 5 thousand rows
i have a proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.what is the optimized way to write query to get records on the basis of in parameters from these tables.
About Character Set And Sort Order Involved In Upgrade SQL 6.5 To 7.0
Jun 13, 1999i have 2 questions,
1. cause there is no record of my old SQL 6.5 installation leaft for me,so i try to find the character set and sort order of my old SQL 6.5 installation, and in the MSSQLlog directory the errlog.? file there are these lines:
99/06/11 12:59:24.29 spid1 SQL Server's default sort order is:
99/06/11 12:59:24.29 spid1 'nocase' (ID = 52)
99/06/11 12:59:24.29 spid1 on top of default character set:
99/06/11 12:59:24.29 spid1 'iso_1' (ID = 1)
so my old installation's character set and sort order is as above,am i right?
2. cause i am in chinese so any good idea for me to upgrade this old SQL 6.5 to use chinese character set (cp936) , is there anyway to transfer the old data?
thanks if any suggestions!
welcome to send me mail about the question.
INSERT OPENQUERY With XML When Remote Server Involved
Mar 13, 2008
Hi Guys,
We already have work around when it comes to read xml data type from remote servers
remote server (eurodata3) table
create table temp_asaf
(
xmlCol XML
)
SELECT
Cast(a.XML_Data as XML) as XML_Data
FROM
OPENQUERY(eurodata3,'
SELECT TOP 10
Cast(xmlCol as Varchar(MAX)) as XML_Data
FROM
em_port.dbo.temp_asaf'
) a
Here is a question for you. How do I insert data into remote server when data type for a column is xml?
INSERT OPENQUERY (eurodata3, '
SELECT Cast(xmlCol as Varchar(MAX)) AS xmlCol
FROM em_port.dbo.temp_asaf')
VALUES ('<html><body>MS Sql Server</body></html>');
Running above code would give me the following error:
OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.
Location: memilb.cpp:1493
Expression: (*ppilb)->m_cRef == 0
SPID: 59
Process ID: 1660
Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.
Analysis :: How To Get List Of Attributes Involved In Particular MDX Query
Aug 3, 2015I have a list of mdx queries extracted by sql server trace file. I want to get list of attributes and measures involved in each query.
View 6 Replies View RelatedUsing SCOPE_IDENTITY Inside A COM+ Component (Trigger Involved)
Jan 7, 2008
Hi,
I'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.
It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following:
1) Insert the record for Object A via ADO
2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger)
4) Insert a record for another Object A via ADO
5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.
The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.
It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.
Any idea why this would happen?
Thanks,
Greg
SSIS Package Runs Fine; SQL Job Fails (DBF Involved)
Jun 14, 2007I have created an SSIS package which, as its 2nd step, picks up a .dbf table and does a Data Flow Task of putting the .dbf table into a SQL Server 2005 table (which was just truncated in Step 1).
The Connection Manager that works on the .dbf table is Native OLE DBMicrosoft Jet 4.0 OLE DB Provider.
The entire SSIS package runs perfectly when i run it from my development environment.
I then assigned this Package to a SQL Server 2005 Job. The entire package is the sole step of the job.
But when I run the SQL Job, the Job fails immediately on Step 2.
Here is the error message in View History of the SQL Job:
-----------------------------------------------------------------------------
Executed as user: DBASE01SQLExec. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:48:37 AM Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 0% complete End Progress Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 33% complete End Progress Error: 2007-06-14 11:48:37.91 Code: 0xC0202009 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. End Error Error: 2007-06-14 11:48:37.91 Code: 0xC02020E8 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: Opening a rowset for "scanus_process" failed. Check that the object exists in the database. End Error Error: 2007-06-14 11:48:37.95 Code: 0xC004706B Source: DBF to ScanUS_Process DTS.Pipeline Description: "component "ScanUS... The package execution fa... The step failed.
-----------------------------------------------------------------------------
Could someone suggest why this works OK within SSIS but not when the SSIS Pakcage is called from SQL?
thx very much/spirits.
seth j hersh
Data Warehousing :: How To See Text Of Query Involved In Deadlock
Sep 6, 2015How to see text of the query involved in deadlock ?
View 2 Replies View RelatedSQL 2012 :: Access Rights To Execute Stored Procedure If There Is No DML Involved
Jul 2, 2014I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
SQL 2012 :: Create A Check Constraint Where Another Table Column Value Is Also Involved
Mar 12, 2015Table ATable B
AIDBID
Col 1BCol1
Col 2
Col3
I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1
How can I achieve this without using function.
SQL 2012 :: Parallel Query Worker Thread Was Involved In A Deadlock
Oct 22, 2015I have a deadlock trace running on a production server that is filling up with the following:
Lock: Deadlock Chain
Parallel query worker thread was involved in a deadlock SPID 8
105 - Resource Type Exchange
Spid 8 is a SQL process LOCK MONITOR
There are no graphs as seen with typical deadlock, so it is not two queries that are deadlocking, and I've never seen a deadlock for "LOCK MONITOR"
What Are Issues Involved With Deploying A Reporting Services Report Outside Of Our Network?
Oct 30, 2007I would like to know if it is resonable and acceptable to make reports created through SQL Server Reporting services available through our Public facing applications.
Also, what are the potential problems? Security? Impact on the SQL Server? Limit to specific types of reports?
If anyone has any info it would be greatly appreciated, I am a big fan of SQL Reporting but we are unsure if we are going to be able to use the reports on the Public side and we don't want two separate sets of reports (internal and Public).
Thanks in advance.
Terry
Table Not Involved In Replica Throws Error When Write Data In It
Aug 8, 2007Hi everybody.
I have a merge replication scenario between 2 servers.
Everything is OK when I write data in UP server (the server which does the replication).
But when I write data in any tables in the DOWN server throws an error saying that the table where I'm trying to insert data is updating or inicializating for merge replica. This table, where the error throws, isn't involved in the replica. In fact, that table is not involved in none replication.
Can you help me?
Thanks in advance
Would A TLOG File Size Change When Inserts/updates/deletes Are Not Involved?
Jan 3, 2008I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
Assistance With Finding Issues Involved In SQL2005 Reporting Services Reports And Exporting Them To Excel
Oct 4, 2007Hello,
We are running SQL2005, SP2.
We are having problems with the time it takes to export reports to Excel.
I am attempting to research the subject and find ways of solving the problem either by modifying the reports or make changes in the way we deliver them.
Either way my attempts at researching this at Microsoft.com and here at the forums have proven to be failures. There are many threads on the subject but with the time it takes to scroll through them all I have yet to find any that seem to relate to the specific issues we are having.
We have a report which is approximately 38 pages, sometimes less, sometimes more, in length. When attempting to export to Excel the process seems to take forever or time out. We can still close out the export attempt and return to the report, but the process of exporting to Excell seems to take forever.
Could anyone here provide me with links to pages either here at Microsoft.com, in the forums, or elsewhere where the factors involved in the exporting to Excell feature are addressed so that I can try to accomodate them as best as possible?
Thanks in advance.
I will continue to try and track down the information on my own but any assistance is appreciated.
T-SQL (SS2K8) :: Rank Duplicates But Only Rows Involved In Duplicates?
Oct 22, 2014I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,
[code]....
Set Operation
Aug 2, 2000I am running SQL Server 7. I have an user that need to run the Set Identity On a table, and he gets an error message:
Server: Msg 8104. The current user is not the database or object owner of table 'tpartners'. Cannot perform SET operation.
I can grant this user with DBO user role, then he can edit anyting on the database.
Is there a work around that he can run the Set cmd and I don't need to grant him the DBO user role?
No Log Operation
Jul 20, 2005HiI have a database with full recovery model.Every night at 11.00pm performs full backup, and dayly every 3 hoursperforms differential backup, and trasnactional log backup performsevery 15 minutes during a day.I am interested what operations i can not recover from log ordifferential backup.I saw that when i drop table i can recover it from log backup, but isthere some operation that can not be recoverThanks a lotAlex*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHow To Use Not In Operation
Apr 6, 2007Dear friends, I was not able to find any perfect toolbox item to use "not in" operation on my selected columns, also how can we use multiple join conditions in the merge join item? Thanks,
View 1 Replies View RelatedOperation Can't Be Completed?
Jan 18, 2008Why do I get the message "Operation can't be completed?" when I try to save a stored procedure!!! When I create a new stored procedure and copy the code into it it works fine!
View 2 Replies View RelatedErr. Msg.: Cannot Perform SET Operation
Jan 18, 2003I have a sp that creates a #temp table and performs a bulk insert.
CREATE TABLE #template (template varchar(8000))
EXEC ('bulk INSERT #template FROM "' + @filename + '"')
SET @html = (SELECT template FROM #template)
DROP TABLE #template
...
When I access this sp with any other user than 'sa' I get this error message:
The current user is not the database or object owner of table '#template'. Cannot perform SET operation.
I've been everywhere (with no success) in the sql srv admin to look for the appropriate check box to allow another named user to access this sp.
Can anyone help?
How To Specify "NON-LOGGED" DTS Operation
Jun 4, 2001On SQL 2k, How can I set my DTS package, which imports a 3 million row .TXT file to a table, as a NON-LOGGED operation?
(LOGGING is taking tooooo long!! I will be sure to run a full DB backup when the job completes.)
thx-
BT
Delete Operation...
Jun 19, 2006If tables are in relation (foreign key), then how should one proceed for deletion of a record from a master table?
how will the database itself tackle the problem? What should be done from the front end for this?
please help.......I urgently require a solution for this.
Bitwise NOT Operation
Sep 6, 2006I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like
update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @loginame and srvid IS NULL
How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?
Look at the following sample code and result. I just wanted to understand how it works with int and smallint
declare @a int
declare @b smallint
declare @c tinyint
select @a = 1, @b = 1, @c = 1
select @a=~@a, @b= ~@b, @c= ~@c
select @a, @b, @c
Result
~~~~~
-2-2254
Thanks in advance
Gnana
Bcp Operation Within Transaction
Feb 18, 2004hello,
I have a problem where I am calling the BCP utility to write a table to a file. I then need to delete the rows of the table. but not all of them. This all works fine. I've been asked to place this into a transaction..incase a piece fails. When I do that...SQL server hangs. I must shutdown SQL Server.
Any idea why that would happen. I am using the xp_cmdshell stored procedure to invoke bcp utility within a stored procdure. The procedure is executed every 15 minutes to provide files to an outboard system.
Salik.