PROBLEM WITH bcp

Hello!!

I have a problem with bcp in Query Analyzer.
The sentence is:

bcp SELECT id_categoria from categorias QUERYOUT 'c:\salida.txt' -c
-Ulogin -Ppassword

With this sentence, the error is:
Line 1: Incorrect syntax near 'c:\SADD.txt'.

PLEASE, I NEED HELP.
THANKSthis command works fine..

bcp "SELECT * from pubs..authors" QUERYOUT "C:\salida.txt" -c -S<server name> -Usa -P<password>

database name, server name and quotes around the query are missing from ur BCP command also the file path should be in full.|||hmmm, doesn't work for me, has a problem around QUERYOUT
strange.

i'll keep looking|||This is true!!, the error now is:
Line 1: Incorrect syntax near 'queryout'.

bcp "SELECT id_categoria from categorias" queryout "C:\salida.txt" -c -S<server_name> -U<login_id> -P<password>|||It works via the OS command line.|||master..xp_cmdshell 'bcp "SELECT * from pubs..authors" QUERYOUT "C:\salida.txt" -c -S<server name> -Usa -P<password>'sql

Problem with arithmetical calculations

Hello, everyone.

I have some experience working with T-SQL, but now I am faced with problem which I never seen before.

I have query:

SELECT2/(9+1)*1000

This query returns 0, but it must return 200.

I have tried this in SQL 2000 and SQL 2005, but result is the same.

What is wrong?

B.R.

Girts

I have found solution for this problem - it looks very simple and stypid :

SELECT 2/(9*1.0+1)*1000

|||

It's very simple, yes, but stupid? Not at all.

Have you thought about what you're calculating?
How much is 2 divided by 10?
How would one write 0.2 using only integers..? You can't, so it's rounded down to 0.

So your real problem is not how it's calculated, but what datatypes are used to do the calculation. When you add the decimalpoint as you found out, it's no longer done by all integers, but with decimals instead, so the rounding doesn't have to happen.
Thus you get the expected result. =:o)

You can find out more in BOL if you look for 'datatypes' and 'datatype precedence'

=;o)
/Kenneth

|||

Well, this is kind of wierd looking (the bold):

SELECT 2/(9*1.0+1)*1000

But like KeWin is saying it does the trick because it causes the 1.0 to be converted to a numeric value. The same effect can be achieved by:

SELECT 2.0/(9+1)*1000

You really have to be careful to consider the datatype at each step of the evaluation of the equation.

Integer + Integer = Integer
Integer / Integer = Integer

select 1/2 = 0 --because in integer math, you truncate the result, not round
select 1%2 = 1 --% is the mod function, gives you the remainder of the operation
--mod is essential when working with integers

Now consider:

select 1.0 / 1 = 1.000000

Numeric / Integer = Numeric

A trick to use to see the exact type is to use a sql_variant dataype:

declare @.value sql_variant
set @.value = 1.0 / 1

--give precision for integer even though not technically part of datatype
select cast(sql_variant_property(@.value,'baseType') as varchar(10))+
'(' + cast(sql_variant_property(@.value,'precision') as varchar(10))+ ',' +
cast (sql_variant_property(@.value,'scale') as varchar(10)) + ')'

select @.value

Using this query you can see what type is chosen for the output of an expression, either a scalar value, or a mathematic equation.

For the 1.0 / 1 before, this returns:

1.000000

SQL Server chooses a datatype that will always be able to store the result of the equation safely with no loss of precision if possible.

The point is that you have to be really careful with math because one little mess up on a type and your numbers are meaningless:

select (1/2) * 5000.00
select (1.0/2.0) * 5000

the first one is 0, because 1/2 returns integer 0 * numeric 5000.00 = 0.00

the second one 2500.000000, since 1.0/2.0 returns numeric(8,6) 0.500000 * 5000

Hopefully this is a bit clear. Datatype conversion can be tricky. In BOL (for 2005), look up Data Types [SQL Server]; Converting. There is an implicit conversion char to tell you what types will convert to what types as needed.

Problem with ADO and MSSQL

We have just changed all of our DB functionality from the BDE with the standard component query to ADO with the ADOquery. The follow doesnt work anymore now :

qRooms->Close();
qRooms->SQL->Clear();
qRooms->SQL->Add("Select * from Billeting where (SDATE >=:startdate and");
qRooms->SQL->Add("SDATE <=:enddate) or (EDATE >=:startdate and");
qRooms->SQL->Add("EDATE <=:enddate) or (SDATE <:startdate and");
qRooms->SQL->Add(" :startdate < EDATE) order by BLDG");
qRooms->Parameters->ParamByName("startdate")->Value = StrToDate(VOQDate);
qRooms->Parameters->ParamByName("enddate")->Value = (StrToDate(VOQDate) + 14);
qRooms->Open();

