Loss of records

Hi, I have the following problem
In a flat file I have 250000 rows when I them go on to the DB only 249995 come, 5 got lost

Not where it can spend the mistake the loggind does not say anything of mistake
Not which can be the reason of the problem
If someone save for that it can be spending this?

helps please.

If you execute the package in BIDS you see how many rows are output from each component. This should make it very easy to see where the rows are being lost from.

-Jamie

Losing temporal tables

Hi, i want to know if there's any particular reason why a temporal
table can be dropped before closing the session.
I'm having the following problem: i create the temp table when a form
of my application is created, work with it and then drop it on the
form's close event.
It works fine most of the time, but from time to time the table seems
to be dropped before i close the form because i'm having a: #MyTable
doesn't exists error message.
Any ideas?
Working against SQL2K, W2K Server, from a W2K Pro machine.
Thanx
If your connection is getting dropped at any time you will loose the temp
table. Temp tables are really designed for a brief life span. If you need
to hold certain information for long periods of time like that you may want
to consider using real tables. Or better yet maybe a RS on the client.
Andrew J. Kelly SQL MVP
"Guillermo Casta?o A" <guillermoc74@.hotmail.com> wrote in message
news:9350d78d.0409170946.33db2291@.posting.google.c om...
> Hi, i want to know if there's any particular reason why a temporal
> table can be dropped before closing the session.
> I'm having the following problem: i create the temp table when a form
> of my application is created, work with it and then drop it on the
> form's close event.
> It works fine most of the time, but from time to time the table seems
> to be dropped before i close the form because i'm having a: #MyTable
> doesn't exists error message.
> Any ideas?
> Working against SQL2K, W2K Server, from a W2K Pro machine.
> Thanx

Loosing text format in pdf-Export

Hi NG,
in my report (html-view) i got the following value (i.e) -43.988.224,29 ?
field-type is currency
After export in pdf i got something like that:
0761<; ; 1557/5<#0 ander othe unknown characters.
Any suggests?
frank
www.xax.deAdobe 5.00 and 5.05 do not render symbols (e.g. Euro symbol) correctly when
the Arial font is used. Which version are you using? Did you try other fonts
(e.g. Tahoma)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:34hkssF46ueteU1@.individual.net...
> Hi NG,
> in my report (html-view) i got the following value (i.e) -43.988.224,29 ?
> field-type is currency
> After export in pdf i got something like that:
> 0761<; ; 1557/5<#0 ander othe unknown characters.
> Any suggests?
> frank
> www.xax.de
>sql

looping through results of a OSQL query

Hello,

I have the following problem.

I wrote a batch file that runs a sql script on SQLServer 2000. This
script must be executed on several databases. This works fine.

The problem is that in my database is a table that holds a databases i
have to update with this script. What i want is run a query with osql
in my batchfile to retrieve this these records so I can loop through
them an run the script for those databases.

I managed to execute the query that return the records and write them
to a textfile.

What i want is store the results in some kind of a resultset so i can
loop through them in my batchfile.

Anyone have the solution

Thanks in advance

Patrickpatrick (pveechoud@.xs4all.nl) writes:
> I have the following problem.
> I wrote a batch file that runs a sql script on SQLServer 2000. This
> script must be executed on several databases. This works fine.
> The problem is that in my database is a table that holds a databases i
> have to update with this script. What i want is run a query with osql
> in my batchfile to retrieve this these records so I can loop through
> them an run the script for those databases.
> I managed to execute the query that return the records and write them
> to a textfile.
> What i want is store the results in some kind of a resultset so i can
> loop through them in my batchfile.

A simple-minded approach is to write the query so that it generates
a complete OSQL command and you write that to the file. Then you
execute the file.

But it would probably better to write this in some script language
like VBscript or Perl from which you can submit queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Looping through records

Hello,

I have following VB code, in which I open a table and check whether there are records:

Dim SQL_CONNECTION_NTFNS As New SqlConnection
Dim SQL_COMMAND_NTFNS As New SqlCommand
Dim SQL_DATASET_NTFNS As New DataSet
Dim SQL_ADAPTER_NTFNS As New SqlDataAdapter(SQL_COMMAND_NTFNS)

SQL_CONNECTION_NTFNS.ConnectionString = str_DBSE_Connect
SQL_COMMAND_NTFNS.Connection = SQL_CONNECTION_NTFNS
SQL_COMMAND_NTFNS.CommandText = "SELECT * FROM Table"

Try
SQL_COMMAND_NTFNS.Connection.Open()
SQL_ADAPTER_NTFNS.Fill(SQL_DATASET_NTFNS)

