Tag Info

New answers tagged

0

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.


1

(This is essentially a duplicate of 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 ...


-1

I have this query in SQL Server: SELECT * FROM [Datos2014Electronica] p RIGHT JOIN ( select ComentarioIncidencias as ComentarioIncidencias2 ,Linea as L FROM [Datos2014Electronica] WHERE Fecha Between '11/17/2014' AND '11/17/2014' AND Turno= 'J' AND Linea= 'PL2-Bot' AND Grupo= 'SMD' and Producto Like '%Parada%' ) p2 on p.Linea=p2.l and p.Fecha ...


-2

This is a great question. First of all I think a "natural foreign key" join would be useful in many cases and I think it would make the sql more readable. It would be interesting for readers to check their code and report back how many of the queries they use in production code are performing these kinds of joins. It is clear that there are cases that ...


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 ...


6

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 ...


8

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 ...


1

Try the -VERBOSE option to see if there are additional information generated by dsqgen. Also, the version of templates downloaded from tpc.org are missing some DEFINES.


2

Converting my comment to answer (read the comments in the script) : if you are using the same backup file name (ICT.bak) every time you backup, its better to use INIT. This way the previous file gets overwritten. USE master -- here no need to use the name of the database that you are backing up. Other RDBMS like SybaseASE will thorw an error saying that ...


0

Based on the answer by @Shawn Melton above I have created a proxy in SQL Server using this code: -- script for creating a proxy in order to run a set of POWERSHELL commands -- this is to delete backups older than 2 days -- marcelo miorelli -- 19-nov-2014 --============================================================================================== -- ...


2

If you are within a domain environment you can setup a proxy account that has permissions on SQLBILON1. However, using remote PowerShell is a bit of overkill and adds an unneeded layer to troubleshoot. If you do not have remote PowerShell enabled on both servers and firewall access between the servers configured properly it will have issues. I would simply ...


3

There is always at least something else you should also know and almost equally, always something else you should be consciously putting a stop to. Specifically in the context of data warehousing, which is a relatively fledgling sector, leveraging relatively new technologies. In regards to what I've seen in the real world, walking into a company for the ...


4

Server name and instance name should be separated by a backslash, not a forward slash. Try: sqlcmd -U myname -P mypwd -S mysys\SQLEXPRESS -d master -Q "Select count(*) from sys.databases"


1

Step 1: ---- Create master table CREATE TABLE [dbo].[tbl_Master] ( [MasterID] [int] NOT NULL, [Value] [varchar](200) NULL ) Step 2: ---- Create PRIMARY KEY on column for having unique values ALTER TABLE [dbo].[tbl_Master] ADD CONSTRAINT [PK_tbl_Master] PRIMARY KEY CLUSTERED ( [MasterID] ASC ) step 3: -- create chlid ...


1

1) Yes 2) Yes, After creating the new files, you'll need to drop and re-create the clustered indexes of the target tables, moving them on to the new filegroup to move the data: CREATE CLUSTERED INDEX CIX_YourTable ON dbo.YourTable(YourClusteringKeyFields) WITH DROP_EXISTING ON [filegroup_name] 3) If you can get the DBA to back you up on this solution, ...


12

When you do top 1 the query optimizer will build a plan that is built to fetch 1 row as fast as possible. When you use a local variable the value of the variable is unknown to the optimizer and instead builds a plan that is optimized to fetch 100 rows as fast as possible. In your case the query plan generated with a row goal of 100 is the better plan to ...


1

I think that your simplest option is a job. You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need. Here's an example based on your requirements: USE master GO CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , ...


1

Use self join with row numbering SELECT Productset1.Product, Productset1.serial, Productset2.Product, Productset2.serial, CAST(DATEDIFF(minute, Productset1.time_checked, Productset2.time_checked) AS VARCHAR(20)) + ' (minutes)' FROM ( SELECT row_number() OVER ( ORDER BY time_checked ) pid, Product, ...


1

Login: A login is any principal that is granted access to a SQL Server instance. The access can be granted to domain users, domain group, or SQL Server authenticated accounts. Credential: A credential provides a mechanism to store login information for a domain account within SQL Server. The credential can then be used to pass that authentication ...


0

You can use audit CREATE SERVER AUDIT AuditDataAccess TO FILE ( FILEPATH ='C:\SQLAudit\' ); ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON); CREATE DATABASE AUDIT SPECIFICATION DatabaseAudit FOR SERVER AUDIT [AuditDataAccess] ADD (SELECT ON SCHEMA::[DataSchema] BY [public]) WITH (STATE = ON); And then you can query that audit file by simple select ...


0

Use the parameter NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name. For example, if the default domain is set to us.acme.com, the connect string CONNECT scott/tiger@sales ...


2

@Joanna.B Allowing access is a tricky and well planned strategy. Ultimately DBA's are responsible to allow access and maintaining of databases and mainly in STG and PRODUCTION environment. You do not want to allow any unnecessary access into STG and PROD environment because ultimately you DBA's will be responsible for any changes. First of all if you allow ...


1

A common practice in my experience has been that if a user needs elevated access to a higher environment, you restore a copy to the level which they have access to. This ensures that you can keep similar security across most environments and mitigates privacy and security concerns as well as the creation of new issues. For example, if tech_leads need to ...


3

This can't be done without bringing the database online, and this is not just a GUI problem. ALTER AUTHORIZATION ON DATABASE::test TO sa; Yields: Msg 942, Level 14, State 4, Line 1 Database 'test' cannot be opened because it is offline. So, as one of the comments suggested, bring it online, set it to single_user if you need to, change the owner, ...


0

Primary Key and Clustered Index are really separate concepts and, although many tables put both of these attributes on the same constraint or index, this is not a requirement. I do not see how changing the Clustered Index to the make it also the Primary Key will particularly help your performance. It sounds like you have a Primary Key (BIGINT) already and ...


2

The difference in logical reads leads me to believe that the two executions are getting different plans. This could be because: there are different set settings between the app that is calling the procedure that profiler is picking up and your instance of SSMS. you have a different default schema (you really should say exec dbo.SM_SP...). You can see if ...


4

SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. That can be found using the STATS_DATE function. You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per ...


8

No. This hard-coded restriction exists for good reasons related to possible excessive query plan compilation time. You can workaround it by listing the VALUES clauses in a CTE, then INSERTing from the CTE, but I do not recommend it. Break the INSERT statement up into VALUES clauses of <= 1,000 lines each as a workaround, or use an alternative data ...


0

On Button Click, I got to do something like this, as suggested by @MarkSinkinson. protected void btnSubmit_Click(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant (CategoryName) values (@CategoryName)", conn); cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text); conn.Open(); ...



Top 50 recent answers are included