Tagged Questions
3
votes
1answer
44 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
4
votes
2answers
68 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
-1
votes
1answer
61 views
Validate each parent intermediary is also a parent to itself
I have a table for which I want to validate where each parent intermediary is also a parent to itself. How to write query to validate this?
COLUMN DATATYPE DESCRIPTION
...
0
votes
3answers
80 views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
3
votes
0answers
38 views
SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows
So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part:
ALTER PROCEDURE ...
1
vote
1answer
47 views
How can we get table name from inside trigger?
I need to pass the table name from trigger to procedure.
ALTER TRIGGER [dbo].[Trg_ProjectCreation]
ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- procedure call
...
1
vote
1answer
60 views
Restoring SQL Server 2012 database onto 2008
I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: ...
7
votes
3answers
336 views
Worse performance on a new server
We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
2
votes
2answers
74 views
Using T-SQL to dynamically create Extended Properties
I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values.
It would be very laborious to add these ...
0
votes
0answers
48 views
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' [closed]
I have 4 SQL Server 2008 instances from which I need to pull data using OPENROWSET. I am adding this data to a table on server 1. Server 1 and 2 are in a cluster, same as 3 and 4. I am remoted into ...
0
votes
1answer
43 views
Generic SQL Job Scheduler for multiple RDBMS's?
I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex ...
0
votes
3answers
93 views
Is transaction log lost after backup on SQL Server?
I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
3
votes
1answer
98 views
How does order of index affect performance in SQL Server?
I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
0
votes
1answer
65 views
Is there a way to truncate table that has foreign a key?
Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?
1
vote
1answer
111 views
SQL Server stored procedure working when run manually, not running from SQL Server Agent
I have a procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error.
line 9, character 9, unexpected end of input
The code ...
-3
votes
2answers
107 views
read multiple values from same column [closed]
how to read multiple values in same column and campare it with the same table id and other table to get its record..
i have a table with column name
pid ,authors ,citing papers
...
2
votes
0answers
103 views
Cannot connect to SQL Server 2008 after updating the service pack
After updating, whenever I start the service of the server in configuration manager, it always stops then.
And I found the error in the log
2013-04-13 19:08:19.63 spid7s Error: 15174, ...
6
votes
1answer
175 views
How do I optimize large table so queries that target only recent data perform optimaly?
So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved.
I am open to ...
1
vote
1answer
60 views
dead lock when updating
Update Operation.TrTable
Set RecordId = RecordTABLE.newRecordId
From Operation.TrTable tr
Inner Join (
SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
From ...
1
vote
0answers
68 views
Getting LCK_M_S On read committed Snapshot Isolation
I'm getting LCK_M_S on read_committed_snapshot isolation. From my understanding, this isn't supposed to happen since my selects are not placing shared locks anymore.
From my logs, I can see that my ...
0
votes
1answer
46 views
Why is CDC capture failing due to a missing dbo principal
I am having a problem running the CDC change capture job against some of my test databases.
The error message I'm getting is the following:
Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
0
votes
2answers
120 views
Get most recent 100 records from the database
I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever ...
-1
votes
1answer
40 views
what kind of owner to defined when creating Sql server DB for web site? [closed]
what kind of owner to defined when creating Sql server DB for web site ?
I am trying to create db and i need to select owner.
my db will serve aspnet web site and I wonder witch owner will be safe to ...
13
votes
6answers
524 views
Why should an application not use the sa account
My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.
I have an absolute belief that ...
2
votes
1answer
130 views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
3
votes
1answer
129 views
Automatic database file creation based on database file size in SQL Server 2008 R2
Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. ...
1
vote
1answer
56 views
Would I lose data if I change a column from varbinary(8000) to varbinary(max)?
Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?
6
votes
1answer
180 views
Landed as BI, but databases are a big WTF, what to do?
Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
1
vote
1answer
105 views
Error on creating database in SSMS
I am using SQL Server Management Studio 2008. I am connect to localhost and I want to create a database. When I click on New Database, type the name and click ok, I get the message:
Index was ...
1
vote
1answer
86 views
SQL Server 2008 search joining query
I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.
for example,
M_MessageMessageId=========
...
1
vote
6answers
67 views
Performance about this simplified where-condition query
I have a query in SQL Server 2008 like this:
SELECT
a.type,
a.name,
a.startDate,
a.endDate,
b.key AS key,
b.is_locked AS is_locked,
...
0
votes
1answer
109 views
SQL Server 2008 SP2 randomly slow down query
I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query.
It is taking 4 sec to 4 min in execution. Please suggest the possibilities.
There is no scope of query optimization. ...
1
vote
1answer
129 views
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server
I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is ...
1
vote
1answer
609 views
IF NOT NULL then UPDATE else keep the value of the field
I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' THEN ...
3
votes
1answer
53 views
Adding column from SSMS wizard in SQL Server
Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process:
Create a New ...
4
votes
1answer
50 views
Under what circumstances can a division expression reduce the number of rows in a query's result?
I'm experiencing a problem with a query that I've written.
Given a table with the schema:
create table somevals(division varchar(100),
subdivision varchar(100),
...
3
votes
2answers
69 views
SQL Server 2005 Database move and rationalisation - Recommended maintenance processes
We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.
I inherited this database and it came to ...
0
votes
1answer
129 views
SQL update set column = @ parameter
doeas anyone knows how to update a table where column = @parameter WHERE SELECT
Basically I have a table with the following values. Table name is UserAttribute
ID|Name |Properties|
...
1
vote
1answer
78 views
Query to determine a count by rolling interval
I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of ...
1
vote
2answers
98 views
Does this query make sense?
I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2.
One category of queries that was broken in transit is:
SELECT person_id
FROM leaveperiods
WHERE group_id=146
GROUP BY ...
1
vote
2answers
123 views
How to find DELETE and UPDATE statements that have been executed on a database?
I am trying to find out what delete and update statements have been executed on a certain database in a certain period of time.
I came across this following query, but it generates an error message:
...
0
votes
1answer
68 views
What is the maximum number of rows in a clustered index on datetime column?
I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
2
votes
2answers
320 views
Check if row exists
I need to write a query that will insert a row only once, even if the query is run multiple times. Being new to SQL (well new-ish), I did an if not exists(...) but a friend said he preferred deleting ...
0
votes
3answers
125 views
Select MaxDate where hh:mm:ss makes the difference
How to select the maximum date from a table where hh:mm:ss is selected?
My query is the following, and returns the latest date, but if there are different times recorded, the date will be returned ...
4
votes
1answer
140 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
2answers
67 views
Need a way to query a table, and JOIN it with the TOP 1 related record from an other table
As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.
As stated, this query does return me 95% of the data I need --
SELECT ...
0
votes
3answers
100 views
Fixing a query that returns failed job information
I' using a SQL Server 2008 Sp3 environment.
I have a query that returns information about failed jobs from the last 10 minutes.
If a job has two schedulers (for example - a job that runs every 10 ...
1
vote
0answers
374 views
The Cluster service failed to bring clustered service or application 'SQL Server (MSSQLSERVER)' completely online or offline
SQL Server service restarted/stopped, which cause the production database down n find this errors in System log.
ERROR5:The Cluster service failed to bring clustered service or
application 'SQL ...
2
votes
1answer
197 views
Select the top 5 records and the specific column from each table in the same database
I have 50+ tables in a database.
I don't want to use the following SQL on each table in a database.
select top 10 * from {table_name}
So, is it possible to display the first 10 records PLUS only ...
0
votes
3answers
211 views
How to select the first 10 records for each table in the same database using sql
I have 50+ tables in a database.
I don't want to use the following SQL on each table in a database.
select * from {table_name}
So, is it possible to display the first 10 records for each table ...