Why doesnt this work? What am I doing wrong?What programming environment are you using ?|||Borland C++ Builder 6|||I am not familiar with bc++ 6 builder - but does it matter that no spaces exist between the 1st and 3rd add methods (I put # where a space was lacking) ?

qRooms->SQL->Add("Select * from Billeting where (SDATE >=:startdate and #");
qRooms->SQL->Add("# SDATE <=:enddate) or (EDATE >=:startdate and #");
qRooms->SQL->Add("# EDATE <=:enddate) or (SDATE <:startdate and");|||What error are you getting?

Is it a runtime error, compile error - do you get an error at all..

More details please.

Stefan|||No errors, I just don't get all the data back. I run that in SQL analyzer and I get all the correct data back, but in Builder I only get some of the data back.sql

Problem with a where clause

I'd like to set the where clause of my query from a field in a table
ex:
Select TableX.field1
from tableX, TableY
where TableY.field2
Field2 is equal to "fieldW like '%blabla%' "
Thanks for your helpDynamic SQL:
DECLARE @.sqlStmt VARCHAR(2000)
DECLARE @.whereClause VARCHAR(500)
SELECT @.whereClause = field2
FROM tableY
WHERE {tableY.key_column} = {value}
SELECT @.sqlStmt = 'SELECT tableX.field1 FROM tableX, tableY WHERE '
SELECT @.sqlStmt = @.sqlStmt + @.whereClause
EXEC (@.sqlStmt)
Be careful about SQL Injection using this method.
"Epervier" <Epervier@.discussions.microsoft.com> wrote in message
news:D8F4A0A0-37CE-4FA5-B9CC-771BCE61B5FF@.microsoft.com...
> I'd like to set the where clause of my query from a field in a table
> ex:
> Select TableX.field1
> from tableX, TableY
> where TableY.field2
> Field2 is equal to "fieldW like '%blabla%' "
> Thanks for your help

problem with a view

We have a view called "vwtblBranchData" which is this: "SELECT *
FROM Branch_Master.dbo.tblBranchData"
We have a query that uses the view that has worked for over a year and now
has stopped working. The query is: select * from vwtblBranchData with
(readuncommitted) where (closed=0) and branch_num between 2000 and 2939 orde
r
by branch_num.
If I run just "select * from vwtblBranchData" or "select * from
vwtblBranchData where branch_num between 2000 and 2939 order by branch_num",
it works but when I add the "closed=0" part, it won't work. The closed colum
n
is a bit and is populated with only 0 or 1 in the table.
If I take the view out and run: "select * from branch_master..tblBranchData
with (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order by branch_num", it works.
Any idea what is happening?
Thanks,
Dan D.I put the query - "select * from vwtblBranchData where closed = 0 and
branch_num between 2000 and 2939 order by branch_num" in the query analyzer
and saw this: WHERE (([tblBranchData].[ActivePest]=0 AND
Convert([tblBranchData].[Branch_num])<=2939 AND
Convert([tblBranchData].[Branch_num])>=2000) ORDERED FORWARD
The "ActivePest" column isn't in the query or the view. Any idea where that
came from?
"Dan D." wrote:

> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939 or
der
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by branch_num
",
> it works but when I add the "closed=0" part, it won't work. The closed col
umn
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from branch_master..tblBranchDat
a
> with (readuncommitted) where (closed=0) and branch_num between 2000 and 29
39
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Could you please tell us the error you are getting? At this point we don't
even know what is happening on your side :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
> order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
> branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
> column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
> branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
> 2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Suspect a schema or metadata change. Try recreating the view. However, the
best approach is to specify the columns you want to select, rather than use
the wildcard.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Sorry. The query wasn't returning any rows when it should have. I solved the
problem. The query analyzer was trying to use the "active_pest". We didn't
need the column so I removed it and the query worked.
Thanks.
"Louis Davidson" wrote:

> Could you please tell us the error you are getting? At this point we don'
t
> even know what is happening on your side :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
>
>|||I solved the problem. The query analyzer was trying to use the "active_pest"
.
We didn't need the column so I removed it and the query worked.
Thanks.
"Scott Morris" wrote:

> Suspect a schema or metadata change. Try recreating the view. However, t
he
> best approach is to specify the columns you want to select, rather than us
e
> the wildcard.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> order
> branch_num",
> column
> branch_master..tblBranchData
> 2939
>
>sql

Problem with a query using MAX(ID)

I have a query regarding and I think I need to use max ID to get round it.I am creating a query and I need to get the Home Telephone number (from table 3).The problem is that there may be more than one home telephone nuber so I want to get the lastest one (highest ID).

I need to display all the person id from table along with their home no even if it is null.I’m not sure how to get round it whether I do a subquery joining the tables or whether I do a select case.

Please help

Table 1

Person ID

111

112

113

114

207

Table 2

PersonIdID

1110122

1110123

1120124

1130125

2070126

2070127

Table 3

IDTel_noType

01220125 23223Hone

01230122 43533Home

01240122 444111Mobile

0125077747474Mobile

012601222747474Home

012701232484848Home

Result

Person IDHome No

1110122 43533

112NULL

113NULL

114NULL

20701232 484848

use the following query...

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

X.PersonId

,Tel_No

,Y.ID

,Type Into #Temp

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select Home.PersonId,Home_No,Mobile_No from

(

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Home

Join

(

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Mobile

On Home.PersonId=Mobile.PersonId

|||Here are two versions, one for SQL Server 2005 and later, and one for SQL Server 2000 and earlier:

-- Requires SQL Server 2005 or later
with TPRanked as (
select
T1.PersonId,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

-- SQL Server 2000 or 7.0
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and T2a.ID > T2.ID
)

Steve Kass
Drew University
http://www.stevekass.com
|||I have just tried the above query in SQL but the problem is that it will show a NULL value if a mobile no has a higher ID than a Home no.|||

I need to do the query without creating any temporary tables.

|||

Ok.. here it is..

Note: If you use temp table then you can increase the performance

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) as D Group By PersonId) A

Left Outer Join (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) B On A.PersonId=B.PersonId And A.id=B.ID

|||I think I have a corrected query for SQL Server 2005. I changed the ranking criteria so that 'Home' numbers are always ranked highest. I'm working on the 2000 query. Obviously this needs careful testing!

with TPRanked as (
select
T1.PersonId, T2.ID,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by
case when T3.Type = 'Home' then 0 else 1 end,
T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

SK
|||Here's a correction for SQL 2000 along with another query that takes a different approach. The different approach doesn't generalize very well, but it's much more concise.

-- Quick and dirty
select
T1.PersonId,
(
select top (1) Tel_no
from #table2 as T2
join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where T2.PersonId = T1.PersonId
order by T3.ID desc
) as Tel_no
from #table1 as T1

-- Second try, first approach
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'

where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and (
(T3a.Type = 'Home' and (T3.Type is null or T2a.ID > T2.ID)) or
(T3.Type is null and T2a.ID > T2.ID)
)
)

SK

Problem with a query

Hi, I have a problem with SQL Server 2000:

this query, who works on the table "Flussi_Rivendite" which contains more or less 700.000 rows, doesn't work speedly:

SQL = "SELECT SUM(Quantità) AS Quantità " _

& "FROM Flussi_Rivendite WHERE " _

& "DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& "ID_AnagraficaRivendita IN " _

& "(SELECT AnagraficaRivendite.ID_AnagraficaRivendita FROM AnagraficaRivendite WHERE " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente") & ")"

The problem is in last part of query:

& "ID_AnagraficaRivendita IN " _

& "(SELECT AnagraficaRivendite.ID_AnagraficaRivendita FROM AnagraficaRivendite WHERE " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente") & ")"

ID_Agente is a foreign key of another table ("AnagraficaRivendite" which contains 60.000 rows)

I have seen I can improve "waiting time" of this query if I add "ID_Agente" field at table Flussi_Rivendite, so I can transform initial query in other query simpler:

SQL = "SELECT SUM(Quantità) AS Quantità " _

& "FROM Flussi_Rivendite WHERE " _

& "DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& "ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente")

I would ask you if it is correct to add column ID_Agente on the table Flussi_Rivendite. Until today I have always avoided to do this, I have always filtered my table by query like: "....IN (SELECT FIELDS FROM TABLE WHERE ecc...)".

But now, these tables have many many records and I have found only this solution to improve the query (like I have just said, adding external key "ID_Agente" directly on table "Flussi_Rivendite". Sorry for my english, I hope someone can suggest something me ;)

Hi Maurodii

I like the table names -- some beautiful language!

Did you try:

"SELECT SUM(Flussi_Rivendite .Quantità) AS Quantità " _& "FROM Flussi_Rivendite, AnagraficaRivendite WHERE " _& " Flussi_Rivendite.DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& " Flussi_Rivendite.ID_AnagraficaRivendita = AnagraficaRivendite. ID_AnagraficaRivendita " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente") & ")"

