My example is similar to this - The only problem is I cannot rewrite this query to cope with columns that have spaces inside it
In the example below suppose that rather then [Oranges] you had ['Oranges And Apples'] in one cell.
For some reason adding an "'" means the pivot function returns NULL everywhere and [Oranges And Apples] is of...
I wish to implement Sql Server Profiler in a C#/VB.net application. Does anyone have a good example of how to do this? I have searched on Google but didn't find a good working example. I don't have Sql Server Profiler on my system and also don't have Sql Server (it is on a different system).
How do I create Profiler for my self?
...
Hi!
I'm wondering if is there a way to force MSSQL Management Studio to produce a script like this:
ALTER TABLE Mytable
ADD MyCol bit NOT NULL
CONSTRAINT MyColDefault
DEFAULT 0 WITH VALUES
ALTER TABLE [dbo].Mytable
ALTER COLUMN MyCol2 int NULL
GO
when I alter a very simple property of a column on a table.
If I do this in the desig...
Suppose a query "select * from employee" returns 80 rows. I need to display middle rows that is from 20th row to 50th row.
I know, like to display first 20 rows we have option like "select top 20 * from employee" but if we need middle rows how to get it in MS SQL specifically.
I m new to this SQL queries...Can anybody answer to this qu...
I've been let down by my Programmer, so I need to change a field size myself today.
Basically, a field is limited to 12 characters on a form which I need to change to 20. I've logged on to the SQL database and changed it there, but its still not working on the main site.
I've been told I need to change it in FluentNHibernate, but I've ...
Suppose a query "select streetAdr from Address" returns "236 a1 road" "333 a2 road" and 444 a4 road" as 3 rows. How i can display only "236" "333" and "444" in SQL Server.
...
In the database I have a field with a .mht file. I want to use FTS to search in this document. I got this working, but I'm not satisfied with the result. For example (sorry it's in dutch, but I think you get my point) I will use 2 words: zieken and ziekenhuis. As you can see, the phrase 'zieken' is in the word 'ziekenhuis'.
When I searc...
Suppose I have a user defined type:
CREATE OR REPLACE TYPE TEST_TYPE AS OBJECT
(
f1 varchar2(10),
f2 number(5)
);
Now, I want to create a table to hold these types. I can do the following:
create table test_type_table (
test_type_field test_type
);
This gives me a table with one column, test_type_field.
Is there an...
Question: I can get the SQL Server database language by querying:
SELECT @@language
And I can get further info via
EXEC sp_helplanguage
How can I query for a column of sp_helplanguage where name= @@language
I do SELECT * FROM sp_helplanguage WHERE name='DEUTSCH'
but that obviously doesn't work.
What's the correct way to query i...
Question: The new SQL Server 2008 database returns me values formatted English (date/float).
Is there a way I can set the return format ?
For example temporarely switching the database language?
Or just set the language for the current query ?
...
Let's assume I have a table magazine:
CREATE TABLE magazine
(
magazine_id integer NOT NULL DEFAULT nextval(('public.magazine_magazine_id_seq'::text)::regclass),
longname character varying(1000),
shortname character varying(200),
issn character varying(9),
CONSTRAINT pk_magazine PRIMARY KEY (magazine_id)
);
And another table ...
I have two table emptable1(empid,status) emptable2(empid,week)
i want to select all the empid whose status is 0 in emptable1 and from that list of empid i need to select empid whose week is 7 from the table emptable2
Please help :-)
...
I'm working on SQL server 2005 and I have a very simple stored procedure:
create PROCEDURE [dbo].[tblTabel_Insert]
@ID int,
@Code nvarchar(50) = null
AS
SET NOCOUNT ON;
IF EXISTS (SELECT ID, code FROM tblTabel WHERE ID = @ID and code = @Code)
UPDATE tblTabel SET ID = @ID,code = @Code WHERE ID = @ID
ELSE
BEGIN
INSERT INTO...
I have 1-to-many relationship between two tables (Product and PLProduct respectively).
This is done through foreign key PLProduct.ParentProductId which relates to Product.Id.
The question is: how can I copy (orphan) records from PLProduct to Product table, setting foreign key at the same time?
...
Table one contains
ID|Name
1 Mary
2 John
Table two contains
ID|Color
1 Red
2 Blue
2 Green
2 Black
I want to end up with is
ID|Name|Red|Blue|Green|Black
1 Mary Y Y
2 John Y Y Y
Thanks for any help.
...
I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format?
Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time.
Given ...
Hi,
Im getting a strange syntax error when I run this in VB
SQLString = "UPDATE Login SET Password = '" + PasswordTextBox.Text + "'"
SQLString += " WHERE UserName = '" + UserNameTextBox.Text + "'"
The Username is checked before getting to this part and is definitly in the db.
It gives an exception saying syntax error ...
Is there anything like this:
TEST DELETE FROM user WHERE somekey = 45;
That can return any errors, for example that somekey doesn't exist, or some constraint violation or anything, and reporting how many rows would be affected, but not executing the query?
I know you can easily turn any query in a select query that has no write or del...
i have some apache logs and i want to be able to query the data in it.
so i wanted to know if its possible to convert this log data to a relational database so i later can query and analyze the data using sql statments.
thanks
...
I'm trying to calculate TimeSpans between dates. I have no problem with this if the date is formatted using the native sqlite3 format 'YYYY-dd-mm'
How would I do this if the date is formatted differently, such as 'dd-mm-YYYY'
I've tried the following with no success.
--Select days between two days; this works if the datetime string i...