0
голосов
1ответов
1549 просмотров

SQL Server equivalent of Javascript UnEscape

I have a table in SQL Server 2008 with a column having the string stored in JavaScript Escape format. When queried from application, I am doing the JavaScript UnEscape of the returned string, so no issues. How do i UnEscape the string when I directly query the database? Is there any equivalent f...

0
голосов
2ответов
3596 просмотров

Application Role connect to SQL Server with "Login Failed" error

I have a program which would use the Application Role to write data to a SQL Server 2005. using (SqlConnection sqlCon = new SqlConnection(connectionString)) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlCon; sqlCommand.CommandType = CommandType.Text; sqlComma...

0
голосов
1ответов
52 просмотров

Can SQL Server do a type conversion at run-time

We have a utility written in C which reads columns extracted from a database using a stored procedure and outputs a csv file. Simple huh. However when reading a smallint column, it crashes out and not being the greatest C programmer on the planet, I can't nail it. As a workaround can you change t...

25
голосов
4ответов
41974 просмотров

How can I use if statement after a CTE (SQL Server 2005)

Last night I was writing a simple T-SQL program something like this DECLARE @ROLEID AS INT SELECT @ROLEID = [ROLE ID] FROM TBLROLE ;WITH CTE AS ( SELECT * FROM SOMETABLE ) IF (@ROLEID = 1) BEGIN //SOMECODE END ELSE IF(@ROLEID = 2) BEGIN //SOMECODE END ELSE BEGIN //S...

1
голосов
2ответов
792 просмотров

SSAS - Moving Annual Total

Environment: SQL-Server-2005, SSAS 2005 I am using the following formula to create a Moving Annual Total SUM ({[Time].CurrentMember.Lag(3):[Time].CurrentMember}, [Measures].[TRx Quantity]) The same column [Measures].[Trx Quantity] is used in other calculations and it works fine. This one, how...

28
голосов
8ответов
28951 просмотров

How to rebuild view in SQL Server 2008

There is a view in my DB that someone defined with a * from one table. I just added a new column to that table and I want the view to reflect the new column. Besides re-executing the view creation script, is there another way to rebuild the view? I am looking for something similar to how sp_re...

3
голосов
3ответов
3219 просмотров

Forcing a SQL Remote Query to filter remotely instead of locally

I have a MS SQL Query that is pulling data via from a remote server. The data that I'm pulling down needs to be filtered by a date that is determined at run time.. When I run the query like this: SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > '1/1/2009' then the filter is...

1
голосов
3ответов
1773 просмотров

TSQL Group By including all columns

I have a TSQL Query that does something like this: SELECT SUM(s.Amount) as TotalSales, p.ProductName FROM SALES s INNER JOIN Product p ON s.ProductID = p.ID GROUP BY p.ProductName The resulting output is TotalSales Product ----------------------- 123.45 Apples 234.56 Oranges 345.6...

1
голосов
2ответов
1021 просмотров

SQL datetime value

I have data formatted "2009-07-17T00:00:00-05:00" in varchar variable. How can I convert this data to datetime field in MS SQL server using query or TSQL?

1
голосов
2ответов
4087 просмотров

Calculating Consecutive Absences in SQL

I need to calculate all Employees that have X number of consecutive absences within a date range in SQL. We have an Absences Table with 1 record for each day an employee is absent and a Calendar Table with the work days for the year. tblAbsences EmployeeID int AbsenceDate datetime tblCalenda...

0
голосов
1ответов
143 просмотров

Dynamically created distributed queries in SQL Server 2008 on Windows 7

I am just doing some stat collection on multiple servers, and as a test I'm working with my machine (Machine A) and another machine (Machine B) on the local network. My Machine (A) is collecting all the information in the staging table from the other Machine (B). I have a sp that runs and dynami...

1
голосов
2ответов
728 просмотров

SQl Server 2008 FILESTREAM, Indexing Services, and FTS

I need to implement a service to search PDFs. Initially I started using SQL Server 2008 FTS, but soon realized that my PDFs would have to be stored in the DB itself. I was then pointed to Indexing Services as well as to the SQL 2008 FILESTREAM data type so that I can store PDFs in the file syst...

0
голосов
5ответов
99 просмотров

Is there a perfomance gain on the language chosen and DB?

We have our current system using PHP and Microsoft SQL Server. I am trying to work out if there will be a performance gain in using PHP and MySQL? It will require some effort to change to this platform so I am hoping to find out as much as I can. Are there any questions I should be asking mysel...

4
голосов
5ответов
950 просмотров

Why SQL Server seems to be so attached to transaction logs

