You can’t do a bulk-update in SSIS within a dataflow task with the OOB components. The general pattern is to identify your inserts, updates and deletes and push the updates and deletes to a staging table(s) and after the Dataflow Task, use a set-based update or delete in an Execute SQL Task. Look at Andy … Read more
In a single database – yes: USE your_database SELECT name FROM sys.tables Getting all tables across all databases – only with a hack…. see this SO question for several approaches how to do that: How do I list all tables in all databases in SQL Server in a single result set?
INSERT INTO table2 (column1, column2, column3) SELECT column1, ‘no’, column3 FROM table2 WHERE column2 = ‘yes’ Hopefully this is a bit clearer as to how you do this. As you can see, I’ve grabbed two columns from table2 and for the other column I used a text value for instead of the value for column2. … Read more
Just in case anyone looking at this is using SQL server 2008, i had to make a couple modifications: SELECT ‘ALTER TABLE [‘ + sys.objects.name + ‘] ALTER COLUMN [‘ + sys.columns.name + ‘] ‘ + sys.types.name + CASE sys.types.name WHEN ‘text’ THEN ‘ ‘ ELSE ‘(‘ + RTRIM(CASE sys.columns.max_length WHEN -1 THEN ‘MAX’ ELSE … Read more
Use DATALENGTH to retrieve the number of bytes and then convert, like this: SQL Fiddle MS SQL Server 2017 Schema Setup: CREATE TABLE supportContacts ( id int identity primary key, type varchar(20), details varchar(30) ); INSERT INTO supportContacts (type, details) VALUES (‘Email’, ‘[email protected]’), (‘Twitter’, ‘@sqlfiddle’); Query 1: select *, gigabytes / 1024.0 as terabytes from … Read more
This isn’t a shortcut key, but there is a menu option in the Query Editor to do this. Open your query and then go to Edit > Outlining > Toggle All Outlining. This will toggle (i.e. expand/collapse) all nodes in the query.
sysadmin is a server role; it can be applied to any login. When installing SQL Server a login sa is created with this privilege; you can specify the password when you’re installing SQL Server. Or you can create your own login: CREATE LOGIN adminuser WITH PASSWORD = ‘ABCDegf123′; GO EXEC master..sp_addsrvrolemember @loginame = N’adminuser’, @rolename … Read more
When installing SQL Express, you’ll usually get a named instance called SQLExpress, which as others have said you can connect to with localhost\SQLExpress. If you’re looking to get a ‘default’ instance, which doesn’t have a name, you can do that as well. If you put MSSQLServer as the name when installing, it will create a … Read more
I presume you mean via SSMS? For a SQL Server Instance: SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) For a SQL Server Installation: Select @@Version
Not Enough Memory This is very likely a Memory problem, perhaps aggravated or triggered by other things, but still inherently a memory problem. there are two other (less likely) possibilities, that you should check and eliminate first (because it is easy to do so): Easy To Check Possibilities: You may have “Auto Close” enabled: Auto … Read more