Tag Info

New answers tagged

0

Relational modelling starts for the identification of classes of "things." To these we apply normalisation to remove data update anomolies. The results are implemented as tables with a column for each attribute we identify. We do not start from a bunch of domains, look around for "things" with the same bunch of domains, shoehorn them into the same table ...


0

try giving the machine the same instance name


0

Use the new ID as a parameter to the second SP. However, you can set the ID as an OUTPUT ofbtge first SP.


1

I found the solution in case anyone else comes across this. The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the connection string properties were empty after importing them to the new server. After setting them, they were able to hit the stored procedures and all reports ran ...


2

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query ...


0

Instead of just replacing the Database can they not script the changes (schema/data) and run the changes on the existing db at your site?


0

As an domain-admin connected to that SQL-Server-Host via Remote Desktop. I had to start SQL Server Management Studio explicitly "as an administrator". (right-click -> run as administrator). If SSMS isn't started in administrator mode, the current login is not in "builtin\administrators"-group. That was the problem. It comes from Windows user access ...


0

I have achieved this(How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job). I have been running backups of SSAS databases, and needed to delete the old backup files, on a different server. Please see run-a-powershell-script-on-a-different-server-from-inside-a-sql-server-job


2

While the syntax of your trigger is not quite correct, this is not the source of the error. The main problem... Msg 8197, Level 16, State 4, Procedure ComputeGrade The object 'dbo.StudentMarks' does not exist or is invalid for this operation. ...is exactly what the error message states. Either you have spelled the name of the table wrong, you are in ...


0

Use command restore header only get backup file header information RESTORE HEADERONLY FROM DISK = 'E:\StackExchangebackupfile.trn' GO


2

@AaronBertrand is right. You should consider a different method of refreshing your staging environment. If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production. However I realize that changing your process can take time (if management will ...


4

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME. For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database: SELECT HAS_PERMS_BY_NAME ( N'Production.Product', N'OBJECT', N'SELECT' ); Related: To ...


3

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed, case when activity.last_executed_step_id is null then 'Step 1 executing' else ...


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


2

select ROW_NUMBER() OVER (PARTITION BY num, com ORDER BY identity_field) as 'RowNumber', t1.* into ##TableA from TableA t1 --delete t1 select t1.* from TableA t1 join ##TableA t2 on t2.identity_field=t1.identity_field where t2.RowNumber > 1 but first make backup of your table


1

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or ...


6

Ok, there are a few things to explain first. (1) The Connect to Server prompt within the Login properties is only asking for authentication. You cannot specify a database name within the Server Name field of that window in order to connect to it. For the server name you specify either server or server\instance name. You would connect to a specific database ...


0

Batching large data sets into smaller data sets, e.g. 100,000 rows or 1,000,000 rows will make the load run better than One Big Insert. But the same is true from SSIS, of course, as it batches inserts. The difference in time that you show in your example is fairly small. That does not give much hope for great speed improvements, but it does encourage you ...


1

You would need to use Dynamic SQL to pass a dynamic file path to the Bulk Insert. DECLARE @FileName NVARCHAR(4000); SET @FileName = '/path/to.csv'; DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #CSV FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )'; EXEC(@sql);


1

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons: No need for disk/memory/maintenance of the mySQL database If the updates to the various databases happen every few seconds, and the users may only ...


0

Continuous integration for the database can be implanted through version control and merging. There are a number of ways continuous integration can be implemented depending on the configuration of your development team and the change management processes used. Regardless, DBmaestro (which I work for) has the ability to generate and or execute database ...


1

Add role member SQLAgentUserRole in msdb, the user will receive access only to the local (owned) jobs. -- Step 1 CREATE LOGIN [AgentJobUser] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english] , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --Step 2 USE [msdb] GO CREATE USER [AgentJobUser] FOR LOGIN [AgentJobUser] WITH ...


1

User need access on that job, he can modify and run too. You can give the user - SQLAgentUserRole. From BOL : SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules ...


0

@gbn has already explained using NVARCHAR, that it holds UCS-2 characters, and COLLATE. But there were two follow-up questions: select statement to list the records in order of below. 1. Arabic, 2. Hebrew 3. English and: can you tell any way to filter only Arabic records or only Hebrew? Yes, it is possible to accomplish both of these. No, it ...


-1

One option to consider is to use SQLCMD to execute code on multiple servers from a single source. You further could log the process for each server by qualifying a distinct log file on each instance calling the code.


2

(This is very similar to a question recently posted on S.O.: Time trigger database updating) There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really ...


2

Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario. In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur ...


0

Rght click on SSMS, Run as administrator, sometimes this error appears. Once i couln't attach mdf file to database due this error.


