|
Hi All,
I am using SQL Server Change Data Capture to track my changes in my Web Application. But the problem I am having is, if I have one page which is updating multiple Tables, parent and child tables in one transaction, how can I track all those tables transactions at one time, because sometimes page can update some tables and may not update other tables. As CDC is working on individual tables it is very difficult for me whether the transaction has updated particular tables. Can anybody please help me how to track the changes on the tables in that approach.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi All,
I am using a stored proc which is using a depending upon that it is pulling some values and make some changes in the database, what I need to do is to throw an error when date conversion from that string or text fails and should not perform further action.
Can anybody please help me with this, any code snippet, a link or even a suggestion helps me, thanks in advance. Below is my code
ALTER procedure [dbo].[TrackUpdatedColumnNamesProc]
(@DateToTrack nvarchar(max))
as
begin
--declare @DateToTrack nvarchar(max)='2016-03-18'
delete from TrackUpdatedColumnNames;
--check here (as soon as conversion of @DateToTrack fails I shouldn't be doing any logic
--doing the logic here
end
GO
declare @DateToTrack nvarchar(max)='2016-03-18'
exec [dbo].[TrackUpdatedColumnNamesProc] @DateToTrack
GO
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
So use try/catch in the stored proc.
However you are not processing sequentially, that requires a cursor or while or a CTE, so you should test the date conversion before performing the delete. Pretty sure there is and IsDate function in TSQL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah sure thank you very much buddy.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
The simplest option is to change your parameter's type to date. Then, if the caller tries to pass in a value that isn't a date, an error will be thrown, and the procedure will not be executed.
You should always use an appropriate data type for the data you're dealing with. Storing dates in strings is a sign of a bad design.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi All,
I am using CDC to capture the modified data, all I need to know is which columns are modified on a particular day so that I can extract those modified columns.
I am able to do it by using the CaptureInstance (like I have to use the name like this: cdc.fn_cdc_get_all_changes_CDC_Provider) name hard coded function, Is there any way I can get it by using the run time without using the hard coded name.
SELECT --sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time', CAST(sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS DATE),
@captureinstance, @ColName, sys.fn_cdc_is_bit_set(@TrackIfColumnChanges, __$update_mask)
--,fn_cdc_get_all_changes_CDC_Provider.__$operation
FROM cdc.fn_cdc_get_all_changes_CDC_Provider(@from_lsn, @to_lsn, 'all')
WHERE sys.fn_cdc_is_bit_set(@TrackIfColumnChanges, __$update_mask)=1
AND CAST(sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS DATE) = CAST(GETDATE() AS DATE)
Can anybody please give me any idea any code snippet, a link or even suggestion would help me a lot.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 17-Mar-16 20:24pm.
|
|
|
|
|
What is CDC?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh sorry man, its Change Data Capture  , somehow I am able to reach it with SQL coding but if I can capture the column names only with the some settings it will be easier.
Thanks in advance buddies
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 17-Mar-16 20:32pm.
|
|
|
|
|
Sorry for a stupid question.
I am desgning a new DB in SQL server.In this i have a table for example TBLData1 that contain 10 field.and a table TBLUser.
User login from my ASP website can change the data in the table TBLData1.And I Want to record all the log in order to know exactly who has change anything and when this action is happen.
In my opition I create another table TBLData1_Log it have ten field same in TBLData1 and some more field like this:
ID_USer.
Date_Change.
Reason_To_Change.
..........
Any time user change the data at the table TBLData1. It will store the old value to TBLData1_Log and added some more field.
I am wondering if this design is ok? Because the more table, the more log table in my Design.
Please help me to design better.
THanks.
|
|
|
|
|
Why would you duplicate all the data from one table in the log table? Use key values, or just save the userid (or whatever you use as identifier) and the transaction details. Then when you want to print a report you can lookup the user's details by the identifier.
|
|
|
|
|
We use triggers spit to populate the following table. IMHO this is the only valid use of triggers spit. This also services all tables that are being audited.
CREATE TABLE [dbo].[AuditLog](
[AuditID] [INT] IDENTITY(1,1) NOT NULL,
[Action] <a href="1">CHAR</a> NULL,
[TableName] <a href="128">VARCHAR</a> NULL,
[PrimaryKeyField] <a href="1000">VARCHAR</a> NULL,
[PrimaryKeyValue] <a href="1000">VARCHAR</a> NULL,
[FieldName] <a href="500">VARCHAR</a> NULL,
[OldValue] <a href="8000">VARCHAR</a> NULL,
[NewValue] <a href="8000">VARCHAR</a> NULL,
[ModifiedDate] [DATETIME] NULL DEFAULT (GETDATE()),
[UserName] <a href="200">VARCHAR</a> NULL
) ON [PRIMARY]
Actions being Add,Edit,Delete
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
Just after opinions here: which of these would be the best practice for an SQLite database:
DELETE FROM Messages WHERE Account = @acct AND NOT EXISTS (SELECT * FROM temp.IdList WHERE MailId=Id)
or
DELETE FROM Messages WHERE Account = @acct AND Id NOT IN (SELECT MailId FROM temp.IdList)
Indexes exist for both Messages(Account, Id) and temp.IdList(MailId)
Each table may hold in excess of 20000 rows
From what I understand, the correlated exists will need to be evaluated for each row of the Messages table, but will stop evaluating as soon as it finds a match (and being indexed, should be pretty quick). However, with the IN expression, the full result set is evaluated only once, but the comparison will be against that full set.
Cheers,
Mick
|
|
|
|
|
Just going by your analysis, I would say the NOT EXISTS would be better. As you say if the table is indexed it should be pretty quick.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I cannot speak for SQLite however I can for SQL Server(which perhaps does not help very much here).
It is best to never use a NOT IN statement, where the results returned could contain many rows, as it will need to evaluate for every single row in the inner select.
Whereas with a NOT EXISTS or a LEFT JOIN with a IS NOT NULL condition, which is another option, it will evaluate as false as soon as it hits the first row where there is a match.
So basically I agree with your evaluation - for what it's worth I always use NOT EXISTS nowadays.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
NOT IN and NOT EXISTS are not directly comparable since they treat nulls differently. EXISTS returns TRUE or FALSE only, IN is trivalent and returns TRUE, <code>FALSE or NULL.
Use the one that gives you the result you expect. (Most probably NOT EXISTS)
|
|
|
|
|
Ok guys - I guess the verdict's in. It's what I thought initially thought would be the case too. Its just that the doco on Correlated vs Uncorrelated sub-queries threw me a little, where correlated sub-queries are re-evaluated for each row of the main query. I do agree, however, that re-evaluating the sub-query will be significantly better than searching the full result set.
Cheers people.
|
|
|
|
|
Hi All,
I am trying to write a SQL Query data on to Excel directly, but it is giving me the following error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
My code is below
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;Database=C:\Test.xlsx;',
'SELECT [ApplicationName], Date FROM [Sheet1$]')
SELECT [ApplicationName], GETDATE() FROM Application
GO
GO
When initially it gave me above error I copied the excel file to the Server itself and ran the linked server script as below
exec sp_addlinkedserver @server = N'webdevsql1',
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xlsx;',
@provstr=N'EXCEL 12.0;Hdr=Yes' ;
Then ran the script again, now it is giving me little bit different error (ie. Cannot execute the query "SELECT [ApplicationName], Date FROM [Sheet1$]" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)").
Can anybody please help me in this regards? any help a code snippet, a link or even a suggestion helps me. I am also searching and trying from online, but if you have already familiar may be your experience helps me. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 8-Mar-16 14:01pm.
|
|
|
|
|
Hi guys some how I could able to find a way to write selected query values into an xls file but not on the xlsx file yet. If there is a way out for it from somebody can help me, if not for now I will live with it.
But my bigger issue is the below script is writing on to Excel file without headers, can somebody please help me in writing on the excel file along with headers?
Here is my script
DECLARE @cmd VARCHAR(255)='',
@sqlQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@FilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xls"'
SET @cmd = 'bcp '+ @sqlQuery + ' queryout ' + @FilePath + ' -T -c'
Exec xp_cmdshell @cmd
Any help a code snippet, a link or even a suggestion would be greatly helpful, thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
BCP does not support headers, I used to insert the header string with a linefeed into position 0 after the BCP operation.
This also indicates a workaround[^].
All of which is a kludge!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK I got an Idea I will create a template file with headers then can I append the contents of the headers file + the output file of the bcp into a final file so that I can have both the headers and content.
Or you can give me how to append row or data in the beginning of the file.
Can you please give me some suggestion or link or code snippet to append header file + content file into a final file using bcp or command shell etc?
Any help is appreciated, thanks in advance I am also searching for it.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 12:00pm.
|
|
|
|
|
Here is my code to do it may be can be helpful to somebody else, first I wrote a query to just print Headers and then query to print content then I appended those two files with cmd shell into one final file
DECLARE @cmd VARCHAR(8000)='',
@sqlContentQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@sqlTemplQuery varchar(max) = '"Select ''WA ProviderId'' WebAdmProviderId, ''WA Last Name'' LastName, ''WA First Name'' FirstName, ''WA Date of Birth'' Dob, '
+ '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
@ContentFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Content.xls"',
@TemplFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\TemplateFile.xls"',
@FinalFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Final.xls"'
SET @cmd = 'bcp '+ @sqlTemplQuery + ' queryout ' + @TemplFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd
SET @cmd = 'bcp '+ @sqlContentQuery + ' queryout ' + @ContentFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd
SET @cmd = 'copy ' + @TemplFilePath +' + '+ @ContentFilePath +' ' + @FinalFilePath + ''
exec master..xp_cmdshell @cmd
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 19:55pm.
|
|
|
|
|
But one small limitation here, bcp is creating files only in xls format (97-2003) but any body know how to do the same thing to get document in xlsx format like (2007) format? For now I can live with it but just want to acquire more knowledge on it.
Any help is appreciated, thanks in advance friends
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 19:57pm.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
