天道酬勤,学无止境

sql-server-2008

How to send a big array to a stored procedure

I need to send a several list (about 20000 id's) to a stored procedure, like this: 1391924, 6546510, 7419635, 6599910, 6546888, 1116510, 6546720, ... I have this data on a List<int> How can I do to send this list to an stored procedure? And then I need to insert the id's list on a temporary table

2022-01-19 02:36:20    分类:问答    c#   sql-server   sql-server-2008

I have lost SQL Server 2008 database, I need to the database structure back from LINQ schema

My SQL Server 2008 server database for one of my new projects has deleted accidentally. What I'm left with is LINQ to SQL schema file (DataContext schema and classes) and with hope to get my database structure back from this. Is there a way?

2022-01-19 01:33:21    分类:问答    c#   sql-server   visual-studio-2008   sql-server-2008

Getting Error: "Error converting data type nvarchar to numeric" in SQL

I am passing 4 Parameters to an asp.net Webservice. This is my Code so far: Webmethod: [ScriptMethod(ResponseFormat = ResponseFormat.Json)] [WebMethod] public List<RaumHelper.RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID) { return RaumHelper.Raum(RAUMKLASSE_ID, STADT_ID, GEBAEUDE_ID, REGION_ID); } Helperclass: public class RaumHelper { public class RAUM { public string RaumName { get; set; } public string RaumID { get; set; } } internal static List<RAUM> Raum( string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID) { List<RAUM>

2022-01-19 01:30:19    分类:问答    c#   sql   web-services   sql-server-2008

SQL Server logical grouping most recent time

I am looking for the best approach to this issue and have not had any luck so far. I am using Microsoft SQL Server 2008. So here is sample data of a user: ====================== Name Status Date ====================== BOB Active 2011-03-07 BOB Active 2011-03-11 BOB Disabled 2011-03-15 BOB Disabled 2011-03-21 BOB Active 2011-03-23 BOB Active 2011-03-28 ====================== I want to have this grouped so that it will show when the user actually changed the status and not the next time they were checked if the result stayed the same so what the query returns would be as follows: ===============

2022-01-19 01:27:45    分类:问答    sql   sql-server-2008   grouping

Duplicates removing [duplicate]

This question already has answers here: Closed 10 years ago. Possible Duplicate: Delete duplicate records from a SQL table without a primary key I have data: SELECT a , b FROM ( select a = 1, b = 30 union all select a = 2, b = 50 union all select a = 3, b = 50 union all select a = 4, b = 50 union all select a = 5, b = 60 ) t I have to get output (next (order by a) dublicate records should be excluded from result set): a b ----------- ----------- 1 30 2 50 3 50 -- should be excluded 4 50 -- should be excluded 5 60

2022-01-18 20:34:00    分类:问答    tsql   sql-server-2008   duplicate-removal

Formatting output in SQLCMD mode?

Is there a way I can specify output file format in SQLCMD mode so that I can read it back using BULK INSERT? I want to do something like this: :CONNECT SERVER1 :OUT 'E:\test' SELECT TOP 100 ID, NAME FROM DB1.dbo.TABLE1 GO :CONNECT SERVER2 :OUT 'E:\test2' CREATE TABLE #TEMP(ID INT, NAME VARCHAR(100)) BULK INSERT #TEMP FROM 'E:\test' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO SELECT * FROM DB2.dbo.TABLE2 WHERE ID IN (SELECT ID FROM #TEMP) -- Other queries for #TEMP GO

2022-01-18 17:28:20    分类:问答    sql   sql-server   sql-server-2008   tsql   sqlcmd

Table Valued Parameters with Estimated Number of Rows 1

I have been searching the internet for hours trying to figure out how to improve the performance of my query using table-valued parameters (TVP). After hours of searching, I finally determined what I believe is the root of the problem. Upon examining the Estimated Execution plan of my query, I discovered that the estimated number of rows for my query is 1 anytime I use a TVP. If I exchange the TVP for a query that selects the data I am interested in, then the estimated number of rows is much more accurate at around 7400. This significantly increases the performance. However, in the real

2022-01-18 16:49:59    分类:问答    sql   sql-server-2008   sql-execution-plan   table-valued-parameters

Is WITH the replacement for a #TEMP table?

So based off the way I've seen WITH used, and the documentation at MSDN: Specifies a temporary named result set, known as a common table expression (CTE). it appears that the WITH is the replacement for #TEMP tables. Is that correct?

2022-01-18 16:20:26    分类:问答    sql-server   sql-server-2008   tsql   common-table-expression

SQL query to get last record of every minute

I have a table which is updated every second with some values. I want to retrieve the last record of every minute. I have tried this code, but it is returning all the records. SELECT t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus FROM brands t0 WHERE t0.cdt BETWEEN '2013-11-15' and '2014-11-15' GROUP BY t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus,(datepart(minute, t0.cdt) / 1) My table structure is: ID brandname cdt udt brandstatus addedby 1 khasim 2013-11-01 19:14:18.120 2013-11-15 19:14:18.123 1 1 2 khasim 2013-11-01 19:14:18.121 2013-11-15 19:14:18.123 1 1 3 khasim

2022-01-18 15:22:47    分类:问答    sql-server   sql-server-2008

Ordering parent rows by date descending with child rows ordered independently beneath each

This is a contrived version of my table schema to illustrate my problem: QuoteID, Details, DateCreated, ModelQuoteID Where QuoteID is the primary key and ModelQuoteID is a nullable foreign key back onto this table to represent a quote which has been modelled off another quote (and may have subsequently had its Details column etc changed). I need to return a list of quotes ordered by DateCreated descending with the exception of modelled quotes, which should sit beneath their parent quote, ordered by date descending within any other sibling quotes (quotes can only be modelled one level deep). So

2022-01-18 15:17:20    分类:问答    sql   sql-server-2008   sql-order-by   inner-join   common-table-expression