Hello,
I use the statement "Bulk insert" to insert data from a file to one table on
SQL Server.
I do like this:
bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
wiht (FieldSeparator = ',', Rowseparator='\n')
Table Clients(num int, name char(50), comment varchar(max))
But im my file "TableClients.txt", I have one row like this:
7,Peter,'Hello,World"\n
You can see, I want to use "," in my column comment but I have this message
error:
Type mismatch...
It's because I use "," in my comment and for statement "bulk insert", it's a
field terminator and then I have a number of colums different from my table
"Client".
How Can I resolve this problem I want to use "," in column "comment"?
Thanks!I'm losing anything, I dont' see anywhere 'fieldseparator' or 'rowseparator
'
options.
"bubixx" wrote:
> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>|||You can define what is your fieldsepartore in your file txt and what is your
rowseparator.
In my case, fieldseparator=','
rowseparator='\n'
But my question, it's: can we use ',' in a string in one line of filetext'
Consult the first message which I sent for more details
"bubixx" wrote:
> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Wednesday, March 28, 2012
Problem with BCP command
I am trying to execute following command using xp_cmdshell
EXEC master..xp_cmdshell 'bcp "select nc_value from fileade.dbo.nms_command (nolock) order by nc_pk desc" queryout \\filw\ExternalTools\NMS3\reports\NMS3_20070525_051507.txt -c -S"FILW2K" -Uabc -Pabc'
but the error message returned is
Copy direction must be either 'in' or 'out'.
Syntax Error in 'queryout'.
usage: bcp [[database_name.]owner.]table_name[
lice_number] {in | out} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-q datafile_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal]
[-V [security_options]] [-Z security_mechanism] [-Q]
NULL
Further the same command is running successfully in my DEV environment.
That sounds like you have a SQL 7 bcp.exe in your path. Run "bcp -v" on both machines and make sure the versions match.|||Wrong forum. Moving to Transact-SQL.|||
When the path for the output file contains spaces or other 'non-acceptable' characters, you 'should' enclose it in double quotes.
The Server name does not need to be in double quotes.
|||Hi Tom,
I have checked the version on all the three plateform
DEV : 8.00.382
PROD : 8.00.382
TestPROD : 8.00.382
and version are same.
|||Hi Arnie,
The command is giving problem on Production environment only while in DEV & staging server working perfectly wheather to export file in local drive or in a network drive.
|||On the production server, does the SQL Agent account have permissions for the file locations?|||Not Sure how to check this,
BUt we have checkred that other scheduled JOBS which also export some file from PROD DB server to other server are working OK, Further the BCP command is executed via windows service
Windows Service
Batch File
Stored Procedure
BCP Command.
|||Somewhere you have an old bcp.exe which is being picked up. The error message you posted above is from the SQL 7 bcp.exe program, not 8.00.382. The "queryout" option was added in SQL 2000 bcp.exe.Search your hard drive for bcp.exe and remove anything not in C:\Program Files\Microsoft SQL Server\90 (or 80)\tools\binn.
|||
Hi Tom,
Your check point really help me to found the the problem although Production Hard Drive was not having the bcp.exe of version 7 instead the server was having Sybase BCP.EXE also , so whenever the window service try to execute the BCP command instead of picking up the SQL Server BCP path it was picking the Syabse BCP due to which the error was coming.
Thanks again for your help.
|||Good. I am glad you found it.Friday, March 23, 2012
problem with a view
We have a view called "vwtblBranchData" which is this: "SELECT *
FROM Branch_Master.dbo.tblBranchData"
We have a query that uses the view that has worked for over a year and now
has stopped working. The query is: select * from vwtblBranchData with
(readuncommitted) where (closed=0) and branch_num between 2000 and 2939 orde
r
by branch_num.
If I run just "select * from vwtblBranchData" or "select * from
vwtblBranchData where branch_num between 2000 and 2939 order by branch_num",
it works but when I add the "closed=0" part, it won't work. The closed colum
n
is a bit and is populated with only 0 or 1 in the table.
If I take the view out and run: "select * from branch_master..tblBranchData
with (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order by branch_num", it works.
Any idea what is happening?
Thanks,
Dan D.I put the query - "select * from vwtblBranchData where closed = 0 and
branch_num between 2000 and 2939 order by branch_num" in the query analyzer
and saw this: WHERE (([tblBranchData].[ActivePest]=0 AND
Convert([tblBranchData].[Branch_num])<=2939 AND
Convert([tblBranchData].[Branch_num])>=2000) ORDERED FORWARD
The "ActivePest" column isn't in the query or the view. Any idea where that
came from?
"Dan D." wrote:
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939 or
der
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by branch_num
",
> it works but when I add the "closed=0" part, it won't work. The closed col
umn
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from branch_master..tblBranchDat
a
> with (readuncommitted) where (closed=0) and branch_num between 2000 and 29
39
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Could you please tell us the error you are getting? At this point we don't
even know what is happening on your side :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
> order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
> branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
> column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
> branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
> 2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Suspect a schema or metadata change. Try recreating the view. However, the
best approach is to specify the columns you want to select, rather than use
the wildcard.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Sorry. The query wasn't returning any rows when it should have. I solved the
problem. The query analyzer was trying to use the "active_pest". We didn't
need the column so I removed it and the query worked.
Thanks.
"Louis Davidson" wrote:
> Could you please tell us the error you are getting? At this point we don'
t
> even know what is happening on your side :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
>
>|||I solved the problem. The query analyzer was trying to use the "active_pest"
.
We didn't need the column so I removed it and the query worked.
Thanks.
"Scott Morris" wrote:
> Suspect a schema or metadata change. Try recreating the view. However, t
he
> best approach is to specify the columns you want to select, rather than us
e
> the wildcard.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> order
> branch_num",
> column
> branch_master..tblBranchData
> 2939
>
>sql
FROM Branch_Master.dbo.tblBranchData"
We have a query that uses the view that has worked for over a year and now
has stopped working. The query is: select * from vwtblBranchData with
(readuncommitted) where (closed=0) and branch_num between 2000 and 2939 orde
r
by branch_num.
If I run just "select * from vwtblBranchData" or "select * from
vwtblBranchData where branch_num between 2000 and 2939 order by branch_num",
it works but when I add the "closed=0" part, it won't work. The closed colum
n
is a bit and is populated with only 0 or 1 in the table.
If I take the view out and run: "select * from branch_master..tblBranchData
with (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order by branch_num", it works.
Any idea what is happening?
Thanks,
Dan D.I put the query - "select * from vwtblBranchData where closed = 0 and
branch_num between 2000 and 2939 order by branch_num" in the query analyzer
and saw this: WHERE (([tblBranchData].[ActivePest]=0 AND
Convert([tblBranchData].[Branch_num])<=2939 AND
Convert([tblBranchData].[Branch_num])>=2000) ORDERED FORWARD
The "ActivePest" column isn't in the query or the view. Any idea where that
came from?
"Dan D." wrote:
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939 or
der
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by branch_num
",
> it works but when I add the "closed=0" part, it won't work. The closed col
umn
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from branch_master..tblBranchDat
a
> with (readuncommitted) where (closed=0) and branch_num between 2000 and 29
39
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Could you please tell us the error you are getting? At this point we don't
even know what is happening on your side :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
> order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
> branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
> column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
> branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
> 2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Suspect a schema or metadata change. Try recreating the view. However, the
best approach is to specify the columns you want to select, rather than use
the wildcard.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> We have a view called "vwtblBranchData" which is this: "SELECT *
> FROM Branch_Master.dbo.tblBranchData"
> We have a query that uses the view that has worked for over a year and now
> has stopped working. The query is: select * from vwtblBranchData with
> (readuncommitted) where (closed=0) and branch_num between 2000 and 2939
order
> by branch_num.
> If I run just "select * from vwtblBranchData" or "select * from
> vwtblBranchData where branch_num between 2000 and 2939 order by
branch_num",
> it works but when I add the "closed=0" part, it won't work. The closed
column
> is a bit and is populated with only 0 or 1 in the table.
> If I take the view out and run: "select * from
branch_master..tblBranchData
> with (readuncommitted) where (closed=0) and branch_num between 2000 and
2939
> order by branch_num", it works.
> Any idea what is happening?
> Thanks,
>
> --
> Dan D.|||Sorry. The query wasn't returning any rows when it should have. I solved the
problem. The query analyzer was trying to use the "active_pest". We didn't
need the column so I removed it and the query worked.
Thanks.
"Louis Davidson" wrote:
> Could you please tell us the error you are getting? At this point we don'
t
> even know what is happening on your side :)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
>
>|||I solved the problem. The query analyzer was trying to use the "active_pest"
.
We didn't need the column so I removed it and the query worked.
Thanks.
"Scott Morris" wrote:
> Suspect a schema or metadata change. Try recreating the view. However, t
he
> best approach is to specify the columns you want to select, rather than us
e
> the wildcard.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:DCFD1C2F-6C74-47AE-B53E-588405749CD4@.microsoft.com...
> order
> branch_num",
> column
> branch_master..tblBranchData
> 2939
>
>sql
problem with a table
Hi All,
When I try it to push a suscriber I got an error:
The process could not bulk copy out of table '[dbo].[OrderDetails]'.
Function sequence error
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
In the snopshot said: Running -- generating conflict schema script for
article ['name']
What is the problem here?
Tks in advance
Johnny
Nobody have an idea of this?
This problem is when I create the publication of a merge replication.
At the snapshot initialization.
Tks
Johnny
"JFB" <jfb@.newSQL.com> wrote in message
news:usaLmKy1EHA.412@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> When I try it to push a suscriber I got an error:
> The process could not bulk copy out of table '[dbo].[OrderDetails]'.
> Function sequence error
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> In the snopshot said: Running -- generating conflict schema script for
> article ['name']
> What is the problem here?
> Tks in advance
> Johnny
>
|||This could mean that the snapshot agent timed out or was
blocked. Try stopping and restarting the snapshot agent.
If you are dealing with a large table, you may need to
manually transfer it to the subscriber. Also, can you
enable logging (http://support.microsoft.com/?id=312292)
to get a detailed output, as it may be a disk issue.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Tks Paul for you reply and help.
1. Restart the snapshot agent give me the same error.
2. The table is no large (database size is 58mb)
3. I enable logging with number 2 and I got this error... what it is mean?
Generating schema script for article '[spTestCSV]'
*** [Article:'spTestCSV'] Time generating all schema scripts: 141 (ms) ***
SourceTypeId = 5
SourceName = SERVER-SQL
ErrorCode = 3724
ErrorText = Cannot drop the procedure
'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal' because it is being used
for replication.
Cannot drop the procedure 'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal'
because it is being used for replication.
Disconnecting from Publisher 'SERVER-SQL'
4. I have plenty space in my Hard drive.
Regards
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:207a01c4d859$0c9ed000$a501280a@.phx.gbl...
> This could mean that the snapshot agent timed out or was
> blocked. Try stopping and restarting the snapshot agent.
> If you are dealing with a large table, you may need to
> manually transfer it to the subscriber. Also, can you
> enable logging (http://support.microsoft.com/?id=312292)
> to get a detailed output, as it may be a disk issue.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Strange
- Is this error definitely produced when
running the snapshot agent (and not the merge agent)? If
it is the latter, do you have a publication of the same
table on the subscriber?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I only create the publication and only the snapshot is there. I will pull a
suscriber later.
Tks
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:07e701c4d883$d0b03220$a301280a@.phx.gbl...
> Strange - Is this error definitely produced when
> running the snapshot agent (and not the merge agent)? If
> it is the latter, do you have a publication of the same
> table on the subscriber?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
When I try it to push a suscriber I got an error:
The process could not bulk copy out of table '[dbo].[OrderDetails]'.
Function sequence error
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
In the snopshot said: Running -- generating conflict schema script for
article ['name']
What is the problem here?
Tks in advance
Johnny
Nobody have an idea of this?
This problem is when I create the publication of a merge replication.
At the snapshot initialization.
Tks
Johnny
"JFB" <jfb@.newSQL.com> wrote in message
news:usaLmKy1EHA.412@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> When I try it to push a suscriber I got an error:
> The process could not bulk copy out of table '[dbo].[OrderDetails]'.
> Function sequence error
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> In the snopshot said: Running -- generating conflict schema script for
> article ['name']
> What is the problem here?
> Tks in advance
> Johnny
>
|||This could mean that the snapshot agent timed out or was
blocked. Try stopping and restarting the snapshot agent.
If you are dealing with a large table, you may need to
manually transfer it to the subscriber. Also, can you
enable logging (http://support.microsoft.com/?id=312292)
to get a detailed output, as it may be a disk issue.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Tks Paul for you reply and help.
1. Restart the snapshot agent give me the same error.
2. The table is no large (database size is 58mb)
3. I enable logging with number 2 and I got this error... what it is mean?
Generating schema script for article '[spTestCSV]'
*** [Article:'spTestCSV'] Time generating all schema scripts: 141 (ms) ***
SourceTypeId = 5
SourceName = SERVER-SQL
ErrorCode = 3724
ErrorText = Cannot drop the procedure
'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal' because it is being used
for replication.
Cannot drop the procedure 'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal'
because it is being used for replication.
Disconnecting from Publisher 'SERVER-SQL'
4. I have plenty space in my Hard drive.
Regards
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:207a01c4d859$0c9ed000$a501280a@.phx.gbl...
> This could mean that the snapshot agent timed out or was
> blocked. Try stopping and restarting the snapshot agent.
> If you are dealing with a large table, you may need to
> manually transfer it to the subscriber. Also, can you
> enable logging (http://support.microsoft.com/?id=312292)
> to get a detailed output, as it may be a disk issue.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Strange
- Is this error definitely produced whenrunning the snapshot agent (and not the merge agent)? If
it is the latter, do you have a publication of the same
table on the subscriber?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I only create the publication and only the snapshot is there. I will pull a
suscriber later.
Tks
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:07e701c4d883$d0b03220$a301280a@.phx.gbl...
> Strange
- Is this error definitely produced when> running the snapshot agent (and not the merge agent)? If
> it is the latter, do you have a publication of the same
> table on the subscriber?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
Tuesday, March 20, 2012
Problem while using xp_sendmail
Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KP
XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP
|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
>
>
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KP
XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP
|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
>
>
Problem while using xp_sendmail
Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is bein
g
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.co
m>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com..
.
>
>
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is bein
g
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.co
m>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com..
.
>
>
Problem while using xp_sendmail
Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> > Hi,
> >
> > When I use xp_sendmail on my local machine, using parameters like
> > Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> > some message', @.subject = ' some subject ', then it works fine and I
> > get
> > the mail in my mailbox ...
> >
> > But when I use the same stuff on any other SQL server (on same network), I
> > get the error --
> > xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> >
> > I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> >
> > What and where is the problem ?
> >
> > regards
> > KP
>
>
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> > Hi,
> >
> > When I use xp_sendmail on my local machine, using parameters like
> > Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> > some message', @.subject = ' some subject ', then it works fine and I
> > get
> > the mail in my mailbox ...
> >
> > But when I use the same stuff on any other SQL server (on same network), I
> > get the error --
> > xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> >
> > I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> >
> > What and where is the problem ?
> >
> > regards
> > KP
>
>
Friday, March 9, 2012
Problem when changing Views
I have 3 views, two of which depend on the other:
Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
purpose. Yes, you may feel that when you do a SELECT on the view
that you get back the rows in the same order as the ORDER BY clause,
but that is due to mere chance. Many people who had this sort of
views found that they no longer the result they expected when they
moved to SQL 2005.
You should stop using SELECT *. SELECT * is great for ad-hoc queries,
but it does not belong in production code. One reason for that is what
you experienced. Had you listed the columns explicitly, you would still
have gotten an error, but at least the error would have been apparent.
Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQuery had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.
Thank you for your warning about the use of Select * . I expect that
there has been extensive discussion elsewhere as to why 'it does not
belong in production code' but I was unaware of it. Perhaps you can
point me in the right direction.
There aree several reasons. One is tracability. Is the column xyz in use
somewhere? It's possible to find via sysdepends it is (although sysdepends
for various reasons isn't always reliable), but then you find that it
is a SELECT *, you cannot tell whether it is use at all. That is,
queries should list columns that are actually used. In any serious system
there are columns that are one point phased out - or could be phased out,
if you could verify that they are no longer in use.
I use neither of the tools, but I guess that Design View resubmits the
view definition something Return all Rows have no reason to do. Using
Profiler would reveal what is going on.
In Query Analyzer where you run other queries. As for how, well, did
you try Books Online?
Yes.
TOP 10 PERCENT ORDER BY is meaningful, because it picks the the top 10
percent according to the ORDER BY clause.
CREATE VIEW dbo.CustomerListQueryAccounts
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 0)
CREATE VIEW dbo.CustomerListQueryProspects
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 1)
which depend on:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber,
dbo.NumberOfJobsPerCustomer.CountOfJobID,
dbo.NumberOfQuotesPerCustomer.CountOfQuoteID,
dbo.NumberOfComplaintsPerCustomer.CountOfComplaint ID,
dbo.NumberOfNotesPerCustomer.CountOfCustomerNoteID ,
dbo.NumberOfEnquiriesPerCustomer.CountOfEnquiryID
FROM dbo.Customers
LEFT OUTER JOIN
dbo.NumberOfJobsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfJobsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfQuotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfQuotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfNotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfNotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfComplaintsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfComplaintsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfEnquiriesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfEnquiriesPerCustomer.CustomerID
ORDER BY dbo.Customers.AccountName
These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerListQueryShorter
AS
SELECT dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber
FROM dbo.Customers
I now wish to make this one CustomerListQuery and so rename the
existing one CustomerListQueryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerListQueryOriginal
I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery
Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.
If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.
It appears that the orginal version is still being used. How do I get
the system to replace this ?Jim Devenish (internet.shopping@.foobox.com) writes:
Quote:
Originally Posted by
I have 3 views, two of which depend on the other:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
>...
ORDER BY dbo.Customers.AccountName
Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
purpose. Yes, you may feel that when you do a SELECT on the view
that you get back the rows in the same order as the ORDER BY clause,
but that is due to mere chance. Many people who had this sort of
views found that they no longer the result they expected when they
moved to SQL 2005.
There is only one way to get an ordered result from a query, and that
is to add ORDER BY to the query itself.
Quote:
Originally Posted by
I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery
>
Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.
>
If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.
You should stop using SELECT *. SELECT * is great for ad-hoc queries,
but it does not belong in production code. One reason for that is what
you experienced. Had you listed the columns explicitly, you would still
have gotten an error, but at least the error would have been apparent.
If you insist on using SELECT *, you need to learn to use sp_refreshview,
because you will need it a lot.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
purpose. Yes, you may feel that when you do a SELECT on the view
that you get back the rows in the same order as the ORDER BY clause,
but that is due to mere chance. Many people who had this sort of
views found that they no longer the result they expected when they
moved to SQL 2005.
>
There is only one way to get an ordered result from a query, and that
is to add ORDER BY to the query itself.
>
Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQuery had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.
Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQuery Order By AccountName
I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.
Similarly when I look up the syntax of Create View I find, within the
select_statement that:
"A Create View statement cannot include Order By clause, unless there
is also a TOP clause in the select list of the Select statement"
It does not say that this is nonsense.|||Erland Sommarskog wrote:
Quote:
Originally Posted by
You should stop using SELECT *. SELECT * is great for ad-hoc queries,
but it does not belong in production code. One reason for that is what
you experienced. Had you listed the columns explicitly, you would still
have gotten an error, but at least the error would have been apparent.
>
If you insist on using SELECT *, you need to learn to use sp_refreshview,
because you will need it a lot.
>
Thank you for your warning about the use of Select * . I expect that
there has been extensive discussion elsewhere as to why 'it does not
belong in production code' but I was unaware of it. Perhaps you can
point me in the right direction.
However you say that I would have still got an error had I listed the
columns explicitly. So I return to my orginal question: why does the
new view produce the expected output from the Design View but not from
'Return all rows'?
How and where do I use sp_refreshview?|||Hey Jim,
There are a number of issues with your approach, but the primary
reason that you're having the problems you are is that you're using
Enterprise Manager (known by many as Enterprise MANGLER) to edit the
script of your views. EM is a good tool for administration, but you
should be using Query Analzer for editing.
In Query Analyzer, you can right-click on the view and select EDIT, and
it will show you the script of the view, which you can then ALTER to
get to the correct format you need.
As far as the other issues, I think Erland tapped into them:
1. Don't use SELECT * in production code (or at least don't do so
wihout commenting). There was a recent discussion in another group
about it that you may want to read.
http://groups.google.com/group/micr...2efe874da82b7ef
2. The TOP...ORDER BY embedded in a view is, at best, flaky. It's
also one of the behaviors that was changed in SQL 2005, so if you can
avoid using it, I'd recommend that you do so. The only way to ensure
an order to your results is to use ORDER BY in your final outer SELECT
statement (e.g., SELECT columnlist FROM dbo.CustomerListQueryAccounts
ORDER BY AccountName).
HTH,
Stu
Jim Devenish wrote:
Quote:
Originally Posted by
I have 3 views, two of which depend on the other:
>
CREATE VIEW dbo.CustomerListQueryAccounts
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 0)
>
CREATE VIEW dbo.CustomerListQueryProspects
AS
SELECT dbo.CustomerListQuery.*
FROM dbo.CustomerListQuery
WHERE (isProspect = 1)
>
which depend on:
CREATE VIEW dbo.CustomerListQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber,
>
dbo.NumberOfJobsPerCustomer.CountOfJobID,
dbo.NumberOfQuotesPerCustomer.CountOfQuoteID,
>
dbo.NumberOfComplaintsPerCustomer.CountOfComplaint ID,
dbo.NumberOfNotesPerCustomer.CountOfCustomerNoteID ,
>
dbo.NumberOfEnquiriesPerCustomer.CountOfEnquiryID
FROM dbo.Customers
>
LEFT OUTER JOIN
dbo.NumberOfJobsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfJobsPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfQuotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfQuotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfNotesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfNotesPerCustomer.CustomerID
LEFT OUTER JOIN
dbo.NumberOfComplaintsPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfComplaintsPerCustomer.CustomerID
>
LEFT OUTER JOIN
dbo.NumberOfEnquiriesPerCustomer ON
dbo.Customers.CustomerID = dbo.NumberOfEnquiriesPerCustomer.CustomerID
>
ORDER BY dbo.Customers.AccountName
>
These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerListQueryShorter
AS
SELECT dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
CustomerIDAsNumber
FROM dbo.Customers
>
I now wish to make this one CustomerListQuery and so rename the
existing one CustomerListQueryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerListQueryOriginal
>
I then rename CustomerListQueryShorter to CustomerListQuery and change
its first line to
CREATE VIEW dbo.CustomerListQuery
>
Now when I 'Return all rows' of CustomerListQueryAccounts I get an
error message:
'dbo.CustomerListQueryAccounts' has more column names specified than
columns defined.
>
If however, I go into the design and then select Run, I get the correct
output reflecting the new version of CustomerListQuery. How do I get
the 'Return all rows' output to show the same.
>
It appears that the orginal version is still being used. How do I get
the system to replace this ?
Quote:
Originally Posted by
Thank you for your warning about the use of Select * . I expect that
there has been extensive discussion elsewhere as to why 'it does not
belong in production code' but I was unaware of it. Perhaps you can
point me in the right direction.
There aree several reasons. One is tracability. Is the column xyz in use
somewhere? It's possible to find via sysdepends it is (although sysdepends
for various reasons isn't always reliable), but then you find that it
is a SELECT *, you cannot tell whether it is use at all. That is,
queries should list columns that are actually used. In any serious system
there are columns that are one point phased out - or could be phased out,
if you could verify that they are no longer in use.
If you add or drop columns, the SELECT * changes, but depending on context
not immediately, so there can be sources of confusion.
Quote:
Originally Posted by
However you say that I would have still got an error had I listed the
columns explicitly. So I return to my orginal question: why does the
new view produce the expected output from the Design View but not from
'Return all rows'?
I use neither of the tools, but I guess that Design View resubmits the
view definition something Return all Rows have no reason to do. Using
Profiler would reveal what is going on.
Quote:
Originally Posted by
How and where do I use sp_refreshview?
In Query Analyzer where you run other queries. As for how, well, did
you try Books Online?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jim Devenish (internet.shopping@.foobox.com) writes:
Quote:
Originally Posted by
Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQuery had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.
>
Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQuery Order By AccountName
Yes.
Quote:
Originally Posted by
I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.
TOP 10 PERCENT ORDER BY is meaningful, because it picks the the top 10
percent according to the ORDER BY clause.
TOP 100 PERCENT ORDER BY is meaningless, because 100 percent is
everything, so it does not matter what you order by. You still get
everything.
That is, the purpose of ORDER BY in views in combination of TOP is to
determine which rows that are selected by the view.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Monday, February 20, 2012
Problem using a parameter in a subquery
The following code works (assuming that there is an employee with the alias
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
Duncan
You do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
Duncan
You do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>
Subscribe to:
Posts (Atom)