I have a view in a SQL Server 2008 db that simply exposes about 20 fields of one table to be consumed via ODBC to a client. When I tried to replicate this view in another database, the client could not consume the data source. Then I noticed some weirdness. The columns in the view are shown, in SQL Server Management Studio, to be varc...
Syntax question: I am using the code below to call a query in Access VBA strSQL = "INSERT INTO tblLoanDetails ([ServerName]) VALUES ('Test') WHERE [ID]=3" Call CurrentDb.Execute(strSQL) And i am getting a runtime error of "3067: Query must contain atleast one table or query." the insert statement string looks like this (Threw the var...
I have a pretty simple question, but for some reason I am drawing a blank. I have the following code in my view file, and I want to display the results in a two column table, so the first entry would be on the left, the next would be on the right then the next one after that would be below the first row, and eventually I will use the pag...
Hello, I am new to using the geography types in SQL Server 2008. I have a table in my database called "Location". "Location" has the following columns and types: Location -------- City nvarchar(256) State nvarchar(256) PostalCode nvarchar(25) Latitude decimal(9, 6) Longitude decimal(9, 6) Each Location is related to a Store record in...
I need something real simple, that for some reason I am unable to accomplish up to this point. I have also been unable to Google the answer surprisingly. I need to number the entries in different tables uniquely. I am aware of AUTO INCREMENT in MySQL and that it will be involved. My situation would be as follows: If I had a table like ...
Anyone have an idea about how to use sp_helptext to view a stored procedure on a linked server? basically something like this. I don't have the credentials to that linked server to look at it. EXEC sp_HelpText '[ServerName].[DatabaseName].dbo.storedProcName' thanks ahead. ...
I work in a location where a single person is responsible for creating and maintaining all stored procedures for SQL servers, and is the conduit between software developers and the database. There are a lot of stored procedures in place, and with a database diagram it is simple enough 90% of the time to figure out what the stored proced...
Is it possible to turn result sets obtained in LINQ through a stored procedure or function call into a "live" set of objects of which I can retrieve Foreign Key related objects? If, for example, my stored procedure returns a set of rows (= LINQ objects) of type "Contact", then I can't seem to obtain Contact.BillingAddress (which is rela...
UPDATE files SET filepath = REPLACE(filepath, `sites/somedomain.com/files/`, `sites/someotherdomain.com/files/`); I have a table called files with a field called filepath. MySQL returns this error: Unknown column 'sites/somedomain.com/files/' in 'field list' ...
I have a table with entries which has a DATE field. Each entry has a distinct date. I'd like to select all the entries from the last month registred in the database. How? I tried: SELECT * FROM registries WHERE reg_date = DATE_FORMAT(MAX(reg_date), "%m")` ...without success ...
Hi, I am trying to get some percentage data from a stored procedure using code similar to the line below. Obviously this is going to cause a (Divide by zero error encountered) problem when base.[XXX_DataSetB] returns 0 which may happen some of the time. Does anyone know how i can deal with this in the most efficient manner? Note: Ther...
I want be able to find tags of items under the a certain category. Following is example of my database design: images +----------+-----+-------------+-----+ | image_id | ... | category_id | ... | +----------+-----+-------------+-----+ | 1 | ... | 11 | ... | +----------+-----+----------...
Is it possible to return groups as an associative array? I'd like to know if a pure SQL solution is possible. Note that I release that I could be making things more complex unnecessarily but this is mainly to give me an idea of the power of SQL. My problem: I have a list of words in the database that should be sorted alphabetically and ...
I have Article table: id | type | date ----------------------- 1 | A | 2010-01-01 2 | A | 2010-01-01 3 | B | 2010-01-01 Field type can be A, B or C. I need to run a report that would return how many articles of each type there is per every day, like this: date | count(type="A") | count(type="B") | count(type="C") -...
Hi, I have 2 tables: comments and posts and I'd like to display a list of 15 posts and maximum 2 most recent comments under each blog post the database scheme looks like this posts_table: post_id, post_txt, post_timestamp comments_table: post_id, comment_txt, comment_timestamp how the mysql query should look like to select 15 posts ...
I need to create a stored procedure which queries a table and creates a CSV file for that data in a specified directory location. how do i do it? ...
Say I have the following tables and columns: comp: id, model dvd: id, model comp2dvd: id, id_comp, id_dvd A computer can have multiple dvd drives, even of the same model, and a dvd drive can appear in multiple computers. How do I make it so that comp2dvd table can have only existing comp and dvd id...
Let’s say you have a table with about 5 million records and a nvarchar(max) column populated with large text data. You want to set this column to NULL if SomeOtherColumn = 1 in fastest possible way. The brute force UPDATE does not work very well here because it will create large implicit transaction and take forever. Doing updates in sma...
Hello everyone, I am a new with T-SQL. So, please help me to write the sql. I have table Price (Code column is primary column): Code Value A1 234 A2 525 A3 566 I will input a string and the sql need to return a table. Ex1: input 'A2' -> return: Code Value A2 525 Ex2: input 'A1 A3' -> return: Code ...
Say I have 2 tables: Person - Id - Name PersonAttribute - Id - PersonId - Name - Value Further, let's say that each person had 2 attributes (say, gender and age). A sample record would be like this: Person->Id = 1 Person->Name = 'John Doe' PersonAttribute->Id = 1 PersonAttribute->PersonId = 1 PersonAttribute->Name = 'Gender...