WHERE (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.documentCategory.docCategoryName LIKE '%olic%') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) OR (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) AND (dbo.document.docDesc LIKE '%olic%') OR(dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) AND (dbo.document.docType BETWEEN 1 AND 4) OR (dbo.document.docVisible = 'yes') AND (dbo.document.docHide = 'NO') AND (dbo.t_files.fileMoved = '1') AND (dbo.minisite.sectionLive = 1) AND (dbo.t_files.fileName <> N'A LINK') AND (dbo.t_files.fileName NOT IN (SELECT exFileID FROM t_exclude)) OR(dbo.t_files.fileName IN ('127-2006-1-27-3321130.pdf', '127-2006-1-30-3726619.pdf', '127-2006-1-27-5700042.pdf', '127-2006-1-27-5678586.pdf', '127-2006-1-27-5693574.pdf', '127-2006-1-27-5873392.pdf')) ORDER BY dbo.document.docDesc
Right - as you can see, some of the line slook pretty similar. When I try and do:
(one AND two AND three) AND (four OR five), I get
(one AND two AND three AND four) OR (one AND two AND three AND five)
when using enterprise manager. Is there anyway to keep the first way of doing it. Otherwise, everytime I add anotehr OR statement in, I'll have to create a new line!
I had received a message that my log file is full and it do not enable to me to do a database backup before free up disk space. How do i clean up de log file (_log.ldf)?
I have the following query which strips out middle initial data from the first_name column and populate the middle_initial column with the relevant data.
What my query does not handle is when a first name has a single character (IE: A). In its current state, the "A" would be moved to the middle_initial field with a period added to the end (IE A.). The first_name column would also include "A". Basically, when a single character first name is found in the first_name column, I do not want to populate the middle_name field.
Hope this does not sound too cryptic; query is below along with some sample data when run.
SELECT first_name, CASE WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' ' THEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name))),1) +'.' ELSE NULL END AS 'middle_initial', CASE WHEN SUBSTRING(LTRIM(RTRIM(first_name)),LEN(LTRIM(RTRIM(first_name)))-1,1)=' ' THEN LEFT(LTRIM(RTRIM(first_name)),CASE WHEN LEN(LTRIM(RTRIM(first_name)))>=2 THEN LEN(LTRIM(RTRIM(first_name)))-2 ELSE LEN(LTRIM(RTRIM(first_name))) END) ELSE LTRIM(RTRIM(first_name)) END AS 'first_name_removing_initials' FROM contact
Sample Data first_name, middle_initial, first_name_removing_initials, Paul, NULL, Paul A, A., A, A Fred, NULL, A Fred, Aaron, NULL, Aaron
I have a db server that had served both my application data and reporting services. I have since installed a second db server to be the reporting services server; the primary box still stores the application data. I am keeping reporting services installed and set up on the primary box, since I would like it to act as a warm standby for the reporting services function.
However, the ReportServerTempDB on my primary box is still quite large. The ChunkData table is quite close to 10 gig. The CleanupCycleMinutes property is set to the default of 10. Clearly any data that might be in this table is far older than that.
Is there some recommended method of clearing this data? Can I just delete the rows or do a truncate on the table? I would prefer not to uninstall/reinstall reporting services, as this machine is actively serving my application data.
Hi everyone, What is the easy way to clean up all ms sql 2005 tables? For example, a database table named Customers which has triggers and primary key and foreign keys. Now I clean up the Customer table using the folowing statement delete Customers And the ms sql 2005 asks me to remove all triggers and foreign keys before allow me to clean up the Customers table. Is there a way to clean up all tables without remove the tiggers and foreign keys? Thanks May
Hi, I need some advise in the log shipping. The log files in the primary server get cleaned up according to what I have specified in the maintenance plan. But the log files that got shipped to the secondary server stay there for ever wasting my hard disc. Will it make any problem if I remove them or can I set it up to remove all files earlier than past 2 hrs? Please advise. Thanks
My problem is that I cannot completely clean buffer cache on SQL Server 2005 version 9.00.2047.00 (probably SP1).
Right after I run DBCC DROPCLEANBUFFERS in the context of my database (this is development server, and so far I am only the one who is working with a particular database), I run a script that quetries sys.dm_os_buffer_descriptors view also from the context of my database to make sure that the buffer cache is really clean. However it shows large number of entries totalling 42 MB.
I ran both DBCC an the script in the past too, and it always showed nothing in the results, that means that buffers were really clean. The reason why I am running this is for benchmarking of existing and new application.
Does anybody have any idea, suggestions, how to troubleshoot this issue ? I already closed all connections to this database, but rebooting the server is not an option since other people are also working on it.
I have a custom Data Flow task that creates temp files to the system temp directory during processing. A lot of times, we'll use SSIS to do one data transformation, running and tweaking the package along the way... we do this in the designer ... if we notice something that's incorrect in the data view, we just hit the stop button and fix it. However, when we do this, the Cleanup() function isn't called, and my temp files are left in the temp directory, when they really ought to be disposed of.
Is there a method that gets called every time when the DtsDebugHost quits, whether it finished, didn't finish properly, or was stopped in the middle? What would be a good way (other than having some service that monitors what temp files are used by what processes) to clean up temp files after we don't need them?
Is there some recommended way to clean input before submitting it to the database? We'd like to develop a library that can be used on our ASP/ASP.NET apps to filter input before it's sent to the SQL Server and Oracle databases. Is there a way to create a .NET DLL that can be used for both ASP.NET and classic ASP apps. Thanks.
I am importing Visual FoxPro (6) views into SQL 2000 tables and I am looking for a snippet of code to "clean up" date fields upon insertion. I can insert the views into the SQL tables fine using the tables/views selection of DTS as my source. However, I would like to ensure the date fields are in fact vaild dates and not garbage using the SQL query option of DTS source. I would like to do the insert and cleanup in one step. Do you have a snippet of code to validate a date field that I can use? Thank you for any assistance in advance, Terry.
I posted this in the SQL Server Express forum as well...
My app deployed via ClickOnce. It ships with a database creation script which creates the database in the ClickOnce data directory. Upon uninstall, the entire data folder is deleted successfully. But if you then reinstall the app, SQL Server Express still thinks the database exists. Right now, to get a re-install to work, I have to go into Management Studio Express, click on the db (get an pop-up error as the physical db file is no longer there) and then click delete.
So my question is... what system proc should I execute in the db creation script before I run the CREATE DATABASE command?
I have an IS package that contains some legacy DTS tasks. The package does run successfully on some machines, but others not at all, generating a weird error that says "Error: The task failed to load. The contact information for the task is "Execute DTS 2000 Package Task;Microsoft Corporate...." There's more to the error but searching on it hasn't turned up any results so I'm not posting the entire thing. I can if requested.
On the server where the DTS packages fail, regular IS packages that contain no DTS legacy tasks run successfully, and the only difference between the machines that work and don't work is SQL Server 2000 is not and was never installed, whereas the machines that work are running 2000 and 2005. The servers where the tasks fail version 9.0.3050.
One thing I have noticed is that on the machines where DTS works there is an extra file in C:Program FilesMicrosoft SQL Server90DTSBinn called Microsoft.SqlServer.Exec80PackageUtil.dll. Copying the file to a non-working server did not solve the issue. I tried installing the backwards compatibility update but it said that a newer version was already installed.
If anyone could help I would greatly appreciate it. If you need more information just ask and I would be happy to provide it.
A customer is about to replace their PDC with SQL Server running on it. MY companies role is to build the new server, and get their SQL Server running again. I need to have the devices, databases, tables, data and the USERS copied across. I cannot use transfer manager as their will be only 1 instance of SQL Server running on the network.
If all we have is an NT backup is perfomed on the entire disk, i.e. the MSSQL/DATA directory. How do we recreate the databases and their associated tables and also, how do we recreate the User accounts ? Any ideas would be appreciated.
Please feel free to e-mail me with a response NEILA@ANGLIABC.CO.UK
Quick history, I have substantial experience using MS Access, VBA, and Regular Expressions to clean up messy text files for use in MS Access.
I want to upgrade an MS Access back end to SQL Server (2008 to be specific). I need to upload several delimited files on a daily basis. The files are full of formatting that I strip out using VBA and Regular Expressions. I've poked around on the site and found some UDFs that implement the VbScript Regular Expressions object as a stored procedure in Transact SQL.
My questions is, am I better off processing the files externally using a procedural language like VB or can SQL Server handle the processing as gracefully as MS Access/VBA/RegEx does?
Thanks for letting me tap into your experience.
I could simply puzzle it out myself by implementing it in SQL Server but I'd rather not waste the time as a newb to transact SQL and then find out that I'd be better off doing it externally.
I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.
I have not change anything since installation of this server.
This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.
Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.
We are using SQL Server 2005 SP2 to do transactional replication.
We and have a separate service account for the SQL Agents (sqladmin) vs. SQL Replication Agents (sqlrepadmin). It is my understanding this is a replication security best practice. The sqlrepadmin has full permissions on the snapshot share folder and it's subdirectories. The sqladmin account does not have permissions at all.
I have been getting an error message when we run the distribution clean up job. Executed as user: PRODsqladmin. Could not remove directory '\Tes01boxRepldatauncqabox01_DB01_TO_ORACLE20070905104896'. Check the security context of xp_cmdshell
I have dropped the publication and recreated which is what appears to have caused the error.
From http://technet.microsoft.com/en-us/library/ms151151.aspx
Note:
If a publication is dropped, replication attempts to remove the snapshot folder under the security context of the SQL Server service account. If this account does not have sufficient privileges, log in with an account that does have sufficient privileges and remove the folder manually. Removing a folder requires the Modify privilege if the folder is a local path or the Full Control privilege if the folder is a network path.
The note above implies that the SQL Server service account (sqladmin) needs permissions on the snapshot folder as well.
Finally my questions: Is there a workaround that will allow the distribution cleanup job to run as sqlrepadmin and perform the delete?
If both sqlrepadmin and sqladmin need permissions to the snapshot what is the reasoning from a security perspective of separating them out?
I have problem when I create the clusters I created domain account and give him permission create computer objects and real all properties but there are problem when clean up the cluster always pop up and error but latter I tried using my domain admin and worked perfectly is there any permission is missing for cluster ? or all I need is Create Computer Object and Read Permission?
I have a database that I have been creating and testing. I have added some junk data and some data into lookup tables. Is there a way to create a clean copy of the db and keep the lookup table data? Also will I be able to create the db under a new name?
I have a number of test databases which were created from the same backup. Set up SQLMaint to run on them each night. SQL Maint seems to run clean on all of them, but on two the Scheduled Task shows "Failed". All tasks have been set up exactly the same.
Has anyone run into something like this?
Here's the output from the maintenance job for one of the failing db's:
Microsoft (R) SQLMaint Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996
Logged on to SQL Server 'COSTGUARD2' as 'sa' (trusted) Starting maintenance of database 'Test15' on Thu Jun 17 23:10:01 1999
[1] Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Check Data and Index Allocation...
** Execution Time: 0 hrs, 0 mins, 2 secs **
[3] Check System Data...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Update Statistics...
** Execution Time: 0 hrs, 0 mins, 27 secs **
End of maintenance for database 'Test15' on Thu Jun 17 23:10:30 1999
What is the difference between CleanExpiredCache and FlushReportFromCache Do we need to run both the SPs to clear all the SSRS reporting cache? Is it possible to clean all the Cache information and retain the logs? If yes how we can do so. Is it by deleting the REportServerTempDB.dbo.ExecutionCache table work in achieving this?
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €śDBCC FLUSHPROCINDB (@dbid)€?? Is there a workaround for €śdbo€? to be able to flush procedure and data cache without being elevated to €śsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans. Thx.
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.