Tag Info

New answers tagged

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


1

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);


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


13

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

Something like the following should be good enough to get you started. It should be quite lightweight as well. DECLARE @Name NVARCHAR( MAX ), @SQL NVARCHAR( MAX ); DECLARE @t_BindingErrors TABLE ( ViewName NVARCHAR( MAX ), ErrorMessage NVARCHAR( MAX ) ); DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR ...


0

You can generate SELECT statements for all the views in a database and just run the results: SELECT 'SELECT * FROM dbo.' + name from sys.sysobjects WHERE type = 'V' Which should return SELECT * FROM dbo.xxxx SELECT * FROM dbo.yyyy etc... For all the views in the current database. Customize as needed for a different schema or columns to SELECT to avoid ...


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

Granted DBID and UPC are not guaranteed to be the same for Price1 and Price2 your code should be similar to this: SELECT (SELECT TOP 1 DBID FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS DBID1 ,(SELECT TOP 1 UPC FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS UPC1 ,(SELECT MIN(Price1) FROM sub) as Price1 ,(SELECT TOP 1 DBID FROM sub WHERE ...


0

Where minimum is Price2 or Price1? If both of them then SELECT DBID,UPC,Price1,Price2 FROM MyTable WHERE Price2 = (SELECT MIN(Price2) FROM MyTable) AND Price1 = (SELECT MIN(price1) FROM MyTable)


1

You can treat the union as a subquery. SELECT TOP 1 DBID, UPC, Price1, Price2 FROM (SELECT DBID, UPC, Price1, Price2 FROM Table1 UNION SELECT DBID, UPC, Price1, Price2 FROM Table2) sub ORDER BY Price1 DESC


1

Does this do what you're after? SELECT TOP (1) WITH TIES TX.COL1, TX.COL2, TX.COL3, TX.COL4 FROM dbo.TESTX AS TX ORDER BY ROW_NUMBER() OVER ( PARTITION BY TX.COL1 ORDER BY -- Priorities CASE COL2 WHEN 'dog' THEN 1 WHEN 'cat' THEN 2 WHEN 'bird' THEN 3 ...


1

Use RANK() function with CTE_X as ( select *, RANK() over (partition by Col1 order by Col2 DESC) rnk from t ) select * from CTE_X where rnk = 1 ; For reference (BOL) http://msdn.microsoft.com/en-us/library/ms176102.aspx


4

You need to double-up apostrophes to embed them within a string, since they also serve as string delimiters: INSERT dbo.table(column) VALUES('''affinity mask'',0');


2

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery. Quote from the Data Loading Performance Guide: Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. Re ...


0

I did something very similar, but not as a single script. I used migrations. I also described this in Version Control and your Database. And indeed, every step is a different script, and the application drives the upgrade (the migration). Every step is tested. Every change is an upgrade, be it DDL or DML, it matters not. There are DML changes like changing ...


9

Don't. While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness. Instead use proper encryption hierarchy. Do not ...


0

SQL Server has no global variables. If you want to make something available to all sessions, use a table and write the data there. Another option is persisting the data in a global temporary table (prefixed with two # signs). It could get tricky to detect whether the table got out of scope though. If you want to make a value available throughout the whole ...


0

Without commenting on your encryption solution per se, storing a string or password in the database can be as trivial as creating a single-row, single-column table and properly restricting the permissions on that table. Another approach would be to create an encrypted view with a hard-coded column. CREATE VIEW dbo.mySecret WITH ENCRYPTION AS SELECT ...


0

Context_info acts like a global variable. It's binary so will require some work to extract the password each time it is used. BOL has details and examples.


0

Yes, you are able to restore a database with NORECOVERY and then perform more restore produces within one script, allowing you to recover a database to a point in time. When you restore full and differential backups you should have a MOVE statement for both the data and logs. I would recommend moving the backups you want to restore to a directory which ...


1

CREATE PROCEDURE sp_insert_allocated_time @p_project_id INT, @p_allocated_time INT AS BEGIN DECLARE @sum_alloc_time int, @alloc_hours int, @fld_id int select @sum_alloc_time = SUM(fld_allocated_time) from dbo.Timesheet where fld_project_id = @p_project_id; select @alloc_hours = p.fld_allocated_hours, @fld_id = p.fld_id FROM dbo.Project p INNER JOIN ...


2

Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length"). As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index). The SQL engine is smart enough to know that and doesn't do anything to ...


0

You also have an error in your select clause, which is why you are getting the message you mentioned in your comment. When you are grouping, you can only reference aggregates of your data, or columns in your grouping set. In your case statement that is determining your description, you are referencing a non-aggregated description. Try just removing the ...


6

I guess that when Type has a certain value ('Test'), you want the Structure and Description columns to be considered for the grouping but otherwise to not use them at all. This is one way to do this: GROUP BY Type, Account, CASE WHEN Type = 'Test' THEN Structure ELSE '' END, CASE WHEN Type = 'Test' THEN Description ELSE '' END The '' should ...


2

You should not be concerned on how SQL Server stores the data. For now, you should use the TIME data type and if you want the 12 hour format, then convert it in the front end application or during SELECT statement using CAST() or CONVERT()


1

I found a script: SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName ,dosi.virtual_machine_type_desc ,Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi If you have a CMS configured, run the below Script from your CMS against multiple servers: SELECT ...


6

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model. OR If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio. DECLARE @result int EXEC @result = xp_cmdshell 'SYSTEMINFO' System Manufacturer: VMware, Inc. System Model: VMware ...


-1

Executed this script : DBCC CHECKIDENT (PixTest, NORESEED) Result says Identity value is NULL. Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Then we believe you need to follow either of the other two ways ...


-1

Or try this: SET IDENTITY_INSERT PixTest ON INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1') SET IDENTITY_INSERT PixTest off


2

The error isn't coming from the trigger. It's coming from your insert statement. INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1') It's getting parsed and returning an error before you ever get to the trigger. Change it to this and it works. INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')


0

As mskinner mentions, you can use Row Number and Partition By to remove duplicates of specific fields. Here's how you might do it using a Common Table Expresion. WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)as RowNumber FROM YourTable) DELETE FROM cte WHERE RowNumber> 1 Partition by the fields you want to ...



Top 50 recent answers are included