Data File Size

SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a

I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.

Howdy!use master
go
sp_helptext sp_spaceused
go

Maybe you will get some ideas from here ... am a little busy ... so just help yourself ...|||You can use code from sp_spaceused to make your own logic ...|||use master
go
sp_helptext sp_spaceused
go

Maybe you will get some ideas from here ... am a little busy ... so just help yourself ...

Thanx for guiding; i would try.

Howdy!

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I need
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!
Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
Good luck.
sql

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I nee
d
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/pr...y/dtssql2k.mspx
Good luck.

data duplication

Dear All

I have SELECT clause which uses loads of OUTER JOINS to produce a table with loads of data needed for a front-end appliation

But, some of the data it produces is replicated because of the JOINS

e.g. an example of data is this

person_id LastName FirstName LanguageSpoken Speciality

1234 mySurNme my1stName FRENCH speciality1
1234 mySurNme my1stName GERMAN speciality2
1234 mySurNme my1stName FRENCH speciality1
1234 mySurNme my1stName GERMAN speciality2

I just want it to come out like this

person_id LastName FirstName LanguageSpoken Speciality

1234 mySurNme my1stName FRENCH speciality1
1234 mySurNme my1stName GERMAN speciality2

Can anyone help

GillYou can try "Select distinct ...". This will eliminate duplicate rows.|||Originally posted by ts555
You can try "Select distinct ...". This will eliminate duplicate rows.

That's the first thing I tried - that doen't work|||If you use distinct, it should not return duplicates. It would be helpful if you give us your query.|||Originally posted by jzhu
If you use distinct, it should not return duplicates. It would be helpful if you give us your query.

A couple of things come to mind.

1) Is the data clean? Dup records could cause a problem.

2) Take a close look at your joins, you could be receiving a cartesian product.

3) If the joins are correct the problem may be in the WHERE criteria. When joining many tables, especially where 1 to many relationships exists, its possible your WHERE clause is not specific enough to bring the number of returns down to a single row.sql

Data Driven

Hi,
I understand I can create a stored procedure to return all the parameters
for data driven. The last statement of the Stored Procedure is Select *
DataDrivenTableName. However, after I put Exec SPName, and when i go to the
next page to fill out all the To, CC, Subject..., there is nothing in each
drop down box and I can't continue to finish the setup.
Any idea what's wrong.
Thanks
EdAFAIK RS uses first resultset to get data, not last.
Stjepan
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2F0D6FE4-2FA8-49EB-A965-D0A57D47362C@.microsoft.com...
> Hi,
> I understand I can create a stored procedure to return all the parameters
> for data driven. The last statement of the Stored Procedure is Select *
> DataDrivenTableName. However, after I put Exec SPName, and when i go to
> the
> next page to fill out all the To, CC, Subject..., there is nothing in each
> drop down box and I can't continue to finish the setup.
> Any idea what's wrong.
> Thanks
> Ed

Data displayed in duplicates

