How to generate alphanumeric tokens in SQL Server?
I am looking to generate soem alpha numeric tokens. Is there a function that can do to generate set length tokens? ...
sql-server-2005
t-sql grouping query
Hi based on the following table Name --------- A A A B B C C C I want to add another column to this table called 'OnGoing' and the values should alternate for each group of names. There are only two values 'X' and 'Y'. So the table will look like Name OnGoing ---------------- A X A X A X B Y B ...
Dynamically change my schema
I am wondering if there is a way to change the schema that I am working in while inside Management Studio. For instance I may have a default schema of dbo. But there are times I may want to query objects in say the accounting schema. It would be nice if I could issue a command and make it so I no longer must include the accounting before...
SQL Self Join Query Help
Hi All, I'm trying to work out a query that self join itself on a table using the eventnumber. I've never done a self join before. What i'm trying to query is when a client has started off in a city which is chester to see what city they moved to. But I dont want to be able to see if they started off in another city. I would also like...
How to format TSQL SELECT output in SQL Sever
How to loop through a select statement results to have a formatted text? for example the select is like: select name from table and we want a variable @names like this: "name1,name2,name3" Database is SQL Server 2005 ...
SQL query to show what has been paid each month
I'm looking for help to create a query, to solve the following problem: Let's imagine the row: Name StartDate EndDate Paid James 10-10-2010 17-02-2011 860 And heres the schema for the table as requested: payment_details (name VARCHAR(50) NOT NULL, start_date DATETIME NOT NULL, end_d...
what's a good way to synchronize a sql server 2008 database from a 2005 database automatically?
Ok, the scenario is... two servers, on completely different parts of the internet. The sql 2008 database just needs to get data updates and schema changes. It doesn't need to send anything to the 2005 database. Basically just suck data and schema as efficiently as possible automatically as a scheduled task. The database is quite huge...
How to get Master and Slave Table data in one row using SQL Server ?
I have main table called 'Employee' and another slave table called 'EmployeeTypes' that has a FK from 'Employee'. Each row in 'Employee' can have zero or many rows in 'EmployeeTypes' and I want to make an SQL Query that returns data of all Employees and each employee row should contain its related data in 'EmployeeTypes' (for example co...
Saving a huge dataset into SQL table in a XML column - MS SQL, C#.Net
Hello all I'm having a requirement where i've to save a dataset which has multiple tables in it in a sql table XML column by converting into an XML. The problem is that in some cases the dataset grows extremely huge and i get OutOfMemoryException. What i basically do is that i convert the dataset into an xml file and save it in the lo...
shreding xml column
Hi, I have a XML column which contains XML like this: <Set> <Element> <ID> 1 </ID> <List> <ListElement> <Part1> ListElement 1 </Part1> </ListElement> <ListElement> <Part1> ListElement2 </Part1> </ListElement> </List> </Element> ...
Tricky SQL - Select non-adjacent numbers
Given this data on SQL Server 2005: SectionID Name 1 Dan 2 Dan 4 Dan 5 Dan 2 Tom 7 Tom 9 Tom 10 Tom How would I select records where the sectionID must be +-2 or more from another section for the same name. The result would be: 1 Dan 4 Dan 2 Tom ...
What's the best way to rename a column referenced in a computed column?
Hi, I'm trying to rename a column using sp_rename but it's referenced in a computed column. I'm getting the following error: 'Table.Column' cannot be renamed because the object participates in enforced dependencies. As far as I can tell the (persisted) computed column is the only place this is referenced. I guess I can drop and re...
SQL Server 2005: Subquery in EXEC ?
EXEC [dbo].[pr_cfgAddFact] @SettingName = 'TransferBatch', @RoleFK = SELECT TOP 1 rolepk FROM cfgRole WHERE cfgRole.Name = 'SuperAdmin' Why would SQL complain about the SELECT clause here? I am trying to run the proc with the sub query getting the data. ...
How can I know which stored procedure is running in SQL Server 2005
How can I know which stored procedure is running in SQL Server 2005. And also how to print that SP in the query? ...
tsql proc logic help
I am weak in SQL and need some help working through some logic with my proc. Three pieces: store procedure, table1, table2 Table 1 stores most recent data for specific IDs Customer_id status_dte status_cde app_dte 001 2010-04-19 Y 2010-04-19 Table 2 stores history of data for specific customer IDs: For exam...
Isolation level and DIRT READ SQL SERVER 2005 (advanced question)
Hi all! I will describe my problem for an easier explanation: I have a table and my soft is accessing it (update, insert) using transaction. The problem is that I want to enable DIRT READ in this table. But I cant use with (nolock) in my sql statements because I cant change the soft source. So I was thinking in enable dirty read in th...
OutOfMemoryException - Dataset <-> XML <-> Dataset
Hello all I'm stuck in a huge problem where i need to handle huge data. I get a data set which has 5 datatables. One of the tables has around 3000 columns and 50,000 records. I need to save this dataset completely in the SQL database as XML and i need to retrieve it later. I CAN'T make any design changes to skip it. When i do dataset...
Dataset.ReadXml() - Invalid character in the given encoding
Hello all I have saved a dataset in the sql database in an xml column using the following code. XmlDataDocument dd = new XmlDataDocument(dataset); and passing this xml document as sql parameter using param.value = new XmlNodeReader(dd); The XML is like <NewDataSet><SubContractChangeOrders><AGCol>1</AGCol><SCO_x0020_Number>001</SC...
Reporting Services Pie Chart
The pie chart is driving me nuts...Excuse me if I sound ignorant but I have figured out the other RS charts with relative ease, and this is the first time I have had to use the Reporting Services pie chart. I have a dataset: Columns: ChildId int AssessmentType varchar Score int All I want is to have a pie chart that displays the ...
Selecting from a Large Table SQL 2005
I have a SQL table it has more than 1000000 rows, and I need to select with the query as you can see below: SELECT DISTINCT TOP (200) COUNT(1) AS COUNT, KEYWORD FROM QUERIES WITH(NOLOCK) WHERE KEYWORD LIKE '%Something%' GROUP BY KEYWORD ORDER BY 'COUNT' DESC Could you please tell me how can I optimize it to speed up th...