sql

Pivot Tables In Sql Server With Spaces In Cell

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...

SQL Server Profiler Implementation Using C#/VB.net

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? ...

forcing Management Studio to use alter table instead of drop/recreate

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...

selecting number of rows from resultset

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...

Amend field - nvarchar(12) to nvarchar(20)

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 ...

Display part of entry

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. ...

SQL Server 2008 full-text search doesn't find word in words?

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...

create table based on a user defined type

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...

SQL Server: How to get a subitem of sp_helplanguage ?

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...

SQL Server: how to set return format ?

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 ? ...

Check if row already exists, if so tell the referenced table the id

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 ...

SQl to select from multiple tables

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 :-) ...

SQL and multiple statements in stored procedure

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...

SQL: Copy records from child table to parent table, setting foreign key at the same time.

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? ...

SQL Join Problem

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. ...

How to make awkward pivot of sql table in SQL Server 2005?

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 ...

SQL syntax error in Update statement VB.net

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 a command to test an SQL query without executing it? ( MySQL or ANSI SQL )

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...

converting web server logs (apache,iis, etc..) to relational Database

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 ...

Date calculations with sqlite3

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...