All Questions
Tagged with sql-server-2005 sql-server-2008
2,014
questions
1
vote
1
answer
59
views
How to dynamically set the value of a parameter in sp_executesql without modifying the calling software
I'm currently working with a stored procedure that executes an INSERT statement using EXEC sp_executesql. The procedure takes several parameters, including @TRANSACTIONNUM, @CUSTOMERNUM, @VALUE, and @...
0
votes
1
answer
42
views
How to pass symmetric key to query
I want to know how to pass symmetric key to SQL query as parameter.
I'm usually hardcoding it within the query as follows:
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY CERTIFICATE CertiFi;
SELECT * FROM ...
0
votes
1
answer
54
views
MS SQL Sort Alphanumeric values (1, 2, 3, 5, 6, 7, 9, 10, 4a, 8a, 4b, 8b) [duplicate]
In SQL database (2005/2008), I have a field named Ordernumber having the following values: 1, 2, 3, 5, 6, 7, 9, 10, 4a, 8a, 4b, 8b I want a way to sort them to be 1, 2, 3, 4a, 4b, 5, 6, 7, 8a, 8b, 9, ...
-2
votes
1
answer
42
views
By using single CTE with multiple statements in it. can I perform mathematical operations on CTE output to get final output?
Below is my CTE Example It's just a example my actual query is different
;with CTE as
( select 1+1 as A,2+2 as B,3+3 as C
)
select (A+B)-C as Final_Count from CTE
here in above example A,B & C ...
-1
votes
2
answers
55
views
Need to rank the data in SQL Server
From the input data, I need to rank the id. The data is order by time ascending.
Example: id number 421492036 has two record with code '05' and is ordered by time ascending.
The ID 421492036 has same ...
1
vote
1
answer
141
views
Get the history values based on Revision Id
I have a two table table, first table storing all the latest data and second table to capture the changed data. For example if an user change value the FIRST table will update the data and SECOND ...
0
votes
2
answers
137
views
Calculate Running Total Summary based on Different column
I need output like below
StockQty = 10
Balance Qty
[Allocate Stock Qty]
Shortage [Stock]
Running Stock
5.000
5.000
0.000
5.000
10.000
5.000
5.000
0.000
5.000
0.000
5.000
0.000
10.000
0.000
10.000
...
-1
votes
1
answer
67
views
Find Running Total In sql - if 1st column has no value then calculate from 2nd column
declare @tbl as table
(
ItemId int,
SOQty int,
DIQty int ,
IssueQty int,
BalanceQty int,
CreateDate datetime,
StockQty int,
WIPQty int
)
insert into @tbl values
(1,10,...
1
vote
0
answers
76
views
Not able to fetch User who is having Permission as part of CERTIFICATE as Securable in SQL Server
Purpose of the query is to fetch all permissions for Particular user with all the details.
Please find my query below :
SELECT 'permUser1@master' AS dbUserName,
dp.NAME ...
-1
votes
1
answer
82
views
How do I get record with Max(date) and then compare values to get result
I am trying to get record with Max(StartDate) for each MeterNumber and then try to get records where startdate, enddate & RateCode is different for specific AccountNumber.
Sample data script as ...
0
votes
2
answers
1k
views
How do I compare values from same column and different row but same table?
I am trying to get records where startdate is different for specific account Name. I have below sample data.
Sample data script as below
create table Meter (AccountNumer varchar(50), MeterNumber ...
1
vote
2
answers
4k
views
Modify SQL_Variant Column to VARCHAR
I have a table where one of the columns has a sql_variant datatype. I'm trying to modify the column to VarChar(800) but I'm getting an error:
Implicit conversion from data type sql_variant to varchar ...
0
votes
2
answers
72
views
How to get the same result from aggregation
Like:
name
metric
A
1
a
1
SELECT SUM(metric), name
FROM table_name
GROUP BY name
Is there a way that I can get the same name every time I run this? How does SQL Server choose a or A to present?
4
votes
2
answers
12k
views
How to get timezone offset from the timezone name
I have a timezone name like America/Vancouver saved in a SQL Server database.
I want to get the UTC offset from the timezone name in SQL like America/Vancouver has -08:00 offset. So how can I write a ...
0
votes
1
answer
1k
views
How to get table's metadata from linked server (SQL Server)
How to get tables metadata from linked server/another database? The query below works ideally when running on the main server, but returns null if calls remote server or even another database.
select ...