OK, here is goes. I’m not a database guru or admin. In fact, besides some occasional index / query tuning, I don’t poke around too often in databases. One of the things which often eludes me is the SQL Server transaction log. I know what it’s for, what it contains and how it works (at least conce...

4
голосов
1ответов
2967 просмотров

How do I figure out if a column size is in bytes or characters from the column metadata?

I am working on some software that has to create dummy entries in various databases (Oracle, DB2, SQLServer) temporarily. Every column in the the row is filled with random data. The code uses java.sql.DataBaseMetaData class in java to get the COLUMN_SIZE attribute to figure out how large of a r...

23
голосов
5ответов
24471 просмотров

How do I search for a ', ' and replace with ', '<cr><lf> in SQL Server Management Studio's editor</lf></cr>

I have a long query that I'd like to replace the , with a carriage return to put all the fields on their own line but I can't figure out how to do it in the editor. It wont be fool proof, but better than doing it by hand.

0
голосов
1ответов
567 просмотров

Zend_Db_Adapter_Sqlsrv insert date sql server 2008

I'm connecting to SQL Server using the Zend_Db_Adapter_Sqlsrv via SQL Server driver for PHP Wondering how I can insert a date which is read in from a web form in format dd/mm/yyyy into a datetime column Thanks in advance

0
голосов
2ответов
504 просмотров

Can parameterized queries have output parameters?

In SQL Server is there a way to have an output parameter return the total number of records (for paging) in a parameterized query?

1
голосов
1ответов
80 просмотров

Batched output in SQL Server

I have a tables like the following CREATE TABLE Company ( Id INT ) CREATE TABLE CompanyNumbers ( CompanyId INT, NumberText VARCHAR (255) ) What I want as an output is this in pseudo code Give me all the numbers for company A as a single comma separated string, if the string contains more tha...

7
голосов
6ответов
20058 просмотров

What tools are available to test SQL statement performance?

In the never-ending search for performance (and my own bludgeoning experience), I've learnt a few things that could drag down the performance of a SQL statement. Obsessive Compulsive Subqueries Disorder Doing crazy type conversions (and nest those into oblivion) Group By on aggregate functions o...

3
голосов
4ответов
22769 просмотров

Store value in a variable after using select statement

How to store the value of the PolicyID returned from database in an integer variable in C#? I am using SQL server 2005. System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=JAGMIT-PC\SQLEXPRESS;Init...

4
голосов
1ответов
2506 просмотров

SqlDependency in ASP.NET

I created the following encapsulation over the SQL Dependency object: public class DependencyTracker { private SqlDependency _SQLDependency = null; public string ConnectionString { get; private set; } public string CommandNotifier { get; private set;...

1
голосов
2ответов
141 просмотров

Query I need to be sped up

I have this query in SQL Server 2005: SELECT J.JobID, dbo.tblCustomers.Name AS CustomerName, J.CustomerJobNumber, J.JobName, (CASE WHEN [tblCustomers].[CoreCust] = 0 THEN 'AUXILIARY' ELSE 'CORE' END) AS Department, J.JobStatusID, dbo.tblJobTypes.Jo...

0
голосов
5ответов
894 просмотров

SQL CORRELATION

I have this query: SELECT COUNT (DISTINCT CUSTOMER_ACCOUNT.ID) AS NUMBER_OF_ACCOUNTS FROM CUSTOMER_ACCOUNT INNER JOIN ACCOUNT ON CUSTOMER_ACCOUNT.ACCOUNT_ID=ACCOUNT.ID INNER JOIN CUSTOMER_ACCOUNT ON LOAD_ACCOUNT_DETAIL_0.ID = CUSTOMER_ACCOUNT.ID WHERE Convert(datetime, convert(char(10), [CU...

7
голосов
1ответов
1710 просмотров

Calculating Moving Range in SQL Server (without arrays)

I have a requirement to calculate the Moving Range of a load of data (at least I think this is what it is called) in SQL Server. This would be easy if I could use arrays, but I understand this is not possible for MS SQL, so wonder if anyone had a suggestion. To give you an idea of what I need: ...

0
голосов
7ответов
201 просмотров

Getting unique rows/values in SQL

Getting unique rows/values in SQL. I have same some data like this valueA ValueB ValueC test "Value1" "Something" test "Value1" "Something else" test2 "Value1" "Something else" So then I want to get the first two rows since valueC is different and then I also want row 3. but...

0
голосов
1ответов
2024 просмотров

VB.NET restore Backup file created on one server to another server

i am using SQL server express 2005 as an backend. I created a backup file programmatically.If i use same server , then it restore the data successfuly. however if we try to restore on different server, then it fails. and throw following message "The Backup set Holds a backup of a database other ...

1
голосов
4ответов
288 просмотров

SQL Query Duplicate Removal Help

I need to remove semi duplicate records from the following table ID PID SCORE 1 1 50 2 33 20 3 1 90 4 5 55 5 7 11 6 22 34 For any duplicate PID's that exist I want to remove the lowest scoring record. In the ...

2
голосов
3ответов
4733 просмотров

INSERT conflicted with foreign key - SQL Server bug?

UPDATE: the issue does not happen when run against SQL Server 2008. So this is something strange (or wrong) with SQL Server 2000. I try to do a simple insert on SQL Server 2000: INSERT INTO UserAddresses (UserId, AddressId) SELECT UserId, Id FROM Addresses and I get this: INSERT state...

1
голосов
1ответов
1013 просмотров

SQL Server Connection String problem?

Using VB6, SQL Server 2000 I want to connect to others system database. Code: ConnectionString = "Provider=SQLOLEDB.1;" &amp; _ "Persist Security Info=False; " &amp; _ "User ID=" &amp; SQL_Username &amp; "; Password = " &amp; SQL...