help emergency: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

ID Field Reset

This morning I made a change to two tables in my live system. All I did was add a new column in each called 'suspense' with a data type of bit and a default value of '0'.

When the users started using the system they noticed that the record ID numbers had reset and were starting from 1 and going up 2, 3, 4 etc. The last good id number was about 20500. The id columns are set as primary key with identity and increment of 1.

This shouldn't happen should it? Is it a bug? I never touched the ID column, just added the new one.I bet you made the change using Enterprise Mangler. EM tends to make these changes not with a simple alter table add column, but with a drop and re-create of the table. You can try running DBCC CHECKIDENT(table) on the table, which should reset the identity column nicely.|||That's interesting. I am going to test that on my test system now to see what happens.

Thanks!

Friday, March 23, 2012

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.
Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/...y/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/...y/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to restrict the my clients to access database through EnterPrise Manger Or Quer ana

Hello All
I am using SQL server 2000 as the backend of my application but don't
want my clients tobe able to view or edit the database tables, stored
procedures , view etc using enterprise manager or query analyser (or
similar tools)How can this be done ?
I searched a lot for this but unable to get the correct answer is
there anybody to give full solution regarding this.
Please relpy me as early as possibleCheck 'Application roles' topic in BOL.

Igor

"Kavita" <kavitajt@.yahoo.com> wrote in message
news:b1112113.0403020411.7eb19264@.posting.google.c om...
> Hello All
> I am using SQL server 2000 as the backend of my application but don't
> want my clients tobe able to view or edit the database tables, stored
> procedures , view etc using enterprise manager or query analyser (or
> similar tools)How can this be done ?
> I searched a lot for this but unable to get the correct answer is
> there anybody to give full solution regarding this.
> Please relpy me as early as possible|||"Kavita" <kavitajt@.yahoo.com> wrote in message
news:b1112113.0403020411.7eb19264@.posting.google.c om...
> Hello All
> I am using SQL server 2000 as the backend of my application but don't
> want my clients tobe able to view or edit the database tables, stored
> procedures , view etc using enterprise manager or query analyser (or
> similar tools)How can this be done ?
> I searched a lot for this but unable to get the correct answer is
> there anybody to give full solution regarding this.
> Please relpy me as early as possible

Here are several things you can look at:

1. Do not give the users permission to create procedures, views etc.

2. Use application roles (see sp_setapprole in Books Online)

3. Only allow access to data through stored procedures, and only grant
EXECUTE permissions on the procedures instead of granting permissions on the
tables (this may not be 100% possible if you require dynamic SQL, however)

4. Code your application to set the application name when it connects, then
use APP_NAME() in stored procedures to check the user is using an
'authorized' application (this is easy to fake, though, so it will only stop
an accidental or 'casual' attempt to connect)

5. Use a middle tier to authenticate users and manage connections instead of
allowing them to connect directly to the database server

Simon

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

|||Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagramssql

Wednesday, March 21, 2012

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables? (The 2 databases are on the same instance of SqlServer 2005)

-perplexed newbie

Madhu

|||Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables|||

ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables

this will cretae a table with only data not with all constraints. To create constratin u have to script it.

Madhu

I want to clarify..

Can we declare any variables in the sql server 2000 reporting services?
because I need to make an array for my tables, for ex: in my database(sql
server) it has many tables like tble27,tble28,tble29,tble30.. etc. all of
the fields inside the tables are the same. every table has
name,age,sex,address. just different records. I was planning on making an
array like tble(i) so i can use the report parameter to just use the
variable for an easy access to whatever my client would prefer to view.
Also just telling me how, and where to declare variables would be a great
help, if it is possible.. advance thank you. please do help me
Diff. Question:
Also how will i access a table from database1 as my report parameter and and
view the records from database2?
--
~SiMPLe~Hi,
For your first question you should consider a stored procedure based on a
parameter which returns the proper underlying table. Then you can create a
dataset and use the sp to retrieve the data. Declare in the properties of the
dataset the mapping of the ReportParameter and the parameter nessesary for
the sp.
Every dataset you create in SRS has it's own connection. And those
connection can point to different datasources. So just create two datasources
to your servers and retrieve the information.
Jan Pieter Posthuma
"Alex" wrote:
> Can we declare any variables in the sql server 2000 reporting services?
> because I need to make an array for my tables, for ex: in my database(sql
> server) it has many tables like tble27,tble28,tble29,tble30.. etc. all of
> the fields inside the tables are the same. every table has
> name,age,sex,address. just different records. I was planning on making an
> array like tble(i) so i can use the report parameter to just use the
> variable for an easy access to whatever my client would prefer to view.
> Also just telling me how, and where to declare variables would be a great
> help, if it is possible.. advance thank you. please do help me
> Diff. Question:
> Also how will i access a table from database1 as my report parameter and and
> view the records from database2?
> --
> ~SiMPLe~

