Here are couple more things you can try. Check if sql server is configured to listen on that IP address. If your server has multiple IP addresses that doesn’t necessarily mean that it can automatically accept connections from all IPs. To set this go to SQL Server Configuration Manager -> SQL Server Network Configuration -> … Read more
Just to give future visitors something to look on, in my case issue was I had two different version of SSIS installed on same machine. One was for 2005 and another for 2014. And oddly enough even I was explicitly pointing to newer version (as shown in later part of post) in my command, it … Read more
Sometimes, the use of SYNONYMs is a good strategy: CREATE SYNONYM [schema.]name FOR [[[linkedserver.]database.]schema.]name Then, refer to the object by its synonym in your stored procedure. Altering where the synonym points IS a matter of dynamic SQL, but then your main stored procedures can be totally dynamic SQL-free. Create a table to manage all the … Read more
Best Practices for Semantic Data Modeling for Perfor… EAV is notoriusly problematic as it leads to severe deployment performance and scalability problems. The Whitepaper in the link, released by the SQL Server Customer Advisory Team tries to offer some guidance to deploy a succesful EAV model.
In line with my comment above and the answer by Yawar, I’m not aware that Pass Through Queries are ever editable/updateable. They are editable in the sense that you can edit a save Pass Through Query object, but I don’t believe it’s possible for a Pass Through Query to produce an editable recordset. There are … Read more
In playing around with the procedure, I can get a a return -6, if I insert a null into foo.KeyCol and remove the RAISERROR in the inner catch. This is something SQL Server is doing, and is documented here: Return Values from Stored Procedures.
you can save the xlsx file as a tab-delimited text file and do BULK INSERT TableName FROM ‘C:\SomeDirectory\my table.txt’ WITH ( FIELDTERMINATOR = ‘\t’, ROWTERMINATOR = ‘\n’ ) GO
SSIS doesn’t implicitly convert data types, so you need to do it explicitly. The Excel connection manager can only handle a few data types and it tries to make a best guess based on the first few rows of the file. This is fully documented in the SSIS documentation. You have several options: Change your … Read more
You can try the following: * Updated ** — Test Data DECLARE @YourTable TABLE(Product INT, Timestamp DATETIME, Price NUMERIC(16,4)) INSERT INTO @YourTable SELECT 5678, ‘20080101 12:00:00’, 12.34 UNION ALL SELECT 5678, ‘20080101 12:01:00’, NULL UNION ALL SELECT 5678, ‘20080101 12:02:00’, NULL UNION ALL SELECT 5678, ‘20080101 12:03:00’, 23.45 UNION ALL SELECT 5678, ‘20080101 12:04:00’, NULL … Read more
Combine the SUBSTRING(), LEFT(), and CHARINDEX() functions. SELECT LEFT(SUBSTRING(YOUR_FIELD, CHARINDEX(‘;’, YOUR_FIELD) + 1, 100), CHARINDEX(‘[‘, YOUR_FIELD) – 1) FROM YOUR_TABLE; This assumes your field length will never exceed 100, but you can make it smarter to account for that if necessary by employing the LEN() function. I didn’t bother since there’s enough going on in … Read more