Tag Info

New answers tagged

-2

Does the UID the sql agent is using have permissions to shut the service down?


4

A FOREIGN KEY constraint cannot reference a table in a different database. It's by design. Here's an example: CREATE DATABASE test1 GO USE test1; GO CREATE TABLE tableA ( PK int PRIMARY KEY CLUSTERED ,someColumn varchar(50) ) GO CREATE DATABASE test2 GO USE test2; GO CREATE TABLE tableB ( PK int PRIMARY KEY CLUSTERED ,aForeignKey ...


1

Since you said you are beginner and is in learning phase I would give you lot of good articles to learn about SQL Server backup. To start with main question as already mentioned in my comment So can I restore 4th one with 1st one. With out the 3rd ? Differential backup only relates to previous full backup you have taken so NO you cannot restore 4th ...


0

Please find dbvis.vmoptions in c:\Program Files\DbVisualizer and add following option there -Djava.library.path="c:/ms_jdbc_41/sqljdbc_4.1/enu/auth/x64" just after the line -Dsun....


2

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


3

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing ...


1

No, it is nothing to be worried about - the Agent isn't considered a clustered service to SQL Server simply because it can't fail over on its own. You should test a failover and be confident that SQL Server Agent will start on the other node and that jobs will run etc. (sometimes there can be local dependencies that won't exist on the other node(s)), but ...


0

I thought I'd create a table for quick reference. CREATE TABLE dbo.TraceDefinition ( TraceFlag INT NOT NULL CONSTRAINT PK_TradeDefinition PRIMARY KEY CLUSTERED, [Description] VARCHAR(MAX) NOT NULL ) INSERT INTO dbo.TraceDefinition (TraceFlag, [Description]) SELECT TraceFlag, [Description] FROM (VALUES (260, 'Prints versioning information about extended ...


1

There's the table sys.sysdepends which will help with your stored objects. For embedded SQL you could SET ROWCOUNT for the batch. This will terminate the query after compilation (and hence name resolution and object binding) but before large quantities of data are processed.


1

2008 supports except. Import the excel data into a different table--mytemptable--then INSERT INTO mymaintable SELECT * from mytemptable EXCEPT SELECT * from mymaintable; Then you can get rid of mytemptable (or whatever you want to call it).


0

I've now got this in production and it's working beautifully. We found the underlying issue before this went out, but it may be very useful for future issues. To any future readers, SET CONTEXT_INFO can indeed be used for correlating SQL requests to the code and/or requests that run them, and I've seen no noticeable performance hit. For anyone curious ...


0

We changed the SQL SERVICE user to one that is "Domain Admin". I've made some researches to know why this happens. It says that when you shutdown the service, you need an account with privileges do create a new SPN 9 when it turns on again ). If you start a service without it, it will show in CANNOT GENERATE SSPI CONTEXT. we are changing the privileges of ...


0

I was able to achieve the update of all records my table, using the below set of commands : DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255)) INSERT INTO @MemberNames SELECT P.ID, u.NAME FROM dbo.tblProjects AS P CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID DECLARE ...


1

Click Add , and put a filename.bak, then retry the backup process.


2

'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth. There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. ...


0

This happened to me when I upgraded our SQL Server from 2008R2 to 2014. db_datareader or db_datawriter or their deny equivalents perfectly work fine in 2008R2 but they doesn't work in 2014. Example to grant permission in SQL Server 2014 If you want to give user Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema ...


0

REAL and FLOAT are approximate data types. FROM: https://msdn.microsoft.com/en-us/library/ms173773.aspx "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly."


6

Stop using REAL and use DECIMAL instead. REAL and FLOAT are approximate data types and can't represent all numbers in the nice, rounded way you are used to. Compare these: DECLARE @a REAL = 0.23, @b REAL = 0.24; SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x; Result: 0.234999999403954 Now using DECIMAL instead: DECLARE @a DECIMAL(10,4) = ...


4

You should make use of a split string function. select left(S.Item, charindex('|', S.Item) - 1), stuff(S.Item, 1, charindex('|', S.Item), '') from dbo.tblProjects as P cross apply dbo.SplitString(P.Documents, '$') as S; Aaron Bertrand blogged about some of the alternatives you have in Split strings the right way – or the next best way


1

SELECT C.customername ,C.customercode ,L.[loan type] ,S.[account number] ,S.status FROM [first table] AS C INNER JOIN [second table] AS L ON C.customercode = L.customercode INNER JOIN [3rd table] AS S ON S.customercode = C.customercode This is only as a guidance; it is better to post some sample data and ...


0