Code below:
If Session("ID") = 1 Or Session("ID") = 2 Then
select case (request.form("Individual"))
case "Individual" sql = "Select V_Monthreport.Q_ID,
V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
V_Monthreport.PersID=" & session("Pers_ID") & " And
V_Monthreport.DeptID=" & request.form("SectID") & " And
V_Monthreport.Maand=" & request.form("Maand") & " And
V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
V_Monthreport.Q_ID"
case "Section"
case "Summary"
end select
End If
<% while not rstMain.eof %>
<tr>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
%></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Question") %></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Answer") %></font></td>
</tr>
<% rstMain.movenext
wend%>
<% End If %>
I am experiencing a problem in that the data when displayed is
duplicated. The data in the db is not duplicated. So I dont have a clue
why the data is displayed in duplicates. Any comments on y?amatuer
How do you expect to get a right answer without posting some data to be
tested and an expected result.?
Take a look at DISTINCT , GROUP BY clause in the BOL
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||here's the table:
CREATE TABLE [dbo].[V_Monthreport] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PersID] [int] NULL ,
[DeptID] [int] NULL ,
[Datum] [datetime] NULL ,
[Maand] [int] NULL ,
[Jaar] [int] NULL ,
[Q_ID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_Monthreport_Q] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AfdelingID] [int] NULL ,
[Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QNo] [int] NULL ,
[Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
& data looks like this:
V_Monthreport_Q:
14 1 Agricultural Service Agricultural Information Services 1 Marketing
of available new services of the Department (List Opportunities)
15 1 Agricultural Service Agricultural Information
Services 2 Opportunity/Involvement in identifying and development of
new clients; products; projects
16 1 Agricultural Service Agricultural Information
Services 3 Involvement in sustaining and improvement of the level of
expertise, as well as the knowledge and skills
17 1 Agricultural Service Agricultural Information Services 4 Intra
and inter-departmental co-operation (disposition/view)
18 1 Agricultural Service Agricultural Information
Services 5 Agricultural condition/State of Agriculture: Climate
(temperature, wind, etc)
19 1 Agricultural Service Agricultural Information
Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
nutritional value)
20 1 Agricultural Service Agricultural Information
Services 7 Agricultural condition/State of Agriculture: Moisture status
21 1 Agricultural Service Agricultural Information
Services 8 Agricultural condition/State of Agriculture: Livestock
(conditions)
22 1 Agricultural Service Agricultural Information
Services 9 Agricultural condition/State of Agriculture: Commodity
prices
23 1 Agricultural Service Agricultural Information
Services 10 Agricultural condition/State of Agriculture: Labour
24 1 Agricultural Service Agricultural Information
Services 11 Agricultural condition/State of Agriculture: Security /
Safe keeping
25 1 Agricultural Service Agricultural Information Services 12 Client
Relations
26 1 Agricultural Service Agricultural Information Services 13 Threats
/ Concerns
V_Monthreport:
53 4 1 6/21/2006 6 2006 1 Test1
54 4 1 6/21/2006 6 2006 2 Test2
55 4 1 6/21/2006 6 2006 4 Test4
56 4 1 6/21/2006 6 2006 5 Test5
57 4 1 6/21/2006 6 2006 6 Test6
58 4 1 6/21/2006 6 2006 8 Test8
59 4 1 6/21/2006 6 2006 9 Test9
60 4 1 6/21/2006 6 2006 10 Test10
61 4 1 6/21/2006 6 2006 11 Test11
62 4 1 6/21/2006 6 2006 12 Test12
63 4 1 6/21/2006 6 2006 13 Test13
Sorry but this was the best i could.see if you cn help Uri
Dimant.thanx.
Uri Dimant wrote:
> amatuer
> How do you expect to get a right answer without posting some data to be
> tested and an expected result.?
> Take a look at DISTINCT , GROUP BY clause in the BOL
>
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> > Code below:
> >
> > If Session("ID") = 1 Or Session("ID") = 2 Then
> > select case (request.form("Individual"))
> > case "Individual" sql = "Select V_Monthreport.Q_ID,
> > V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> > JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> > V_Monthreport.PersID=" & session("Pers_ID") & " And
> > V_Monthreport.DeptID=" & request.form("SectID") & " And
> > V_Monthreport.Maand=" & request.form("Maand") & " And
> > V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> > V_Monthreport.Q_ID"
> > case "Section"
> > case "Summary"
> > end select
> > End If
> >
> > <% while not rstMain.eof %>
> > <tr>
> > <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> > %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Question") %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Answer") %></font></td>
> > </tr>
> > <% rstMain.movenext
> > wend%>
> > <% End If %>
> >
> > I am experiencing a problem in that the data when displayed is
> > duplicated. The data in the db is not duplicated. So I dont have a clue
> > why the data is displayed in duplicates. Any comments on y?
> >|||No so much
INSERT INTO Table.. VALUES (...
Can you provide a full DDL?
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150883115.391130.319520@.p79g2000cwp.googlegroups.com...
> here's the table:
> CREATE TABLE [dbo].[V_Monthreport] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PersID] [int] NULL ,
> [DeptID] [int] NULL ,
> [Datum] [datetime] NULL ,
> [Maand] [int] NULL ,
> [Jaar] [int] NULL ,
> [Q_ID] [int] NULL ,
> [Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[V_Monthreport_Q] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AfdelingID] [int] NULL ,
> [Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QNo] [int] NULL ,
> [Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> & data looks like this:
> V_Monthreport_Q:
> 14 1 Agricultural Service Agricultural Information Services 1 Marketing
> of available new services of the Department (List Opportunities)
> 15 1 Agricultural Service Agricultural Information
> Services 2 Opportunity/Involvement in identifying and development of
> new clients; products; projects
> 16 1 Agricultural Service Agricultural Information
> Services 3 Involvement in sustaining and improvement of the level of
> expertise, as well as the knowledge and skills
> 17 1 Agricultural Service Agricultural Information Services 4 Intra
> and inter-departmental co-operation (disposition/view)
> 18 1 Agricultural Service Agricultural Information
> Services 5 Agricultural condition/State of Agriculture: Climate
> (temperature, wind, etc)
> 19 1 Agricultural Service Agricultural Information
> Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
> nutritional value)
> 20 1 Agricultural Service Agricultural Information
> Services 7 Agricultural condition/State of Agriculture: Moisture status
> 21 1 Agricultural Service Agricultural Information
> Services 8 Agricultural condition/State of Agriculture: Livestock
> (conditions)
> 22 1 Agricultural Service Agricultural Information
> Services 9 Agricultural condition/State of Agriculture: Commodity
> prices
> 23 1 Agricultural Service Agricultural Information
> Services 10 Agricultural condition/State of Agriculture: Labour
> 24 1 Agricultural Service Agricultural Information
> Services 11 Agricultural condition/State of Agriculture: Security /
> Safe keeping
> 25 1 Agricultural Service Agricultural Information Services 12 Client
> Relations
> 26 1 Agricultural Service Agricultural Information Services 13 Threats
> / Concerns
> V_Monthreport:
> 53 4 1 6/21/2006 6 2006 1 Test1
> 54 4 1 6/21/2006 6 2006 2 Test2
> 55 4 1 6/21/2006 6 2006 4 Test4
> 56 4 1 6/21/2006 6 2006 5 Test5
> 57 4 1 6/21/2006 6 2006 6 Test6
> 58 4 1 6/21/2006 6 2006 8 Test8
> 59 4 1 6/21/2006 6 2006 9 Test9
> 60 4 1 6/21/2006 6 2006 10 Test10
> 61 4 1 6/21/2006 6 2006 11 Test11
> 62 4 1 6/21/2006 6 2006 12 Test12
> 63 4 1 6/21/2006 6 2006 13 Test13
> Sorry but this was the best i could.see if you cn help Uri
> Dimant.thanx.
>
> Uri Dimant wrote:
>> amatuer
>> How do you expect to get a right answer without posting some data to be
>> tested and an expected result.?
>> Take a look at DISTINCT , GROUP BY clause in the BOL
>>
>>
>> "amatuer" <njoosub@.gmail.com> wrote in message
>> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
>> > Code below:
>> >
>> > If Session("ID") = 1 Or Session("ID") = 2 Then
>> > select case (request.form("Individual"))
>> > case "Individual" sql = "Select V_Monthreport.Q_ID,
>> > V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
>> > JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
>> > V_Monthreport.PersID=" & session("Pers_ID") & " And
>> > V_Monthreport.DeptID=" & request.form("SectID") & " And
>> > V_Monthreport.Maand=" & request.form("Maand") & " And
>> > V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
>> > V_Monthreport.Q_ID"
>> > case "Section"
>> > case "Summary"
>> > end select
>> > End If
>> >
>> > <% while not rstMain.eof %>
>> > <tr>
>> > <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
>> > %></font></td>
>> > <td><font face="Tahoma" size="-2" color="Black"><%=>> > rstMain("Question") %></font></td>
>> > <td><font face="Tahoma" size="-2" color="Black"><%=>> > rstMain("Answer") %></font></td>
>> > </tr>
>> > <% rstMain.movenext
>> > wend%>
>> > <% End If %>
>> >
>> > I am experiencing a problem in that the data when displayed is
>> > duplicated. The data in the db is not duplicated. So I dont have a clue
>> > why the data is displayed in duplicates. Any comments on y?
>> >
>|||> The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
Do you get duplicates when you run the same query from Query Analyzer? The
same rows will be returned multiple times when more than one row exists in
both tables with the same value in the joined columns.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||Thanx Dan, i sorted it out. The prob was that the inner join was not
unique.Anothr one had to be created in order not to yield more then the
required possible results.
Dan Guzman wrote:
> > The data in the db is not duplicated. So I dont have a clue
> > why the data is displayed in duplicates. Any comments on y?
> Do you get duplicates when you run the same query from Query Analyzer? The
> same rows will be returned multiple times when more than one row exists in
> both tables with the same value in the joined columns.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> > Code below:
> >
> > If Session("ID") = 1 Or Session("ID") = 2 Then
> > select case (request.form("Individual"))
> > case "Individual" sql = "Select V_Monthreport.Q_ID,
> > V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> > JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> > V_Monthreport.PersID=" & session("Pers_ID") & " And
> > V_Monthreport.DeptID=" & request.form("SectID") & " And
> > V_Monthreport.Maand=" & request.form("Maand") & " And
> > V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> > V_Monthreport.Q_ID"
> > case "Section"
> > case "Summary"
> > end select
> > End If
> >
> > <% while not rstMain.eof %>
> > <tr>
> > <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> > %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Question") %></font></td>
> > <td><font face="Tahoma" size="-2" color="Black"><%=> > rstMain("Answer") %></font></td>
> > </tr>
> > <% rstMain.movenext
> > wend%>
> > <% End If %>
> >
> > I am experiencing a problem in that the data when displayed is
> > duplicated. The data in the db is not duplicated. So I dont have a clue
> > why the data is displayed in duplicates. Any comments on y?
> >