Hope this helps and Good Luck!

Fouwaaz

|||

IN can be a performance killer. Look into using EXISTS instead of IN.

|||

Hello, I have tried this but the performance is the same, unfortunately :(

SQL = "SELECT SUM(Flussi_Rivendite.Quantita) AS Quantita " _

& "FROM Flussi_Rivendite, AnagraficaRivendite WHERE " _

& "Flussi_Rivendite.DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& "Flussi_Rivendite.ID_AnagraficaRivendita = AnagraficaRivendite.ID_AnagraficaRivendita AND " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente")

Dear ndinakar, may you post me how you suggest me to do with the clause EXISTS? I have tried it, but I didn't make it :(

I remember you that my target is don't preserve column "ID_Agente" in table Flussi_Rivendite.

Thank you

|||

Try running the query directly in Query Analyzer or Management Studio and see how long its taking. Also I would recommend using >= and <= instead of using BETWEEN.

SELECTSUM(Flussi_Rivendite.Quantita)AS Quantita

FROM Flussi_Rivendite, AnagraficaRivendite

WHERE Flussi_Rivendite.DataFlusso>='20070101'AND Flussi_Rivendite.DataFlusso<='20071031'

AND Flussi_Rivendite.ID_AnagraficaRivendita= AnagraficaRivendite.ID_AnagraficaRivendita