In relation to the ANSI_PADDING portion of your question, you can use sys.columns From ANSI_PADDING MSDN article: Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. ... This setting affects only the ...


0

Unless you control login privelages , you will not able to restrict them.so think like you have granted them only basic select to see the table data.since you are not maintaining server ,, they can easily get admin privelages.maintenance stuff still should be taken by client and once bavkups are taken they can see schema , sp..one way I can think of is ...


2

Instead of issuing PRINT @tsql;, you can issue EXEC( @tsql );, if I understand what you're asking. If so, I would suggest declaring a preview parameter ( something like DECLARE @Preview BIT = 1; ) and then use an IF to either print or execute. IF ( @Preview = 1 ) BEGIN PRINT @tsql; END ELSE BEGIN EXEC( @tsql ); END;


2

The only thing you're really changing by using simple recovery is you're shifting the time when you have to perform additional backup work. Instead of taking log backups throughout the process (which shouldn't be extra work, because you should already be doing that!), now you have to take a full backup at the end of the process. In the meantime, you've ...


-1

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Therefore unless until you can grant any permission to users for their login they wont be able to access tables, however can only see the databases ...


9

I don't think you can perform this operation online, since you can't just move the clustered index from one column to another, and you can't use DROP_EXISTING to reduce the amount of work that has to be done. However, you can avoid an outage, if you're willing to perform the work. Create a new table with the new structure Rename the original table, and ...


0

In SQL Server 2012 and later the format function is very handy for this sort of thing. Note that MM is for months, mm is for minutes. Using yyyy-MM makes sorting very simple. SELECT count(column1), format(dateColumn, 'yyyy-MM') FROM table GROUP BY format(dateColumn, 'yyyy-MM') ORDER BY 2


2

UPDATE sd SET sd.FlagEmailNota = sdt.FlagEmailNota, sd.FlagEmailSLA = sdt.FlagEmailSLA, sd.FlagEmailFechado = sdt.FlagEmailFechado FROM dbo.Tblservicedesk AS sd INNER JOIN dbo.Tblservicedesk_temp AS sdt ON sd.ID_Solicitacao = sdt.ID_Solicitacao


3

Assuming both Tblservicedesk & Tblservicedesk_temp having same columns. You can write update statement to update the three columns (FlagEmailNota, FlagEmailSLA, FlagEmailFechado ). UPDATE tsd SET tsd.FlagEmailNota = tsdt.FlagEmailNota , tsd.FlagEmailSLA = tsdt.FlagEmailSLA, tsd.FlagEmailFechado = tsdt.FlagEmailFechado FROM Tblservicedesk ...


1

Yes you execute stored procedure via linked server. The EXECUTE statement must contain the name of the linked server as part of its syntax. Syntax EXECUTE servername.dbname.owner.procedure_name e.g EXEC [KAP-PC\SQL2012DXD02].[master].[dbo].[sp_helpserver] where sp_helpserver is the stored procedure on the master DB with owner as DBO and server name ...


0

I got alternate solution for the same. declare @S nvarchar(100) = 'www.abc.com/a/b/c/d/e/f/g/h/i' while PATINDEX('%[/]%' , @S) > 0 BEGIN SET @S = LEFT (@S,LEN(@S) - PATINDEX('%[/]%' , REVERSE(@S))) SELECT @S END


4

Here's one which is heavily based on Jeff Moden's DelimitedSplit8k: declare @url VARCHAR(8000), @delimiter char(1) set @url = 'www.abc.com/aaa/bb/c/ddd/ee/ff/g/h/iii' set @delimiter = '/' ;WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL ...


-1

USE database; GO EXEC sp_change_users_login 'Update_One', 'username1', 'username1'; GO


1

You can use the available views in msdb to find the information regarding backups : dbo.backupset: provides information concerning the most-granular details of the backup process dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets. dbo.backupfile: this system view provides the most-granular information for ...


1

In your code I see a CONVERT(NUMERIC,...) but not a CONVERT(INT,...). So where is the converting to data type int happening? SELECT CONVERT(INT, N'0.0000') -- Conversion failed message SELECT CONVERT(NUMERIC, N'0.0000')-- Converts to 0 For what it is worth, if you do the following, you also get a '0'. SELECT CONVERT(INT, CONVERT(NUMERIC, N'0.0000')) ...


0

As a very generic answer, if you want to test code of this nature without actually changing the data then it is possible to run your code, query the results, and then undo any changes before they are committed. -- Begin a new transaction begin transaction -- Show initial data select * from [dbo].[employee] -- Your statement to change the data ...



Top 50 recent answers are included