Data Conversion Issue

Hi,

I have a simple query that does the following :-

select desc1,desc2,desc3,desc4,desc5 from testdata

So I select the data for the above

What I would like to achieve without having to go to great lengths the
following:-

So taking the column desc1 I want to insert this into a table

e.g.

Desc1 is record 1 in the table
Desc2 is record 2 in the table
Desc3 is record 3 in the table
Desc4 is record 4 in the table
and so on

Is there a tool or a special type declaration that can be used ?

Regards
Andrew[posted and mailed, please reply in news]

Info (info@.schnof.co.uk) writes:
> I have a simple query that does the following :-
> select desc1,desc2,desc3,desc4,desc5 from testdata
>
> So I select the data for the above
> What I would like to achieve without having to go to great lengths the
> following:-
> So taking the column desc1 I want to insert this into a table
> e.g.
> Desc1 is record 1 in the table
> Desc2 is record 2 in the table
> Desc3 is record 3 in the table
> Desc4 is record 4 in the table
> and so on

I'm afraid that I don't really understand what you are asking for. Could
you provide the following:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired result from this sample data.

The reason I ask for this is that it clarifies your question, and with
the script it's possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Data Change

Can anybody suggest the best way I can achieve the following.
To select anybody whose surname has change in the last week, and to automatically flag a code field with "C". :cool:Hi,