AND AnagraficaRivendite.ID_Agente=<somevalue>

|||

I have changed clause Between like you suggested me but performance doesn't improve. Moreover, I have tried to run both query in Query Analyzer, I post here results:

Query N.1

SELECT SUM(Flussi_Rivendite.Quantità) AS Quantità
FROM Flussi_Rivendite, AnagraficaRivendite WHERE
Flussi_Rivendite.DataFlusso>=20070901 AND Flussi_Rivendite.DataFlusso<=20070931 AND
Flussi_Rivendite.ID_AnagraficaRivendita = AnagraficaRivendite.ID_AnagraficaRivendita AND
AnagraficaRivendite.ID_Agente=1

Flussi_Rivendite: Costs: 67% Anagrafica_Rivendite: Costs: 300%

Query N.2

SELECT SUM(Quantità) AS Quantità
FROM Flussi_Rivendite WHERE
DataFlusso BETWEEN 20070901 AND 20070931 AND
ID_Agente=1

Flussi_Rivendite: Costs: 0% Anagrafica_Rivendite: Costs: 0%

In my opinion when I directly filter ID_Agente in the same table (Query n.2) is best solution for me, also if database's structure will be a little more complicated. But it is too faster then Query n.1

|||

Do you have any indexes on DataFlusso column? or on AnagraficaRivendite.ID_Agente column? Having proper indexes is important for faster data retrieval otherwise SQL Server has to scan your entire table to get to the rows and then perform the computation.

|||

IMPORTANT IMPROVEMENT!!!!

I have built a new index like you suggested me in table Flussi_Rivendite: DataFlusso, ID_AnagragraficaRivendite, ID_Agente.

Now waiting time is pull down until 6/7 seconds!!!! It is good, also if with my alternative procedure waiting time it was 3/4 seconds. Thank you again, next monday I will come back in the office and I'll try it better (now I am working from my home by Remote Desktop and it's not easy...).

Have a good week end!!

ps: If you some indication to built/edit better this new index please tell me ;)

|||

Build this index on AnagraficaRivendite : ID_Agente, ID_AnagraficaRivendita

|||

Motley:

Build this index on AnagraficaRivendite : ID_Agente, ID_AnagraficaRivendita

I've already created.

Please note that if I change range of DataFlusso (for es. 20060101 and 20071031) waiting time increases again, but I think it's normal because so I'm asking for more records. I think I need a newer and faster processor... ;) or not?

|||

I think this is best solution if I don't add a column ID_Agente to table Flussi_Rivendite:

SELECT SUM(dbo.Flussi_Rivendite.Quantità) AS Quantità
FROM dbo.Flussi_Rivendite INNER JOIN
dbo.AnagraficaRivendite ON dbo.Flussi_Rivendite.ID_AnagraficaRivendita = dbo.AnagraficaRivendite.ID_AnagraficaRivendita
WHERE (dbo.Flussi_Rivendite.DataFlusso >= 20070101) AND (dbo.Flussi_Rivendite.DataFlusso <= 20070131) AND
(dbo.AnagraficaRivendite.ID_Agente = 2)

Bye

Problem with a query

I have 3 tables Workers, Event and Worker_Event_Persmissions.The workers table contains a list of workers, the events table contains a list of events – exam, assessments and tests, and finally the worker_event_permissions contains details about the permissions for each worker whether they can view, amend or check.

I want to create a select query that shows all events for each worker and the permissions assigned.I would like the role and event to be displayed even if there are no workerevent permissions setup.

Can anyone help?

Workers

RoleDescription

TeaTeacher

TutTutor

Event

TypeDescription

ExExam

AsmAssessment

TeTest

