Error 156 Creating View

I tried writing the following statement in a stored procedure, and I get an
Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
CREATE VIEW VW_TotalAccepted
AS
SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
FROM [DelDetail]
GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
What's wrong with the syntax? Isn't it the way to create a View?You cannot create a VIEW inside a SP
"wrytat" wrote:

> I tried writing the following statement in a stored procedure, and I get a
n
> Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
> CREATE VIEW VW_TotalAccepted
> AS
> SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
> FROM [DelDetail]
> GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
> What's wrong with the syntax? Isn't it the way to create a View?|||Then... Just ask a stupid question regarding View. If I add a new row or
change some values of some columns in a table that is included in a View
after creating the View, will the change be reflected in the View?
"Chandra" wrote:
> You cannot create a VIEW inside a SP
> "wrytat" wrote:
>|||Yes. A view doesn't store any data, it is only a "view" into the data stored
in your tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:B14BFAD9-CF63-4645-A61C-86AA8854F4D1@.microsoft.com...
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>|||Yes. View doesnt store any data. Selecting rows from a view is as good as
executing the query that created it.
We go for views because, it improves the performance of a query.
thanks and regards
Chandra
"wrytat" wrote:
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>

Error 1389 (aka 0x80070576 or STATUS_TIME_DIFFERENCE_AT_DC or ERROR_TIME_SKEW)?

Hello
When executing a BULK INSERT sql statement from within a program one of our
clients gets:
SQL Server Error: SQL State: 42000, SQL Error Code: 4861
Could not bulk insert because file
'\\W2K3SRV2\DTASQL$\Xtb860_dv_salspec.TXT' could not be opened. Operating
system error code 1398(There is a time and/or date difference between the
client and server.).
I have looked around the MS Knowledgebase, Google groups, and the internet
in general and have come up with two articles
http://support.microsoft.com/?id=830619
http://www.myitforum.com/articles/8/view.asp?id=6853
but they do not seem to apply to SQL Server. Our app *is* running on a
W2003Server machine named W2K3SRV2 with SQL Server 2000 on another
W2003Server machine named W2K3SRV4, so SQL server is fetching the text file
from W2K3SRV2.
BITS (as mentioned in the first link) is running on W2K3SRV2 but has no
connection with our app.
What could be going on here?
TIA
JanHave you tried syncing the times on the client and server with the domain
controller? Although the article doesn't mention SQL Server specifically,
any application using the underlying Win32 API could get error code 1398 due
to the time difference.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:%23nErEnaeGHA.3792@.TK2MSFTNGP03.phx.gbl...
> Hello
> When executing a BULK INSERT sql statement from within a program one of
> our
> clients gets:
> SQL Server Error: SQL State: 42000, SQL Error Code: 4861
> Could not bulk insert because file
> '\\W2K3SRV2\DTASQL$\Xtb860_dv_salspec.TXT' could not be opened. Operating
> system error code 1398(There is a time and/or date difference between the
> client and server.).
> I have looked around the MS Knowledgebase, Google groups, and the internet
> in general and have come up with two articles
> http://support.microsoft.com/?id=830619
> http://www.myitforum.com/articles/8/view.asp?id=6853
> but they do not seem to apply to SQL Server. Our app *is* running on a
> W2003Server machine named W2K3SRV2 with SQL Server 2000 on another
> W2003Server machine named W2K3SRV4, so SQL server is fetching the text
> file
> from W2K3SRV2.
> BITS (as mentioned in the first link) is running on W2K3SRV2 but has no
> connection with our app.
> What could be going on here?
> TIA
> Jan
>

Error 120