Does your table have date field which is updated everytime a user updates the table?|||Yes I have found that it has a Modifcation Date field.|||Is there anyway to tell whether it is the surname that has been updated? Or could it be another field?|||Are you looking for a general solution, or is this a one-time issue?

For a general solution, you should use a trigger to stamp a datefield anytime a surname is modified.

For a one-time solution, restore a backup of your table from a week ago under a different name, and then join them on their primary keys to compare surnames.

....you do have weekly backups, right?|||if you have no record of this information,(the prior updating of the surname) you will need to create a audit column or and audit table then you can create a trigger that will indicate as such from here on out.

here are some {books online} articles you might want to look at.

Create Trigger
Alter Trigger
Programming Triggers

data caching on SQL Server?

Suppose 40 winform clients of SQL Server(SS) 2005 request the same data (select * from sometable).

Is there any proxy service in SS or all clients will be queueing/competing for the same answer?

Hi,

I think its the dot net that do the caching not Sql server.

http://www.codeproject.com/csharp/webservicecache.asp

regards,

joey

|||

Different winform clients are on different machines with different .NET frameworks generally without any knowledge about each other connecting to one central MS SQL Server db?

So, each winform client may cache data to avoid repeting the request to SQL Server... according to your link. Or it is done by ASP.NET/IIS (for webforms)