Monday, March 19, 2012

I want the Northwind Script !

Hello,

I'm a new user here ,

i want the script"query" for Northwind database

wich create tables and inserting the data

wait your help ?

You can download The Northwind database and script from here: http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en. After downloading SQL2000SampleDb.msi, xtract the sample database scripts by double-clicking SQL2000SampleDb.msi. SQL2000SampleDb.msi will extract the database scripts and this readme file into this default folder:
C:\SQL Server 2000 Sample Databases.

Regards,

Gail

I want the Northwind Script !

Hello,

I'm a new user here ,

i want the script"query" for Northwind database

wich create tables and inserting the data

wait your help ?

Regards,

Gail

Monday, March 12, 2012

I receive MSG 7707 when trying to split a partition for the second time. Why ?

I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem.
I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.

At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"

Here's what i do:

/*-*/
CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/

CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/

CREATE TABLE [dbo].[Letture](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data)
) ON schemaPartizioneLive(data)

ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))

CREATE TABLE [dbo].[LettureStorico](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data)
) ON schemaPartizioneStorico(data)

ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))

/*-*/

Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:

/*--*/
SET @.NewBoundary = dateadd(dd,-1, @.dateOfToday)

--this new partition contains the rows i want to switch
ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)
--this new partition is empty
ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@.NewBoundary)

--this works fine, rows are moved
ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2

--these two merges lead to two tables partitioned in two partitions each
ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@.OldBoundaryLive)
ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@.OldBoundaryStorico)

/**/

Till now, everything is working as expected.
Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)" i receive a 7707 error message:
"Msg 7707, Level 16, State 1, Line 1
The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."

How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?

Someone can help me on this, please ?

Many thankx

Wentu

Hello,

I've experienced the same problem. Even if you map all your partitions to the PRIMARY filegroup when you create your scheme, each time before you split your range, you have to call

ALTER PARTITION SCHEME schemapartizioneLive NEXT USED [PRIMARY].

However, why it works the first time is still a mystery to me. Probably when you mal all your partitions, the "next used" is probably also set. As you haven't done it when you split the first time, you got the error the second time.

Greetings,

Adriano

i need your opinion

I'm migrating denormalized database to a normalized one.
I'm planning not to use DTS but do it this way
1. Use a front end to read tables to dataset
2. insert the dataset to a database view
3. the view has an instead-of-insert triggers that
shall handle the transforms and other logical needs
4. the view will log failed inserted records to logstables
if you are to choose between DTS and this approach which one will you choose
and whyHi
4. Certain errors are not able to be trapped in a SP or trigger. So the
whole batch gets rolled back automatically. This means your 'logged error'
too.
I would still use DTS and with SSIS from SQL Server 2005, it will be even
easier to do.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jose g. de jesus jr mcp, mcdba" wrote:

> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you choo
se
> and why
>|||Take a look at OpenRowSet rather than putting the schema into your new
database, you can use this command to connect two SQL Servers. Then you can
use a series of very big, but fast performing insert commands to migrate the
data one table at a time. Once the conversion is completed there's no
residual stuff from the old structure, unless of course you want them.
Regards
Colin Dawson
www.cjdawson.com
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:E06E117B-3E21-4C11-BE03-6721A1284DDF@.microsoft.com...
> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you
> choose
> and why
>|||Personally, I would perform the entire data transformation using T-SQL
scripts. There is no reason to develope a front end application or to use a
dataset, unless you love writing C# or VB.NET. If you pull the data to a
client side cursor, then it's going to run a lot slower.
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:E06E117B-3E21-4C11-BE03-6721A1284DDF@.microsoft.com...
> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you
> choose
> and why
>|||I need a very complex transform huhuhu.
I need to maintain relationship and
no data loss
table1 >>> target_table2, target_table3 and target_table4.
it must maintain relationship.
tt2 has a one to one relationship with tt3 and tt4
pk is guid
"jose g. de jesus jr mcp, mcdba" wrote:

> I'm migrating denormalized database to a normalized one.
> I'm planning not to use DTS but do it this way
> 1. Use a front end to read tables to dataset
> 2. insert the dataset to a database view
> 3. the view has an instead-of-insert triggers that
> shall handle the transforms and other logical needs
> 4. the view will log failed inserted records to logstables
> if you are to choose between DTS and this approach which one will you choo
se
> and why
>|||On Mon, 15 Aug 2005 07:09:05 -0700, jose g. de jesus jr mcp wrote:

