I have a question related to database design. The database that I'm working with requires data to treated in some way that it is never physically deleted. We started going down a path of adding a "DeleteDateTime" column to some tables, that is NULL by default but once stamped would mark a record as deleted. This gives us the ability ar...
Consider a SQL script designed to copy rows from one table to another in a SQL 2000 database. The transfer involves 750,000 rows in a simple: INSERT INTO TableB([ColA],[ColB]....[ColG]) SELECT [ColA],[ColB]....[ColG] FROM TableA This is a long running query, perhaps in part because ColB is of type ntext. There are a handful of CONVER...
Trying to perform a conditional sql query. My formatting is incorrect. Any suggestions? Any help would be appreciated. Select misc, SUM(IF(processdate BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000', getskusold.sprice, NULL) ) AS totalprice_date1, SUM(IF(processdate BETWEEN '2009-11-01 00:00:00.000' AND '2009-12...
Is there a way in .NET SqlClient to translate SqlType to actual SQL type declaration, like SqlInt32 -> "int" SqlGuid -> "uniqueidentifier" SqlXml -> "xml" SqlString -> "nvarchar(??)" without doing it manually? CLARIFICATION: I am trying to automatically refactor a number of SQL select statements, and I need to know what types d...
Hi all, I have a problem when trying to create a new sproc for a tableadapter. On my development server, Visual Studio is using my windows login; domain name/ username. When I try and create the same tableadapter sproc on our production server, Visual Studio uses "dbo" for the db connection user ID. This is what I want for both environm...
How do I write a Microsoft SQL Server command to update every row with the corresponding value from a second table? UPDATE Person SET FirstName=Temp.FirstName FROM Temp WHERE Temp.PersonID=Person.PersonID Here I want to replace every FirstName with Temp.Firstname where the personid in Person is equal to Temp.PersonID ...
I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned. Here is what I have so far (not working) SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1 RIGHT JOIN (SELECT DIST...
ALTER TABLE Physician Modify (RefLastName nvarchar(500), RefFirstName nvarchar(500)); Getting Incorrect syntax error... ...
i want to check for a particular set of values. eg check columnname should be between 1 to 5 check columnname should be either 1 or 2 or 4 ...
I am creating a table to summarize data that is gathered from about 8 or so queries that have very light logic/WHERE clauses and all select against different tables. I was wondering what the best option would be to fetch the summarized data: One query with multiple JOINS to gather all relevant information A stored proc that encapsulat...
I come from a linux/apache/php/mysql background. For my current project, I am forced to write web services using ASP.NET. I have installed visual studio and created an ASP.NET web service project. The web service I'm creating will use a SQL database backend. I see that visual studio installed "SQL Server Express 2008." I can see that the...
In a specific table I have a SortOrder integer field that tells my page in which order to display the data. There are sets of data in the this field (based on a CategoryID field), and each set will have its own ordering. Users can add/remove/update records in this table. My question is what is the best way to manage this SortOrder fie...
Hello, I have another post which resulted in this SELECT DISTINCT a.ArticleID, COUNT(*) AS KeywordMatch, a.Headline, a.ShortDescription, a.CategoryID, a.ArticleSectionImage, a.DatePublished FROM Article a JOIN SearchWords sw ON a.ArticleID = sw.ArticleID WHERE EXISTS ( ...
I have a table with INT id and DATETIME date, amongst other fields. Rows are inserted into this table each weekday (not guaranteed), and several other tables use this id as a foreign key. My question is, how can I get the id for the max date of each month, which I can then use to join to other data tables? For example, if the process ...
I was readin this month edition of SQL Server Magazine and in an article about securing Sql Server environment , the author mentioned that developer should try to have the website and the databases run in separate servers for security. I have a shared hosting account and was wondering if it makes sense to buy a second account to move all...
I want to add the value from my database to a label the value has a datatype varbinary(max) string buF = dt.Rows[i]["BuF"].ToString(); Label3.Text = buF; for this i get the output as System.Byte[] which is not the correct value.. any suggestions?? thanks ...
I am working on a WPF application for a vertical market. In a discussion today about how to build the data structure to support the selection of a dozen checkboxes and radio buttons, I thought about the possibility of serializing an object to XML and storing that in SQL Server, then deserializing it when the data needs to be displayed. ...
Is there any way to specify that a foreign key should also be indexed using fluent nhibernate? MS Sql Server does not by default index foreign keys, and I would like the schema generated by the nhibernate schema generation/update tools to create these. When I just use the HasMany or HasManyToMany methods, no such indexes are created. Is ...
the value buf has a datatype varbinary(max) and the value is 0x0000002D string buF = "0x" + BitConverter.ToString((byte[])dt.Rows[i]["BuF"]).Replace("-", ""); Label3.Text = buF; i use the value to find the fileid DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(); connection.Connec...
I've got a project where we'll need to generate a lot of fixed-length random codes (Read: Millions) from a set of characters (EG: 12 digit alpha-numeric or 9 digit alpha-numeric lower-case only without the l character). We're going to then store these codes in an MSSQL database (SQL Server 2008). The language we're using is C#. We also ...