But the question is about caching by SQL Server, to avoid the server to service the same results to different WINFORM clients.

Since SQL Server 2005 is integrated with .NET, it is quite logical to expect such proxy service from SQL Server or .NET framework whith which MSS2005 is integrated with, isn't it?

Is it available by MS SQL Server 2005? how it is called? or I should create it?

|||

hi,

you might want to create a 'view' or an 'indexed view'

if the query is not parameterized or a stored procedure if it is parameterized

if you're into a very fast performance i'll recommend index view though you

will be penalized for disk space.

if its is a stored procedure execution is cached not the the result .

the link i gave you was caching the data by having winforms to consume

a webservice which stores the cached dataset

regards,

joey

|||

joeydj , Thanks for your guidance

Having rephrased my doubt more concisely:
if ASP.NET server data webcontrols permit declarative specification for caching data (on server side), I do not see why winforms controls have not the ability to do the same, say, through one of MS SQL Server2005 configurable and built.in (web)sevice...

|||

In my question I really wanted to ask whether the MSSSdb engine needs to compute/execute data according query again (even according to compiled/cached plan) and whether it repeats I/O operations in order to to get the same data structures.

This is rather important to know in order to make decisions on where to customly cache (on server or client), when, for how long and how much data.

Reporting Services has cached reports.
Really(and obviously) there are data buffers in memory. Also one can explicitly place data structures into memory [1]

Code Snippet

--Use the pubs database USE pubs DECLARE @.dbid INTEGER SET @.dbid = DB_ID('pubs') --Determine id number for the dbo.authorstable DECLARE @.obid INTEGER SET @.obid = OBJECT_ID('dbo.authors') --Pin the dbo.authors table to memory DBCC PINTABLE (@.dbid,@.obid) GO

Though details how to control memory management are not very explicit in docs

[1]
Introduction into Caching in SQL Server 2000

http://www.extremeexperts.com/SQL/Articles/SQLCacheObjects.aspx

|||

Well, after all there is direct match to my question.

ADO.NET 2.0 (SQL Server 2005) supports

1)
server-side cursors

ExecuteResultSet of SqlResultSet

2)

Paging

ExecutePageReader(CommandBehavior.Default, nStartRow, nPageSize);

3)

Asynchronous execution of commands

4)

MARS - Multiple Result Sets

Data by ID and Getdate function

I am trying to return data by date but I also need to return data by date "and" by ID. I've queried various select statements but always get only one days worth of data and the date is repeated many times. I only want to return data for today only but with ID as the other variable.

For example I want only the shows for today by date and by ID. ID of course being the key in the DB. Below I will show you a code block followed by a text version of what it looks like in the browser when tested.

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Kuow\Kuow.UDL"
set recProgram = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101))"

'strSQL = "SELECT *, Air_Date AS Expr1, Unit AS Expr2 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) AND (Unit = 'TB')"
recProgram.Open strSQL,con
%>

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Output:
ID Unit Subject Title Long_Summary Body_Text Related_Events Air_Date AudioLink

(Reading across the screen from left to right)
1234 WK1 Subject Title a summary some body text Event text 4/13/2007 wkdy20070413-a.rm

Here is the URL used for testing:
http://Test Server IP/test/defaultweekday2.asp

I need to be able to append to this URL an ID number so that not only do I get content by Air_Date but also by ID.

http://Test Server IP/test/defaultweekday2.asp?ID=1234

How to do this?

You might want to look in Books Online about the usage of GROUP BY.

Code Snippet

GROUP BY convert( varchar(10), getdate(), 101 )) , Unit

|||Ok I will do that but for now is your example a working example? If not what other examples could I try?|||

It 'should' work IF you change the SELECT to

"SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

|||I tested your suggested by replacing the second half of my select query with your code starting with WHERE...

When I tested it I got this

Microsoft OLE DB Provider for SQL Server error '80040e14'

GROUP BY expressions must refer to column names that appear in the select list.

/test/defaultweekday2.asp, line 24

Code Snippet

strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

|||Ok I see what your getting at however I just tested it in my browser and got this:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near ')'.

/test/defaultweekday2.asp, line 22

Code Snippet

strSQL = "SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