>I need a very complex transform huhuhu.
>I need to maintain relationship and
>no data loss
>table1 >>> target_table2, target_table3 and target_table4.
>it must maintain relationship.
>tt2 has a one to one relationship with tt3 and tt4
>pk is guid
Hi jose,
Are you sure you need a guid column? Using surrogate keys has both pros
and cons; the currently fashionable choice appears to be to use a
surrogate key even if it doesn't have a benefit. And even if a surrogate
key would be beneficial in your case, odds are that an identity column
would be a better choice.
Anyway, that was not what you're asking. Assuming that you do indeed
need a guid surrogate key, you still don't need to pull data from the
server to a front and and back again for the transformation. Here's a
rough sketch of how you could do it in SQL Server only:
-- Step 1: Fill tt2 from main table
INSERT INTO tt2 (GuidKey, NaturalKey1, NaturalKey2, Other1, Other2)
SELECT NEWID(), NaturalKey1, NaturalKey2, Other1, Other2
FROM TableToBeTransformed
-- WHERE '
-- Step 2: Fill tt3, with reference to tt2.
INSERT INTO tt3 (Col1, Col2, Col3, RefToTT2)
SELECT a.Col1, a.Col2, a.Col3, b.GuidKey
FROM TableToBeTransformed AS a
JOIN tt2 AS b
ON b.NaturalKey1 = a.NaturalKey1
AND b.NaturalKey2 = a.NaturalKey2
-- WHERE '
-- Step 3: Same as step 2, but for tt4.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Wednesday, March 7, 2012

I need some serious help

Out of the 3 tables below. I need to get a tabular report like this
First Name Last Name Email
Can you receive HTML email Address State
Zip
Tom Thompson xxxx@.xxxxxxxx.com
Yes 575 mystreet Rd
AK 14525
steve Smith
aaaa@.aaaaaaaa.com No
575 double Dam Rd AL 13323
table 1
QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip
Table 2
Resultid QuestionID OptionD
OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom
140 362
2052 19 -55
Thompson 140 362
2053 20 -55
xxxx@.xxxxxxxx.com 140 362
2055 21 47
140 362
2056 23 -55 575
mystreet Rd 140 362
2057 24 52
140 362
2058 25 -55 14525
140 362
2059 18 -55 steve
140 362
2060 19 -55 Smith
140 362
2061 20 -55
aaaa@.aaaaaaaa.com 140 362
2063 21 48
140 362
2064 23 -55 575
double Dam Rd 140 362
2065 24 53
140 362
2066 25 -55 13323
140 362
Table3
optionid QuestionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zipOn Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type
int.
SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
> >Out of the 3 tables below. I need to get a tabular report like this
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conversion so that end
result is of the datatype which has the highest precedence according to "Datatype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
integer so you convert them to appropriate strings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'David' to a column of data type
> int.
> SELECT SurveyResultID,
> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
--
David Portas
SQL Server MVP
--|||Oops. See David's post. I missed the fact that SUM is performed, and it is pretty darn hard to sum
strings. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype conversion so that end
> result is of the datatype which has the highest precedence according to "Datatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
>> Server: Msg 245, Level 16, State 1, Line 1
>> Syntax error converting the varchar value 'David' to a column of data type
>> int.
>> SELECT SurveyResultID,
>> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
>> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
>> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
>> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
>> FROM FormCreator_Results
>> GROUP BY SurveyResultID
>> GO
>> --
>>
>> David Fetrow
>> Helixpoint LLC.
>> http://www.helixpoint.com
>> davef@.helixpoint.com
>> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>

I need some serious help

I need some help with a complex query

I've written a lot of queries in the past, but I'm having a lot of trouble with this one.

I have 3 tables: Thread, Reply, User

Thread has these relevant fields: ThreadID, UserID, DTStamp, Subject

Reply has these relevant fields: ThreadID, UserID, DTStamp

User has these relavent fields: UserID, Name

A few details:
- Thread and Reply connect with ThreadID
- Thread and Reply both connect to Person with UserID
- Thread and Reply share a 1 to many relationship (1 Thread with many Replies)
- It is also possible there are no replies

What I need is a query that looks at thedata and returns a set of records with the following data fields:

Subject: The Subject of the thread
CreationDate: The Date that Subject thread was created
Author: The Name of the UserID that created that thread
Replies: The Number of Replies to the Subject thread
LastPost: The Date of the Last Reply
LastPostAuthor: The Name of the UserID of the Last Reply