Hello.
I'm trying to run the following statement:
insert into [destination table]
([fields])
select [fields] from [source table]
When I try to run it, I get the following error messages:
Server: Msg 120, Level 15, State 1, Line 8
The select list for the INSERT statement contains fewer
items than the insert list. The number of SELECT values
must match the number of INSERT columns.
Server: Msg 8180, Level 16, State 1, Line 8
Statement(s) could not be prepared.
The select list matches the insert list exactly.
The source and destination tables are located on two
different (linked) servers.
Both servers are running MSSQL 2000 SP3a.
What am I doing wrong?
Thank you.Without seeing the actual insert statement we can only guess. My guess
would be you have a space in one of the column names.
--
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:12a5601c4432a$dcaeb0e0$a301280a@.phx.gbl...
> Hello.
> I'm trying to run the following statement:
> insert into [destination table]
> ([fields])
> select [fields] from [source table]
> When I try to run it, I get the following error messages:
> Server: Msg 120, Level 15, State 1, Line 8
> The select list for the INSERT statement contains fewer
> items than the insert list. The number of SELECT values
> must match the number of INSERT columns.
> Server: Msg 8180, Level 16, State 1, Line 8
> Statement(s) could not be prepared.
> The select list matches the insert list exactly.
> The source and destination tables are located on two
> different (linked) servers.
> Both servers are running MSSQL 2000 SP3a.
> What am I doing wrong?
> Thank you.
>|||No, the statement is definitely OK, it works on other
servers, there's only one server it doesn't work on. And
it works if I run it locally, but it doesn't work when I
run it from another server.
Here are the statements:
declare @.IdSpatiu int,
@.IdTran int
set @.IdSpatiu = 15
set @.IdTran = 20
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select @.IdSpatiu, @.IdTran, Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9
If I replace "select @.IdSpatiu, @.IdTran, Numar"
with "select 15, 20, Numar", it works. It doesn't like
the variables in the select list.
If I'm connected to the server [ts-331], it works. But if
I'm connected to another server (to which ts-331 is
linked), it doesn't work.
It must be something about the ts-331 server. At first I
thought it must be the service pack, because it had no
service pack installed, but then I installed SP3a and the
problem persisted.
Thank you.
>--Original Message--
>Without seeing the actual insert statement we can only
guess. My guess
>would be you have a space in one of the column names.
>--|||It looks fine to me as well. Maybe there is something strange with the way
the Linked server is set up on that machine. Why do you want to do an
insert like that remotely anyway? It would be much cleaner if you had a
stored proc on the linked server that you call and pass in the 2 parameters.
That way the sp does not have to make any linked server calls at all when
actually doing the Insert.
--
Andrew J. Kelly
SQL Server MVP
"Vlad Soare" <vsoare@.hotmail.com> wrote in message
news:1318b01c443b8$d05fa8b0$a401280a@.phx.gbl...
> No, the statement is definitely OK, it works on other
> servers, there's only one server it doesn't work on. And
> it works if I run it locally, but it doesn't work when I
> run it from another server.
> Here are the statements:
> declare @.IdSpatiu int,
> @.IdTran int
> set @.IdSpatiu = 15
> set @.IdTran = 20
> insert into [ts-331].Ploiesti.dbo.Factura
> (IdSpatiu, IdTran, Numar)
> select @.IdSpatiu, @.IdTran, Numar
> from [ts-331].Deva.dbo.Factura
> where IdTran = 11 and IdSpatiu = 9
> If I replace "select @.IdSpatiu, @.IdTran, Numar"
> with "select 15, 20, Numar", it works. It doesn't like
> the variables in the select list.
> If I'm connected to the server [ts-331], it works. But if
> I'm connected to another server (to which ts-331 is
> linked), it doesn't work.
> It must be something about the ts-331 server. At first I
> thought it must be the service pack, because it had no
> service pack installed, but then I installed SP3a and the
> problem persisted.
> Thank you.
> >--Original Message--
> >Without seeing the actual insert statement we can only
> guess. My guess
> >would be you have a space in one of the column names.
> >
> >--
>|||I had the same problem. Try this. I don't know why it works but it
does.
insert into [ts-331].Ploiesti.dbo.Factura
(IdSpatiu, IdTran, Numar)
select (select @.IdSpatiu), (select @.IdTran), Numar
from [ts-331].Deva.dbo.Factura
where IdTran = 11 and IdSpatiu = 9

Error = Arithmetic overflow error converting expression to data type smalldatetim