Dim SQL_READER_NTFNS As SqlClient.SqlDataReader = SQL_COMMAND_NTFNS.ExecuteReader
With SQL_READER_NTFNS.Read
If SQL_READER_NTFNS.HasRows Then
?
SQL_READER_NTFNS.NextResult()
End If
End With
Catch
' Catch Something
Finally
' Dispose everything
End Try

At the ? I want to loop through the records and perform some actions, until it reaches the end (EOF?), thus a 'do while until loop'.

How can I do this?

Hello Seppe001,

Not sure what this has to do with Reporting Services however, you can use following code to loop through the records:

If SQL_READER_NTFNS.HasRows Then
while(SQL_READER_NTFNS.Read())
'your actions
end while
End If

|||Txs Alain, that helped!!

Looping through list in a query

Hello,
I am trying to write a query that will execute the same command to each
database in a list. More specifically I use the following command to get a
list of all the user-defined databases on the server:
SELECT catalog_name from information_schema.Schemata
WHERE NOT (CATALOG_NAME in
('tempdb','master','msdb','model','Northwind','pub s'))
and now I want to perfrom that same action (ie dettach or check for orphan
users etc) on each DB that I get from this query. Is there a way to do this
in SQL?
thanks
christos
Christos Kritikos wrote:
> Hello,
> I am trying to write a query that will execute the same command to
> each database in a list. More specifically I use the following
> command to get a list of all the user-defined databases on the server:
> SELECT catalog_name from information_schema.Schemata
> WHERE NOT (CATALOG_NAME in
> ('tempdb','master','msdb','model','Northwind','pub s'))
> and now I want to perfrom that same action (ie dettach or check for
> orphan users etc) on each DB that I get from this query. Is there a
> way to do this in SQL?
> thanks
> christos
Checking user information can be done in sysprocesses. No need to
enumerate the databases. If you really need a way to run the same
command against each database, you can use xp_MSForEachDB or just use a
temp table and interate through the results.
David Gugick
Imceda Software
www.imceda.com

looping through field in table

Dear All

I have a table, and in one of the fields is the following information:
1,2,3,4,5, etc...

How do I 'loop' through this field to remove each comma and put the number into it's own field in another table - so remove the first comma, and put the number 1 into its own field, remove the 2nd comma and put the number 2 into it's own field etc, until all the numbers are in their own fields.

So it ends up being like this:

Col1 Col2 Col3 Col4 Col5 Col n.............
1 2 3 4 5 n...........

There may not be 5 numbers in the field, sometimes more, sometimes less, so i need to be able to tell when there are no more commas and numbers left

Your help is much appreciated

Thanks

GillI have a function for extracting the nth element of a delimited string, if you can use functions try this:

-- =============================================
-- Create inline function
-- =============================================
IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement
GO

CREATE FUNCTION GetStringElement(
@.String varchar(100)
, @.Element int
, @.Seperator char(1) = ',')

RETURNS varchar(100)
AS
begin
declare @.Pass int, @.Index int, @.LastIndex int, @.Return varchar(100)

if (@.String like '%' + replicate(',%',@.Element - 1)) begin
select @.Pass = 1
, @.Index = 1
, @.LastIndex = 0

while (@.Pass <= @.Element) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)
, @.Pass = @.Pass + 1
end

if (@.LastIndex > 0 and @.Index = 0) set @.Index = len(@.String) + 1

set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)
end

RETURN @.Return
end
GO

if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.pass int, @.ColCount int, @.TSQL varchar(255)
select @.pass = 1
, @.ColCount = 6
while (@.pass <= @.ColCount) begin
select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = dbo.GetStringElement(f2,' + cast(@.pass as varchar) + ',default)'
, @.pass = @.pass + 1
exec(@.TSQL)
end

select * from #psy

IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement

if object_id('tempdb..#psy') is not null
drop table #psy|||if functions are not your thing you could try:
if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.RecordID int, @.TSQL varchar(255), @.pass int
, @.Index int, @.LastIndex int, @.Return varchar(100)
, @.String varchar(100), @.Element int

select @.RecordID = min(f1) from #psy
while (@.RecordID is not null) begin
select @.String = f2 from #psy where f1 = @.RecordID

select @.Index = 1
, @.LastIndex = 0
, @.pass = 1

while (@.Index > 0) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)

if (@.LastIndex > 0 and @.Index = 0)
set @.Return = substring(@.String,@.LastIndex + 1, 100)
else
set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)

select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = ''' + @.Return + ''' ' +
'where f1 = ' + cast(@.RecordID as varchar)
, @.pass = @.pass + 1

exec(@.TSQL)
end

