Newbie needs help with timestamp calculation

I'm a newbie, so please be gentle

I have a table that has a timestamp column that I can reference, e.g.,

SELECT *
FROM mytable
WHERE RECORDTIME < {TS '2006-05-01 00:00:00.000' }

This type of selection works.

What I want to do now is select the rows where the timestamp is less than 30 days prior to the current date instead of hardcoding a timestamp every time. I'm trying to select anything older than 30 days.

SELECT *
FROM myfile
WHERE RECORDTIME < [ current date's timestamp] - 30 days

I don't even know where to start so any help is greatly appreciated.

Thanks in advance,

Robert

I think I have it. This seems to work.

SELECT *
FROM myfile
WHERE RECORDTIME < ( cast( getdate() as datetime) - day(30) )

Please advise if you see anything that would be an issue.

Thanks!

|||

I don't think that it works exactly as the spec 'today less 30 days'.

Consider these two different results:

select getdate() - day(30), getdate()
select dateadd(day, -30, getdate()), getdate()

-- -
2006-04-30 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)

-- --
2006-05-01 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)

/Kenneth

Newbie HELP (ON SQL)

Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lostohhh found it

SELECT Whatever, WhateverElse FROM TableName WHERE DateField >= DATEADD(d, -7, GETDATE())

Originally posted by webstep
Hi all
I am very new to SQL and trying to select some records from a M/S
SQL database to display 7 days worth of data

My date column is set as a small date UK format

15/10/2002 00:33:13

Would I use the commands something like this

Select *
From my database
Where my table between GETDATE () AND GETDATE ()-7

Can anybody help as i am real lost|||If you put this at the top of your script:

declare @.date1 datetime, @.date2 datetime
set @.date2 = convert(varchar(11), getdate(), 111)
set @.date1 = convert(varchar(11), getdate()-7, 111)

...then you can just use @.date1 as your from date and @.date2 as your to date. You won't have to put in any dates.

Steve

Newbie encounts DF_TableName_ColumnName errors

Hi Guys,

I am new to the database administration game. I encountered a dependency issue when I was trying to change the datatype of a column from smalldatetime to datetime.

This is the code I use:

ALTER table AllNetCategories ALTER COLUMN actiontime datetime
Go

When I run the above code, the database comes back this error message
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_AllNetCategories_ActionTime' is dependent on column 'actiontime'.

I also checked the dependency on the target table using

sp_depends AllNetCategories


and I cannot see any object name like "DF_AllNetCategories_ActionTime" dependent on it.

Can anyone please help?It looks like 'DF_AllNetCategories_ActionTime' is a default constraint,
if it is so, sp_depends procedure is not going to list it down.
Query sys.default_constraints view instead -

Select * from sys.default_constraints
where [Parent_object_id] = object_id('AllNetCategories')|||Thanks mihirclarion.
I am using SQL 2000 and I can not find sys.default_constraints table.
The only table I can find those 'DF_%' is at sysobjects.
How can I find out what those DF_% constraints for?|||you can use Information_schema views to know about constraints exists -

Select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_name like '<Constraint_name>'
Select * from information_schema.CHECK_CONSTRAINTS where constraint_name like '<Constraint_name>'
Select * from information_schema.TABLE_CONSTRAINTS where constraint_name like '<Constraint_name>'

or you can also use a simple query like -

Select * from sysobjects where name like '<Constraint_name>'
and check value of 'xtype' column -
D - Default
FN - Function
P - Procedure
PK - Primary key
U - User tables
S - System table ... etc.

In general, prefix 'DF_' stands for default colstraints
Query smiliar to following can give you details you need -

Select inf_cols.* from sysobjects sysobj1
INNER JOIN sysconstraints syscon1
INNER JOIN syscolumns syscols1 ON syscols1.colid = syscon1.colid and syscols1.id = syscon1.id
ON syscon1.Constid = sysobj1.ID
INNER JOIN information_schema.columns inf_cols ON inf_cols.Column_name = syscols1.name
and object_id(inf_cols.table_name) = syscols1.id
where sysobj1.name like '<Constraint_name>' and inf_cols.column_default is not null|||It works~ Thank you Mihir|||sp_helpconstraint may be easier for you to use and is available in SQL 2000 and SQL 2005.

newbie column header line break question

