Can any body tell me what measures I have to take to improve the performance of my SQL QUERIES and Stored procedures any thing relatted to SQL?Lookup "Query Tuning" in Books Online.
blindman|||We can definitely identify optimization opportunities for a specific query, but for all of your queries you either have to do it yourself or hire a consultant to do it for you.
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
importing queries from access
im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
mark
Replied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
mark
Replied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>
importing queries from access
im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissio
nAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_Oth
erBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCo
mmission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions
.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,&#
91;Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,&#
91;Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipeagreen">
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusineeagreen">
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissioneagreen">
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBueagreen">
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,
[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,
[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>sql
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissio
nAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_Oth
erBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCo
mmission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions
.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,&#
91;Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,&#
91;Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipeagreen">
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusineeagreen">
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissioneagreen">
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBueagreen">
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,
[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,
[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>sql
importing queries from access
im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>
Importing MS Access queries into SQLServer?
Hello,
I have an Access db with several tables and several queries defined. Is
there a way to import the results of a query into SQLServer, or do I first
have to save the result of the query as a table and import that?
Thanks.
You might find it easier to perform a make table and then import the
results.
Another solution that you might be able to look at would involve moving the
query into SQL Server and let SQL Server (not MS Access) combine the data
and create (or populate) a table.
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:%23TG4NxcwEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an Access db with several tables and several queries defined. Is
> there a way to import the results of a query into SQLServer, or do I first
> have to save the result of the query as a table and import that?
> Thanks.
>
|||Unfortunately, moving the queries into SQLServer is not an option.
Thanks for the reply.
> You might find it easier to perform a make table and then import the
> results.
> Another solution that you might be able to look at would involve moving
the
> query into SQL Server and let SQL Server (not MS Access) combine the data
> and create (or populate) a table.
|||Hi,
Have you tried "Import and Export Data" in SQL Server? What's your
concerns? Would you please show us more detailed scenario about your issue?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Our application imports the user's data (vendors and customers, for example)
from their datasources into SQLServer for the application to use. The user
can select to import from Access, Excel, or an ODBC datasource. When Access
is selected, we create an OleDbConnection object; the ConnectionString
property is something like:
@."Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\SrcDataSmall.mdb;User Id=;Password=;""
To get the list of tables in the db, we call:
DataTable schemaTable =
dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.T ables, new object[] {null,
null, null, "TABLE"});
Is there a way to get a list of queries in the db, and import the result set
as though it were a table?
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:FQ5ZqQlwEHA.3984@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you tried "Import and Export Data" in SQL Server? What's your
> concerns? Would you please show us more detailed scenario about your
issue?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Developer,
The following statement returns all the views in an Access database.
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
HTH.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
|||Thank you, Kevin, I'll try that. As I looked in to GetOleDbSchemaTable(), I
wondered if "View" was what I needed to use.
"Kevin Yu [MSFT]" <v-kevy@.online.microsoft.com> wrote in message
news:$0skStjxEHA.1884@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> The following statement returns all the views in an Access database.
> DataTable dt =
> this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
> object[] {null, null, null, "VIEW"});
> HTH.
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
|||Hi Developer,
I would like to follow up on this issue and see if any progress has been
made. I haven't heard from you in 2 days, were you able to check my reply?
Should you have any questions, please feel free to post here.
Looking forward to your reply!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I will use Kevin's suggestion:
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:eRfbpKKyEHA.764@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> I would like to follow up on this issue and see if any progress has been
> made. I haven't heard from you in 2 days, were you able to check my reply?
> Should you have any questions, please feel free to post here.
> Looking forward to your reply!
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi,
Great to hear that, I am just checking whether Kevin's suggestion work fine
for you and it seems it does :-)
Free feel to let us know whenever you have any questions or concnerns, we
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
I have an Access db with several tables and several queries defined. Is
there a way to import the results of a query into SQLServer, or do I first
have to save the result of the query as a table and import that?
Thanks.
You might find it easier to perform a make table and then import the
results.
Another solution that you might be able to look at would involve moving the
query into SQL Server and let SQL Server (not MS Access) combine the data
and create (or populate) a table.
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:%23TG4NxcwEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an Access db with several tables and several queries defined. Is
> there a way to import the results of a query into SQLServer, or do I first
> have to save the result of the query as a table and import that?
> Thanks.
>
|||Unfortunately, moving the queries into SQLServer is not an option.
Thanks for the reply.
> You might find it easier to perform a make table and then import the
> results.
> Another solution that you might be able to look at would involve moving
the
> query into SQL Server and let SQL Server (not MS Access) combine the data
> and create (or populate) a table.
|||Hi,
Have you tried "Import and Export Data" in SQL Server? What's your
concerns? Would you please show us more detailed scenario about your issue?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Our application imports the user's data (vendors and customers, for example)
from their datasources into SQLServer for the application to use. The user
can select to import from Access, Excel, or an ODBC datasource. When Access
is selected, we create an OleDbConnection object; the ConnectionString
property is something like:
@."Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\SrcDataSmall.mdb;User Id=;Password=;""
To get the list of tables in the db, we call:
DataTable schemaTable =
dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.T ables, new object[] {null,
null, null, "TABLE"});
Is there a way to get a list of queries in the db, and import the result set
as though it were a table?
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:FQ5ZqQlwEHA.3984@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you tried "Import and Export Data" in SQL Server? What's your
> concerns? Would you please show us more detailed scenario about your
issue?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Developer,
The following statement returns all the views in an Access database.
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
HTH.
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
|||Thank you, Kevin, I'll try that. As I looked in to GetOleDbSchemaTable(), I
wondered if "View" was what I needed to use.
"Kevin Yu [MSFT]" <v-kevy@.online.microsoft.com> wrote in message
news:$0skStjxEHA.1884@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> The following statement returns all the views in an Access database.
> DataTable dt =
> this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
> object[] {null, null, null, "VIEW"});
> HTH.
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
|||Hi Developer,
I would like to follow up on this issue and see if any progress has been
made. I haven't heard from you in 2 days, were you able to check my reply?
Should you have any questions, please feel free to post here.
Looking forward to your reply!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I will use Kevin's suggestion:
DataTable dt =
this.oleDbConnection1.GetOleDbSchemaTable(OleDbSch emaGuid.Tables, new
object[] {null, null, null, "VIEW"});
Thanks for your help.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:eRfbpKKyEHA.764@.cpmsftngxa10.phx.gbl...
> Hi Developer,
> I would like to follow up on this issue and see if any progress has been
> made. I haven't heard from you in 2 days, were you able to check my reply?
> Should you have any questions, please feel free to post here.
> Looking forward to your reply!
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi,
Great to hear that, I am just checking whether Kevin's suggestion work fine
for you and it seems it does :-)
Free feel to let us know whenever you have any questions or concnerns, we
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Importing MS Access queries and relationships to MS SQL Server
Dear fellow programmers,
I am having a big problem on how to import queries and relationships
from MS Access to SQL Server. Any suggestions will be a great help & will be
appreciated.
Thanks in advance,
Jeri
What is the specific problem? More generally, understand that there are
differences between MS Access and SQL Server.
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:CE9B458D-6EAA-4678-87E5-08CC7D5D8739@.microsoft.com...
> Dear fellow programmers,
> I am having a big problem on how to import queries and
relationships
> from MS Access to SQL Server. Any suggestions will be a great help & will
be
> appreciated.
> Thanks in advance,
> Jeri
sql
I am having a big problem on how to import queries and relationships
from MS Access to SQL Server. Any suggestions will be a great help & will be
appreciated.
Thanks in advance,
Jeri
What is the specific problem? More generally, understand that there are
differences between MS Access and SQL Server.
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:CE9B458D-6EAA-4678-87E5-08CC7D5D8739@.microsoft.com...
> Dear fellow programmers,
> I am having a big problem on how to import queries and
relationships
> from MS Access to SQL Server. Any suggestions will be a great help & will
be
> appreciated.
> Thanks in advance,
> Jeri
sql
Wednesday, March 21, 2012
importing from access to SQL Server
can i import my Access queries into SQL Server, i've tried and all it does is create new tables with the data form the queriescan i import my Access queries into SQL Server, i've tried and all it does is create new tables with the data form the queries
I'm assuming you want the query created as a view in sql server.
Go the the SQL view and cut and paste the DML in to query analyzer...you may need to modify it some.
Post the code here so we can have a look.|||The reason you may need to modify it is because Access SQL syntax is not exactly the same as MSSQL syntax.
I'm assuming you want the query created as a view in sql server.
Go the the SQL view and cut and paste the DML in to query analyzer...you may need to modify it some.
Post the code here so we can have a look.|||The reason you may need to modify it is because Access SQL syntax is not exactly the same as MSSQL syntax.
Sunday, February 19, 2012
Importing Access 2000 queries
Hello,
Brand new to SQL. I got all my data over to the SQL database. Is there a
way to get the access queries into SQL?
Thanks,
Gale
"Gale Coleman" <gcoleman@.legalassist.org> wrote in message
news:uxh9DkRkEHA.3828@.TK2MSFTNGP10.phx.gbl...
> Brand new to SQL. I got all my data over to the SQL database. Is there a
> way to get the access queries into SQL?
Access queries may or may not translate depending on how they are written.
On the SQL Server side, to access or manipulate data you have the option of
creating Views or Stored Procedures. Views are probably closest to Access
queries, and even have an 'SQL' window display of the query (you could try a
copy/paste of the Access query to a view as a starting point).
Steve
Brand new to SQL. I got all my data over to the SQL database. Is there a
way to get the access queries into SQL?
Thanks,
Gale
"Gale Coleman" <gcoleman@.legalassist.org> wrote in message
news:uxh9DkRkEHA.3828@.TK2MSFTNGP10.phx.gbl...
> Brand new to SQL. I got all my data over to the SQL database. Is there a
> way to get the access queries into SQL?
Access queries may or may not translate depending on how they are written.
On the SQL Server side, to access or manipulate data you have the option of
creating Views or Stored Procedures. Views are probably closest to Access
queries, and even have an 'SQL' window display of the query (you could try a
copy/paste of the Access query to a view as a starting point).
Steve
Importing Access 2000 queries
Hello,
Brand new to SQL. I got all my data over to the SQL database. Is there a
way to get the access queries into SQL?
Thanks,
Gale"Gale Coleman" <gcoleman@.legalassist.org> wrote in message
news:uxh9DkRkEHA.3828@.TK2MSFTNGP10.phx.gbl...
> Brand new to SQL. I got all my data over to the SQL database. Is there a
> way to get the access queries into SQL?
Access queries may or may not translate depending on how they are written.
On the SQL Server side, to access or manipulate data you have the option of
creating Views or Stored Procedures. Views are probably closest to Access
queries, and even have an 'SQL' window display of the query (you could try a
copy/paste of the Access query to a view as a starting point).
Steve
Brand new to SQL. I got all my data over to the SQL database. Is there a
way to get the access queries into SQL?
Thanks,
Gale"Gale Coleman" <gcoleman@.legalassist.org> wrote in message
news:uxh9DkRkEHA.3828@.TK2MSFTNGP10.phx.gbl...
> Brand new to SQL. I got all my data over to the SQL database. Is there a
> way to get the access queries into SQL?
Access queries may or may not translate depending on how they are written.
On the SQL Server side, to access or manipulate data you have the option of
creating Views or Stored Procedures. Views are probably closest to Access
queries, and even have an 'SQL' window display of the query (you could try a
copy/paste of the Access query to a view as a starting point).
Steve
Importing Access 2000 queries
Hello,
Brand new to SQL. I got all my data over to the SQL database. Is there a
way to get the access queries into SQL?
Thanks,
Gale"Gale Coleman" <gcoleman@.legalassist.org> wrote in message
news:uxh9DkRkEHA.3828@.TK2MSFTNGP10.phx.gbl...
> Brand new to SQL. I got all my data over to the SQL database. Is there a
> way to get the access queries into SQL?
Access queries may or may not translate depending on how they are written.
On the SQL Server side, to access or manipulate data you have the option of
creating Views or Stored Procedures. Views are probably closest to Access
queries, and even have an 'SQL' window display of the query (you could try a
copy/paste of the Access query to a view as a starting point).
Steve
Brand new to SQL. I got all my data over to the SQL database. Is there a
way to get the access queries into SQL?
Thanks,
Gale"Gale Coleman" <gcoleman@.legalassist.org> wrote in message
news:uxh9DkRkEHA.3828@.TK2MSFTNGP10.phx.gbl...
> Brand new to SQL. I got all my data over to the SQL database. Is there a
> way to get the access queries into SQL?
Access queries may or may not translate depending on how they are written.
On the SQL Server side, to access or manipulate data you have the option of
creating Views or Stored Procedures. Views are probably closest to Access
queries, and even have an 'SQL' window display of the query (you could try a
copy/paste of the Access query to a view as a starting point).
Steve
Subscribe to:
Posts (Atom)