select @.RecordID = min(f1) from #psy where f1 > @.RecordId

end

select * from #psy

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GOIf running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GO
If running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>
sql

looping result printed

hi,
i am running the following script. My question is that I see the results in
diff query panels... How can I put result set together and so I could copy
and paste to notepad? thanks
DECLARE cur_tables CURSOR FOR
SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
BEGIN
SET @.SQL = ''
BEGIN
SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
select @.returns = EXEC ( @.SQL)
END
FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
END --WHILE
CLOSE cur_tables
DEALLOCATE cur_tables
GOIf running it manually in either QA, or MS there is an option on the Query
menu to send 'Results to text', or something like that.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23I2NuwiJIHA.2712@.TK2MSFTNGP06.phx.gbl...
> hi,
> i am running the following script. My question is that I see the results
> in diff query panels... How can I put result set together and so I could
> copy and paste to notepad? thanks
> DECLARE cur_tables CURSOR FOR
> SELECT NAME FROM dbo.sysobjects where type = 'U' ORDER BY NAME asc
> OPEN cur_tables
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> BEGIN
> SET @.SQL = ''
> BEGIN
> SET @.SQL = 'sp_spaceused [' + @.CurrentObjectName + ']'
> select @.returns = EXEC ( @.SQL)
> END
>
> FETCH NEXT FROM cur_tables INTO @.CurrentObjectName
> END --WHILE
> CLOSE cur_tables
> DEALLOCATE cur_tables
> GO
>

Looping in a stored proceedure

What I would like to be able to do but am not sure if I can is the following
.
I need to set a variable = to the results of a single field recordset:
@.X = select EmployeeID from Employees where DepartmentID = 1
Then I need to build a dynamic sql statement based on the above results.
If the first recordset has three records. I need to loop through it three
times and concatinate the results of the sql that would look like this:
This or something like it would be in the loop...
@.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
END RESULT: @.SQL would now look like this
select * from customers where EmpID = 1, select * from customers where EmpID
= 2, select * from customers where EmpID = 3
The text between the comma's can increase or decrease depending on the
number of records in the first sql statement at the top of the page.
Any thoughts
Thank you
KentHi Kent.
You could try:
select ' select * from customers where EmpID=' + EmployeeID from
Employees where DepartmentID = 1
Bryce|||Why not just:
SELECT *
FROM Customers
WHERE empid IN
(SELECT employeeid
FROM Employees
WHERE departmentid = 1)
Dynamic SQL is bad news in a production application. Also, avoid SELECT
*. Code is safer, easier to maintain and maybe more efficient if you
list just the required column names.
David Portas
SQL Server MVP
--|||Kent,
What is the reason of doing this?
why not:
select employeeid, departmentid, ...
from employees
where departmentid = 1
AMB
"Kent Prokopy" wrote:

> What I would like to be able to do but am not sure if I can is the followi
ng.
> I need to set a variable = to the results of a single field recordset:
> @.X = select EmployeeID from Employees where DepartmentID = 1
> Then I need to build a dynamic sql statement based on the above results.
> If the first recordset has three records. I need to loop through it three
> times and concatinate the results of the sql that would look like this:
> This or something like it would be in the loop...
> @.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
>
> END RESULT: @.SQL would now look like this
> select * from customers where EmpID = 1, select * from customers where Emp
ID
> = 2, select * from customers where EmpID = 3
> The text between the comma's can increase or decrease depending on the
> number of records in the first sql statement at the top of the page.
> Any thoughts
> Thank you
> Kent|||I need to build an Excel report that has each Department on a diferant sheet
.
On each sheet will be a column for each employee. The number of column will
vary depending on the number of employee's in each department. So if
Department Dep1 has 5 employee's the sheet will have five columns. I could
populate each column one at a time, but would like to be able to do this
dynamicly. If posable.
"bd" wrote:

> Hi Kent.
> You could try:
> select ' select * from customers where EmpID=' + EmployeeID from
> Employees where DepartmentID = 1
> Bryce
>|||Correction,
select c.*
from customers as c inner join employees as e
on c.empid = e.employeeid and e.departmentid = 1
AMB
"Alejandro Mesa" wrote:
> Kent,
> What is the reason of doing this?
> why not:
> select employeeid, departmentid, ...
> from employees
> where departmentid = 1
>
> AMB
>
> "Kent Prokopy" wrote:
>|||My bad. Sorry I do not need * from... I need
For each employee I need a column/field.
select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
This will give me three columns in an Excel report. or two columns or XXXXX
"David Portas" wrote:

