I did batch to run SQL query like use [AxDWH_Central_Reporting] GO EXEC sp_spaceused @updateusage = N'TRUE' GO It displays 2 tables and generates some ugly report with some kind of 'P' unneeded letters... See below Changed database context to 'AxDWH_Central_Reporting'. database_name ...
In Java you can do the following to locally scope variables within a method: public void blah() { { int a = 1; int b = 2; } { int a = 3; int b = 4; } } I don't even know what the name for this technique is, I've only needed to use it once or twice. In my current situation, this may really come in ha...
I have some code that disables a trigger on a table, runs an update, and reenables the trigger. It doesn't drop the source table or update the schema. This would all be fine, but at the same time there are separate queries running that use that table as the source for a SELECT INTO. For some reason, when the two queries are running at t...
We have a 3rd-party app that wants to call a stored proc using SQL 2005-style "schema" syntax: Customer.InsertNewOrder This db is still on SQL 2000, though, so we're trying to make it "look" like a SQL 2005 schema, and still work right (with minimum necessary permissions). So we: Created a user called "Customer" Created the stored p...
I would like to understand this error I'm getting on a SQL Server 2005 instance. Error below: Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation. Both databases included in a simple query have Latin1 General CI AI, but had SQL Latin1 General CP1 CI...
I have a statement that SELECTs distinct rows from a table with a two value index SELECT distinct Reference, Value1, Value2, Value3, Value4 FROM [tblHistory] Where Reference is an index with another field "Project". For a particular system this data is inserted into another table using only Reference as the index because Value1 throug...
Guys we are designing an e commerce web site using asp.net. we are using a bsic design and dont know if we can add something or shift onto a better version of asp or silver light etc. wud like ur suggestions and inputs. we are makin 3 main pages one for the login,another for the products and one for the payment.wheneva there is a purcha...
Is it possible to return the last row of a table in MS SQL Server. I am using an auto increment field for the ID and i want to get the last one just added to join it with something else. Any idea? Here's the code: const string QUERY = @"INSERT INTO Questions (ID, Question, Answer, CategoryID, Permission) " + @"VALUE...
Hi All, I have to edit a number of SQL Windows applications that I have inherited. This is not something that i've done before and i'm having trouble finding an IDE or similar (never mind editing the code!) as it seems a number of responsible companies have gone bust in the years since these apps were written, can anybody advise on how ...
We use SQL Server 2005. All our data access is done through stored procedures. Our selection stored procedures always return multiple result sets. For instance: CREATE PROCEDURE hd_invoice_select(@id INT) AS SELECT * FROM Invoice WHERE InvoiceID = @id SELECT * FROM InvoiceItem WHERE InvoiceID = @id SELECT * FROM InvoiceComments WHE...
I have a products table in sql server 2005 which needs to be updated by certain fields in a csv file. Both files have a vendor part number which can be linked to where I can update the products.discontined field with another in the csv file. My question is what is the best way to approach this? I've considered creating a odbc conne...
I thought this would be trivial, but it isn't... I'm sure there is a simple way to do it but I can't manage to find it. Shame on me. I want to import/export the database itself, the tables, the constraints (foreign keys and so on). I'd rather not get the data with it, but I can get rid of it after if there's no other way. So... how do ...
I get the following error. [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'STATS.VisitorIP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I used VISITORIP column in both select lists. I used GROUP BY clause in one of them so I addded VISITORIP to it. But I dont ...
I have several content tables that I want to fill up with random paragraphs of text. In MS Word, I can simply put =rand() and presto! I get three paragraphs of fresh-off-the-press text. Is there a SQL script/command that I can use to generate random dictionary words using t-sql? ...
Hi all I am in the process of optimising my database and I was thinking of changing the datatype for some columns from DATETIME to SMALLDATETIME on my tables. Is there a system stored procedure that returns both the contents/code of a store procedure and the dependent table which will then allow me to do a join on a filtered list of ta...
Many of my employers applications share a similar internal permission structure for restricting data to a specific set of users or groups. Groups can also be nested. The problem we're currently facing with this approach is that enumerating the permissions is incredibly slow. The current method uses a stored procedure with many cursors a...
I have an application running on mssql 2005 express and I want to install the full text search. What should I do? Can I download the full text search service and install it separately. ...
I have 2 procedures. One that builds a temp table and another (or several others) that use a temp table that's created in the first proc. Is this considered bad form? I'm walking into an existing codebase here and it uses this pattern a lot. It certainly bothers me but I can't say that it's explicitly a bad idea. I just find it to be an ...
I have the following query, which is trying to figure out the percentage of a certain product compared to the total number of products. IE: [Product Count] / [Total Products] = Percent ;WITH totalCount AS( SELECT CAST(COUNT(id) as Integer)as totalCount FROM TABLE_NAME ) SELECT ((CAST(COUNT(DISTINCT id) as Integer)/(SE...
I have a SQL Server Reporting Services site that I can get to by https://localserver/reports Now when for setting permissions on each folder and report item or datasource item in that folder I have to click on the "properties" then "security" of the item, and then assign the appropriate permissions. It's just that there are hundreds of i...