Extracting Sql: required

Showing posts with label required. Show all posts

Wednesday, March 21, 2012

Failed scheduled job

I have a scheduled job which has been failing for a while,
and the only way I have now been able to have it concluded
successfully and with required data transfer result is
when the service-account is member of the domain-admins
group. Adding the service-account only to the local-admins
group of concerned systems gives successfull job
completion but with empty result.
Thus my question is, why must the service-account be
member of the domain-admins group, when the job itself is
to run under the domain-admin account that has full
permission on all systems.
I will appreciated any clarification.What exactly is the job trying to do ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"topokin" <bimosekin@.hotmail.com> wrote in message
news:b9c101c381a1$241e5030$a601280a@.phx.gbl...
I have a scheduled job which has been failing for a while,
and the only way I have now been able to have it concluded
successfully and with required data transfer result is
when the service-account is member of the domain-admins
group. Adding the service-account only to the local-admins
group of concerned systems gives successfull job
completion but with empty result.
Thus my question is, why must the service-account be
member of the domain-admins group, when the job itself is
to run under the domain-admin account that has full
permission on all systems.
I will appreciated any clarification.|||Whether or not you need a domain admin account depends on what you are
trying to do. In general, there is really no need for the service account or
the account tha runs the job to be a member of the domain admin. In most
places, you'd be told to get lost by the NT admin if you ask for the domain
admin rights.
I'd suggest you carefully log your jobs. For instance, you can specify an
output file for each step in its Advanced tab. You can then review this
output file to find more about the nature of the failure.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"topokin" <bimosekin@.hotmail.com> wrote in message
news:b9c101c381a1$241e5030$a601280a@.phx.gbl...
> I have a scheduled job which has been failing for a while,
> and the only way I have now been able to have it concluded
> successfully and with required data transfer result is
> when the service-account is member of the domain-admins
> group. Adding the service-account only to the local-admins
> group of concerned systems gives successfull job
> completion but with empty result.
> Thus my question is, why must the service-account be
> member of the domain-admins group, when the job itself is
> to run under the domain-admin account that has full
> permission on all systems.
> I will appreciated any clarification.
>

Friday, February 17, 2012

Extracting data as xml from 4 tables

Hi,
I am having some difficulty extracting the xml that is required using FOR
XML EXPLICIT and I am wondering if you can help me please?
The target xml format is this;
<?xml version="1.0"?>


<c i="1238" t="1">
<n>Test Phone</n>
<a>1-544-552-8900</a>
<f>0x0022</f>
</c>
<c i="1233" t="1">
<n>Phone Tester</n>
<a>1-788-111-42322</a>
<f>0x0011</f>
</c>
<c i="1234" t="1">
<n>Cell Tester</n>
<a>1-445-255-3339</a>
<f>0x0111</f>
</c>