And I need this all sorted by Date of Last Reply

Is this even doable? Are there any suggestions on the best way to get started?

Thanks in advance,

Chris

Hi Chris my friend, I will help you on this.

First run the following SQL to create a function: -

CREATE FUNCTION fn_GetLastPostUserNameByThreadID
(
@.ThreadID AS INT
)

RETURNS varchar(30)

BEGIN

DECLARE @.Author AS VARCHAR(30)

SET @.Author = (
SELECT Name FROM User WHERE UserID =
(SELECT TOP 1 USERID FROM Reply WHERE ThreadID = @.ThreadID
ORDER BY dtstamp DESC)
)

RETURN @.Author

END

Now for the SQL to get you the results: -

SELECT thread.Subject,
thread.dtstamp,
user.name,
COUNT(reply.threadid) AS Replies,
MAX(reply.dtstamp) AS LastPost,
dbo.fn_GetLastPostUserNameByThreadID(thread.threadid) AS LastPostAuthor

FROM thread

INNER JOIN user on thread.userid = user.userid
LEFT OUTER JOIN reply ON thread.threadid = reply.threadid

GROUP BY Subject,
thread.dtstamp,
user.name,
dbo.fn_GetLastPostUserNameByThreadID(thread.threadid)

ORDER BY reply.dtstamp DESC

|||

Thank you so much! I can't wait to try this out.

I have created stored procedures before, but not functions, so I have a couple follow-up questions.

When I look at my database through SQL Server Management Studio Express, I see funtions and under that 4 categories:

Table-valued Functions|||

Hi Chris,

A Table-valued function returns a table. To use one, you do "SELECT * FROM dbo.MyFunction()". The following example takes a string and turns it into a table.

create FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(10)

SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN
END

The first parameter is the string. The second is the separator. Test it with the following: -

select * from dbo.StringArrayIntoTable('red,blue,yellow', ',')
select * from dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

This is useful if you need to pass an array of values into a stored procedure. Just pass in a string that you can separate!

Scalar valued functions only return one value and when using them, you don't use "SELECT * FROM", just "SELECT FunctionName()". You would create one of these if you wanted a function that only returned one value, like the one I gave to you that returns 1 varchar; the author's name.

Aggregate functions are built-in scalar valued functions. For example, SUM and AVG; select SUM(SaleValue) AS Total, AVG(SaleValue) AS AverageSale FROM tblSales.

As for System functions, some are more useful than others. For example, if you want to return 0 for SaleValue if the field value is null, you can use SELECT IsNull(SaleValue, 0) AS SaleValue. It returns whatever the SalesValue is, but 0 if it is NULL.

As for your second question, always use a stored procedure. When a stored procedure cannot give you directly what you need without calling a function, as in the problem you posted, have the procedure call a function. Stored procedures have pre-compiled execution plans and execute more efficiently. On the other hand, performance hits are associated with functions so use them only when necessary.

By the way. I noticed that within the SQL I gave to you I used "user" to refer to the author table. This cannot be right because user is not a valid table name. I did not realize this at first because I did it in Notepad. Please substitute this with the correct table name and the SQL should work.

In return for all of this help, I only ask that you mark me as the answerer of your question in this forum.

Kind regards

Scotty

|||

Scotty,

Wow! I can't begin to thank you enough for your help. Your code and explanations are just what I needed.

FYI, my table is called User. I probably should change the name, but for now I just refer to it like this [User] and it works okay.

Also, I made one other change to the code, I changed the last line to "Order By LastPost Desc" as it had a problem with "reply.dtstamp" not being part of the result set.

Thanks again,

Chris

p.s. If you want to see your code in action, feel free to check out my site MoviePoet.com early next week.

Friday, March 30, 2012

ID Field Reset

Friday, March 23, 2012

I want to use multiple select queries in a stored procedure

I want to use multiple select queries in a stored procedure

I want to use multiple select queries in a stored procedure

I want to restrict the my clients to access database through EnterPrise Manger Or Quer ana

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

Wednesday, March 21, 2012

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to ''Copy'' 3 tables from an existing DB into a new DB on the same SqlServer instance

I want to clarify..

Monday, March 19, 2012

I want the Northwind Script !

I want the Northwind Script !

Monday, March 12, 2012

I receive MSG 7707 when trying to split a partition for the second time. Why ?

i need your opinion

Wednesday, March 7, 2012

I need some serious help

I need some serious help

I need some serious help

I need some help with a complex query

help emergency

Blog Archive

About Me