Newbe Dev Stack

Answer : Problem was apparently not the SQL server, but the NAV system that updates the field. There is a compression property that can be used on BLOB fields in NAV, that is not a part of SQL Server. So the custom compression made the data unreadable, though the conversion worked. The solution was to turn off compression through the Object Designer, Table Designer, Properties for the field (Shift+F4 on the field row). After that the extraction of data can be made with e.g.: select convert(varchar(max), cast(BLOBFIELD as binary)) from Table Thanks for all answers that were correct in many ways! The accepted answer works for me only for the first 30 characters. This works for me: select convert(varchar(max), convert(varbinary(max),myBlobColumn)) FROM table_name It depends on how the data was initially put into the column. Try either of these as one should work: SELECT CONVERT(NVarChar(40), BLOBTextToExtract) FROM [NavisionSQL$Customer]; Or if it was just varchar ... ...

Answer : Make sure that the Key column's datatype is int and then setting identity manually, as image shows Or just run this code -- ID is the name of the [to be] identity column ALTER TABLE [yourTable] DROP COLUMN ID ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1) the code will run, if ID is not the only column in the table image reference fifo's When you're creating the table, you can create an IDENTITY column as follows: CREATE TABLE ( ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ... ); The IDENTITY property will auto-increment the column up from number 1. (Note that the data type of the column has to be an integer.) If you want to add this to an existing column, use an ALTER TABLE command. Edit: Tested a bit, and I can't find a way to change the Identity properties via the Column Properties window for various tables. I guess if you want to make a column an identity column, you HAVE to use an ALTER TABLE command. You have to ...

Answer : InfoPlus21 is process historian containing list of templates of different tag structure e.g. IP_AnalogDef, IP_DescreteDef, IP_TextDef etc. Based on process tags from DCS/OPC/Any other historian, the IP21 records are created and each record acts as a table in historian. ANS1: Aspentech software is only windows based compatibility however IP21 aspenONE Process Explorer is web based and therefore you can access it over any operating system using host url. ANS2: you can try SELECT statement to get data from IP21 Historian using it's end-user component SQLPlus or on excel add-ins. e.g. SELECT NAME, IP_DESCRIPTION, IP_PLANT_AREA, IP_ENG_UNITS FROM IP_ANALOGDEF RESULTS: I hope this help you understand better. Otherwise you need to first learn the structure of your IP21 historian tags to build the query e.g. If it has customized structure, then you have to build your own. Welcome in industrial-IT ! For these technology, the best option is the 'AspenTech...

Answer : As Grant said bacpac is dacpac+data.Below is one more definition that might help "A bacpac includes the schema and data from the database. A dacpac containers only the schema and not the data" You might have understood, you need bacpac. I need this process to run in the fastest way possible, so I'd be curious if one option is better for performance. We have few databases(400Gb) in Azure.we observed it is faster to import data when we load data from storage..The approach we followed was to load data into storage which is in same location as database and then import data.. SQLSERVER team tested a few options and they observed BCP is fastest compared to remaining options .. Below is how you import bcp data into azure bcp TestDB.dbo.Customer in "C:\Users\cesardl\BCP\Customer.txt" -c -U mysqlazureuser@mysqlazureservername -S tcp:mysqlazureservername.database.windows.net -P mypassword Further reading/References: ...

Answer : Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT. Try this: DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 int DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectCol1=col1 FROM @table1 WHERE RowID=@CurrentRow --do your thing here-- END DECLARE @table1 TABLE ( idx int identity(1,1), col1 int ) DECLARE @counter int SET @counter = 1 WHILE(@counter < SELECT MAX(idx) FROM @table1) BEGIN DECLARE @colVar INT SELECT @colVar = col1 FROM @table1 WHERE idx = @counter -- Do your work here SET @counter = @counter + 1 END Believe it or not, this is actually more efficient and performant than using a cursor. ...

Answer : Strictly, method a is the least resource intensive: a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time? I saw a similar test elsewhere with similar results too. I prefer the DATEADD/DATEDIFF because: varchar is subject to language/dateformat issues Example: Why is my CASE expression non-deterministic? float relies on internal storage it extends to work out first day of month, tomorrow, etc by changing "0" base Edit, Oct 2011 For SQL Server 2008+, you can CAST to date i.e. CAST(getdate() AS date) . Or just use date datatype so no time to remove. Edit, Jan 2012 A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server Edit, May 2012 Do not use this in WHERE clauses and the like without thinking: adding a function or CAST...

Answer : For modifying an existing column in an existing table: ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn This can also be done through the SSMS GUI. Put your table in design view (Right click on table in object explorer-> Design ) Add a column to the table (or click on the column you want to update if it already exists) In Column Properties, enter (getdate()) in Default Value or Binding field as pictured below In that table in SQL Server, specify the default value of that column to be CURRENT_TIMESTAMP . The datatype of that column may be datetime or datetime2 . e.g. Create Table Student ( Name varchar(50), DateOfAddmission datetime default CURRENT_TIMESTAMP );

Answer : If you want to provide a timeout for a particular query, then CommandTimeout is the way forward. Its usage is: command.CommandTimeout = 60; //The time in seconds to wait for the command to execute. The default is 30 seconds. You can set the timeout value in the connection string, but after you've connected it's read-only. You can read more at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx As Anil implies, ConnectionTimeout may not be what you need; it controls how long the ADO driver will wait when establishing a new connection. Your usage seems to indicate a need to wait longer than normal for a particular SQL query to execute, and in that case Anil is exactly right; use CommandTimeout (which is R/W) to change the expected completion time for an individual SqlCommand. A cleaner way is to set connectionString in xml file, for example Web.Confing(WepApplication) or App.Config(StandAloneApplication) . ...

Answer : Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster declare @when datetime = GETUTCDATE() select @when -- date + time declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime) select @day -- date only In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed): declare @when datetime = 'Feb 15 2012 7:00:00:000PM' declare @min datetime = FLOOR(CAST(@when as float)) declare @max datetime = DATEADD(day, 1, @min) select * from sampleTable where DateCreated >= @min and DateCreated < @max Simple Cast to Date will resolve the problem. DECLARE @Date datetime = '04/01/2016 12:01:31' DECLARE @Date2 datetime = '04/01/2016' SELECT CAST(@Date as date)...

Answer : SQL Server does not currently support subqueries for CHECK CONSTRAINTs. As you have discovered, there can be trouble with CHECK constraints involving UDFs when attempting to circumvent the subquery limitation. The alternative constraint implementation strategies are triggered procedural and embedded procedural . The former is preferred because, in common with declarative constraints, they cannot be circumvented. Implementing a triggered procedural strategy that is well optimized and handles concurrency issues is non-trivial but still doable. I highly recommend the book Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11 (the code examples are Oracle but can be easily ported to SQL Server). As others have mentioned already, this type of Check constraints is not yet implemented in SQL-Server. Besides triggers, you could also examine the possibility of changing the table's design. A possible alternative includes storing the...