Tag Info

New answers tagged

0

Please confirm if the service account of Integration services also has permission to the UNC file location. Please check using services.msc and let us know.


1

For one, you can certainly simplify your query, for example (I've added schema prefixes, which you should always use): Select A, B, C From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS) Where TransmittalNumber = 'TS- 0001' UNION Select A, B, C From ...


1

After finding out exactly what the data was I had to modify the code given from Aaron Bertrand to handle "over punch dibol ascii" DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645v'), ('00000000012345w'),('00000000012845x'), ('000000004123450'),('000000004512345'); WITH x AS ( SELECT col, switch = ...


2

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome. DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645n'), ('00000000012345p'),('000000004512345'); ;WITH x AS ( SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END FROM ...


2

While it's true the SQL needs one of these enabled, there's a workaround. Open gpedit.msc. In the Local Group Policy Editor, double-click Windows Settings under the Computer Configuration node, and then double-click Security Settings. Under the Security Settings node, double-click Local Policies, and then click Security Options. In the details pane, ...


4

The problem is that the trigger was initially written with very simplistic logic and with minimal testing - it assumed that the update or insert could only ever affect a single row at a time. The line you are asking about is attempting to assign a single value to a variable from a table that may contain more than one row (triggers in SQL Server fire per ...


0

Folks, I was able to resolve the issue by repairing SQL Server installation in Add/Remove Program. I restarted the service in SQL Server Configuration management and SQL Server Service,SQL Agent started and the application is also up and running. Hope this will help out someone. Thanks


1

I use something like this to create dynamic pivots with a dynamic number of columns. Perhaps it will help you with your solution. It's a single statement to avoid looping or cursors. declare @strSQL as varchar(max) declare @columns as varchar(max) declare @columns1 as varchar(max) set @columns = (select stuff ((select ',cast(' + quotename(columnname) + ' ...


1

Adding an example to Michael's answer. The problem with the PIVOT is two fold. 1st, it wants to aggregate. You can get around this by defining your dataset to be distinct and using MAX or MIN functions. But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question. You would end up with only one ...


2

You don't need to change database to Full recovery or backup and restore the transaction log for the scenario you are describing. If your database is always in Simple recovery then the transaction log is "cleared" as soon as a transaction is complete. Your transaction log on production is 7GB because at one time you had a transaction that used 7GB of ...


3

Does the ALTER DATABASE XYZ SET RECOVERY FULL imply that you are normally running in SIMPLE recovery model? If so, the logs from the SIMPLE mode period are discarded once they are no longer needed. There is no time machine to go back and recover the logs that were not saved. So, to interpret your script: ALTER DATABASE XYZ SET RECOVERY FULL; -- Begin ...


-1

I think you should debug SSIS package in visual studio, if you have solution file. I got this situation couple of month before, when I execute the package they stuck on the data flow. I had resolved my problem by removing the transaction script from the package. Because I importing bulk of data and transaction create kind of deadlock situation.... Hope ...


0

You can achive this by creating History table. In that table you can store all changes from those tables. You have to use tiggers to populate data in History table. Take into account that if the history table stores all changes you need engine to populate it. Another approach is to have History table for each table that you want to observe. In your case ...


0

There isn't a standard option to do this, but you can achieve this several ways. create a separate db and move your log tables there. set a max file size. create a trigger which checks row count or table size and limits inserts when value exceeded and/or deletes old data. create a foreign key constraint on an id column pointing to a table with a set number ...


1

Put EXECUTE AS ... at the start of your script, to impersonate the appropriate login.


3

There is no Run As proxy for T-SQL Job steps in SQL Server Agent. You could upvote this with the chance that one day it will be filled: https://connect.microsoft.com/SQLServer/feedback/details/632955/enable-sql-agent-proxies-to-run-t-sql-job-steps The current way to get a T-SQL command to run with a proxy is to run it as an Operating System Command ...


3

Could I through indexing alone influence SQL server to run this much faster. Possibly. There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the ...


2

As kin mentioned above. The easist way seems to be bcp out and in. Script out your table to a file ( The create table script ) Run the following command Export Data at command line using bcp "Database.Schema.Table" out "D:\filename.dat" -N -S "SQLServer\Instance" -T Once you copy that file / move to other system create the table you need then do the ...


11

Each row has a minimum of 7 bytes of overhead when stored in FixedVar format (the default). There will also be a (typically relatively small) number of pages used for the upper levels of the clustered index. Optimally stored, and disregarding the upper index levels, 2 million rows would require just over: (7 + 8 bytes) * 2,000,000 = 28.61MB. More ...


1

Why not use a Common Table Expression aka with clause? It's designed for exactly this purpose (among others). with orderIds as ( select orderId from ... ) select * from Customers where orderId in (select orderId from orderIds) or secondaryOrderId in (select orderId from orderIds); See ...


1

You query should probably be rewritten as an exists instead of an in See this link for more examples. Your query would then look something along the lines of select * from Customers C where exists (select 'x' from ordertable o where c.orderid = o.orderid) or exists (select 'x' from ordertable o where c.secondaryOrderId = o.orderid) If both subqueries ...


7

Try: where exists (select * .... where Customers.orderId = ... or Customers.secondaryId = ... ) Eg, if you were planning on: where orderId in (select value from ...) or secondaryorderid in (select value from ...) Then you make it so that you only call your subquery once, and build your OR clause into it. where exists (select * ...


0

You can skip the filecopy step and just insert the data directly to SQL Server. yum install freetds (use EPEL repository) and then use freebcp to insert the data into SQL Server from the linux box.


0

Have you tried to detach it from the 2008 R2 server, copying the .mdf and .ldf files over to the 2012 server and re-attaching them to it? It doesn't solve the backup error I know. It would get the database over to the 2012 server and if it attaches successfully would tell you that its good.


0

Try running a RESTORE FILELISTONLY or RESTORE HEADERONLY. If these run successfully, that would prove that SQL Server is successfully accessing the .bak file. (And rule out some sort of permission issue.) If that is successful, try running your RESTORE VERIFYONLY and RESTORE DATABASE with STATS=1. If it continues to fail in the same spot each time, it would ...


5

To answer your first question : If you are not sure, then just manually launch the setup.exe and verify. When you launch SQL Server 2008 installation using setup.exe, you will see the Below is for SQL Server 2008R2 : To answer your second question : From SQL Server 2008 and up, Microsoft has changed as the edition depends on the PID that can be ...


1

This one works in SQL Server 2008 R2, will probably work on SQL Server 2008 too : DECLARE @DSL [DATETIME] SET @DSL = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)) PRINT 'Declar variabila pentru ultima zi din luna anterioară.' PRINT @DSL


1

DECLARE @dtCurrentDate DATETIME = GETDATE() print @dtCurrentDate SELECT DATEADD(DAY, -1, DATEADD(MONTH,DATEDIFF(MONTH,0,@dtCurrentDate),0)) ,DATEADD(DAY ,-DAY(@dtCurrentDate) ,DATEADD(DAY,DATEDIFF(DAY,0,@dtCurrentDate),0))


0

A lot of testing later, I have the answers. Does the filter apply to the snapshot? Yes. So out of my numbered list, 1 is correct. The reason I didn't really believe this to be true is because when quickly testing this I forgot about the option in Article Properties that controls how the Snapshot is applied. This also made me ask the question after the ...


1

The Jet.OLEDB.4.0 is data access library for Excel/Access so the code is trying to read data from CSV file into a temporary table. If you change the source to OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ETU;Extended Properties="Text;HDR=YES;"')...[etu#csv] you will open c:\etu\etu.csv so changing the last parameter to ...EV20150126000001#csv ...


2

There is no way that you are going to get this 100% accurate. Though you can use PWDCOMPARE to check against a list of weak passwords (you can add to the list of weak passwords and do a comparison). I have written a similar script that does the comparison and gives you the results. I have posted it on github.


8

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make ...


0

You will need to reinstall the service pack, it will only update the feature you installed from source. (and I second Aarons humble option)


2

I would always err on the side of just installing the service pack again (it will know which components, if any, to update). Especially when adding client tools to a workstation - where this is not a major disruption. (And if you're adding BIDS to a production server, you should reconsider IMHO.)


0

Password Policy per SQL Login is only a flag for on or off. If the Password Policy flag is checked, then the Windows Password Policy from the operating system are enforced. Check the CREATE LOGIN documentation for the details on what happens when CHECK_POLICY and CHECK_EXPIRATION are set. You can see the settings per SQL user by checking columns ...


0

The database has log and data files that are stored on the file system. If the sys admin has a process of regularly backing up the location where the database files are stored then you might be able to get some of the data back. Assuming that you can get these files from before you overwrote them - then create a new database and attach the db and log file to ...


0

it seems like your only option is a super long shot hail mary. WITH REPLACE replaces the old DB but I'm not sure if it explicitly searches for those DB pages on disk and tries to overwrite the pages even if the DB is different. If the data is only 'marked' for deletion from the OS then you can have a very expensive data recovery center look at it. ...


0

Look to see if the data is stored somewhere else. Perhaps the system sends email confirmations. If so, retrieve the emails (from sent items or from auditing on the email system) and hire some temps to retype the information. Perhaps the system prints out reports. If so, obtain the printouts and get the temps typing. Perhaps the system exports data and ...


8

A couple of feasible options, in order of my preference: Option 1 Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that ...


1

You can write this more tersely (e.g. without any variables at all), but I thought logically breaking it up might be more helpful. DECLARE @Today DATE = SYSDATETIME(); DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@Today), @Today)); DECLARE @LastWorkDayThisMonth DATE; SELECT @LastWorkDayThisMonth = MAX(CalendarDate) FROM ...


