New answers tagged sql-server-2008
1
Assuming you have access and sufficient rights to the database on the SQL server via SSMS.
Create a shared folder on a different network server 192.I.Have.Access. Grant the SQL engine and agent accounts read and write access to the shared folder.
You can take backups to the share (with write access) and you can restore backups from the share (with read ...
0
You can simoly put around the Select ... ORDER BY RAND() in parenthesis, so that MysSQL knows how to process it
(SELECT *
FROM
(
SELECT hobber_deal_page.id,
hobber_deal_page.merchant_id,
hobber_deal_page.category_id,
hobber_deal_page.emirate_id,
hobber_deal_page.title,
hobber_deal_page.description,
...
1
You can turn it off via Auto list members via: Tools->Options->Text Editor->Transact-SQL->General->Auto list members, however this actually causes a bug in SSMS where you get an object null error whenever you hit backspace that repeatedly annoys you.
I find Apex SQL to be quite slow, so I don't use it.
Intellisense is incredibly useful, so I ...
0
you should split the string using the appropriate function and then put two tables in join
Begin
declare @mylistofstrings varchar(max) = 'Love,Need'
create table #fooTable(my_id int, my_value varchar(50));
insert into #fooTable(my_id, my_value) values ( 1, 'I Love Sql');
insert into #fooTable(my_id, my_value) values ( 2, 'I Hate Linq');
insert into #...
0
I had a similar problem (see https://stackoverflow.com/questions/63043157/converting-binary-data-to-ascii-in-sql-server-with-illegal-characters).
I found a solution for my purposes, which uses the heuristic approach of converting the binary to a hex string, then doing string replacement of "00", before doing back to binary and string again.
IF ...
2
Refresh Management Console
The first step to perform, is to check that the SSMS (SQL Server Management Studio) is displaying the actual state of all the databases. To achieve this, you would right-click the databases branch in the Management Console and in the context menu select Refresh.
Possible Outcomes
Now depending on the actual state of the database ...
1
Even if the database status is 'Recovery pending' you should still be able to drop it the regular way:
USE [master] ;
GO
DROP DATABASE [MyDeadDatabase] ;
GO
3
OK, found how to do it in SQL Server 2008:
EXEC master..sp_dropsrvrolemember
@loginame = N'YourLogin',
@rolename = N'dbcreator';
Thanks again for the suggestions: when this DB will be migrated now I know that probably I'll have to modify the script to use ALTER SERVER ROLE
2
Are you attempting this with SSMS? I followed this process using SSMS v18.4:
Take DB offline
Manually delete mdf and ldf files.
Right click on database in SSMS and click 'Delete'.
This worked just fine for me.
3
You can only add logins to server roles, not database users. To remove a login, you use ALTER SERVER ROLE ... DROP MEMBER:
ALTER SERVER ROLE dbcreator DROP MEMBER login_name;
Please stop using the stored procedures; they're all deprecated. For example, from the sp_addsrvrolemember documentation:
This feature is in maintenance mode and may be removed in a ...
0
This will allow you to choose which part by simply filtering on RowNum. It will work no matter how many values you have...(assuming data is in a table called #temp)
SELECT
FIELD1,Value,ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELD1) RowNum
FROM
#Temp T
CROSS APPLY STRING_SPLIT(FIELD1, ' ') T1
Specifically, this query will give you the first names. ...
0
In Postgres you can use split_part()
split_part(the_column, ' ', 1);
Or, if you need to deal with multiple spaces, you can use regexp_split_to_array()
(regexp_split_to_array(the_column, '\s+'))[1]
1
You can use "Substrig" and "charindex" functions to get what you want.
See this code as a demo on how to use it:
declare @name varchar(30)
set @name = 'shyam banarjee'
select SUBSTRING(@name,1,charindex(' ',@name)-1)
In your case, instead of being a variable in the select, it will be your column name.
You could also look at "...
2
Create a new filegroup. Duplicate the tables you want to keep into the new filegroup. Copy the data you want to keep to these new tables using bulk insert instructions with optimizations for ordered data. Drop the old tables.
When all tables are processed drop the old filegroup, if possible. If not, move the objects and data back again then drop the new ...
1
I've always liked using UNPIVOT for these types of operations. Assuming you have some sort of ID column on the source table, this also works.
DECLARE @Company TABLE
(
PKID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
, Company1 NVARCHAR(20) NULL
, Company2 NVARCHAR(20) NULL
, Company3 NVARCHAR(20) NULL
)
INSERT INTO @Company
(...
0
You could use a union of three queries like something like this
SELECT 1 AS tableNro, Company1 AS CompanyName
FROM yourTable
UNION ALL
SELECT 2 AS tableNro, Company2
FROM yourTable
UNION ALL
SELECT 3 AS tableNro, Company3
FROM yourTable
To get the serial number you could use row_number like something like this
SELECT ROW_NUMBER() OVER(ORDER BY (x....
2
One way to get the desired result is with UNION ALL of a SELECT query for each company column. Assign the serial number value in order by source column and company name using ROW_NUMBER in the outer SELECT.
INSERT INTO #Company(Company1, Company2, Company3)
VALUES
('ABC', NULL, NULL)
,('Test1', 'Test3', 'Test5')
,('Test2', 'Test4', 'Test6') ...
Top 50 recent answers are included