> Why not just:
> SELECT *
> FROM Customers
> WHERE empid IN
> (SELECT employeeid
> FROM Employees
> WHERE departmentid = 1)
> Dynamic SQL is bad news in a production application. Also, avoid SELECT
> *. Code is safer, easier to maintain and maybe more efficient if you
> list just the required column names.
> --
> David Portas
> SQL Server MVP
> --
>|||select EmpID , count(*) from DataTable
where DataDate = yesterday
GROUP BY EmpID
and do the pivoting in Excel.
Jacco Schalkwijk
SQL Server MVP
"Kent Prokopy" <KentProkopy@.discussions.microsoft.com> wrote in message
news:2126E40D-33DA-4389-A865-73E33F716A0D@.microsoft.com...
> My bad. Sorry I do not need * from... I need
> For each employee I need a column/field.
> select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
> This will give me three columns in an Excel report. or two columns or
> XXXXX
> "David Portas" wrote:
>|||Thank you all for your help/thoughts.
I have come up with a solution that will work.
I am going to build the sql statement in vb code and pass it tp the SP as a
varchar.
"David Portas" wrote:

> Use an Excel Pivot Table for that. You can query the database directly
> and it will create the columns for you. Alternatively you could use
> DTS.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have come up with a solution that will work.
> I am going to build the sql statement in vb code and pass it tp the SP as
a
> varchar.
Ugh, WHY? Sure, that will *work* but it is far and away from the best
solution. This is like going to the grocery store with a list of bar codes
for the products you want to buy.

Looping columns in instead of trigger

I have the following view (vProcurementPlan)

SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
dbo.tblRequisition.ROS AS ROS,
dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
ProjectID
FROM dbo.tblProcurementPlan INNER JOIN
dbo.tblRequisition ON
dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID

If I try inserting a record from Access it complains about multiple base
tables, I'm happy to write an "instead of" trigger and handle the 5 columns
from tblRequisition but as it contains all columns from tblProcurementPlan I
don't want to have to list them separately in any insert or update
statement.

The idea is that a record will be inserted into both tables simultaneously
upon insert to the view.Trev@.Work (bouncer@.localhost) writes:
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID
> AS ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5
> columns from tblRequisition but as it contains all columns from
> tblProcurementPlan I don't want to have to list them separately in any
> insert or update statement.

I am afraid you don't have much choice.

Besides, in my opinion SELECT * does not belong in production code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try not using the * and referencing all the column names explicitly.
It should insert without a problem.
eg
SELECT pp.RequisitionID ppreqid, pp.ReqNo ppReqNo, pp.Am AS Am, ppROS
AS ROS, pp.ActivityID ppAcID, pp.ProjectID ppProjID,rq.RequisitionID
rqReqId, rq.ReqNo rqReqNo, rq.Am Am, rqROS ROS, rq.ActivityID rqAcID,
rq.ProjectID rqProjID
FROM dbo.tblProcurementPlan pp INNER JOIN dbo.tblRequisition rq ON
pp.RequisitionID = rq.RequisitionID

Pachydermitis

"Trev@.Work" <bouncer@.localhost> wrote in message news:<3fddeed0$0$13894$afc38c87@.news.easynet.co.uk>...
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
> ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5 columns
> from tblRequisition but as it contains all columns from tblProcurementPlan I
> don't want to have to list them separately in any insert or update
> statement.
> The idea is that a record will be inserted into both tables simultaneously
> upon insert to the view.

Loopback linked servers cannot be used in a distributed transactions? This worked in Sql20

