I have 2 tables: #tmptable1 and #tmptable2. These tables have an ID to uniquely identify each record. I want to return all records from table 1, but I only want to return the records from table 2 that aren't in table 1 I want this done in 1 query. Thanks! ...
Is it possible to set up SQL Server to give developers read-only access to the contents of stored procedures on our production database? ...
With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = bstate .Fields(...
what is the difference in the way these work: Sql = "INSERT INTO mytable (datapath, analysistime,reporttime, lastcalib,analystname,reportname,batchstate,instrument) " & _ "VALUES (dpath, atime, rtime,lcalib,aname,rname,bstate,instrument) SELECT SCOPE_IDENTITY()" Set rs = cn.Execute Set rs = rs.NextRecordset and this: With rs ...
I have a column in the database (SQL Server 2005) that has data with a "\0" at the end. When querying in SQL Server, this character is not visible and does not "seem" to exist. When I look in my C# code, the character is there. This character is causing an error on our website, and we need it removed from all the affected rows. Is th...
Lets say I have a database that looks like this: tblA: ID, Name, Sequence, tblBID 1 a 5 14 2 b 3 15 3 c 3 16 4 d 3 17 tblB: ID, Group 14 1 15 1 16 2 17 3 I would like to sequence A so that the sequences go 1...n for each group of B. So in this case, the s...
We have a problem with our table schema falling out of sync with our view schema. I would like to know how I could have a stored procedure (for Sql Server) that gets all views in the database, and executes each one via select * Here is what I imagined (pseudo): Declare x Set x = Select object from sysobjects where object = view fore...
I have a string that always reads as follows: re-assigning LastName, FirstName re-assigned the order to LastName, FirstName (Administrator) Reason Code: Reassign order Comment: The name is always diffent but I only want to return the LastName, Firstname part on the first occurence so right after re-assigning. The spaces and every...
Four column table - id (int), double1 (bit), double2 (bit), score (int) Want to write a query the returns the SUM() of the score column, grouped by id, where each row score can be changed based on the two double columns. So if the score is 10 and double1 and/or double2 columns are true, the row score is doubled once or twice. ...
Can someone help me construct the SQL that I need to query the Projects_dim table using the Linked Server "idwd"? To test the connection, I ran a sample query using the linked server name. To access the tables on the linked server, I used a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name. replacing t...
Someone please change my title to better reflect what I am trying to ask. I have a table like Table(id,value,value_type,data) ID is NOT unique. There is no unique key. value_type has two types. lets say A and B. Type B is better than A, but often not available. For each id if any records with value_type B exsits I want all the reco...
Currently, we have information about our products in a variety of places. ERP, Various Databases, etc. Generally we're using SQL Server to store most of the database. We want to create a centralized place where we can store all the information related to our products and start replacing these disjoint databases (with the exception of t...
I'm trying to set up an automated process to regularly transform and export a large MS SQL 2008 database to MongoDB. There is not a 1-1 correspondence between tables in SQL and collections in MongoDB -- for example the Address table in SQL is translated into an array embedded in each customer's record in Mongo and so on. Right now I ha...
After resolving issues with RIA installation here, I'm still getting this following error. Not much special - I tried to create a new screen based off of data sources from an existing database in a full SQL 2005 instance An error occurred while establishing a connection to SQL Server instance '.\SQLEXPRESS'. A network-related or...
Hi All, We have a legacy database which is a sql server db (2005, and 2008). All of the primary keys in the tables are UniqueIdentifiers. The tables currently have no clustered index created on them and we are running into performance issues on tables with only 750k records. This is the first database i've worked on with unique id...
I am trying to develop a query to insert unique records but am receiving the SQL Server Primary Key error for trying to insert duplicate records. I was able to insert some values with this query but not for this record (score_14). So now I am trying to find duplicate record with the following query. The challenge is that my PK is ba...
Hi All, What are the best practices for working with Sql server Xml columns to ensure quick performance and also ease of reporting? How do you set up the column? do you leave it as untyped? or associate it with a schema? Does associating the xml column with a schema improve query performance? Our use of xml columns is as follows: ...
I'm a newbie to Entity Framework, so I'd like to try something simple to get started on Visual Studio 2010. Suppose I start out with the following SQL statement (the View sys.databases exists in the master database on SQL Azure): SELECT name, create_date FROM sys.databases WHERE database_id > 3 So instead of using traditional ADO...
I have an Access 2003 project in which all data is stored in SQL Server 2008. I am using ADO to view/update data via forms that are completely unbound. For example, a form has several textboxes and combo boxes on it. When the form is loaded I use ADO to make a call to a stored procedure on SQL SQL, it returns a recordset and I populate t...
Hi Guys, Here's my query. What I want to do is run this query every week so table PlanFinder.InvalidAwps will have new records. But when I run the query it gives me this error : There is already an object named 'InvalidAwps' in the database. I can't change the table name. It has to remain the same. So how can I run this query every...