I counted the ( ) to make sure there was the correct number of left and rights ones. Does it not like the end of the line or what?|||

As the error message indicates (and you might want to read up on using GROUP BY), any column in the GROUP BY MUST also be in the SELECT list.

Your GROUP BY includes Unit, and the SELECT list does not.

I think that the query I posted earlier 'should' work. But this alteration will not.

GROUP BY requires ALL columns in the SELECT list to EITHER be in the GROUP BY clause, or be aggregations. Trying to select all columns with a [SELECT * ] will not work.

I suggest that you start out small, perhaps with the query that I posted earlier, try to understand how GROUP BY works, and then expand your query a small piece at a time.

|||Arnie -

Sounds good. I'm reading up on it now as I learn and thank you for your most recent reply. Your thinking is helping me think. I'm sure it's obvious I'm new to SQL. I'll be glad when I get good enough that I can provide the people I work for and with the answers they seek in a relatively quick turnaround.

There is nothing worse then having work that is over your head and your figuring out how to do it while your solving real world business problems. Can be stressful. But hey it's one way to ensure I'll remember it.

Data access in a variable

Hello Friends

i have one problem, i have to need fatch the data from database .

in the web form i take three grid view and i put the query "Select Top 1 coloum1 from tanlename order by newid()" .

when the data came from database there is no sequence series . so i want to take fatch the data from database in variable like int a , b , c and call the data in those variable

and put up in the feild

example :- welcome to Mr "Data call from data base (1) " how are you "Data call from data base (2)" bbye and "Data call from data base (3)"

Answer "- welcome to Mr "ASHWANI" how are you FINE OR NOT" bbye and "TAKE CARE"

there is all capslock on value came from databse and these value in the web page store in a variable

like int a ;

int b;

int c;

please help me please i have a huge problem

Ashwnai

Hi ashwani2kumar,

Not 100% sure what you mean. How do you fetch those data from your database? what does you select query look like?

welcome to Mr "Data call from data base (1) " how are you "Data call from data base (2)" bbye and "Data call from data base (3)"

Answer "- welcome to Mr "ASHWANI" how are you FINE OR NOT" bbye and "TAKE CARE"

If your string is fixed, i mean, those "welcoem to, how are you ..etc" stuff are fixed, i think you can assemble that string within a stored procedure, like this:

create procedure sp_string par1_defination,par2_defination,// i don't know how do you fetch those data, so, fill in those query parameters by yourself@.ret_str varchar(200) outputasdeclare @.str1 varchar(20),@.str2 varchar(20),@.str3 varchar(30)select @.str1=Namefrom table1 where// your query 1select @.str2=colnamefrom table2 where// your query 2select @.str3=colnamefrom table3 where// your query 3set @.ret_str='welcome to Mr'+@.str1+'how are you'+@.str2+'bbye and'+@.str3return

Hope my suggestion helps

cycling through results of a select statement

I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.

So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @.nameofcompany. @.nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.

Like I said I am new and this is probably very simple but i need a little help getting started

thanksSELECT *
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)

But you wouldn't have to do that if you defined the table like

CREATE TABLE myTable99(Company_Name varchar(50) UNIQUE)

EDIT: Where in Jersey? And what school?|||I am originally from Vernon (Mountain Creek). Went to school at Stevens Institute of Technology in Hoboken, NJ.

I didn't create the dll for this database so i just loaded the schema by the .sql file. Right now I am handed an excel template and i wrote somce vb code to go through that excel file pull out the information i want and then write it to a text file delimited with "#" and then i load it into SQL server with a bulk load command. If the user sends me a template that already has duplicate entries in it and i try to load the data into SQL column that has a unique indentifier what will happen? Will it throw an error? If this is the case then it would probably be better to get the data in the database and then decided whether or not it is a duplicate.
Redefining the table seems like the simplest way to go but i don't want to break functionality in the process

thanks|||I just tried to add this code and it is complaining on the second line in reference to the o. Here is the error: Error 170: Line 2: incorrect syntax near 'o'. As I said i am new to stored procedures and T-SQL do i need to declare the o and i as variables somewhere?|||I didn't test the code, so you gave me a start...but the code does work...