I recently did an upgrade to SQL 2005 and there's some odd behavior
that I'm seeing.
Given the following SQL:
Use MyDB1
go
create table #test (a int)
insert into #test
exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
where MyServer is a linked server that points back to itself. This
SQL works under SQL2000 but does not work in SQL2005. The error
returned in SQL 2005 is:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
In this whitepaper (http://msdn2.microsoft.com/en-us/library/
ms188716.aspx) i see the following warning:
"Loopback linked servers cannot be used in a distributed transaction.
Trying a distributed query against a loopback linked server from
within a distributed transaction causes an error, such as error 3910:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
use by another session."
The reason for the loopback linked server here is that in my live
enviornment, the linked server points to a different machine, (MyDB1
and MyDB2 are on different physical servers) but for my development
enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
to be able to keep that configuration without having to change code
when promoting stored procedures from the development branch to QA and
LIVE. Any way to get around this?You should be able to get around it by running a second instance in
your development environment, one instance for each of the production
systems.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 07:27:06 -0700, MS <spilich@.gmail.com> wrote:
>I recently did an upgrade to SQL 2005 and there's some odd behavior
>that I'm seeing.
>Given the following SQL:
>Use MyDB1
>go
>create table #test (a int)
>insert into #test
>exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
>where MyServer is a linked server that points back to itself. This
>SQL works under SQL2000 but does not work in SQL2005. The error
>returned in SQL 2005 is:
>Msg 3910, Level 16, State 2, Line 1
>Transaction context in use by another session.
>In this whitepaper (http://msdn2.microsoft.com/en-us/library/
>ms188716.aspx) i see the following warning:
>"Loopback linked servers cannot be used in a distributed transaction.
>Trying a distributed query against a loopback linked server from
>within a distributed transaction causes an error, such as error 3910:
>"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
>use by another session."
>The reason for the loopback linked server here is that in my live
>enviornment, the linked server points to a different machine, (MyDB1
>and MyDB2 are on different physical servers) but for my development
>enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
>to be able to keep that configuration without having to change code
>when promoting stored procedures from the development branch to QA and
>LIVE. Any way to get around this?|||On Aug 15, 10:53 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You should be able to get around it by running a second instance in
> your development environment, one instance for each of the production
> systems.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 15 Aug 2007 07:27:06 -0700, MS <spil...@.gmail.com> wrote:
> >I recently did an upgrade to SQL 2005 and there's some odd behavior
> >that I'm seeing.
> >Given the following SQL:
> >Use MyDB1
> >go
> >create table #test (a int)
> >insert into #test
> >exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
> >where MyServer is a linked server that points back to itself. This
> >SQL works under SQL2000 but does not work in SQL2005. The error
> >returned in SQL 2005 is:
> >Msg 3910, Level 16, State 2, Line 1
> >Transaction context in use by another session.
> >In this whitepaper (http://msdn2.microsoft.com/en-us/library/
> >ms188716.aspx) i see the following warning:
> >"Loopback linked servers cannot be used in a distributed transaction.
> >Trying a distributed query against a loopback linked server from
> >within a distributed transaction causes an error, such as error 3910:
> >"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
> >use by another session."
> >The reason for the loopback linked server here is that in my live
> >enviornment, the linked server points to a different machine, (MyDB1
> >and MyDB2 are on different physical servers) but for my development
> >enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
> >to be able to keep that configuration without having to change code
> >when promoting stored procedures from the development branch to QA and
> >LIVE. Any way to get around this... Hide quoted text -
> - Show quoted text -
Yes, you are correct, that works and I am aware of this. I was hoping
not to go this route because I already have multiple instances already
defined to allow different branches of development to run in
parallel. So, I have a DEV01, DEV02, and DEV03 instances to allow
three parallel branches of development. I'd have to effectively
double the number of instances (so i would have a DEV01a, DEV01b,
DEV02a DEV02b, DEV03a, DEV03b instance). I was hoping to not have to
manage this many instances on a single server. Sounds like I need to
acquire additional hardware. Is anyone aware of a way to get around
this error? Why does it work in 2000 and not in 2005?
Thanks

Loopback linked servers cannot be used in a distributed transactions? This worked in Sql20

I recently did an upgrade to SQL 2005 and there's some odd behavior
that I'm seeing.
Given the following SQL:
Use MyDB1
go
create table #test (a int)
insert into #test
exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
where MyServer is a linked server that points back to itself. This
SQL works under SQL2000 but does not work in SQL2005. The error
returned in SQL 2005 is:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
In this whitepaper (http://msdn2.microsoft.com/en-us/library/
ms188716.aspx) i see the following warning:
"Loopback linked servers cannot be used in a distributed transaction.
Trying a distributed query against a loopback linked server from
within a distributed transaction causes an error, such as error 3910:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
use by another session."
The reason for the loopback linked server here is that in my live
enviornment, the linked server points to a different machine, (MyDB1
and MyDB2 are on different physical servers) but for my development
enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
to be able to keep that configuration without having to change code
when promoting stored procedures from the development branch to QA and
LIVE. Any way to get around this?
You should be able to get around it by running a second instance in
your development environment, one instance for each of the production
systems.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 07:27:06 -0700, MS <spilich@.gmail.com> wrote:

