Can I Find Change Log Information For A DTS
Nov 30, 2004Hi,
Is it possible to find the date n time when a particular DTS was changed. All that I can see is thecreation date of a DTS :(
Will appreciate your help.
ADVERTISEMENT
Where May I Find Information On SQL 2K5 And What It Hooks In To?
Aug 16, 2006I'm new to server 2K3 and I know even less about SQL 2K5. I've read some 'reviews' on the program that talk about how it now 'hooks in to' other programs on a network. My question more precisely is what does it attach itself to and should I be careful about anything? Developers here have been using SQL 2000 and have not made the jump to 2005 yet, so they can't answer my questions. So I decided to come here and try to learn the answers I'm looking for. Can some one point me in the right direction?
View 3 Replies View RelatedSearching And Finding The Information Users Really Want To Find
Dec 26, 2005Hi,
Imagine that I have a select something like this
select * from T where T.Name Like '%Maria%'
"Maria" would be what the user what to find. But for example in spanish we use í, ó, ú, ü... The same happens in french, german...
The user want to find Maria and does not care if another user (or himself) has inserted the client as Maria, María, Marïa or whatever.
Of course one solution is putting in the DB only the simple characters (a,e,i,o,u) and look for these, replacing the "strange" chars in the application to the "normal" ones. But if we want to be professionals we would need to insert in the DB the name in their original spelling (the image is the most important!)
Any idea, help or reference?
Thx in advance and happy new year.
David
DB Engine :: Stored Procedure Change History Information?
Jun 5, 2015one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
Edit / Update Query - Change Current Sales Information For A Specific Product
Apr 11, 2012I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
Find Existing Data Within A Date Range Change
Jul 30, 2014We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:
DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8))
INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99')
DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8))
INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)
[code]....
DB Engine :: How To Find Last Updated Change Tracking Time
Jul 30, 2015In a change tracking enabled database I can find the latest change tracking version number by using
Select CHANGE_TRACKING_CURRENT_VERSION() As Latest ChangeTrackingID.
Which will give latest change tracking id (example 1022), Is there a way to find the datetime of this latest change tracking id.
Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table
Nov 16, 2015For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
SecurityException Class SQLServerDatabaseMetaData's Signer Information Does Not Match Signer Information Of Other
May 23, 2007Hello,
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Can anyone help me with this problem?
Simon
SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions
May 12, 2015Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedAnything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?
Jul 26, 2005I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedDB Engine :: Collation Change And DDL Change In Same Script
Nov 18, 2015We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
Problem: Find All Table Names In Db And Then Find
Jun 12, 2008I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Information About A Job After It Ran
Jul 2, 2001I have a job that runs each night that deletes all records from a table that are older than two weeks old. Is there some way I can view how many records were deleted from the table each night. Is it written to a log somewhere? Thanks in advance!
View 1 Replies View RelatedGetting Information
Aug 4, 2005Hi all,
This is my first thread here.
I got an information about
SET NO_BROWSETABLE ON/OFF
in your forum.. The replier told that this is an undocumented option.
My question is how you guys find information on such undocumented things....
Thanks in advance.
Saint
Information !
Mar 26, 2008Hello,
Can anyone clarify the nuances of How to deal with the change management environment in SQL Server.
Thanks.
Get Job Information
Sep 6, 2007I am trying to get job information and have a query set up. However, I would also like to get the owner information. However, the job only stores the sid of the owner. Does anybody know how I could change this query to get the owner name instead of sid? Don't want to use the sp_help_job command.
SELECT @@SERVERNAME AS Server, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description, msdb.dbo.sysjobs.notify_level_eventlog, msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend, msdb.dbo.sysjobs.notify_level_page, msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created, msdb.dbo.syscategories.name AS Category_Name, msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time, msdb.dbo.sysjobservers.last_run_outcome, msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date, msdb.dbo.sysjobservers.last_run_time, msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator, msdb.dbo.sysoperators.email_address, msdb.dbo.sysjobs.date_modified, GETDATE()
AS Package_run_date
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN
msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
Information Being Cut Off
Mar 17, 2008I have a report where the information is getting cut off the report.
At the beginning of or at the ending of the page it is dropping the information.
My layout setting are set 8.5in wide by 11in height. .5in for either left or right margin. 1in for top and bottom.
I run the report and I'm missing a record.
I adjust the height to 10in to force diffferent page breaks and it's there.
I have the setting in one of the groups set for a page break at end. If I uncheck that it doesn't help.Still no records.
What is going on?
Looking For Information
Mar 15, 2006I'm looking for books or information on command line configuration of settings for things like activating Remote Connections under the Service Area Configuration and setting a port for that connection.
What books would be good to get or where can I download papers on this?
Any help would be appreciated.
Locking Information
Dec 23, 2005Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
Trigger Information
Jul 26, 2001Hi,
I have a problem here in my SQL Server Database. I want to find which trigger is working backend affecting my table input. Since the original author of this trigger was somebody else, I am not able to find the trigger information
A simple line of syntax would be great.
Thanks in advance
sandeep
Last Login Information
Aug 30, 2001I am doing an audit of user information in our database and was wondering if there is a way for me to see the last time a user logged into the database. I am using a SQL Server 7.0 box. Any Information would be greatly appreciated. Thanks!
View 1 Replies View RelatedAlias Information In 70
Jun 27, 2000Hi,
In my database one user is aliased like a guest. When i run sp_helpuser on the particular database it is giving following result.
username login name database
-------- ----------- --------
aaaa NULL XYZ
NOrmally we fill see like this for GUEST User. Now i want to drop this user 'aaaa' only. IF i use
sp_dropuser 'null'
i think guest user will also drop from XYZ database.
So can anyone pls suggest me how to drop this user. I have to add a same user with full permissions. i know in 65 we have sysalternates table gives all alias information. Is there any table gives the same information in sql7.
Pls suggest me regarding this.
thank u
raj
HELP (URGENT) Need Information!
Sep 26, 2000Hi,
I administering SQL server and one of or Db (size ~7 Gb) have a table with size 396 MB and 25 indexes created on this one. We working with this DB using Siebel Call center.
I have met a very strange situation: when I tried to get some data (using Siebel) my request take ~5 min. I run Profiler and catched this T-SQL statement which Siebel send to SQL server. After that, I run this T-SQL query using Query Analyser and found that Query Optimiser used wrong index. (clustered index) and try to compare every 470436 rows from the tables.
This is the statement. @P1, @P2, and @P3 just variable which siebel transfer to SQL server.
SELECT ........
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.TARGET_PER_ID = T2.ROW_ID
WHERE
((T1.APPT_REPT_FLG != @P1 OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != @P2 OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_DEFECT_ID = @P3)
But I found the very strange situation - when I manualy put the value '1-FRK' instead of @P3 - Query Optimizer used RIGHT index and I had a very good performance.
And addition when I tried to run the first Query on the same table but with the only 1000 rows the Query Optimiser choose right index.
Wat's on, wat happend with My Query Optimizer? Wy his decision so different from time to time?
Thank's a lot for any information about how to make Optimiser obedient.
Getting User Information
Jan 19, 2005I need to write an auditing trigger that will capture time of an insert and user that is making the insert. The time part i think i can use getdate(), but how can I find out which user is making the insert? Any help appreciated.
View 2 Replies View RelatedLogin Information
Oct 3, 2005Hi,
Can we retrieve the Login Failed and succedded for SQL Server?
I want to know the login failed and succedded information of SQL Server.
Can we retrieve the same?
Let me know ASAP.
Thanks and Regards,
Pramod
INFORMATION ON PROCESS ID
Nov 17, 1998Is there any way to use Transact SQL to get the detailed information on a process like you get when you use the GUI interface under the Enterprise manager for server activity.
Thanks!
Trigger Information
Jul 26, 2000Is there a way to view the triggers on a table using query analyzer?
I have a developer that wants to see this information without having to right click every table in the database and viewing the triggers through enterprise manager.
Thanks,
Brad
Logon Information
Feb 15, 2001I just started at a new company. The security stinks, everyone logs into the servers as sa (including many applications). I am taking steps to tighten security and restrict access to the sa login. I would like to capture some additional information at logon as an interim measure.
Can anyone assist me in automatically recording the following:
Host Name / IP Address
Login time
I realize I could periodically run an sp_who2 and capture the results - I also realize that if I turn on auditing, the logon will be written to the SQL Srvr LOG - I'm looking for an elegant / low impact way to capture the logon and host information.
Thanks in advance for any help.
Scott Wright
Need Help Getting Information From 2 Tables At Once
Jan 2, 2002I'm trying to access data from one table by matching ids pulled from another table. I can't quite get the syntax though if anybody could help I would appericate it.
Here is where I'm at
select * from table1 where variable 1 = (select distinct(variable 2) from table 2 where variable 3 = variable 4)
User Information!!
Jun 24, 2004Hi,
I'm trying to get the information about the user who modified the record in the table. I'm able to capture the datetime of modification through trigger.
CREATE TRIGGER [upd_dtm] ON dbo.Sample
FOR INSERT, UPDATE
AS
BEGIN
UPDATE Sample SET update_dtm = (SELECT GETDATE())
WHERE Sample.ID IN (SELECT ID FROM inserted)
END
I'm stuck, how to get the User who modified it?
Any help is greately appreciated!
Job Schedule Information
Nov 7, 2005Hi,
Does anyone has a script to find out what are the scheduled timining for all sql server agent jobs.
I'm trying to get the result with the following query, it doen't seem working..
select sj.name,
case sjs.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end as Frequency,
case sjs.freq_interval
when '1' then 'Sunday'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday' end as DAY
from sysjobs sj, sysjobschedules sjs
where sj.job_id = sjs.job_id
Any help would be greately appreciated.
Thanks
Passing Information
Jun 21, 2007What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.
SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHEREcontact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)