Where are you running this from? Do you have query analyzer and the other sql server client toools?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Company_Name varchar(50))
GO

INSERT INTO myTable99(Company_Name)
SELECT 'Vernon Valley' UNION ALL
SELECT 'Mountain Creek' UNION ALL
SELECT 'Hidden Valley' UNION ALL
SELECT 'Campgaw' UNION ALL
SELECT 'Break Neck Road' UNION ALL
SELECT 'High Point' UNION ALL
SELECT 'Octogon Lounge' UNION ALL
SELECT 'Great Gorge' UNION ALL
SELECT 'Mountain Creek'
GO

SELECT *
FROM myTable99 o
WHERE EXISTS ( SELECT Company_Name
FROM myTable99 i
WHERE o.Company_Name = i.Company_Name
GROUP BY Company_Name
HAVING COUNT(*) > 1)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sorry i am an a** i have an extra space floating in there. Man i am an idiot|||Hey...you're from Jersey...never apologize

Customizing Report Parameter control; handling "available values"

I have a report that I show a bill of material based on a selected
parent. I have about 17,000 parents to select from. When I use
"available values from query" the current behavior on the report is a
drop down box where I can choose between all 17,000 where at least for
large collections I'd prefer being able to start typing in and have the
control filter the query based on what's typed into the parameter. Is
there a way to do this?Hi Benjamin.
I believe what you're looking for can be accomplished with a custom
parameter page. There is no way I know of with reporting services to
provide narrowing filter functionality out of the box.
"Benjamin Chan" <benjamin.no.spam.chan@.controlproductsinc.com> wrote in
message news:u1CkX$$gGHA.5096@.TK2MSFTNGP02.phx.gbl...
>I have a report that I show a bill of material based on a selected parent.
>I have about 17,000 parents to select from. When I use "available values
>from query" the current behavior on the report is a drop down box where I
>can choose between all 17,000 where at least for large collections I'd
>prefer being able to start typing in and have the control filter the query
>based on what's typed into the parameter. Is there a way to do this?

Customizing replication

Hi,
Is it possible to replicate some field from multiple tables (as a result of select query with joins) at source to a single table at target ?
For example i have two tables at source 'source_table1' and 'source_table2' and one table at target namely 'target_table'. Now i want Field1 from 'source_table1' and field1 from 'source_table2' to be replicated into 'target_table'.
pictorial depiction of behavious i need is as under:
Tables at Source :
Table 1 Table at Target:
\ _________ Table
/
Table 2
1) Is is possible using SQL Server replication ? If not is there any workaround ?
2) Is is a good practice to replicate GBs of data from source to target over internet with security being an issue ?
Thanks in advance,
Hatim Ali.
1) it sure is, you create a custom sync object. The problem is that the log
reader can generate sql statements or insert procs based on updates of a
single object.
So one your publication or article will have to key off one table, and
probably replicate the second table as well so your custom proc can read
this data and merge it to the target table.
The simple way of doing this is using an indexed view.
2) To be secure you should use a VPN. Replicating Gbs of data can be
difficult, but it can be done. Security is a matrix of risk and liability.
Risk is small using FTP, anonymous authentication, and only allowing a range
of IP addresses to download your snapshot, but the liability can be
signficant.
I have had a client who couldn't care less if you snag their data because it
has a very high time value (they were a news agency). Then I have financial
clients and if I mention the word FTP I loose all credibility - not that I
have much to begin with mind you
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
news:99A07D96-B98C-41CC-947D-05F35FB8E018@.microsoft.com...
> Hi,
> Is it possible to replicate some field from multiple tables (as a result
of select query with joins) at source to a single table at target ?
> For example i have two tables at source 'source_table1' and
'source_table2' and one table at target namely 'target_table'. Now i want
Field1 from 'source_table1' and field1 from 'source_table2' to be replicated
into 'target_table'.
> pictorial depiction of behavious i need is as under:
> Tables at Source :
> Table 1 Table at Target:
> \ _________ Table
> /
> Table 2
> 1) Is is possible using SQL Server replication ? If not is there any
workaround ?
> 2) Is is a good practice to replicate GBs of data from source to target
over internet with security being an issue ?
> Thanks in advance,
> Hatim Ali.
>
>