>I recently did an upgrade to SQL 2005 and there's some odd behavior
>that I'm seeing.
>Given the following SQL:
>Use MyDB1
>go
>create table #test (a int)
>insert into #test
>exec [MyServer].MyDB2.dbo.SP_RETURNS_INT
>where MyServer is a linked server that points back to itself. This
>SQL works under SQL2000 but does not work in SQL2005. The error
>returned in SQL 2005 is:
>Msg 3910, Level 16, State 2, Line 1
>Transaction context in use by another session.
>In this whitepaper (http://msdn2.microsoft.com/en-us/library/
>ms188716.aspx) i see the following warning:
>"Loopback linked servers cannot be used in a distributed transaction.
>Trying a distributed query against a loopback linked server from
>within a distributed transaction causes an error, such as error 3910:
>"[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in
>use by another session."
>The reason for the loopback linked server here is that in my live
>enviornment, the linked server points to a different machine, (MyDB1
>and MyDB2 are on different physical servers) but for my development
>enviornment MyDB1 and MyDB2 are on the same physical server. I'd like
>to be able to keep that configuration without having to change code
>when promoting stored procedures from the development branch to QA and
>LIVE. Any way to get around this?
|||On Aug 15, 10:53 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You should be able to get around it by running a second instance in
> your development environment, one instance for each of the production
> systems.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 15 Aug 2007 07:27:06 -0700, MS <spil...@.gmail.com> wrote:
>
>
>
> - Show quoted text -
Yes, you are correct, that works and I am aware of this. I was hoping
not to go this route because I already have multiple instances already
defined to allow different branches of development to run in
parallel. So, I have a DEV01, DEV02, and DEV03 instances to allow
three parallel branches of development. I'd have to effectively
double the number of instances (so i would have a DEV01a, DEV01b,
DEV02a DEV02b, DEV03a, DEV03b instance). I was hoping to not have to
manage this many instances on a single server. Sounds like I need to
acquire additional hardware. Is anyone aware of a way to get around
this error? Why does it work in 2000 and not in 2005?
Thanks
sql

Loop...

Can someone please tell me if it is possible to create variables dynamically
or with the use of a loop and if so how...
I have the following SQL statement and would like to cut down the number of
variables by using some kind of loop to generate the variables and update
statement!
Thanks...
CREATE PROCEDURE dbo.MenuUpdate
(
@.ItemID Int,
@.Date SmallDateTime,
@.mItem1 nvarchar (255),
@.mItem2 nvarchar (255),
@.mItem3 nvarchar (255),
@.mItem4 nvarchar (255),
@.mItem5 nvarchar (255),
@.mItem6 nvarchar (255),
@.mItem7 nvarchar (255),
@.mItem8 nvarchar (255),
@.mItem9 nvarchar (255),
@.mItem10 nvarchar (255),
@.mPrice1 nvarchar (255),
@.mPrice2 nvarchar (255),
@.mPrice3 nvarchar (255),
@.mPrice4 nvarchar (255),
@.mPrice5 nvarchar (255),
@.mPrice6 nvarchar (255),
@.mPrice7 nvarchar (255),
@.mPrice8 nvarchar (255),
@.mPrice9 nvarchar (255),
@.mPrice10 nvarchar (255)
)
AS
UPDATE tblRestaurant
SET
DateCreated = @.Date,
MenuItem = @.mItem1,
ItemPrice = @.mPrice1
WHERE itemID = @.itemID
GOHave a look at
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:099D2D78-9B32-4EE0-AB1C-103A828F8E54@.microsoft.com...
> Can someone please tell me if it is possible to create variables
> dynamically
> or with the use of a loop and if so how...
> I have the following SQL statement and would like to cut down the number
> of
> variables by using some kind of loop to generate the variables and update
> statement!
> Thanks...
>
> CREATE PROCEDURE dbo.MenuUpdate
> (
> @.ItemID Int,
> @.Date SmallDateTime,
> @.mItem1 nvarchar (255),
> @.mItem2 nvarchar (255),
> @.mItem3 nvarchar (255),
> @.mItem4 nvarchar (255),
> @.mItem5 nvarchar (255),
> @.mItem6 nvarchar (255),
> @.mItem7 nvarchar (255),
> @.mItem8 nvarchar (255),
> @.mItem9 nvarchar (255),
> @.mItem10 nvarchar (255),
> @.mPrice1 nvarchar (255),
> @.mPrice2 nvarchar (255),
> @.mPrice3 nvarchar (255),
> @.mPrice4 nvarchar (255),
> @.mPrice5 nvarchar (255),
> @.mPrice6 nvarchar (255),
> @.mPrice7 nvarchar (255),
> @.mPrice8 nvarchar (255),
> @.mPrice9 nvarchar (255),
> @.mPrice10 nvarchar (255)
> )
> AS
> UPDATE tblRestaurant
> SET
> DateCreated = @.Date,
> MenuItem = @.mItem1,
> ItemPrice = @.mPrice1
> WHERE itemID = @.itemID
> GO
>|||OK...
That just blew my mind...
Is there not a way of just adding a loop in a sp or something to generate
the parameters and Update statemet?
Thanks anyway...
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Tim::.." <myatix_at_hotmail.com> wrote in message
> news:099D2D78-9B32-4EE0-AB1C-103A828F8E54@.microsoft.com...
>
>|||>Is there not a way of just adding a loop in a sp or something to generate
>the parameters
It seems that you are passing the values for these parameters from your
client application.
Then how can you just loop inside the SP and get the values?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BCADDEAA-5591-4D1F-81FD-C83AC4D2DAA9@.microsoft.com...
> OK...
> That just blew my mind...
> Is there not a way of just adding a loop in a sp or something to generate
> the parameters and Update statemet?
> Thanks anyway...
>
> "Roji. P. Thomas" wrote:
>|||No. I know, this can be frustrating sometimes when you're used to working in
a Real programming language, declaring variables on the fly, arrays and
loops.
But SQL server is not a programming language.
Use the techniques in the link Roji told you.
I use the Iterative Method in a few procedures.
A bit of work the first time you use it but the second time around will be a
lot easier.
In the example you give you would have only 3 or 4 parameters (depending on
how you set it up) and 1 update statement.
And you could pass as many or as few mItem/mPrice as you want and the
procedure would handle it.
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BCADDEAA-5591-4D1F-81FD-C83AC4D2DAA9@.microsoft.com...
> OK...
> That just blew my mind...
> Is there not a way of just adding a loop in a sp or something to generate
> the parameters and Update statemet?
> Thanks anyway...
>
> "Roji. P. Thomas" wrote:
>|||> Is there not a way of just adding a loop in a sp or something to generate
> the parameters and Update statemet?
No. You can create a function, once, to deal with this, and you shouldn't
have to think hard about it again. I have one for integers and one for
varchar listed at http://www.aspfaq.com/2248 .. they couldn't be much easier
to use.

