Thanks for the note Vishal,
What if each had a date. So that
create table #cartype(manufacturer varchar(500), itemnumber int, datemade date)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
So that
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
Would get deleted because there the exact same records (same number) of records are duplicated for that date.
But the records:
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
The GE records would stay because all of the records are not duplicated, just the GE records are so I want to keep all the records.
Thanks for any ideas!
Try query as follows:
delete a
from #cartype a join
(select manufacturer, datemade, count(*) cnt
from #cartype
group by manufacturer, datemade
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.datemade = b.datemade and b.cnt <>
(select count(*)
from #cartype x
group by manufacturer
having x.manufacturer = b.manufacturer)
Vishal Parkar
vgparkar@.yahoo.co.in
|||Here is what I have done to make it fit my query. Here are my records:
store, deliverydate, itemnumber, qty
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
(this should get deleted, exact same as 2 lines above)
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,090495,020
007SS,04/15/2004,090495,020
(all lines should stay because it is not exact same.)
selext a.*, cnt
from tblItemOrder a join
(select itemnumber, quantity, store, deliverydate, count(*) cnt
from tblItemOrder
group by itemnumber, quantity, store, deliverydate
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.quantity = b.quantity and a.store = b.store and a.deliverydate = b.deliverydate and b.cnt <>
(select count(*)
from tblItemOrder x
group by store, deliverydate
having x.store = b.store and x.deliverydate = b.deliverydate)
I get all records that have duplicate lines, not just the ones with same count of duplicate records (storeno, deliverydate).
Any ideas? I have thought about it many different ways and have not come up with a solution yet. Thanks again,
|||hi ashley,
Remember SELECT and DELETE are different statements. DELETE will delete
the data from the table while with the help of SELECT statement you can
filterout the rows from the table.
See following example:
create table tt
(store varchar(50),
deliverydate datetime,
itemnumber varchar(50),
qty int)
--insert some data
insert into tt
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','090495','020' union all
select '007SS','04/15/2004','090495','020'
--Try this query:
select a.*
from tt a join
(select store, deliverydate, itemnumber,count(*) cnt
from tt
group by store, deliverydate, itemnumber
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.store = b.store and a.deliverydate = b.deliverydate and 1 not in
(select 1
from tt x
group by store, deliverydate, itemnumber
having x.store = b.store and x.deliverydate = b.deliverydate and
x.itemnumber <> b.itemnumber and count(*) = b.cnt)
Vishal Parkar
vgparkar@.yahoo.co.in
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Sunday, March 11, 2012
another question
Hi guys,
please create a table in tempdb running following
USE tempdb
CREATE TABLE delete_me (c1 int, c2 int )
INSERT delete_me (c1, c2)
SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
Then running the script below you can get (I do) the error message :
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'c3'.
But sometimes it works. The workaround seems to be to wrap UPDATE statement
up into EXEC, - it works always. Why is that?
BEGIN TRAN
ALTER TABLE delete_me
ADD c3 int
-- EXEC ('UPDATE delete_me SET c3 = 0')
UPDATE delete_me SET c3 = 0
ALTER TABLE delete_me
ALTER COLUMN c3 int NOT NULL
ROLLBACK TRAN
Thanks
AlexThats quite a normal behaviour. Object resolution takes place if the
object is already know so, this will fail due to the non existing
column. Look for
http://msdn.microsoft.com/library/d...>
_07_5wa6.asp
"Note Deferred Name Resolution can only be used when you reference
nonexistent table objects. All other objects must exist at the time the
stored procedure is created. For example, when you reference an
existing table in a stored procedure you cannot list nonexistent
columns for that table."
HTH, Jens Suessmeyer.|||AlexM
What is your SQL Server version?
It worked fine on my workstation (SS2000,SP3,Personal Edition)
"AlexM" <alex_remove_this_mak@.telus.net> wrote in message
news:CHYDf.157789$AP5.28253@.edtnps84...
> Hi guys,
> please create a table in tempdb running following
> USE tempdb
> CREATE TABLE delete_me (c1 int, c2 int )
> INSERT delete_me (c1, c2)
> SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
> Then running the script below you can get (I do) the error message :
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'c3'.
> But sometimes it works. The workaround seems to be to wrap UPDATE
> statement up into EXEC, - it works always. Why is that?
>
> BEGIN TRAN
> ALTER TABLE delete_me
> ADD c3 int
> -- EXEC ('UPDATE delete_me SET c3 = 0')
> UPDATE delete_me SET c3 = 0
> ALTER TABLE delete_me
> ALTER COLUMN c3 int NOT NULL
>
> ROLLBACK TRAN
>
> Thanks
> Alex
>|||Thanks Jens, that note apparently slipped my mind ;-)
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138778592.061349.312360@.o13g2000cwo.googlegroups.com...
> Thats quite a normal behaviour. Object resolution takes place if the
> object is already know so, this will fail due to the non existing
> column. Look for
> http://msdn.microsoft.com/library/d...
es_07_5wa6.asp
> "Note Deferred Name Resolution can only be used when you reference
> nonexistent table objects. All other objects must exist at the time the
> stored procedure is created. For example, when you reference an
> existing table in a stored procedure you cannot list nonexistent
> columns for that table."
>
> HTH, Jens Suessmeyer.
>|||ss2000, enterprise & developer, sp4
Jens pointed to the note which explains clearly why it happens. What it
worries me though that this behaviour is not consistent. Most of the tine it
acts according to BOL and that particular note, but sometimes the resolution
stage comes through with flying colors when referencing a missing column for
existing table. But this is a bit different story...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eL4xwIwJGHA.3332@.TK2MSFTNGP11.phx.gbl...
> AlexM
> What is your SQL Server version?
> It worked fine on my workstation (SS2000,SP3,Personal Edition)
>
> "AlexM" <alex_remove_this_mak@.telus.net> wrote in message
> news:CHYDf.157789$AP5.28253@.edtnps84...
>
please create a table in tempdb running following
USE tempdb
CREATE TABLE delete_me (c1 int, c2 int )
INSERT delete_me (c1, c2)
SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
Then running the script below you can get (I do) the error message :
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'c3'.
But sometimes it works. The workaround seems to be to wrap UPDATE statement
up into EXEC, - it works always. Why is that?
BEGIN TRAN
ALTER TABLE delete_me
ADD c3 int
-- EXEC ('UPDATE delete_me SET c3 = 0')
UPDATE delete_me SET c3 = 0
ALTER TABLE delete_me
ALTER COLUMN c3 int NOT NULL
ROLLBACK TRAN
Thanks
AlexThats quite a normal behaviour. Object resolution takes place if the
object is already know so, this will fail due to the non existing
column. Look for
http://msdn.microsoft.com/library/d...>
_07_5wa6.asp
"Note Deferred Name Resolution can only be used when you reference
nonexistent table objects. All other objects must exist at the time the
stored procedure is created. For example, when you reference an
existing table in a stored procedure you cannot list nonexistent
columns for that table."
HTH, Jens Suessmeyer.|||AlexM
What is your SQL Server version?
It worked fine on my workstation (SS2000,SP3,Personal Edition)
"AlexM" <alex_remove_this_mak@.telus.net> wrote in message
news:CHYDf.157789$AP5.28253@.edtnps84...
> Hi guys,
> please create a table in tempdb running following
> USE tempdb
> CREATE TABLE delete_me (c1 int, c2 int )
> INSERT delete_me (c1, c2)
> SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
> Then running the script below you can get (I do) the error message :
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'c3'.
> But sometimes it works. The workaround seems to be to wrap UPDATE
> statement up into EXEC, - it works always. Why is that?
>
> BEGIN TRAN
> ALTER TABLE delete_me
> ADD c3 int
> -- EXEC ('UPDATE delete_me SET c3 = 0')
> UPDATE delete_me SET c3 = 0
> ALTER TABLE delete_me
> ALTER COLUMN c3 int NOT NULL
>
> ROLLBACK TRAN
>
> Thanks
> Alex
>|||Thanks Jens, that note apparently slipped my mind ;-)
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138778592.061349.312360@.o13g2000cwo.googlegroups.com...
> Thats quite a normal behaviour. Object resolution takes place if the
> object is already know so, this will fail due to the non existing
> column. Look for
> http://msdn.microsoft.com/library/d...
es_07_5wa6.asp
> "Note Deferred Name Resolution can only be used when you reference
> nonexistent table objects. All other objects must exist at the time the
> stored procedure is created. For example, when you reference an
> existing table in a stored procedure you cannot list nonexistent
> columns for that table."
>
> HTH, Jens Suessmeyer.
>|||ss2000, enterprise & developer, sp4
Jens pointed to the note which explains clearly why it happens. What it
worries me though that this behaviour is not consistent. Most of the tine it
acts according to BOL and that particular note, but sometimes the resolution
stage comes through with flying colors when referencing a missing column for
existing table. But this is a bit different story...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eL4xwIwJGHA.3332@.TK2MSFTNGP11.phx.gbl...
> AlexM
> What is your SQL Server version?
> It worked fine on my workstation (SS2000,SP3,Personal Edition)
>
> "AlexM" <alex_remove_this_mak@.telus.net> wrote in message
> news:CHYDf.157789$AP5.28253@.edtnps84...
>
Another Problem with variables
I have this...
declare
@.variable int
create table #tmp_datos
( variable int null )
insert into #tmp_datos
exec sp_prueba_era
select @.variable = variable
from
#tmp_datos
select @.variable
drop proc sp_prueba_era
go
create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)
are there other way to do this??are you able to connect to your server using isql ?|||Yes, but I need to pass dinamically the name of the server, by this I'm using strings and exec...
But the original query it's this...
Select @.var = Select 1 from xx
I need to pass this to string...|||Originally posted by ramshree
are you able to connect to your server using isql ?
Not clear what you are trying to do.
but this works :
declare @.var char(100)
select @.var = 'select * from sysobjects'
exec (@.var)
-|||I'm trying to store a value in a variable into select statement, but my select statement it's a string that I'm going to execute with exec clause..., so I can't to do this...
declare
@.variable int,
@.cmd varchar(100)
select @.cmd = "select @.variable = 20"
exec (@.cmd)
because @.variable it's not a defined variable..., so how can I do for make a string like this...|||Maybe there is something, i haven't understood, but why is it so important, that the select is stored in the string?|||Hi,
Hope this is wat u want--
here i am assigning the count of the number of records to the variable @.varCountTable. but the whole string has to be executed in EXEC().
Personalize it to ur needs--
SET @.varSelectString = N'SELECT @.varCountTable = COUNT(*) FROM CPGSTAGEDB.DBO.' + @.varTableName
EXEC SP_EXECUTESQL @.varSelectString, N'@.varCountTable nvarchar(50) OUTPUT',@.varCountTable = @.varCountTable OUTPUT
Regards,
Ramya
Originally posted by ericka
I have this...
declare
@.variable int
create table #tmp_datos
( variable int null )
insert into #tmp_datos
exec sp_prueba_era
select @.variable = variable
from
#tmp_datos
select @.variable
drop proc sp_prueba_era
go
create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)
are there other way to do this??
declare
@.variable int
create table #tmp_datos
( variable int null )
insert into #tmp_datos
exec sp_prueba_era
select @.variable = variable
from
#tmp_datos
select @.variable
drop proc sp_prueba_era
go
create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)
are there other way to do this??are you able to connect to your server using isql ?|||Yes, but I need to pass dinamically the name of the server, by this I'm using strings and exec...
But the original query it's this...
Select @.var = Select 1 from xx
I need to pass this to string...|||Originally posted by ramshree
are you able to connect to your server using isql ?
Not clear what you are trying to do.
but this works :
declare @.var char(100)
select @.var = 'select * from sysobjects'
exec (@.var)
-|||I'm trying to store a value in a variable into select statement, but my select statement it's a string that I'm going to execute with exec clause..., so I can't to do this...
declare
@.variable int,
@.cmd varchar(100)
select @.cmd = "select @.variable = 20"
exec (@.cmd)
because @.variable it's not a defined variable..., so how can I do for make a string like this...|||Maybe there is something, i haven't understood, but why is it so important, that the select is stored in the string?|||Hi,
Hope this is wat u want--
here i am assigning the count of the number of records to the variable @.varCountTable. but the whole string has to be executed in EXEC().
Personalize it to ur needs--
SET @.varSelectString = N'SELECT @.varCountTable = COUNT(*) FROM CPGSTAGEDB.DBO.' + @.varTableName
EXEC SP_EXECUTESQL @.varSelectString, N'@.varCountTable nvarchar(50) OUTPUT',@.varCountTable = @.varCountTable OUTPUT
Regards,
Ramya
Originally posted by ericka
I have this...
declare
@.variable int
create table #tmp_datos
( variable int null )
insert into #tmp_datos
exec sp_prueba_era
select @.variable = variable
from
#tmp_datos
select @.variable
drop proc sp_prueba_era
go
create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)
are there other way to do this??
Another NQ: Inserting Image
How can i insert images into column easily. Can i do this by Query Browser?Easily? If you have the hex representation of the image you can.
Otherwise you will likely need to use a API. Google it in their groups
server and you might find what you need:
http://groups-beta.google.com/group...into+sql+server
----
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 :)
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:exhH8ruOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> How can i insert images into column easily. Can i do this by Query
> Browser?
>
Otherwise you will likely need to use a API. Google it in their groups
server and you might find what you need:
http://groups-beta.google.com/group...into+sql+server
----
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 :)
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:exhH8ruOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> How can i insert images into column easily. Can i do this by Query
> Browser?
>
Another Newbie
Trying to insert into a field that is varchar(20) but I want to pad the
entry with "0" on the beginning of it... so if the item to be written is 10
chars long I want to write 10 "0" on the front of the item.. how to
accomplish?
Thanks
--
Austin Henderson <><
Network Administratorprint replicate('0',20 -len('microsoft')) + 'microsoft'
--
gani
"Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
news:u06WsobcDHA.652@.tk2msftngp13.phx.gbl...
> Trying to insert into a field that is varchar(20) but I want to pad the
> entry with "0" on the beginning of it... so if the item to be written is
10
> chars long I want to write 10 "0" on the front of the item.. how to
> accomplish?
> Thanks
>
> --
> Austin Henderson <><
> Network Administrator
>|||Got it THANKS!
--
Austin Henderson <><
Network Administrator
"gani" <a@.a.com> wrote in message
news:Ow8oA5bcDHA.1540@.tk2msftngp13.phx.gbl...
> print replicate('0',20 -len('microsoft')) + 'microsoft'
> --
> gani
>
>
> "Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
> news:u06WsobcDHA.652@.tk2msftngp13.phx.gbl...
> > Trying to insert into a field that is varchar(20) but I want to pad the
> > entry with "0" on the beginning of it... so if the item to be written is
> 10
> > chars long I want to write 10 "0" on the front of the item.. how to
> > accomplish?
> >
> > Thanks
> >
> >
> > --
> > Austin Henderson <><
> > Network Administrator
> >
> >
>
entry with "0" on the beginning of it... so if the item to be written is 10
chars long I want to write 10 "0" on the front of the item.. how to
accomplish?
Thanks
--
Austin Henderson <><
Network Administratorprint replicate('0',20 -len('microsoft')) + 'microsoft'
--
gani
"Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
news:u06WsobcDHA.652@.tk2msftngp13.phx.gbl...
> Trying to insert into a field that is varchar(20) but I want to pad the
> entry with "0" on the beginning of it... so if the item to be written is
10
> chars long I want to write 10 "0" on the front of the item.. how to
> accomplish?
> Thanks
>
> --
> Austin Henderson <><
> Network Administrator
>|||Got it THANKS!
--
Austin Henderson <><
Network Administrator
"gani" <a@.a.com> wrote in message
news:Ow8oA5bcDHA.1540@.tk2msftngp13.phx.gbl...
> print replicate('0',20 -len('microsoft')) + 'microsoft'
> --
> gani
>
>
> "Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
> news:u06WsobcDHA.652@.tk2msftngp13.phx.gbl...
> > Trying to insert into a field that is varchar(20) but I want to pad the
> > entry with "0" on the beginning of it... so if the item to be written is
> 10
> > chars long I want to write 10 "0" on the front of the item.. how to
> > accomplish?
> >
> > Thanks
> >
> >
> > --
> > Austin Henderson <><
> > Network Administrator
> >
> >
>
Wednesday, March 7, 2012
Another Date time question
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
convert (char (8), getdate(), 112)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Charlie" <ckerns@.hotmail.com> wrote in message =news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index (Primary key). I need to insert only the date part(not the time) so that when I run my DTS package it only inserts one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
convert (char (8), getdate(), 112)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Charlie" wrote in =message news:446c01c37baa$88=07dfa0$a601280a@.phx.gbl...I have a datetime column. This column has an index (Primary key). I =need to insert only the date part(not the time) so that when I run my DTS =package it only inserts one date (TODAY's DATE) without the time. How =can I insert today's date with only date part ?Thanks.
--=_NextPart_000_030E_01C37B89.95A72100--|||This will do it:
SELECT CAST(CONVERT(char, CURRENT_TIMESTAMP, 112) AS datetime)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Charlie" <ckerns@.hotmail.com> wrote in message
news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
convert (char (8), getdate(), 112)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Charlie" <ckerns@.hotmail.com> wrote in message =news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index (Primary key). I need to insert only the date part(not the time) so that when I run my DTS package it only inserts one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
convert (char (8), getdate(), 112)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Charlie"
--=_NextPart_000_030E_01C37B89.95A72100--|||This will do it:
SELECT CAST(CONVERT(char, CURRENT_TIMESTAMP, 112) AS datetime)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Charlie" <ckerns@.hotmail.com> wrote in message
news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
Saturday, February 25, 2012
Annoying, cant insert into DB for some reason, even using a stored procedure.
Hello, I am having problems inserting information into my DB.
First is the code for the insert
Sub AddCollector(Sender As Object, E As EventArgs)
Message.InnerHtml = ""If (Page.IsValid)
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database='MyCollection'"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As SqlCommand
Dim InsertCmd As String = "insert into Collectors (CollectorID, Name, EmailAddress, Password, Information) values (@.CollectorID, @.Name, @.Email, @.Password, @.Information)"myCommand = New SqlCommand(InsertCmd, myConnection)
myCommand.Connection.Open()
myCommand.Parameters.Add(New SqlParameter("@.CollectorID", SqlDbType.NVarChar, 50))
myCommand.Parameters("@.CollectorID").Value = CollectorID.TextmyCommand.Parameters.Add(New SqlParameter("@.Name", SqlDbType.NVarChar, 50))
myCommand.Parameters("@.Name").Value = Name.TextmyCommand.Parameters.Add(New SqlParameter("@.Email", SqlDbType.NVarChar, 50))
myCommand.Parameters("@.Email").Value = EmailAddress.TextmyCommand.Parameters.Add(New SqlParameter("@.Password", SqlDbType.NVarChar, 50))
myCommand.Parameters("@.Password").Value = Password.TextmyCommand.Parameters.Add(New SqlParameter("@.Information", SqlDbType.NVarChar, 3000))
myCommand.Parameters("@.Information").Value = Information.TextTry
myCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>"
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record"
End If
Message.Style("color") = "red"
End TrymyCommand.Connection.Close()
End If
End Sub
No matter what I get a "Could not add record" message
Even substituting the insert command string with my stored procedure I would get the same thing
Stored Procedure:
CREATE Procedure CollectorAdd
(
@.Name nvarchar(50),
@.Email nvarchar(50),
@.Password nvarchar(50),
@.Information nvarchar(3000),
@.CustomerID int OUTPUT
)
ASINSERT Collectors
(
Name,
EMailAddress,
Password,
Information
)VALUES
(
@.Name,
@.Email,
@.Password,
@.Information
)
GO
Can anyone see any problems with this code? It looks good to me but I get the same message always.
ThanksWhy not print out the actual exception text (exp.ToString(), for instance)? Then the exception will tell you why.|||Wow, nice little trick. It helped me find the problem. I had an expected parameter in the SP that I was not supplying.
Thank You
Friday, February 24, 2012
Annotated Mapping Schema
I have a stored procedure that uses the FOR XML EXPLICIT mode to return an xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like to use an xsd schema, however I can't find examples that let you apply the transformation in a stored procedure / test it in Query Analyzer. All the examples I've found involve m
apping the schema in the URL or using a SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Mapping Schemas are a client-side technology - actually, they just generate
FOR XML EXPLICIT statements on the server (you can see this by running a
trace when retrieving data with a schema). As such, there's no way to
reference them from within a T-SQL sproc. Sorry!
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Paul Bibby" <Paul Bibby@.discussions.microsoft.com> wrote in message
news:4B7E0882-5EED-4A7F-987D-125262683276@.microsoft.com...
I have a stored procedure that uses the FOR XML EXPLICIT mode to return an
xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like
to use an xsd schema, however I can't find examples that let you apply the
transformation in a stored procedure / test it in Query Analyzer. All the
examples I've found involve mapping the schema in the URL or using a
SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like to use an xsd schema, however I can't find examples that let you apply the transformation in a stored procedure / test it in Query Analyzer. All the examples I've found involve m
apping the schema in the URL or using a SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Mapping Schemas are a client-side technology - actually, they just generate
FOR XML EXPLICIT statements on the server (you can see this by running a
trace when retrieving data with a schema). As such, there's no way to
reference them from within a T-SQL sproc. Sorry!
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Paul Bibby" <Paul Bibby@.discussions.microsoft.com> wrote in message
news:4B7E0882-5EED-4A7F-987D-125262683276@.microsoft.com...
I have a stored procedure that uses the FOR XML EXPLICIT mode to return an
xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like
to use an xsd schema, however I can't find examples that let you apply the
transformation in a stored procedure / test it in Query Analyzer. All the
examples I've found involve mapping the schema in the URL or using a
SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Andrea ... MSDE Error ! ... Contd ...
Hi,
I checked the links, you suggested me to research on this error “Unknown
token received from SQL Server”.
I am trying to insert 8-10 million rows in the table. I am using a while
loop to insert the row into a single table and not using cursor (Website
suggested to use LOCAL FAST FORWARD). The process was running smooth for 4.3
million rows inserted.
I am using a field VARBINARY(20) and the other fields are VARCHAR, TINYINT &
DATETIME if that helps.
Please advice,
David
hi David,
"David" <David@.discussions.microsoft.com> ha scritto nel messaggio
news:DBFF3C8B-81F2-4655-B645-3CD21B679B84@.microsoft.com
> Hi,
> I checked the links, you suggested me to research on this error
> “Unknown token received from SQL Server”.
> I am trying to insert 8-10 million rows in the table. I am using a
> while loop to insert the row into a single table and not using cursor
> (Website suggested to use LOCAL FAST FORWARD). The process was
> running smooth for 4.3 million rows inserted.
> I am using a field VARBINARY(20) and the other fields are VARCHAR,
> TINYINT & DATETIME if that helps.
> Please advice,
> David
perhaps break your transaction in smaller transactions every n rows you have
to insert...
say every 50,000 rows...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I checked the links, you suggested me to research on this error “Unknown
token received from SQL Server”.
I am trying to insert 8-10 million rows in the table. I am using a while
loop to insert the row into a single table and not using cursor (Website
suggested to use LOCAL FAST FORWARD). The process was running smooth for 4.3
million rows inserted.
I am using a field VARBINARY(20) and the other fields are VARCHAR, TINYINT &
DATETIME if that helps.
Please advice,
David
hi David,
"David" <David@.discussions.microsoft.com> ha scritto nel messaggio
news:DBFF3C8B-81F2-4655-B645-3CD21B679B84@.microsoft.com
> Hi,
> I checked the links, you suggested me to research on this error
> “Unknown token received from SQL Server”.
> I am trying to insert 8-10 million rows in the table. I am using a
> while loop to insert the row into a single table and not using cursor
> (Website suggested to use LOCAL FAST FORWARD). The process was
> running smooth for 4.3 million rows inserted.
> I am using a field VARBINARY(20) and the other fields are VARCHAR,
> TINYINT & DATETIME if that helps.
> Please advice,
> David
perhaps break your transaction in smaller transactions every n rows you have
to insert...
say every 50,000 rows...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Subscribe to:
Posts (Atom)