0

It might be worth looking at XACT_STATE instead of @@TRANCOUNT. From BOL: Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. So there may be some cases ...


7

Kankuru is a great and FREE monitoring tool for SQL Server developed here in France. Aside from its monitoring capabilities, it also has a DB compare tool that is easy and simple to use. It will not only show you if your stored procedures, tables, indexes, types, etc. exist on two servers, but also what those differences are between those two servers. ...


5

I think the script in question is sp_BlitzIndex. The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question: Is it valid for those columns to all really contain nulls? When tables are ...


9

The clustered index IS the table. In the classic phone book analogy where everything is ordered by Lastname, Firstname there's not some other book that the phone book refers to. The Phone book IS the book, and it's in that order. When you have a clustered index, it contains all the data for all the columns in the table, ordered by the key you pick. In the ...


1

Microsoft's MSDN blog has a table describing the possible conditions and their outcomes. See Selectively using secure connection to SQL Server to understand client side setting and connection property options impacting secure connections for just the client involved. The server and other clients are not impacted.


1

I'm not sure what specific problem the script is trying to point out, but no, it is not best practice to unilaterally prevent all columns from allowing nulls. Allow NULLs when you need them, and don't allow them when you don't. There is no right answer to this; it all depends on how you are using the data and how likely it is that you'll have all of the ...


2

You are missing a new column generated by unpivoting attributes SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue] FROM ( SELECT itemNumber, type, code, description FROM myTable ) AS tb UNPIVOT ( Value FOR attributes IN ( type, code, description) ) AS myValues


2

There is also a way to create SQL Scripts Repository. Add new template folder Add script files


0

I've used both Subversion and GIT in the past (both free), and I think Red Gate has some tools that integrate into SSMS.


0

The following will work: sqlcmd -U user -P pass -S Server -Q "sp_who" -r1 1> test.log The output from the query will be put into the log and nothing is printed on the screen. In the case of an error, it will run like this: sqlcmd -U user -P pass -S Server -Q "sp_notreal" -r1 1> test.log Msg 2812, Level 16, State 62, Server XXXX, Line 1 Could ...


2

When your machine is not connected to the network, it uses the cached information for authentication purposes. This is how you can take the laptop home and log on with your domain account. If you created a login for a domain account while you weren't connected to the domain and then they tried to log in with that account, it would fail because that ...


1

Your server has to be a member of the SharePoint farm before it will allow you to install SSRS in integrated mode. Refer to this article for more information.


1

As Kin noted above, it is not possible to get this information since it is not stored by SQL Server. Please refer to this article on technet. Since you are using Ola's scripts, unless you changed the defaults, the output of the IndexOptimize job should go to the same location as your error log.


2

You can use the following construct: SELECT name, email, completedClaims, canceledClaims, MAX(datecolumn) OVER (PARTITION BY idcolumn) AS lastDate FROM Table ; I'm not sure what your columns and tables are called or what your id is (name? email?) so I called it idcolumn.


-1

I always use this TSQL command. -- the utc value declare @utc datetime = '20/11/2014 05:14' -- the local time select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc) It is very simple and it does the job.


0

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem. select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price, SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode then 1 else 0 end) as 'Selling Qty', SUM(case when e.description = 'Stock Quantity' ...


0

Could you please let me know if there is a way we can receive alerts when the status changes on a particular replication. You can achieve that using Replication Alerts e.g. Below will set up alert for Replication: agent retry. Note that you must have database mail enabled and configured as well as sql agent operator configured. EXEC ...


0

I write this query and this work. USE DestinationDatabase DELETE sysDiagrams WHERE name IN ( SELECT name FROM SourceDatabase.dbo.sysDiagrams ) SET IDENTITY_INSERT sysDiagrams ON INSERT sysDiagrams ( name , principal_id , diagram_id , version , definition ) SELECT name , ...


1

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes. Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index. The missing index feature isn't always ...


2

From BOL: Be made using the login's current security context Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using ...


3

The syntax is just slightly different: DELETE q FROM dbo.questions_tags AS q WHERE EXISTS ( SELECT 1 FROM dbo.questions_tags AS q1 WHERE q1.ctid < q.ctid AND q.question_id = q1.question_id AND q.tag_id = q1.tag_id ); Personally, I prefer to use a CTE. Then I can easily swap in a SELECT to validate what I am about to delete, and ...


0

According to a post in the MSDN forums, SQL Server 2005 (and probably earlier) contained a flaw that results in the error. This was reported by "A Fellows" in his problem resolution back in 2010. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9626c8f8-9171-4625-a9c0-ef72628066b9/fulltext-error-indexing-rows-with-jpgs In part he says: "Well ...



Top 50 recent answers are included