Loop thru data subset

hi

I have the following query:
select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid
from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid

This returns multiple rows of data. I want to loop through each row, extract 2 values and see if they exist in another table. I need to do this in Query Analyser.

Can anyone please help!

Thanking you in advance
PORRASTAROf course you can use cursor, but I prefere to use fake cursor:

Save your result in temporary table and do loop by newid.

select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid,
IDENTITY(int, 1, 1) AS newid -- add identity filed
into #tmp -- temp table
from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid|||I don't think you need to loop through anything if you just want to see if the values exist in another table. Just treat your query as a subquery and join it to your other table:

select Value1, Value2
from YourOtherTable
inner join
(select sum(iunits) as iunits, sum(ivalue) as ivalue, sum(ivolume) as ivolume, sum(ivolumeHL) as ivolumehl, periodid as periodid, productid as productid, tbstores.storeid as storeid
from tbstoredata
inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111
group by tbstores.storeid, periodid, parentid, productid) SummarySubquery
on YourOtherTable.Value1 = SummarySubquery.Value1 and YourOtherTable.Value2 = SummarySubquery.Value2

...or if you want to use the dataset multiple times, store it in a temporary table or table variable and join that instead.

blindman

Loop inside View

Hello,

is it possible to build a loop for the following statement?

CREATE VIEW vwObjects as (

Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union

Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union

Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)

I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

Thank you!Huh?

YEAR(edate), MONTH(edate)

What are you trying to do?

And what's with LOOP? I don't see no loop|||Oh,sorry. I have one Table for the objects. Every object as a startdate and an enddate. For my cube, I need kind of dimension, so the user can pick a quarter and he will get the sum of all active objects. I tried several ways to realize this.

My idea is to create of view, that looks like:

year quarter id
2001 1 1
2001 1 2
2001 1 3
2001 2 2
2001 2 4

From objects table:

id startdate enddate
1 2001/05/01 2001/13/02
2 2001/25/02 2001/03/04
3 2001/03/01 2001/5/01
4 2001/09/05 2001/22/05

I hope it's more more understandable now.|||Ok, forget that, I found another way.|||Ok, forget that, I found another way.

Can you elaborate? Your solution may help other users in the future.|||I couldn't solve this. Even if I could, this will be very slow for big tables. I will have to do a little work off on my design and then I will try this loop with a INSERT INTO, not a view. Greets, Silas

Loop Error

I have the following code in my selection criteria (using CR 10):

Global datetimevar firstDate:= datetime(year(CurrentDateTime), month(CurrentDateTime),1,0,0,0) ;
Global datetimevar lastMonth:= dateadd("m",+0,firstDate);
lastMonth:=dateadd("d",-1, lastMonth);

