Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
0
votes
0answers
14 views
Compare Oracle Table with Sql Server Table and Update/Insert
Below is the current code wish used to update/insert records from Oracle view to Sql server table using Dapper. there is not field to check last record updated date in the oracle view so I have added ...
0
votes
2answers
77 views
My simple CMS system - security and code obscurity?
I'm working on a simple CMS with the intent of making it as secure as possible (a personal challenge) and the code as clean as possible. I think I've a long way to go so I would appreciate any input, ...
3
votes
0answers
67 views
Optimising a query
Consider the query given below:
SELECT * FROM ((SELECT c.cust_id ,
c.username ,
REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group,
...
0
votes
0answers
47 views
Binding and iterating a database table to a List<T> in C#
I would like to iterate through the records of a database table using a C# List of my custom class. Is this the most performant way to handle this? (Simplified for readability)
static void ...
2
votes
1answer
38 views
Extract data from one column of SQL Server table
I have an SQL Server table from which I want to extract data from one column (DD) and return as multiple columns.
The data is in NVARCHAR and looks like:
...
-2
votes
1answer
33 views
Oracle to MS SQL Server 2008 Code Conversion Problems [closed]
CREATE OR REPLACE FUNCTION CHI_X2 (a1 in number, b1 in number, a2 in number, b2 in number)
RETURN NUMBER IS
DECLARE @tr1 INT;
DECLARE @tr2 INT;
DECLARE @tc1 INT;
...
0
votes
0answers
43 views
Open/Close Connection in a Loop [closed]
I want to enroll certain students into a certain class. I obviously have tables for 'Students', 'Classes', and the 'many to many' table 'ClassesStudents' which has the candidate key (ClassID and ...
1
vote
1answer
101 views
how to write complex stored procedure very simple way
i have one stored procedure like this:
declare @compl integer = null, @transid integer = null, @complid integer = null
select @transid = t.transactID
from Transaction_tbl t
where ...
1
vote
4answers
49 views
How to refactor the following sql statement?
Declare @IsExistRoleAR As bit
Declare @IsExistRoleEN As bit
Declare @IsExist AS bit
set @IsExistRoleAR=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
FROM ...
-2
votes
1answer
54 views
advanced asp.net improve performance [closed]
asked for help on my application , that's run on windows server 2003 and iis 6.0 ,this applications gross now , users now about 60,000 per day. and that what i did to make it faster
a.SQL server 2005 ...
1
vote
1answer
25 views
Archiving data while running update statement
I'm using sql server and I'm trying to update certain columns in a table but before it updates the data, I want to insert the data into another table.
This is my code
declare @table1 table (Id int, ...
2
votes
1answer
57 views
SQL Server 'Execute As'/Revert pattern in a 'Try/Catch' Block
I wish to ensure I am using the "best" pattern when using an Execute As/Revert from within a Try/Catch block on SQL Server 2012. The below code "seems" to behave correctly... Am I missing anything or ...
0
votes
0answers
61 views
Looking to optimize SQL Server merge statement
I have a merge statement that takes around 10 minutes to process 5 million or more records.
The merge statement is part of a stored procedure that takes my newly bulk loaded staging table then runs ...
2
votes
1answer
90 views
How can I improve the following stored procedure?
I have created the following stored procedure which duplicates a record in a table and also all its related records in other tables however since I am a newbie to SQL I would appreciate it if someone ...
3
votes
2answers
121 views
Simple DBLayer class review
I have created a simple DBLayer class. But I am not sure whether this class have any bug/issue using in production.
public class DBLayer
{
private static string connectionString = ...
1
vote
1answer
65 views
Request review of sql validation trigger
I have the following SQL structure (simplified for brevity):
TABLE [Posts]
[Id] INT NOT NULL IDENTITY PRIMARY KEY,
[ParentId] INT NULL,
[Type] INT NOT NULL,
FOREIGN ...
0
votes
1answer
83 views
Query Performance too Slow
Im having performance issues with this query. If I remove the status column it runs very fast but adding the subquery in the column section delays way too much the query 1.02 min. How can I modify ...
1
vote
1answer
1k views
SQL stored procedure that returns a boolean value?
CREATE PROCEDURE dbo.foo
AS
BEGIN
DECLARE @true BIT, @false BIT;
SET @true = 1;
SET @false = 0;
IF (some condition)
Select @true;
ELSE
Select @false;
END
SQL is not the language that ...
-1
votes
1answer
99 views
Using xp_cmdshell
Am I doing it fine?? Its only for setup not executed repeatedly
CREATE TABLE #temp
(
id INT IDENTITY(1, 1),
name_file VARCHAR(500),
depth_tree ...
0
votes
1answer
68 views
Daily, Weekly, Monthly Individual Tech time in task Repoert
I'm trying to create daily, monthly and weekly SQL Query report to our services time we spent int task and total billing time just want to see if I'm on right track
GO
--Daily
SELECT ...
2
votes
1answer
96 views
SQL PreparedStatement; Am I doing it right?
I am building a web app with a single (not pooled) full time (jdbc) connection between static classes and the database. This is expected to be a low traffic site and the static methods are ...
1
vote
1answer
93 views
Sql query to obtain totals and subtotals
I'm interested in knowing if there's a better/cleaner/more efficient way, to obtain totals and subtotals within a query, than my solution below.
The query works fine but I'm just intrigued to know if ...
2
votes
1answer
594 views
Custom Paging in ASP.Net Web Application
I have following code for doing custom paging from an asp.net web application.
Points of interest
It uses Link Buttons as suggested in
...
2
votes
2answers
179 views
Subquery v/s inner join in sql server
I have following queries
First one using inner join
SELECT item_ID,item_Code,item_Name
FROM [Pharmacy].[tblitemHdr] I INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = ...
0
votes
2answers
117 views
Backup a database over an SQL connection
Developing some industrial WinForms application for some industrial setting, I wanted to provide users of our software with a convenient way to back up the database the software uses (to send it to ...
1
vote
1answer
80 views
Throwing a custom error in a stored procedure
I'm wanting to throw an error when reference keys are missing, however instead of failing through a integrity check, I want to list out the missing keys. I've created the below which works. However ...
5
votes
2answers
179 views
SQL Query Tuning
I have a mammoth SQL statement, however it's taking a long time to load (27 secs on the server). I think the issue lies with the IN statements towards the bottom (the IN statement is repeated too, can ...
0
votes
3answers
318 views
Using a Dedicated Class & Constructor to initialize Stored Procedure for SQL Transaction
as i thought it will be sutible as a Quetion for SO...
i started to describe the issue of my Problem, though as i completed Editing the post , i could see that it will better fit here, In CR.
so to ...
0
votes
1answer
145 views
Is this a good way to limit the occurrence of OutOfMemoryException?
I have a class that uses the SqlBulkCopy to bulk insert data into a SQL Server database. My original implementation just passed the m_buffer (which is a reference to a class that implements ...
0
votes
1answer
88 views
Review Insert/Update stored procedure
I am not a DBA nor do I play one on TV.
My application is retrieving information from Facebook and storing it into our own database. I want to retrieve the Location Post information of Id and ...
1
vote
1answer
235 views
How to dump UCCX stored procedure results via c# to SQL Server
I am writing a simple-ish c# CLI app to improve our telephony reporting system.
At present, I have a nightly DTS package which dumps a couple of tables from the Cisco telephony box into a database ...
1
vote
2answers
111 views
Is My Data Access Leaking Connections?
Folks
I have an N-Tiered ASP.Net Web Forms application which uses Enterprise Library 5.0 for data persistence. Recently I have noticed my application has been spitting out the following error
...
3
votes
1answer
124 views
Is it necessary to replace this cursor in SQL Server 2005?
The Problem
I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a ...
6
votes
1answer
86 views
Auditing a complex mix of reasonably small tables
I plan to use the following approach to audit the tables that represent user-editable configuration of an automated system (over the course of system's life these will be inevitably extended in their ...
3
votes
1answer
102 views
SQL view, better way?
I there a better way to write this query? I feel sure it can be done in one statement with joins, but I couldn't get it, and I'm afraid I've made it too slow. What suggestions do you have? I'm ...
3
votes
4answers
2k views
SQL Server Split function optimized
I know there are some answers about a user function to split chars and I have one already running (found one years ago in the net and modified it to my own needs).
Since I use this function very ...
2
votes
3answers
1k views
Search using stored procedure
I want to search a DB table and display the records in a gridview below is my stored proc
create procedure search
@Firstname varchar (50),
@Lastname varchar (50),
@Gender ...
1
vote
1answer
36 views
SQL iteration-Insertion plus renaming
Base Info
Two tables: tData, tData2
Exactly the same columns
About 200,000 records
SQL Server 2008 R2
Logic
At first sight we need to insert tData rows into tData2. What else?
We need a ...
2
votes
2answers
2k views
PHP Login/User Creation Validation Function
I'm VERY new to PHP and have begun writing a basic login for a website that uses data from a Microsoft SQL server to display information on the web. Below are 3 snippets of code (two functions from ...
0
votes
1answer
884 views
Data Structure for Categories and SubCategories
I have this relationship:
Many Objects to One Category
One Category to Many SubCategories
I am looking for the best way to store this in my Relational Database? Here were some of the ways I was ...
3
votes
2answers
559 views
Which SQL statement is faster
My co-worker claims that using a parameterized Like statement is equivalent to dynamic sql and won't have its execution plan cached for reuse. He says that using sp_executesql will allow the execution ...
3
votes
1answer
132 views
Getting rows from several tables where one table doesn't include the rows from the next tables
I have some entity that is spread across three tables.
I have to get in a single result the following values:
All the values that are in the first table but not on the second nor the third.
All ...
0
votes
1answer
134 views
How to refactor this cursor used in a SQL query
So I got this query from another analyst here and I'm a little stumped as to why a cursor was used instead of just joins. Could I please have some help trying to refactor it? I am using MSSQL 2008.
...
5
votes
1answer
1k views
Conditional Create: must be the only statement in the batch
I only want to create this SQL function if the dependent Assembly exists.
I can do it using dynamic SQL, but it seems messy and I lose syntax checking (in
management studio). This function's ...
0
votes
2answers
203 views
Critique my Database design/help me simplify my C# code please
I wish to create a database for a webpage where users are able to add their own events to a timetable. Users will be able to decide if the events should be recurring (weekly) or not. Users should be ...
2
votes
1answer
110 views
Refactor my simple SQL Statement
I have the following SQL statement that I think could be improved in areas (I believe there may be a way to use where over having, but I'm not sure how and I'm sure there's a way to reference the last ...
3
votes
1answer
118 views
output parameter and exists condition
I want to return title of a record if exists or the word 'invalid' if not.
@ID int ,
@Title nvarchar(50) output
...
if exists(select * from MyTable where ID=@ID)
select @Title = ...
1
vote
1answer
95 views
SQL table normalization upto what extent [closed]
I have an asp.net form which contains like 5 tabs and each tab has at least 15 fields in it. They all belong to one record so for the sake of simplicity lets call the PK as RecordId. Now i have 2 ...
2
votes
1answer
115 views
Slow Oracle to MSSQL migration
I wrote a method that gathers data from an Oracle server, formats and encrypts the data then inserts it into a MS SQL server. The method moves about 60000 records and takes a bit long and is a little ...
2
votes
1answer
75 views
Critique my SQL
Table Structure
ApprovalOrder int
EntityCode varchar
CostCentre varchar
DelegationCode varchar
ProjectCode varchar
RoleGroup varchar
Position varchar
...
