Transact-SQL (t-sql) is a dialect of SQL used by Micrsoft's [SQL Server](http://www.microsoft.com/sqlserver/en/us/default.aspx) and SAP's [Sybase](http://www.sybase.com/).
4
votes
3answers
194 views
Copy 8 Million Records
I have a table in a local MS SQL Server database that has 72 columns and over 8 million records.
Most of the columns are floats except for the primary key which is an INT Identity column.
The table ...
2
votes
1answer
3k views
Table-Valued Parameter as Output parameter for stored procedure
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
id int ...
5
votes
1answer
381 views
Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?
When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...
2
votes
1answer
157 views
Execute Queries Against CMS within SSRS Reports?
Is it possible to execute queries against a Central Management Server (CMS) using SSRS?
For example, I know of no way to tell a stored procedure that it is inherently supposed to call the CMS, so it ...
2
votes
2answers
474 views
How to check when statistics was last executed?
We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
3
votes
3answers
780 views
What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?
Many times I need to write something like the following when dealing with SQL Server.
create table #table_name
(
column1 int,
column2 varchar(200)
...
)
insert into #table_name
execute ...
1
vote
1answer
83 views
SQL Server function invocation syntax question
The following excerpt is extract from this page.
CURRENT_USER (Transact-SQL)
Returns the name of the current user. This function is equivalent to
USER_NAME().
When I execute the two ...
2
votes
1answer
5k views
Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function
If this is not the right forum please let me know and move it for me.
I've just got a bunch of inserts like this toward the end of my scalar function:
INSERT INTO [Raptor].[dbo].[UserRole]
...
2
votes
1answer
443 views
Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2
I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. ...
5
votes
2answers
624 views
The order of INNER JOIN's
I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.
I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
2
votes
1answer
1k views
CREATE DATABASE Permission denied in database 'master' error
I'm working with a tool that as one of the automated steps will attach databases from my file system to my local SQL Express 2008 instance. The tool requires a login in the sysadmin server role, for ...
0
votes
1answer
123 views
Help me to better design my query so I can gain more performance
I need to find so called first entry in database for items regarding their partners.
I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed ...
3
votes
3answers
347 views
t-sql - combinatorics
I'm trying to find all possible character combinations in a variable length string.
For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--'
I think that essentially I need ...
1
vote
2answers
267 views
Can I send a string over TCP using T-SQL?
I've set up some alerts, based on this article
Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
3
votes
2answers
3k views
CASE ORDER BY with multiple columns, and different sort options
I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.
@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column ...
-4
votes
1answer
3k views
T-SQL to list all the user mappings with database roles/permissions for a User
I am looking for a t-sql script which can list the databases and and the respective roles/privileges mapped for a particular user. Using SQL Server 2008 R2.
Regards
Mohammed
1
vote
0answers
194 views
Solve sudoku table by tsql [closed]
I am real fun of SUDOKU games, and I also have passion for solving problems writing TSQL queries. Just for fun ill love to try make code which is going to solve Sudoku puzzles. Basic idea is make ...
3
votes
2answers
306 views
Tool to create a TSQL script from a SQL Server 2008 trace file
Are there any tools available to create an executable tsql script from a SQL Server trace file?
Is there an easy manual way?
1
vote
2answers
537 views
Solving high page load time using SQL Profiler
I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what ...
0
votes
1answer
250 views
Small Squares or Blocks after string in table field
My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query ...
0
votes
1answer
157 views
How to find “outer join in the where” syntax in SQL 2005 using cmptlevel 80?
Well last Christmas I was very happy, because we ceased support for SQL Server 2000.
I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored ...
3
votes
2answers
176 views
switch TOP clause between percentage and rows by parameter
For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.
I have two ideas
Calling two different sub sproc based on passed param.
if ...
2
votes
2answers
853 views
Transact SQL using WITH in CREATE VIEW
I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.
I want smth like this
WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl
And ...
2
votes
2answers
669 views
AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2
Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2?
If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date?
...
2
votes
2answers
673 views
Why is query using Clustered Index when it shouldn't?
Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
0
votes
2answers
222 views
How do I record SQL transactions with SQL Server Express
With a test migration from 2000 to 2008R2 Express, one function from the client application fails silently. I'd like to find out what SQL is running and why it fails.
I think I would have attacked ...
4
votes
1answer
524 views
How to get text for default language
I have so tables:
and so data at Language table:
and so data at Text table:
I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
4
votes
1answer
380 views
T-sql: Select most specific match from table
Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem.
Here's the scenario:
Users can set up service instructions to be attached to a work order ...
0
votes
1answer
73 views
What would cause the speed difference between servers?
I have this query on my dev server:
select
snStudyNumber as ProjectNumber
from CDB.cdb.Quotes q
inner join CDB.cdb.LineItems l
on q.PK_quID=l.FK_quID
inner join ...
2
votes
2answers
163 views
What is an industry standard method for verifying openquery connection is open prior to running queries?
I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well.
I'm seeking an ...
1
vote
1answer
246 views
What is SQL/PSM and how does it differ from other versions of SQL?
I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL ...
0
votes
1answer
296 views
How to start and then stop one cyclic process
I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
1
vote
1answer
536 views
Conversion of varchar data to datetime failing
We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering
As part of this effort, we are taking data from the base Entity table and ...
3
votes
4answers
403 views
Cannot reproduce client SQL 2008R2 error: subqueries are not allowed
EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?"
We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
5
votes
3answers
449 views
How do I row count into groups?
I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
4
votes
1answer
702 views
SELECT INTO creates a new table?
I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
8
votes
1answer
249 views
Generating large strings for test data
I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
13
votes
3answers
2k views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
1
vote
2answers
824 views
Getting current date as part of an Stored Procedure
I have the following SP:
SELECT
moncallAdd.FirstListing,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
...
3
votes
1answer
302 views
Does T-SQL MOVE actually move the database, or just copy?
I'm a bit confused over the T-SQL command MOVE. I have a statement saying:
MOVE
N'Prod_Database_Data'
TO
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_Database.mdf'
...
7
votes
3answers
234 views
When are procedural queries absolutely necessary?
I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
5
votes
1answer
2k views
Find all ancestor nodes of a HierarchyId using a single SQL statement?
I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
2
votes
5answers
157 views
How can I store function names to be executed on a subset of rows?
I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations ...
0
votes
2answers
109 views
Determine free space on “nonfixed” drive
I am writing a stored procedure that needs to send an email when a dvd rw drive is almost full. I found
master.sys.xp_fixeddrives
but it only shows free disk space for fixed hard drives. I have not ...
2
votes
2answers
476 views
Filters Joins or Where clauses TSQL
Where should i put the filters on a tsql query
SELECT a.colum FROM A
JOIN B ON A.ID = B.ID AND B.STATUS = 1
OR
SELECT a.colum FROM A
JOIN B ON A.ID = B.ID
WHERE B.STATUS = 1
Im ...
2
votes
2answers
237 views
Search on part of a column in a SQL Server cross-database view
I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
5
votes
2answers
692 views
How to change SQL sever configuration manager settings using TSQL?
SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc...
Is it possible to make these changes that is done in SQL server ...
5
votes
1answer
368 views
Why can the target of the OUTPUT INTO clause not be a view?
Here is my statement:
DELETE FROM table1
OUTPUT deleted.col1, deleted.col2
INTO view1;
It gives me this error:
The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
3
votes
2answers
626 views
MS SQL : Use Computed value to Compute other values
I'm having problem with generating results which are computed from data that exists in database. This is the case
I have Coupons that can be amount or percent, if percent is 0 then amount is used
...
5
votes
3answers
1k views
Which problems arise, declaring the size of all varchar parameters as max in stored proc?
Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.
Increasing just the size of a column in a table is a rather simple task.
...
