Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYT
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies View RelatedIf there are two different transactions, both of which update the username column to 'xyz' for userid = 234 in 'Users' table. This is a unique value for username. Ater this update each transaction adds a row to 'AppLog' table. The transaction is only committed after second operation.
The 'username' column has a unique constraint on it.
If transaction isolation level is 'read committed', and both transaction execute the first operation when neither of the transactions have been committed, then the transaction that calls COMMIT later will error out or not? If COMMIT does not check constraints then it will NOT error out. As a result we will have a violation of unique constraint happening without any error being thrown by SQL Server.
Hi,
In my DB, many of my tables have a column named upsize_ts I have already been told that it is not linked in any way with the data in the DB. The data type of these columns is timestamp and every record in those colum is this : "<Binary>"
Now maybe it's because the whole DB comes from an export from an Access DB, or maybe not. I just had a few questions:
1/ Does anyone know why that column got generated and what it means? (optional)
2/Now the real question: is there any statement allowing me to "mass-drop" all those columns named "upsize_ts" in every table of my DB at the same time? Or at least any way not to do it one drop table at a time?
Thanks.
Hello, everyone:
I imported a table from a database to another one in same server. How to check if two tables have the same data?
Thanks
ZYT
What I am trying to do is this. I have two tables that someone else created and now I have to fix there mess. Both tables have 3 like fields. I want to check and see if the ProjectNumber field from the Artifacts table does not find a match in the Projects table. If no match is found return the record from artifacts table.
Code:
SELECT *
FROM Artifacts INNER JOIN Project ON Artifacts.ProjectNumber = Project.ProjectNumber
WHERE Artifacts.ProjectNumber NOT IN (Project.ProjectNumber);
The next thing I have to do is insert a record of the three fields from the Artifacts table to the Projects table. Any help would be great.
Hello, everyone:
Some tables werer sent from customer. They ask to check if these tables have been normalized. How to check them? Thanks a lot.
ZYT
I have two procedures (old and new) that return queries and have to check that for every possible parameter they return the same data. Any hints?
Thanks, Pablo
I am new with SQL and I am Oracle DBA, trying to learn SQL. Any way, in SQL, is there a view or tables that you can run to pull out a list of the databases inside an instance? I don't want to use the GUI though.
thx
Hi
I am trying to check the size of each table in my database?
SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE
I can't for the lif of me figure out how this is done.
Any help would be greatly appreciated
Kind Regards
Carel Greaves
Hi,DDLs and DMLs:create table #job (jobID int identity(1,1) primary key, jobNamevarchar(25) unique not null, jobEndDate dateTime, jobComplete bitdefault(0), check (([JobEndDate] is null and [JobComplete] = 0) OR([JobEndDate] is not null and [JobComplete] = 1)));Q1 with check constraint:sample dagtainsert into #job (jobName)values('first job');transaction Aupdate #jobset jobEndDate = '12/19/2003', JOBCOMPLETE=1where jobID = 3;RESULTSET/STATUS = Successupdate #jobset jobEndDate = NULL, JOBCOMPLETE=0where jobID = 3;RESULTSET/STATUS = Successtransaction Cupdate #jobset jobEndDate = '12/19/2003'where jobID = 3;RESULTSET/STATUS = Failurehow come check constraint can't set a value which is preset in thecheck constraint? If it's the way how it works with MS SQL Server2000, well, IMHO, it's limiting because the above transaction C is avalid one. Or maybe check constraint is not fit for this purpose?Maybe, it doesn't make much sense for me to go into Q2 but I'll try-- create job's child table, taskcreate table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique not null, taskEndDate dateTime, taskComplete bitdefault(0), jobID int not null references #job (jobID));-- skip check constraint for taskEndDate and taskComplete for nowNow, the Business Rule says,1) if all tasks are complete then automatically set jobComplete forthe #job table to yes;2) the jobEndDate in the #job table must be >= the last/MaxtaskEndDateI tend to think trigger would slow down data update quite a bit, so,try to stay away for this purpose if possible.Always appreciate your idea.
View 2 Replies View RelatedI want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?
Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D
I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Thanks
I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
I have the following setup:
- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful
This is the problem I have:
- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.
I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:
use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.
Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.
I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.
I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedHi,
I just want to know how do you called when you are accessing another pc thru \. I forgot how it is called.
Second, I like to know what [MACH] and [INST]. They don't look like directories.
\MultforestHEALTH[MACH][INST]HEALTH_WAREHOUSEDeploy_20080228p_Vitals_FACT.sql'
How can I execute or access a web page from a DTS package?
Both SQL server AND website are hosted on the same server (a Dual 2.4Gz Xeon with 2Gb RAM, RAID 5 etc)
I have 2 tables in SQL server 2000 that hold orders. These need to be posted into another table at a predefined time (ie: 4:30pm) and at the same time, access a remote address (a web service) and post certain elements of the order back.
Basically, can anyone help me out on how to execute a web page from a DTS.
I do NOT want to access a DTS from a webpage, which is all I'm finding at the moment.
Hello,
Question is, when I try to create relationships in SQL 7 without using the wizard..upon adding the tables I get an errror message that says "Coinitialization has not been called". What does this mean?
CREATE PROCEDURE sp_getT
@m1 int ,
@txn int ,
@Pan varchar(50) ,
@Act varchar(50) OUTPUT,
@Bal Decimal(19,4) OUTPUT,
@CBal Decimal(19,4) OUTPUT
AS
declare @pBal money, @pCbal money, @pAct money
SET NOCOUNT ON
IF @m1 = 200
BEGIN
IF @txn = 31
BEGIN
exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
END
END
SET @Act = @pAct
SET @Bal = cast(@pBal as Decimal(19,4))
SET @CBal = cast(@pCBal as Decimal(19,4))
return @Act
return @Bal
return @CBal
the above code returns this error message
"Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
Procedure or function ChkBal has too many arguments specified."
How do i specify all the arguments i want in the called procedure?
Hi everyone -
I'm stumped on what to cal this, there might even be
a method or pattern named for what i am trying to accomplish...
In the database, a number field is included on each table
When the DAL reads the record from the database, it is passed to
the client - work is possibly done to the record and is sent
back to the DAL for update.
A query is done against the table to retrieve the record again,
the numbers are compared - if they don't match, it is assumed the record
been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.
if the numbers match, the record is updated with the number field being incremented by one.
what is this methodology called (beside crap :-) )
thanks
tony
Hello, perhaps you guys have heard this before in the past, but here iswhat I'm looking for.I have a SQL 2000 table with Social security numbers. We need tocreate a Member ID using the Member's real SSN but since we are notallowed to use the exact SSN, we need to add 1 to each number in theSSN. That way, the new SSN would be the new Member ID.For example:if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.Sounds simply enough, but I can't seem to get it straight.I need this number to be created using a query, as this query is areport's record source.Again, any help would be appreciated it.
View 9 Replies View Related
Hi
I have a drop down list which gets populated from database but I want to add the default value as
--Select----
This seems like a basic question but I have not been able to find the answer in the help files or by searching this forum.
Is a trigger called for each row updated or is it called once for all rows updated?
for example if I have:
Code Snippet
CREATE TRIGGER mytrigger
ON mytable
AFTER UPDATE
AS
BEGIN
EXEC e-mail-me inserted, N'mytrigger', getdate()
END
and I do this
Code Snippet
UPDATE mytable
SET mycolumn = N'whatever'
WHERE ID > 5 AND ID <= 10
Assuming there is a record for each nteger value of ID, than will mytrigger run 5 times (once for each row updated) or one time (with inserted containing all 5 rows)?
Hi,
When a column is evaluated against an UDF in a SELECT ... or WHERE ... It makes sense that the UDF is called for every row of the SELECT. But is it still true if the UDF is called in a subquery as below?
Code Snippet
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
I've made a test and the SQL Profiler Trace shows that the UDF is called only once.
Can anyone confirm if my test is valid and most importantly that the UDF is called only once? FYI, I never use sub queries. This is to clarify a technical detail for our performance investigation.
Thank in advance for any help.
Here is the code to setup the test:
USE NorthWind
GO
CREATE TABLE dbo.UdfTest (
LineID int Identity(1,1) NOT NULL,
AnyText varchar(200) COLLATE database_default NOT NULL
)
GO
INSERT dbo.UdfTest (AnyText) VALUES ('Test1')
INSERT dbo.UdfTest (AnyText) VALUES ('Test2')
INSERT dbo.UdfTest (AnyText) VALUES ('Test3')
GO
CREATE FUNCTION dbo.F_Bogus (
)
RETURNS @tab TABLE (
LineID int NOT NULL,
AnyText varchar(100) COLLATE database_default NOT NULL)
AS
BEGIN
INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1')
INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2')
INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3')
INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4')
INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4')
RETURN
END
GO
Here is the capture of SQL Profiler when executing the statement:
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
SQL:BatchStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQLtmtStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SPtarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus RETURN 51 2008-05-06 17:58:31.577
SQLtmtCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQL:BatchCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
My package inserts rows into tables with triggers.
The triggers are not being called.
What might be causing this?
I am encountering an issue which is effecting our production environment, none of our sql jobs are now saving any kind of job history, nor are the status of the jobs being saved.
I have run a profiler trace and it seems as if procedure : sp_sqlagent_log_jobhistory is not being called during the execution of any of our jobs
Has anyone else encountered anything similar?
Hi
In a stored procedure the following code snippet 1 checks against duplicate data being inserted. I've tested it with snippet 2 and it works as expected. However, when the procedure is called from ASP.NET the check seems ineffective. I still get the error msg. The application uses a SqlDataSource with the following parameters.
Any suggestions?
Thanks,
Bakis.
PS I want to ask a question on the ASP.NET forum .The login/pwd for this forum "get me in" to the .net forum in the sense that when I log in I see a logout link. I don't get an "ask a question" button though. Is there a separate screening for each forum?
<UpdateParameters>
<asp:Parameter Name="CatItemUID" Type="Int32" />
<asp:Parameter Name="CatName" Type="String" />
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quad" Type="Int16" />
<asp:Parameter Name="UID" Type="Int64" />
</UpdateParameters>
snippet 1 :
if (@CatItemComboExists > 0 )
BEGIN
--print @CatItemComboExists
return 0
END
snippet 2:
begin tran
declare @return_status int
EXECUTE @return_status = spUpdateCatItemRec 343, 'blah','blih', 2,3
print @return_status
rollback
error msg only if proc is called from app
Violation of UNIQUE KEY constraint 'IX_tblCatItemUID'. Cannot insert duplicate key in object 'tblCatItemUID'.
I am maintaining some C# and ASP.NET code with SQL server 2000. My code calls a stored procedure. The code is a little confusing as to the name of the SQL Server stored procedure that is calling. At this point I don't know how to trace into or debug the stored procedure. Kind of hard to do in the first place when you are not absolutely certain as to the stored proc to be called.I can take an educated guess as to which stored procedure is being called. I figure if I can deliberately make a certain stored procedure fail then this might be able to somehow give me the name of the stored proc that I am calling.So is there a way to do this. Namely make a stored procedure fail, or to return the name of the stored Proc?I would sincerely appreciate some help with this problem.
View 1 Replies View RelatedIm using a SqlDataSource control. Ive got my "selectcommand" set to the procedure name, the "selectcommandtype" set to "storedprocedure"What am i doing wrong ? Ive got a Sql 2005 trace window open and NO sql statements are coming through "ds" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="my_proc_name" SelectCommandType="StoredProcedure">
"txtF1" Name="param1" Type="String" />
"txtF2" Name="param2" Type="String" />
"" FormField="txtF3" Name="param3" Type="String" />
"" FormField="txtF4" Name="param4" Type="String" />
Hi All,I have a report ASP.NET page that allow users to run a report by clicking a buttion to call a store procedure to generate the report, however, the store procedure is taking a few minutes to return the data, thus I got the 'timeout' error message on my page. How do I extend the time on my page?Thanks
View 3 Replies View RelatedCan someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
Hi,
Please help me to elucidate a mistery !
I have a little SP in a database:
CREATE PROCEDURE XXXTest AS
DECLARE @a varchar(50)
SELECT @a = NULL
IF @a NOT IN ('x', 'y')
BEGIN
RAISERROR ('Error',16,1)
RETURN 0
END
RETURN 1
If I execute it from SQL Server, it returns 0. (after the error, of course).
If I execute it from MS ACCess through a Pass Through Query, it returns 0.
On the same server !!!
How could it to make me that ? I have been thought the SP is executed entirely on the server !!!
Thanks in advance clearing me the mind.
Mircea.