if {ENG_ORD_MERGE_VW.SHP_DT} = lastMonth
then
Do
lastMonth:=dateadd("d",-1, lastMonth)
While {CTDATE.WRK_DAY} <> 1.00 ;

The report appears to be searching records but then part way through it kicks me out with this error message:

"A loop was evaluated more than the maximum number of times allowed"

What is the maximum number of times a loop can be evaluated? I didn't think there was a limit. Does anyone know how I can write this so that it won't kick me out?You need to increase or decrese {CTDATE.WRK_DAY} in the loop
Otherwise it will result in infinite loop|||I can't increment the WorkDay because all workdays need to equal 1, will it work if I put in a dummy counter, just to increment 1?

Lookupcube and Parameter not working together

Please help me
I have the following MDx that works perfect, but I need now to attach a
parameter, but it would work. please help
With
Member Measures.[Sales Current Year Target] as
'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
Target],[DistrictGeo].[District Id].["+[DistrictIsManaged].CurrentMember.Name
+"]," + [Time].CurrentMember.UniqueName + ")")'
SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
{NONEMPTYCROSSJOIN(
{ [DistrictIsManaged].[District Id].[Dallas]},
[Territory].[Territory Desc].members)} on
ROWS
FROM [Sales vs RepBudget]
where (" & Parameters!pTime.Value & ")"You need to prefix your mdx with =" and end with ". And the whole mdx
statement needs to be in on one line. It will break by itself, but you can
check it out by copying the whole statement to Notepad, turn off word wrap
and see that it's all on one line.
Also, you might have to escape the quotes "s in your statement. You do this
by adding more quotes.
"
Visual Basic Language Specification
2.4.4 String Literals
A string literal is a sequence of zero or more Unicode characters beginning
and ending with an ASCII double-quote character, a Unicode left double-quote
character, or a Unicode right double-quote character. Within a string, a
sequence of two double-quote characters is an escape sequence representing a
double quote in the string."
Try adding an extra " infront of you "s.
Kaisa M. Lindahl
"Tomas" <Tomas@.discussions.microsoft.com> wrote in message
news:E32EB005-182D-4315-A873-FF6C6606AFCB@.microsoft.com...
> Please help me
> I have the following MDx that works perfect, but I need now to attach a
> parameter, but it would work. please help
> With
> Member Measures.[Sales Current Year Target] as
> 'LookupCube("Sales vs SalesBudget","( [Measures].[Current Year
> Target],[DistrictGeo].[District
> Id].["+[DistrictIsManaged].CurrentMember.Name
> +"]," + [Time].CurrentMember.UniqueName + ")")'
> SELECT NON EMPTY { Measures.[Sales Current Year Target] } ON COLUMNS ,
> {NONEMPTYCROSSJOIN(
> { [DistrictIsManaged].[District Id].[Dallas]},
> [Territory].[Territory Desc].members)} on
> ROWS
> FROM [Sales vs RepBudget]
> where (" & Parameters!pTime.Value & ")"
>

LookupCube

The following is the MDX query for the report

WITH MEMBER

Measures.[TreatmentCount]

AS

'LookupCube(

"Patient Hospital and Drug",

"(" + MemberToStr(AgencyID.CurrentMember) + ", [Measures].[Pharmacy DW Count])"

)'

SELECT

{ Measures.[TreatmentCount]} ON COLUMNS,

NON EMPTY { ([Agency Id].[Agency Id].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Last Six Months Pharmacy DW])) WHERE ( IIF( STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED).Count = 1, STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED), [Last Six Months Drug].[Drug Name].currentmember ), IIF( STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED).Count = 1, STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED), [Last Six Months Drug].[Protocol Code].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The MDX query is based on the two OLAP cubes, "Patient Hospital and Drug" and "Last Six Months Pharmacy DW". The second OLAP cube is used to fined out all the "Agency ID" for a selcted "Drug" and "Protocol Code" prescriped to a patient at least once for the last six months from today date. Then apply these set of "Agency ID" to filter out the entire warehouse data i.e. the first OLAP cube and find out the total count of the treatment for the patient for this selected "Drug" and "Protocol Code".

OLAP cube:Patient Hospital and Drug

Measures: Pharmacy DW

Pharmacy DW Count

Dimension: Agency Id

Agency Id

Agency Id

Dimension: Drug

Drug Name

Drug Name

Dimension: Drug

Protocol Code

Protocol Code

The second OLAP cube has the same structure as the first one and it has only the last 6 months data (filter out in the data source view). The data warehouse is updated and the OLAP cube is re-build daily.

Thanks

Maybe I'm missing something, but what's your question in this case - does the MDX query work as expected?