$exception {"Arithmetic overflow error converting expression to data type smalldatetime.\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}

occurs

here is my code

protectedvoid EmailSubmitBtn_Click(object sender,EventArgs e)

{

SqlDataSource NewsletterSqlDataSource =newSqlDataSource();

NewsletterSqlDataSource.ConnectionString =

ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();

//Text version

NewsletterSqlDataSource.InsertCommandType =

SqlDataSourceCommandType.Text;

NewsletterSqlDataSource.InsertCommand =

"INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@.EmailAddress, @.IPAddress, @.DateTimeStamp)";

//storeprocedure version//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;//NewsletterSqlDataSource.InsertCommand = "EmailInsert";

NewsletterSqlDataSource.InsertParameters.Add(

"EmailAddress", EmailTb.Text);

NewsletterSqlDataSource.InsertParameters.Add(

"IPAddress", Request.UserHostAddress.ToString());

NewsletterSqlDataSource.InsertParameters.Add(

"DateTimeStamp",DateTime.Now.ToString());int rowsAffected = 0;try

{

rowsAffected = NewsletterSqlDataSource.Insert();

}

catch (Exception ex)

{

Server.Transfer(

"NewsletterProblem.aspx");

}

finally

{

NewsletterSqlDataSource =

null;

}

if (rowsAffected != 1)

{

Server.Transfer(

"NewsletterProblem.aspx");

}

else

{

Server.Transfer(

"NewsletterSuccess.aspx");

}

Do not pass the Value asDateTime.Now.ToString() . Just pass the value asDateTime.Now if the db datatype is smalldatetime. If the input datatype for stored procedure is string , try

DateTime

.Now.ToShortDateString(); . Let me know if you need any clarifications

Error - Trancount Reset!

Hi,
I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
setting the autoCommit feature of JDBC.
We often have objects that contain transactional updates that may be called
by another object that has already initiated a transaction or that may be
used alone, thus we need a form of transaction nesting which the autoCommit
feature does not provide.
However, we have noticed that the @.@.TRANCOUNT will reset to 0 once a
statement object is created on the same connection - for example, we would
expect:
FIRST OBJECT
BEGIN TRANSACTION
... SOME WORK (TRANCOUNT = 1)
SECOND OBJECT
BEGIN TRANSACTION (TRANCOUNT = 2)
.. SOME WORK
COMMIT TRANSACTION (TRANCOUNT = 1)
FIRST OBJECT
COMMIT TRANSACTION (TRANCOUNT = 0)
However, we have noticed that as soon as a Statement object is created in
the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
COMMIT TRANSACTION statement is issued, an error is raised because there is
no matching BEGIN TRANSACTION.
Has anyone ever encountered this error? Is it a bug?
Thanks,
Mike
MikeF wrote:

> Hi,
> I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
> and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
> setting the autoCommit feature of JDBC.
Very dangerous.

> We often have objects that contain transactional updates that may be called
> by another object that has already initiated a transaction or that may be
> used alone, thus we need a form of transaction nesting which the autoCommit
> feature does not provide.
That doesn't explain why the autoCommit() path won't work. You set autoCommit(false)
and call what you want. anything below needn't know whether it's in a tx or not.
Then commit or roll back.

> However, we have noticed that the @.@.TRANCOUNT will reset to 0 once a
> statement object is created on the same connection - for example, we would
> expect:
> FIRST OBJECT
> BEGIN TRANSACTION
> ... SOME WORK (TRANCOUNT = 1)
> SECOND OBJECT
> BEGIN TRANSACTION (TRANCOUNT = 2)
> .. SOME WORK
> COMMIT TRANSACTION (TRANCOUNT = 1)
> FIRST OBJECT
> COMMIT TRANSACTION (TRANCOUNT = 0)
> However, we have noticed that as soon as a Statement object is created in
> the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
> COMMIT TRANSACTION statement is issued, an error is raised because there is
> no matching BEGIN TRANSACTION.
> Has anyone ever encountered this error? Is it a bug?
You need to add a connection property selectMethod=cursor. Otherwise
the driver will make *new connections* under the covers to implement
concurrent statements, and each statement will be independent of
the connection/statement on which you think you started a transaction.
This would have been prevented/revealed immediately if you had stuck to
JDBC control calls for defining your transactions.
Joe Weinstein at BEA
> Thanks,
> Mike

Error - Application uses a value of the wrong type for the current operation

I have a sql statement using MS SQL Server as follows
SELECT TowerNumber, TowerNumber AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth) AND
(LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
When I use the same query and add group by, I get the error - Application
uses a value of the wrong type for the current operation
SELECT TowerNumber, COUNT(TowerNumber) AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth)
GROUP BY TowerNumber, LocationID, SystemID
HAVING (LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
How do I get past this error?
ThanksHi,
Have u used any variable with datatype uniqueidentifier.If yes then
change the datatype.
from
Doller|||Not using uniqueidentifier.
I do have a field that is an identity field but it is not in the query and I
have never run into this before.
Plus the first query does work the second does not.
"doller" <sufianarif@.gmail.com> wrote in message
news:1128420745.167906.89260@.o13g2000cwo.googlegroups.com...
> Hi,
> Have u used any variable with datatype uniqueidentifier.If yes then
> change the datatype.
> from
> Doller
>|||Hi Craig,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood adding GROUP BY will lead to the error
message "Application uses a value of the wrong type for the current
operation". If I have misunderstood your concern, please feel free to point
it out.
Based on my knowledge, this could be caused by various reasons and let
perform the troubleshooting step by step.
1. Check the length of parameters. The error could if the length of the
data passed to the parameter exceeds the
maximum size of the parameter or field.
2. Use the Profiler to trace what was inserted into SQL Server
3. If we replace all the parameters with real value and execute the
replaced statements in Query Analyzer, will it report the error message?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Error - Application uses a value of the wrong type for the current operation

I have a sql statement using MS SQL Server as follows
SELECT TowerNumber, TowerNumber AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth) AND
(LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
When I use the same query and add group by, I get the error - Application
uses a value of the wrong type for the current operation
SELECT TowerNumber, COUNT(TowerNumber) AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth)
GROUP BY TowerNumber, LocationID, SystemID
HAVING (LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
How do I get past this error?
ThanksHi,
Have u used any variable with datatype uniqueidentifier.If yes then
change the datatype.
from
Doller|||Not using uniqueidentifier.
I do have a field that is an identity field but it is not in the query and I
have never run into this before.
Plus the first query does work the second does not.
"doller" <sufianarif@.gmail.com> wrote in message
news:1128420745.167906.89260@.o13g2000cwo.googlegroups.com...
> Hi,
> Have u used any variable with datatype uniqueidentifier.If yes then
> change the datatype.
> from
> Doller
>|||Hi Craig,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood adding GROUP BY will lead to the error
message "Application uses a value of the wrong type for the current
operation". If I have misunderstood your concern, please feel free to point
it out.
Based on my knowledge, this could be caused by various reasons and let
perform the troubleshooting step by step.
1. Check the length of parameters. The error could if the length of the
data passed to the parameter exceeds the
maximum size of the parameter or field.
2. Use the Profiler to trace what was inserted into SQL Server
3. If we replace all the parameters with real value and execute the
replaced statements in Query Analyzer, will it report the error message?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Error - Application uses a value of the wrong type for the current operation

I have a sql statement using MS SQL Server as follows
SELECT TowerNumber, TowerNumber AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth) AND
(LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
When I use the same query and add group by, I get the error - Application
uses a value of the wrong type for the current operation
SELECT TowerNumber, COUNT(TowerNumber) AS CountEntriesPerTowerNumber
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningMonth AND @.EndingMonth)
GROUP BY TowerNumber, LocationID, SystemID
HAVING (LocationID = @.LocationID) AND (SystemID = @.SystemID)
ORDER BY LocationID, SystemID, TowerNumber
How do I get past this error?
Thanks
Hi,
Have u used any variable with datatype uniqueidentifier.If yes then
change the datatype.
from
Doller
|||Not using uniqueidentifier.
I do have a field that is an identity field but it is not in the query and I
have never run into this before.
Plus the first query does work the second does not.
"doller" <sufianarif@.gmail.com> wrote in message
news:1128420745.167906.89260@.o13g2000cwo.googlegro ups.com...
> Hi,
> Have u used any variable with datatype uniqueidentifier.If yes then
> change the datatype.
> from
> Doller
>
|||Hi Craig,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood adding GROUP BY will lead to the error
message "Application uses a value of the wrong type for the current
operation". If I have misunderstood your concern, please feel free to point
it out.
Based on my knowledge, this could be caused by various reasons and let
perform the troubleshooting step by step.
1. Check the length of parameters. The error could if the length of the
data passed to the parameter exceeds the
maximum size of the parameter or field.
2. Use the Profiler to trace what was inserted into SQL Server
3. If we replace all the parameters with real value and execute the
replaced statements in Query Analyzer, will it report the error message?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.