0

I would like to inquire if there is an possible way to return lost data that stored in a Sql server Database after making a wrong database restore? No. There is NO way to get the data back. You might have used RESTORE .. with REPLACE which REPLACES the data in the database with the data from the backup that you just restored. So there is no way. You ...


0

No backup, its almost a zero chance of recovering the original data: Only thing can help if you have anyone of these like snaphsot, ,mirroring, LOGshipping or replication enabled for that database. Have not heard, a roll back without proper backups!


1

after installation Restart agent and server services Enable OLEDB Driver in SQL Server EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO


1

In real world environments the situations where a keyword is encountered and then removed and will never be encountered again is quite low. The process for updating a record in the full text index process probably removes all previous references to the record then updates all new references. As such when the references to a keyword are removed it does not ...


0

Do you have documentation or change control regarding indexes? Perhaps someone that has admin access to the database deleted or disabled an index. Get the sp_askbrent stored procedure from brentozar.com installed and run it.


1

One good source for the data is plan cache, and you can find top cpu or logical reads by querying dm_exec_query_stats DMV, for example with something like this: select top 100 SUBSTRING(t.text, (s.statement_start_offset/2)+1, ((CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset)/2) + 1) as ...


1

As per error it does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold. Log shipping is out of synchronization beyond the backup threshold. Instead, this message might ...


0

For the purpose of not leaving this question open and in hopes that it helps someone in the future, I wanted to detail the steps I followed to solve my problem. In following the advice given about running a trace around the time the Recovery model is changed, I found that there was indeed a task that was shrinking the log. Summary Using SQL Server ...


3

You need to use the OUTPUT parameter, as it is described in the MSDN article: Given this dummy stored procedure: CREATE PROCEDURE sp_test AS RETURN 2 This code will give you the result: declare @ret int exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT select @ret as result result ----------- 2 (1 row(s) affected)


1

Frankly, I wouldn't use either. You can find your biggest tables immediately - with more flexibility - and not worry about where @updateusage has been set. CREATE PROCEDURE dbo.TopTables @NumberOfObjects INT = 100, @MinimumSizeInMB INT = 10 AS BEGIN SET NOCOUNT ON; SELECT TOP (@NumberOfObjects) [object] = QUOTENAME(s.name) + N'.' + ...



Top 50 recent answers are included