Hi,
Can anyone tell me the syntax to setup a table column header with a
line break embedded like:
Shares
Voted
ThanksI think this is what you need. Type exactly in the header textbox:
="Shares" & vbcrlf & "Voted"
'vbcrlf' will embed a carriage return.
Hope that helps
rgorslin
"tom" wrote:
> Hi,
> Can anyone tell me the syntax to setup a table column header with a
> line break embedded like:
>
> Shares
> Voted
>
> Thanks
>

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
Randy
SQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegr oups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>
|||Actually, SQL Server Management Studio Express
|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy
|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>
|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy
|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy
|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegro ups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:
> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;
> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||VB.NET you should be able to grab the binary data from the database, store
it in a Byte() array and create a Graphics object from it. I'm doing
something similar right now in VB 2005 with dynamically generated images
being passed from SQL Server to a client-side VB app where the binary
content is converted to a bitmap and displayed on a form. Just be sure to
properly dispose of your Graphics objects, etc., when you're done with them.
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182982110.182760.129310@.e16g2000pri.googlegroups.com...
> Ignore that last reply. Apparently, I wasn't looking at refreshed
> view of the db. The table is there, as are both columns. There was
> no data in the table, so I added the key column values and just ran
> the UPDATE part of the query, which seems to have populated the
> image_data values. Now, i just have to figure out how to pull this
> onto my VB form. When I look at the table data, it just says <Binary
> Data> in each of the fields, so I'm not certain that I have everything
> in place just yet. If I have trouble, I'll re-post.
> Thanks for everybody's help!
> Randy
>|||I need to do the exact same thing but in SQL Server 2000.
I have been tinkering with BULK INSERT and OPENROWSET but have not been able
to get anything to load the image data.
Actually in my case, the image data can be a Word document, Excel
spreadsheet, PDF file, text file, etc.
Thank you in advance for any information that you can provide.
Joe|||Hi Joe,
The BULK rowset provider functionality to load BLOBs is only available since
SQL Server 2005.
See the following example by Erland Sommarskog on how this can be done is
SQL Server 2000:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Help with Constraints

Dear all,
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>

Newbie - Help with Constraints

Dear all,
I am new to SQL Server (using the express edition.) I need to set up a
contraint that checks values from one column in three tables against
one column's value in a pending insertion. If the value that is
attempting to be inserted equals a value from either of the three
tables, the insertion should fail (throw a SQL exception').
Essentially this constraint should operate just like a "no duplicates"
constraint would operate if all the data was in a single column in a
single table and a client attempted to add a duplicate value.
Any advice is appreciated.
Thanks,
JohnnyJohnny,
You could embed the INSERT in a stored procedure and perform the table
checks (SELECTs) prior to the INSERT. Or you could create an INSERT trigger
that would ROLLBACK if the value existed in one of the tables. The former
would be better for performance.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148501225.921395.254460@.y43g2000cwc.googlegroups.com...
> Dear all,
> I am new to SQL Server (using the express edition.) I need to set up a
> contraint that checks values from one column in three tables against
> one column's value in a pending insertion. If the value that is
> attempting to be inserted equals a value from either of the three
> tables, the insertion should fail (throw a SQL exception').
> Essentially this constraint should operate just like a "no duplicates"
> constraint would operate if all the data was in a single column in a
> single table and a client attempted to add a duplicate value.
> Any advice is appreciated.
> Thanks,
> Johnny
>|||If I put this in a stored procedure, how could I ensure that the stored
procedure is called every time an insert statement is executed against
3 different tables? Is this a situation where a trigger is the only
choice?
Thanks,
Johnny|||Johnny,
Put the INSERT statement within the stored procedure and call the proc to
perform the INSERT.
HTH
Jerry
"Johnny Meredith" <jmeredith@.gmail.com> wrote in message
news:1148527276.838343.179960@.i39g2000cwa.googlegroups.com...
> If I put this in a stored procedure, how could I ensure that the stored
> procedure is called every time an insert statement is executed against
> 3 different tables? Is this a situation where a trigger is the only
> choice?
> Thanks,
> Johnny
>

Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack

NEWBE

I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate that
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.
If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:

>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each request
>was submitted. The only function that i found to automatically populate that
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.

NEWBE

I have web forms on my site that store data in an sql dbase.
I have a DATE column that i would like to store each date, that each request
was submitted. The only function that i found to automatically populate tha
t
field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
field?
thanks
sorry if im in the wrong room.If you want the current date, time you can use GetDate() in
your SQL statement. Or you can use it as a default value.
Depends on what you mean by populate and if it's just for
the creation of a new record, etc.
-Sue
On Wed, 15 Mar 2006 09:17:29 -0800, tony
<tony@.discussions.microsoft.com> wrote:

>I have web forms on my site that store data in an sql dbase.
>I have a DATE column that i would like to store each date, that each reques
t
>was submitted. The only function that i found to automatically populate th
at
>field was TIMESTAMP... any ideas on how i can autopopulate just the DATE
>field?
>thanks
>sorry if im in the wrong room.