Worker_Event_Permissions

Role_TypeEvent_TypeViewAmendCheck

TeaExYNY

TeaAsmNNY

TutTeYYY

Query

TypeRoleViewAmendCheck

ExamTeacherYNY

AssessmentTeacherNNY

TestTeacherNULLNULLNULL

ExamTutorNULLNULLNULL

AssessmentTutorNULLNULLNULL

TestTutorYYY

here You go..

Code Snippet

Create Table #workers (

[Role] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #workers Values('Tea','Teacher');

Insert Into #workers Values('Tut','Tutor');

Create Table #event (

[Type] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #event Values('Ex','Exam');

Insert Into #event Values('Asm','Assessment');

Insert Into #event Values('Te','Test');

Create Table #worker_event_permissions (

[Role_Type] Varchar(100) ,

[Event_Type] Varchar(100) ,

[View] Varchar(100) ,

[Amend] Varchar(100) ,

[Check] Varchar(100)

);

Insert Into #worker_event_permissions Values('Tea','Ex','Y','N','Y');

Insert Into #worker_event_permissions Values('Tea','Asm','N','N','Y');

Insert Into #worker_event_permissions Values('Tut','Te','Y','Y','Y');

Query

Select WD,WD,[View],[Amend],[Check] From

(

Select

W.Role WR, E.Type ET,

W.[Description] WD,E.[Description]ED

from

#workers W Cross Join #event E

) as Data

Left Outer Join

#worker_event_permissions WEP

On WEP.Role_Type = Data.WR And WEP.Event_Type=Data.ET

problem with a clustered index?

Hello,
I have a table with about 5 milion records, and the performance is un-acceptable (more than 45 seconds for a query). The table was imported from a text file. I have defined a primary key (two varchar(20) columns). Then I checked and the database has created a clustered index on these columns. But I suspect that something is wrong with the index: When I run a select query, the rows are returned in an arbitrary order, and not in the order defined by the index. Is it possible that something is wrong with the index? If yes, how can I fix that?
thanks, David
David
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message news:um00pGXdEHA.3148@.TK2MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is un-acceptable (more than 45 seconds for a query). The table was imported from a text file. I have defined a primary key (two varchar(20) columns). Then I checked and the database has created a clustered index on these columns. But I suspect that something is wrong with the index: When I run a select query, the rows are returned in an arbitrary order, and not in the order defined by the index. Is it possible that something is wrong with the index? If yes, how can I fix that?
thanks, David
|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
David Portas
SQL Server MVP
|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
http://www.aspfaq.com/
(Reverse address to reply.)

problem with a clustered index?

Hello,
I have a table with about 5 milion records, and the performance is un-accept
able (more than 45 seconds for a query). The table was imported from a text
file. I have defined a primary key (two varchar(20) columns). Then I checked
and the database has created a clustered index on these columns. But I susp
ect that something is wrong with the index: When I run a select query, the r
ows are returned in an arbitrary order, and not in the order defined by the
index. Is it possible that something is wrong with the index? If yes, how ca
n I fix that?
thanks, DavidDavid
Did you spesify ORDER BY clause when you run SELECT statement.
What is your search criteria in the query?
"David" <dboaz@.bgumail.bgu.ac.il> wrote in message news:um00pGXdEHA.3148@.TK2
MSFTNGP10.phx.gbl...
Hello,
I have a table with about 5 milion records, and the performance is un-accept
able (more than 45 seconds for a query). The table was imported from a text
file. I have defined a primary key (two varchar(20) columns). Then I checked
and the database has created a clustered index on these columns. But I susp
ect that something is wrong with the index: When I run a select query, the r
ows are returned in an arbitrary order, and not in the order defined by the
index. Is it possible that something is wrong with the index? If yes, how ca
n I fix that?
thanks, David|||A clustered index does not determine the sort order of a query result, so
there is no reason to suspect any integrity problem. Use an ORDER BY clause
in your SELECT statement to fix the order of the returned rows.
Did you check the query plan to see what indexes are being used? Could you
post the query and a CREATE TABLE statement for the table to give us an idea
of what optimizations might be possible.
David Portas
SQL Server MVP
--|||> When I run a select query, the rows are returned in an arbitrary order
A table is an unordered set of rows. What were you expecting? If you want
a defined order, use an ORDER BY clause.
I don't know where everyone gets the idea that the existence of a clustered
index means that is the order all SELECT queries will return the data. This
is NOT TRUE! It does happen more often that way, but it is not a law. The
plan will return the rows in the best way it sees fit, you could run the
query 10 times and it *could* return the rows 10 different ways. Usually
doesn't, but could.
I'll repeat: if you want a specific order, use an ORDER BY clause.
http://www.aspfaq.com/
(Reverse address to reply.)sql