With any number of P's
each with any number of c's
each c may or may not have 1 each of n, a & f
or another way of looking at it- apart from PK's and FK's
All NOT NULL's will always be present & all NULL values will not be
represented in the resulting file
The data is contained in 3 tables;
TABLE [U]
[UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
[sid] [varchar] (20) NOT NULL
TABLE [P]
[PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
[UID] [int] NOT NULL, (FK)
[num] [varchar] (20) NOT NULL,
[f] [int] NULL,
[x] [int] NULL,
[d] [int] NULL,
[t] [int] NOT NULL
TABLE [c]
[CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
[PID] [int] NOT NULL , (FK)
[i] [varchar] (20) NOT NULL,
[n] [varchar] (20) NULL,
[a] [varchar] (20) NULL,
[f] [varchar] (8) NULL
I have a fourth table which contains pointers to the data that needs
including for a given U.
TABLE [Todo]
[id] [int] IDENTITY (1, 1) NOT NULL, (PK)
[DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
[UID] [int] NULL ,
[PID] [int] NULL ,
[CID] [int] NULL ,
[dir] [Int] NOT NULL
So for the given xml file above the contents of Todo is;
row1=n,[thedate],1,NULL,NULL,"0"
row2=n+1,[thedate],1,1,NULL,"0"
row3=n+1,[thedate],1,1,1,"0"
row4=n+1,[thedate],1,1,2,"0"
row5=n+1,[thedate],1,1,3,"0"
I hope that is clear and that you can help me.
Many thanks
Andy LThis is one of the more complicated dialects; however, check out the
following link--it has quite a bit of detailed syntax information.
http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_4y91.asp?frame=true
Sincerely,
Anthony Thomas
"Ultradiv" wrote:
> Hi,
> I am having some difficulty extracting the xml that is required using FOR
> XML EXPLICIT and I am wondering if you can help me please?
> The target xml format is this;
> <?xml version="1.0"?>
> 
> 
> <c i="1238" t="1">
> <n>Test Phone</n>
> <a>1-544-552-8900</a>
> <f>0x0022</f>
> </c>
> <c i="1233" t="1">
> <n>Phone Tester</n>
> <a>1-788-111-42322</a>
> <f>0x0011</f>
> </c>
> <c i="1234" t="1">
> <n>Cell Tester</n>
> <a>1-445-255-3339</a>
> <f>0x0111</f>
> </c>
> 
> 
> With any number of P's
> each with any number of c's
> each c may or may not have 1 each of n, a & f
> or another way of looking at it- apart from PK's and FK's
> All NOT NULL's will always be present & all NULL values will not be
> represented in the resulting file
> The data is contained in 3 tables;
> TABLE [U]
> [UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> [sid] [varchar] (20) NOT NULL
> TABLE [P]
> [PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
> [UID] [int] NOT NULL, (FK)
> [num] [varchar] (20) NOT NULL,
> [f] [int] NULL,
> [x] [int] NULL,
> [d] [int] NULL,
> [t] [int] NOT NULL
>
> TABLE [c]
> [CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> [PID] [int] NOT NULL , (FK)
> [i] [varchar] (20) NOT NULL,
> [n] [varchar] (20) NULL,
> [a] [varchar] (20) NULL,
> [f] [varchar] (8) NULL
>
> I have a fourth table which contains pointers to the data that needs
> including for a given U.
> TABLE [Todo]
> [id] [int] IDENTITY (1, 1) NOT NULL, (PK)
> [DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
> [UID] [int] NULL ,
> [PID] [int] NULL ,
> [CID] [int] NULL ,
> [dir] [Int] NOT NULL
> So for the given xml file above the contents of Todo is;
> row1=n,[thedate],1,NULL,NULL,"0"
> row2=n+1,[thedate],1,1,NULL,"0"
> row3=n+1,[thedate],1,1,1,"0"
> row4=n+1,[thedate],1,1,2,"0"
> row5=n+1,[thedate],1,1,3,"0"
> I hope that is clear and that you can help me.
> Many thanks
> Andy L
>
>
>|||Yeah it is complicated!
Its fine with two queries and UNION ALL
but adding the third part has proved very difficult
Thanks for the reference Anthony, I'll read through to see if there is
anything more than my MS book 'Programming Microsoft SQL Server 2000 with
XML Second edition' and let you know
Cheers,
Andy
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:F890E101-9DE3-4F60-8714-71174E65A39E@.microsoft.com...
> This is one of the more complicated dialects; however, check out the
> following link--it has quite a bit of detailed syntax information.
>
http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_4y91.asp?frame=true
> Sincerely,
>
> Anthony Thomas
>
> "Ultradiv" wrote:
> > Hi,
> > I am having some difficulty extracting the xml that is required using
FOR
> > XML EXPLICIT and I am wondering if you can help me please?
> >
> > The target xml format is this;
> >
> > <?xml version="1.0"?>
> > 
> > 
> > <c i="1238" t="1">
> > <n>Test Phone</n>
> > <a>1-544-552-8900</a>
> > <f>0x0022</f>
> > </c>
> > <c i="1233" t="1">
> > <n>Phone Tester</n>
> > <a>1-788-111-42322</a>
> > <f>0x0011</f>
> > </c>
> > <c i="1234" t="1">
> > <n>Cell Tester</n>
> > <a>1-445-255-3339</a>
> > <f>0x0111</f>
> > </c>
> > 
> > 
> >
> > With any number of P's
> > each with any number of c's
> > each c may or may not have 1 each of n, a & f
> > or another way of looking at it- apart from PK's and FK's
> > All NOT NULL's will always be present & all NULL values will not be
> > represented in the resulting file
> >
> > The data is contained in 3 tables;
> >
> > TABLE [U]
> > [UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > [sid] [varchar] (20) NOT NULL
> >
> > TABLE [P]
> > [PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
> > [UID] [int] NOT NULL, (FK)
> > [num] [varchar] (20) NOT NULL,
> > [f] [int] NULL,
> > [x] [int] NULL,
> > [d] [int] NULL,
> > [t] [int] NOT NULL
> >
> >
> > TABLE [c]
> > [CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > [PID] [int] NOT NULL , (FK)
> > [i] [varchar] (20) NOT NULL,
> > [n] [varchar] (20) NULL,
> > [a] [varchar] (20) NULL,
> > [f] [varchar] (8) NULL
> >
> >
> > I have a fourth table which contains pointers to the data that needs
> > including for a given U.
> >
> > TABLE [Todo]
> > [id] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > [DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
> > [UID] [int] NULL ,
> > [PID] [int] NULL ,
> > [CID] [int] NULL ,
> > [dir] [Int] NOT NULL
> >
> > So for the given xml file above the contents of Todo is;
> >
> > row1=n,[thedate],1,NULL,NULL,"0"
> > row2=n+1,[thedate],1,1,NULL,"0"
> > row3=n+1,[thedate],1,1,1,"0"
> > row4=n+1,[thedate],1,1,2,"0"
> > row5=n+1,[thedate],1,1,3,"0"
> >
> > I hope that is clear and that you can help me.
> > Many thanks
> > Andy L
> >
> >
> >
> >
> >|||Ultradiv
Perhaps instead of using SQL Server ,you may write some VB.NET script to
deal with it.
"Ultradiv" <andy@.visualpurple.net> wrote in message
news:%23tkI7V4vEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Yeah it is complicated!
> Its fine with two queries and UNION ALL
> but adding the third part has proved very difficult
> Thanks for the reference Anthony, I'll read through to see if there is
> anything more than my MS book 'Programming Microsoft SQL Server 2000 with
> XML Second edition' and let you know
> Cheers,
> Andy
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:F890E101-9DE3-4F60-8714-71174E65A39E@.microsoft.com...
> > This is one of the more complicated dialects; however, check out the
> > following link--it has quite a bit of detailed syntax information.
> >
> >
>
http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_4y91.asp?frame=true
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "Ultradiv" wrote:
> >
> > > Hi,
> > > I am having some difficulty extracting the xml that is required using
> FOR
> > > XML EXPLICIT and I am wondering if you can help me please?
> > >
> > > The target xml format is this;
> > >
> > > <?xml version="1.0"?>
> > > 
> > > 
> > > <c i="1238" t="1">
> > > <n>Test Phone</n>
> > > <a>1-544-552-8900</a>
> > > <f>0x0022</f>
> > > </c>
> > > <c i="1233" t="1">
> > > <n>Phone Tester</n>
> > > <a>1-788-111-42322</a>
> > > <f>0x0011</f>
> > > </c>
> > > <c i="1234" t="1">
> > > <n>Cell Tester</n>
> > > <a>1-445-255-3339</a>
> > > <f>0x0111</f>
> > > </c>
> > > 
> > > 
> > >
> > > With any number of P's
> > > each with any number of c's
> > > each c may or may not have 1 each of n, a & f
> > > or another way of looking at it- apart from PK's and FK's
> > > All NOT NULL's will always be present & all NULL values will not be
> > > represented in the resulting file
> > >
> > > The data is contained in 3 tables;
> > >
> > > TABLE [U]
> > > [UID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > > [sid] [varchar] (20) NOT NULL
> > >
> > > TABLE [P]
> > > [PID] [int] IDENTITY (1, 1) NOT NULL,(PK)
> > > [UID] [int] NOT NULL, (FK)
> > > [num] [varchar] (20) NOT NULL,
> > > [f] [int] NULL,
> > > [x] [int] NULL,
> > > [d] [int] NULL,
> > > [t] [int] NOT NULL
> > >
> > >
> > > TABLE [c]
> > > [CID] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > > [PID] [int] NOT NULL , (FK)
> > > [i] [varchar] (20) NOT NULL,
> > > [n] [varchar] (20) NULL,
> > > [a] [varchar] (20) NULL,
> > > [f] [varchar] (8) NULL
> > >
> > >
> > > I have a fourth table which contains pointers to the data that needs
> > > including for a given U.
> > >
> > > TABLE [Todo]
> > > [id] [int] IDENTITY (1, 1) NOT NULL, (PK)
> > > [DateTime] [datetime] NOT NULL DEFAULT (GetUtcDate()),
> > > [UID] [int] NULL ,
> > > [PID] [int] NULL ,
> > > [CID] [int] NULL ,
> > > [dir] [Int] NOT NULL
> > >
> > > So for the given xml file above the contents of Todo is;
> > >
> > > row1=n,[thedate],1,NULL,NULL,"0"
> > > row2=n+1,[thedate],1,1,NULL,"0"
> > > row3=n+1,[thedate],1,1,1,"0"
> > > row4=n+1,[thedate],1,1,2,"0"
> > > row5=n+1,[thedate],1,1,3,"0"
> > >
> > > I hope that is clear and that you can help me.
> > > Many thanks
> > > Andy L
> > >
> > >
> > >
> > >
> > >
>|||This is how I did it & it works great!
Thanks alot,
Select Top 1 1 as Tag,
Null as Parent,
#Inter.SystemID as [U!1!sid],
#Inter.Direction as [U!1!dir],
Null as [P!2!num],
Null as [P!2!f],
Null as [P!2!x],
Null as [P!2!d],
Null as [P!2!t],
Null as [c!3!i],
Null as [c!3!t],
Null as [c!3!n!element],
Null as [c!3!a!element],
Null as [c!3!f!element]
FROM #Inter
UNION ALL
Select 2,
1,
#Inter.SystemID,
Null,
#Inter.PhoneNumber,
#Inter.AllottedTime,
#Inter.ExtraTime,
#Inter.ResetDate,
#Inter.Command,
NULL,
NULL,
NULL,
NULL,
NULL
FROM #Inter Where ContactRef is NULL
Union all
Select 3,
2,
NULL,
NULL,
#Inter.PhoneNumber,
NULL,
NULL,
NULL,
NULL,
#Inter.ContactRef,
#Inter.Command,
#Inter.ContactName,
#Inter.ContactAddress,
#Inter.ContactFlags
FROM #Inter Where (ContactRef is NOT NULL)
Order By [P!2!num],[c!3!i]
FOR XML EXPLICIT|||Hi Uri
I didn't want to use anything outside the 'SQL Server box' because the
server application that will be calling this sp is C++ and is already built
with the names of the sp's in it and I don't want to get the developer to do
any more work on it. Also I didn't fancy the idea of using templates or any
other 'referenced file means' to produce this xml coz then I have to write
other xsl stuff which I don't understand!
Once conquered FOR XML EXPLICIT seems the best solution really.
Andy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eKuqcA%23vEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Ultradiv
> Perhaps instead of using SQL Server ,you may write some VB.NET script to
> deal with it.

Wednesday, March 21, 2012

Failed scheduled job

Friday, February 17, 2012

Extracting data as xml from 4 tables

Extracting data as xml from 4 tables

Extracting data as xml from 4 tables

Extracting Sql

Blog Archive

About Me