Problem with "select a, b, a+b from table" in SQL Server 2000

Using SQL Server 2000, I'm having a problem trying to derive a query field
from two other fields returned in the same query.
select
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id ),
[Total Computers] = [Num Laptops] + [Num PCs],
...
The erroneous line is the [Total Computers] line.
If I remove that, the query works fine and correctly returns the number of
laptops and pcs for each user.
How can I derive this field from the other fields returned in the query?
Any help most appreciated.
Mr Nice#laptops and #pc are calculated values that only exist at runtime. Thus,
they're not available for you to reference in the select to calc #total.
However, you can derive the whle select and the use the #laptops and #pc.
e.g.
select *,lp+pc
from (
select
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id )
from ...
where ..
) as derived
-oj
"Mr Nice" <no.spam@.thanks> wrote in message
news:420c6897$0$21947$cc9e4d1f@.news.dial.pipex.com...
> Using SQL Server 2000, I'm having a problem trying to derive a query field
> from two other fields returned in the same query.
>
> select
> [User - Name] = user.name,
> [User - ID] = user.id,
> [Num Laptops] = (select sum(...) where id = user.id ),
> [Num PCs] = (select sum(...) where id = user.id ),
> [Total Computers] = [Num Laptops] + [Num PCs],
> ...
> The erroneous line is the [Total Computers] line.
> If I remove that, the query works fine and correctly returns the number of
> laptops and pcs for each user.
> How can I derive this field from the other fields returned in the query?
> Any help most appreciated.
> Mr Nice
>|||Mr Nice wrote:
> select
> [User - Name] = user.name,
> [User - ID] = user.id,
> [Num Laptops] = (select sum(...) where id = user.id ),
> [Num PCs] = (select sum(...) where id = user.id ),
> [Total Computers] = [Num Laptops] + [Num PCs],
SELECT
[User - Name] = user.name,
[User - ID] = user.id,
[Num Laptops] = (select sum(...) where id = user.id ),
[Num PCs] = (select sum(...) where id = user.id ),
[Total Computers] = (select sum(...) where id = user.id ) + (select
sum(...) where id = user.id ),
However I'm sure that there is a way to do it without using as many
sub-queries. But that's my own personal pet peeve (I dislike subqueries
for some reason, that and in this case it loooks excessive).
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Please include DDL with your posts otherwise we can only guess what
your tables look like.
Given:
CREATE TABLE Users (user_id INTEGER NOT NULL PRIMARY KEY, user_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Assets (asset_number VARCHAR(20) NOT NULL PRIMARY KEY,
asset_type CHAR(2) NOT NULL CHECK (asset_type IN ('PC','LT')), user_id
INTEGER NOT NULL REFERENCES Users (user_id))
You could do this:
SELECT U.user_id, U.user_name,
COUNT(CASE WHEN asset_type = 'LT' THEN 1 END) AS num_laptops,
COUNT(CASE WHEN asset_type = 'PC' THEN 1 END) AS num_pcs
FROM Users AS U
LEFT JOIN Assets AS H
ON U.user_id = H.user_id
GROUP BY U.user_id, U.user_name
David Portas
SQL Server MVP
--

Problem with "Compile Error"

I got some problem about message "Compile Error, In Query expression "
Someone help me please
Best Regard
My E-mail = paiboonm@.cuel.co.thoooooooommmmmmmmmmmmmmm

oooooooommmmmmmmmmmmmmm

oooooooommmmmmmmmmmmmmm

Nope not getting anything on the telepathic channel...

maybe if you post your code and the actual error message

Problem with " , " and " "

I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?whether you use stored procedures or straight sql instructions from a VB client, you still need to pass parameters and if you need to pass a string parameter that contains a single quote (') insert another quote just next to it and it should be fine. As for commas, a string parameter containing a comma and delimited by two single quotes should work fine.

try in QA:

create table #temp(field1 varchar(500))
insert into #temp(field1) values ('test1''')
insert into #temp(field1) values ('test2 ,')
select * from #temp
drop table #temp|||...but use stored procedures anyway...|||is it nececessary to user storedprocedures for data inserting?
cant we use

sSql = "insert into tablename values (" & var1 & "," & var2 & ")"
dbConn.Execute sSql

cud u pl tell, if thers ne advantage in using sp for data insertion|||Using an sp for data insertion can have the advantage of shielding the db layout from applications, so applications may not have to be modified, recompiled and distributed if changes are made. Some database administrators like to know all the update/insert statements that could be executed so they can tune the database. It might also help seperating business logic from your applications.

I'm sure there are more, but these are the ones I can come up with.

One thing I haven't mentioned is that the company you work for may have chosen for one type of approach (having all in vb or all in sp), which sort of overrules advantage/disadvantage.|||apart from separating the business logic from applications, will there be an improved performance for large insert/update statements while using an sp

i.e, for an insert statement like

sSql = "insert into table1(field1,field2,....fieldn) values ("
& val1 & "," & val2 & "," .... & "," & valn & ")"
dbConn.Execute sSql

pl post ur comments|||I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?

you can use this code to sole ur Problem

Pvar_DataBase.Execute "insert into " & TablName _
& "(Filed01,Filed02)" _
& " Values('" & value01 & "','" & Single_Qute(Value02) & "')"

Public Static Function Single_Qute(String_Value As String) As String
Single_Qute= Replace(String_Value, "'", "''")
Single_Qute= Replace(String_Value, ",", "''")
End Function

If u have any problem

send me to
tgamil@.egysoft-it.com

Best Regards

Tarek Gamil

Problem when using linked server

Hi everyone,

I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.

If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."

I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)

The strange thing is that i used to run the same query a week or two ago and faced no problem.

Please help.

Hey Kilo

I have had this problem before also. I think its a bug in SQL 2005. Although I have not tried it yet, I think SP2 fixed the issue. Give that a shot.

|||See this blog http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx that refers the issues.|||

Hey Satya,

Thank u for your reply.

The link tha u sent me explains thye problem / posiible solution for sql server 2005 + Windows Server 2003. However in our case sql server 2005 is installed on a Windows 2000 operating system. Here is another clue:

Using profiler to map communication between sql server 2005 and sql server 2000 we get the following rows:

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0 0 0 776 68 2007-02-13 23:44:55.530 2007-02-13 23:44:55.530 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
User Error Message Changed database context to 'msdb'. SQLAgent - Step History Logger Administrator OPEN24\Administrator 2804 67 2007-02-13 23:44:58.890

User Error Message Changed language setting to us_english. SQLAgent - Step History Logger Administrator OPEN24\Administrator 2804 67 2007-02-13 23:44:58.890

When is sp_reset_connection exececuted?

Is there another parameter / event that we have to map in order to find the source of the problem?

|||It is an undocumented Proc that's used internally by SQL. You usually see it when connection pooling is being used--SQL Server uses it to reset the connection options and settings before reusing the connection.

Problem when using linked server

Hi everyone,

I've got problem querying remote tables via a specific linked server.
The server from which I execute the query is an SQLServer2005 and the linked server is an SQLServer2000.

If I do select * from <linked server>.<database name>.<db owner>.<Table1> after a while I get the following error message
"Server: Msg 10054, Level 16, State 1, Line 0, TCP Provider: An existing connection was forcibly closed by the remote host."

I execute the query using profiler and I got the following profiler error: 'OLE DB provider "Unknown" for linked server "(null)" supported the schema lock interface, but returned "0x80040e96" for "ReleaseSchemaLock".' (The query returns a subset of records before it is forcibly stopped)

The strange thing is that i used to run the same query a week or two ago and faced no problem.

Please help.

Hey Kilo

I have had this problem before also. I think its a bug in SQL 2005. Although I have not tried it yet, I think SP2 fixed the issue. Give that a shot.

|||See this blog http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx that refers the issues.|||

Hey Satya,

Thank u for your reply.

The link tha u sent me explains thye problem / posiible solution for sql server 2005 + Windows Server 2003. However in our case sql server 2005 is installed on a Windows 2000 operating system. Here is another clue:

Using profiler to map communication between sql server 2005 and sql server 2000 we get the following rows:

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider sa 0 0 0 0 776 68 2007-02-13 23:44:55.530 2007-02-13 23:44:55.530 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
User Error Message Changed database context to 'msdb'. SQLAgent - Step History Logger Administrator OPEN24\Administrator 2804 67 2007-02-13 23:44:58.890

User Error Message Changed language setting to us_english. SQLAgent - Step History Logger Administrator OPEN24\Administrator 2804 67 2007-02-13 23:44:58.890

When is sp_reset_connection exececuted?

Is there another parameter / event that we have to map in order to find the source of the problem?

|||It is an undocumented Proc that's used internally by SQL. You usually see it when connection pooling is being used--SQL Server uses it to reset the connection options and settings before reusing the connection.

problem when trying to query on datetime field

Hello All ,
my name is ron ,
i have the following problem:
when i running the query on my Sql server Database:
SELECT AVG(VOLUME) AS AvgVolume
FROM STOCKS_VOLUME
WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
i get an error:
The conversion of char data type to a dattime data type resulted in an
out-of-ranged datetime value .
what is wrong with my query does anyone have any idea how to solve this ?
thanksUse a safe and language neutral datetime format for your datetime literal. I
prefer the unseparated format:
'yyyymmdd'
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roni Bar Yosef" <ronby@.malamall.co.il> wrote in message
news:402a4c5f@.news.bezeqint.net...
> Hello All ,
> my name is ron ,
> i have the following problem:
> when i running the query on my Sql server Database:
> SELECT AVG(VOLUME) AS AvgVolume
> FROM STOCKS_VOLUME
> WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
> i get an error:
> The conversion of char data type to a dattime data type resulted in an
> out-of-ranged datetime value .
> what is wrong with my query does anyone have any idea how to solve this ?
> thanks
>

problem when trying to query on datetime field

Hello All ,
my name is ron ,
i have the following problem:
when i running the query on my Sql server Database:
SELECT AVG(VOLUME) AS AvgVolume
FROM STOCKS_VOLUME
WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
i get an error:
The conversion of char data type to a dattime data type resulted in an
out-of-ranged datetime value .
what is wrong with my query does anyone have any idea how to solve this ?
thanksUse a safe and language neutral datetime format for your datetime literal. I
prefer the unseparated format:
'yyyymmdd'
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Roni Bar Yosef" <ronby@.malamall.co.il> wrote in message
news:402a4c5f@.news.bezeqint.net...
> Hello All ,
> my name is ron ,
> i have the following problem:
> when i running the query on my Sql server Database:
> SELECT AVG(VOLUME) AS AvgVolume
> FROM STOCKS_VOLUME
> WHERE (SYMBOL = 'AUDC') AND (QUOTE_DATE >= '23/12/2003')
> i get an error:
> The conversion of char data type to a dattime data type resulted in an
> out-of-ranged datetime value .
> what is wrong with my query does anyone have any idea how to solve this ?
> thanks
>

Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike

Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike

Problem when inserting to MS database

ok i've got several pages that insert with no problem but this one is giving me fits. The query it's generated works, because i've plugged it into the database through access and it worked. here is my code what am i doing wrong? It opens the connection and then gets to the executeNonQuery and doesn't do it not sure why.

Dim sqlInsert As String
 Dim ssn As String
 Dim ssnDash As String
 Dim mma As String
 Dim position As String
 Dim subject As String
 Dim mmaCheck As String
 Dim subjectCheck As String
 Dim bothCheck As String
 Dim otherCheck As String
 Dim flag As Boolean = False
 Dim added As Boolean = False
 Try
 If txtSSN.Text <> "" Then
 ssn = txtSSN.Text
 ssnDash = ssn.Substring(0, 3) & "-" & ssn.Substring(3, 2) & "-" & ssn.Substring(5, 4)
 End If
 mma = dlMMA.SelectedValue.ToString
 position = dlPosition.SelectedValue.ToString
 subject = dlMajor.SelectedValue.ToString
 Catch ex As Exception
 Response.Write("Please fill in all fields")
 End Try
 otherCheck = mma & subject
 Dim dr As DataRow
 For Each dr In dsMajor.Tables("List_Of_MMA").Rows
 If mma = dr.Item("mma").ToString And subject = dr.Item("Subject").ToString Then
 mmaCheck = dr.Item("MMA").ToString
 subjectCheck = dr.Item("Subject").ToString
 flag = True
 bothCheck = mmaCheck & subjectCheck
 End If
 Next
 If flag = False Then
 Response.Write("Didn't Work")
 Else
 sqlInsert = "INSERT INTO Student_Major(SSN, MMA, Subject, Position) Values('"
 sqlInsert += ssnDash & "','"
 sqlInsert += mmaCheck & "','"
 sqlInsert += subjectCheck & "','"
 sqlInsert += position & "')"
 Dim com As OleDbCommand = New OleDbCommand(sqlInsert, myConnection)
 Try
 myConnection.Open()
 com.ExecuteNonQuery()
 added = True
 Catch ex As Exception
 Response.Write(sqlInsert)
 Finally
 myConnection.Close()
 End Try
 'If added = True Then
 Response.Write("New Major, Minor, or Area added: " & subject)
 'End If
 End If

what is the xact error that you get ?|||i don't get an error it just will not execute the executeNonQuery command. I wish i got an error then i could fix it.|||Does it throw an exception? What's the message from exception handler??|||try doing a repsonse.write of your sqlInsert to see how its building up. also i'd recommend using parameterized queries..

hth|||Ok thanks for the replies we fixed the problem by changing the query to this

sqlInsert = "INSERT INTO Student_Major Values ('"
sqlInsert += ssnDash & "','"
sqlInsert += mma & "','"
sqlInsert += subject & "','"
sqlInsert += position & "')"

Where can i find information on parameterized queries?|||http://aspnet101.com/aspnet101/tutorials.aspx?id=1