SQL Server 2005 - Working with IDENTITY column - Database Discussion Boards

Re: horizontal union operation

Shameel9-Nov-09 3:05

Shameel

9-Nov-09 3:05

Add a new AutoNumber column to both these tables and use it to join the tables.

Help! Database Problem, Unknown Information in a Access DataBase by the MEMO type!

maysam gamini5-Nov-09 8:26

maysam gamini

5-Nov-09 8:26

Hi friends.

I have problem reading images from ms access database!
there is a clinic software who was written by a bankrupted company,
and now it doesn’t work by the some reason, and there is no support on this software,
All things that I have to do are recover the patient’s data that was written by doctor handwriting in his notebook tablet-pc.

let me describe a little...

software has a feature that allow to the doctor to write the patients information by hand writing with an electrical pen, it so like paint brush software and it save this information in the database like bellow...
I think the doctor's handwriting must be saving as images in the patient’s records. The problem is I couldn’t read it from database because the data are like this:

AIfIAR0E2A/uBgIZAkgRBUgRRP8BBEgQRTUKSBFXDQAAAFD/AQQDATgAGCIgMgkA9P8CAekiOkUzCQD4nwIBWiA6RTgIAP4DAAAAgH8ADw4S+SONP88jjT8RAACAPx4+4gKD/glK/glJAAAAAAAAAAAYyAAAAAAAAAAAAAAAAAiQIkAAAAAAVMgAAAAESAEJAAAAAAxkAAAAAAIfEAAKLw+E+44+492VloilKFtOjSCG4PDhA6KAjpKCkI

I think images must be converted from image to another type like String Base 64 format.

i attached the database file here/:

http://rapidshare.com/files/302706380/test.accdb.html

there is a tables in the database, and all thing that i need is the "VISIT Table" 's information on the column by the name "NOTES", i think it must be image.

HELP ME,THis IS UrGENT Problem!

thank you very much!
With best Regards!

Re: Help! Database Problem, Unknown Information in a Access DataBase by the MEMO type!

maysam gamini5-Nov-09 12:37

maysam gamini

5-Nov-09 12:37

Dear Friends,

Well i found that, it's definitely a String Base 64, 'cause I was able to build a byte array from it with this code:

string strData = dataGridView1[2, 0].Value.ToString();//this from visit table in sample database that i //attached
byte[] data = Convert.FromBase64String(strData);

and i use this :

public Image ByteToImageConvert(byte[] byteArrayIn)
            {
                  MemoryStream ms = new MemoryStream(byteArrayIn);
                  Image returnImage = Image.FromStream(ms);
                  return returnImage;
            }

but i face with this problem on " Image returnImage = Image.FromStream(ms);":
Parameter is not valid.

What should i do?
PLease HELP!
thank you!

Re: Help! Database Problem, Unknown Information in a Access DataBase by the MEMO type! - CrossPost

Richard MacCutchan5-Nov-09 23:19

Richard MacCutchan

5-Nov-09 23:19

This question has been answered in the C# forum. Please don't post more than once.

SQL Server 2005 - Passing 2 Dim. Array to SP

Anusha.19805-Nov-09 7:42

Anusha.1980

5-Nov-09 7:42

Hi,

I am working on SQL Server 2005.

Here I need to pass an array (2 dimensional, E.g: Emp No, Emp Name ) having multiple records from my ASP.NET application to a Stored Procedure.

Would you please let me know if there is a way to do it?

Many Thanks,
Regards.

Anusha.

Re: SQL Server 2005 - Passing 2 Dim. Array to SP

Eddy Vluggen5-Nov-09 8:39

Eddy Vluggen

5-Nov-09 8:39

Anusha.1980 wrote:
Would you please let me know if there is a way to do it?

One way would be by using XML[^] Smile | :)

I are Troll Suspicious | :suss:

Re: SQL Server 2005 - Passing 2 Dim. Array to SP

Niladri_Biswas5-Nov-09 16:56

Niladri_Biswas

5-Nov-09 16:56

Even you can try with Bulk Insert

Have a look

http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx[^]
Smile | :)

Niladri Biswas

SQL Server 2005 - Working with IDENTITY column

Anusha.19805-Nov-09 7:04

Anusha.1980

5-Nov-09 7:04

Hi,

I am working on SQL Server 2005 tables. Here I need to add a column named ‘ID’ as ‘IDENTITY’ column (With starting and incrementing values as 1, 1).

Now my problem is that these tables already have thousands of records. So could you please suggest the best and easy way to perform this job?

Many Thanks,

Regards.
Anusha.

Re: SQL Server 2005 - Working with IDENTITY column

Mycroft Holmes5-Nov-09 14:20

Mycroft Holmes

5-Nov-09 14:20

Just add the field, SQL server will insert the ID values for existing records (It does in 2008 and I think it did in 2005). If not add the field, use row_number() to update the value and then add the identity constraint.

Re: SQL Server 2005 - Working with IDENTITY column

Niladri_Biswas5-Nov-09 17:22

Niladri_Biswas

5-Nov-09 17:22

Suppose I have a table

declare @tbl1 table(name varchar(20), address varchar(50))
insert into @tbl1
select 'John','302 ABC Street' union all select 'Smith','412 DEF Street'
select * from @tbl1

The output is

name	address
John	302 ABC Street
Smith	412 DEF Street

Now I want to insert identity

Change the table definition by adding an identity field like this

declare @tbl1 table(id int identity,name varchar(20), address varchar(50))

The output is

id	name	address
1	John	302 ABC Street
2	Smith	412 DEF Street

Else go ahead with the Row_Number() function(since sql server 2005 onwards)

select ROW_NUMBER() over(order by name) id, * from @tbl1

Niladri Biswas

Re: SQL Server 2005 - Working with IDENTITY column

Shameel9-Nov-09 3:08

Shameel

9-Nov-09 3:08

Just add the IDENTITY column, SQL Server will fill the values automatically.

MS SQL Selecting all rows in one table and only rows matching in a second

Steven J Jowett5-Nov-09 2:48

Steven J Jowett

5-Nov-09 2:48

I have 2 tables, Products and Receipts. In need to select all rows in the Products table and link it to the Receipts tables where the ReceiptId equals a given value.

Products Table
---------------
ProductId BIGINT

Receipts Table
--------------
ReceiptId BIGINT
ProductId BIGINT
Quantity DECIMAL(15,4)

If I execute a SELECT query on the products table I get 126 rows returned
If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.

I created a new view as follows

SELECT TOP (100) PERCENT dbo.Products.ProductId, dbo.Receipts.ReceiptId, dbo.Receipts.Quantity
FROM         dbo.Products LEFT OUTER JOIN
                      dbo.Receipts ON dbo.Products.ProductId = dbo.Receipts.ProductId

Then if I perform a SELECT query on the new view as follows :-

SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL

I would expect to get 126 rows returned, but I am only getting 63.

Any help would be appreciated.

Thanks

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Re: MS SQL Selecting all rows in one table and only rows matching in a second

J4amieC5-Nov-09 3:05

J4amieC

5-Nov-09 3:05

Steven J Jowett wrote:
If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.

Steven J Jowett wrote:
SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL

Steven J Jowett wrote:
I would expect to get 126 rows returned, but I am only getting 63.

Based on the above three asserions, im guessing there are 61 products without any receipts.

What's your question?

Re: MS SQL Selecting all rows in one table and only rows matching in a second

Niladri_Biswas5-Nov-09 3:41

Niladri_Biswas

5-Nov-09 3:41

Hi,
But I have used your code only and is getting the correct result.

declare @tblProduct table(Productid int )
insert into @tblProduct select 1 union all select 2 union all select 3 union all
select 4 union all select 5

declare @tblReceipts table(Receiptid int,Productid int,Quantity decimal )
insert into @tblReceipts
select 1,1,20 union all
select 1,1,30 union all
select 2,1,40 union all
select 2,1,12 union all
select 3,1,45 union all
select 2,2,45 union all
select 1,1,20 union all
select 1,1,30 union all
select 2,1,40 union all
select 2,1,12 union all
select 2,1,45 union all
select 2,2,45

select ProductId,ReceiptId,Quantity from
(SELECT TOP (100) PERCENT p.ProductId, r.ReceiptId, r.Quantity
FROM         @tblProduct p LEFT JOIN
             @tblReceipts r 
ON p.ProductId = r.ProductId ) X(ProductId,ReceiptId,Quantity)
where   X.ReceiptId = 2 OR X.ReceiptId is NULL

The output is as desired:

ProductId	ReceiptId	Quantity
1	2	40
1	2	12
1	2	40
1	2	12
1	2	45
2	2	45
2	2	45
3	NULL	NULL
4	NULL	NULL
5	NULL	NULL

So what is ur doubt? Can u give us some datapoints so that we can give a better insight!
Smile | :)

Niladri Biswas

Re: MS SQL Selecting all rows in one table and only rows matching in a second

David Skelly5-Nov-09 5:27

David Skelly

5-Nov-09 5:27

You can't really figure out what's going on here without knowing more about the data that is actually in the tables.

There are two rows in the Receipts table with ReceiptId 330804. Do these have the same ProductId or different ProductIds?

My guess is that there are two products for receipt id 330804 and 61 products with no receipt id. Total, 63 rows in your result set.

Re: MS SQL Selecting all rows in one table and only rows matching in a second

Steven J Jowett5-Nov-09 6:57

Steven J Jowett

5-Nov-09 6:57

I have a table called products with a unique id column called ProductId.
The products table contains 124 rows.

I have another table called receipts containing

ReceiptId
ProductId
Quantity

The ProductId would contain a ProductId from the Products table.

So what I want is a list of 124 products and the quantities received next to each product with a particular ReceiptId

Basically

ProductId Quantity
1.........10
2.........NULL
3.........NULL
4.........5
5.........NULL

NULL being where there is no receipt

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Re: MS SQL Selecting all rows in one table and only rows matching in a second

David Skelly5-Nov-09 23:08

David Skelly

5-Nov-09 23:08

This page covers everything you need:

http://www.firstsql.com/tutor3.htm[^]

The sections you are interested in are Outer Join, Group By and Set Functions (specifically, SUM).

Re: MS SQL Selecting all rows in one table and only rows matching in a second

Steven J Jowett5-Nov-09 23:36

Steven J Jowett

5-Nov-09 23:36

Dave

Thanks for the help.

My solution is as follows :-

SELECT NULL as TicketId, *, NULL as Quantity 
FROM vuDepotProducts 
WHERE NOT EXISTS(
SELECT * FROM vuReceipts WHERE DepotId = vuDepotProducts.DepotId AND ProductId = vuDepotProducts.ProductId AND TicketId = 330804
)
UNION
SELECT * FROM vuReceipts WHERE vuReceipts.TicketId = 330804

Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.

Using OPENROWSET

si_695-Nov-09 2:08

si_69

5-Nov-09 2:08

Hi
I am trying to import a CSVs file using OPENROWSET and import this into a database

my problem is the CSV files may contain a varying number of columns

i have tried using

SELECT BulkColumn
FROM OPENROWSET (BULK '\\10.1.2.107\rp_uploaded_files\file.csv', SINGLE_CLOB) MyFile

but this puts all the values into one fields

i have also tried
set @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
exec (@string)

but this requires the destintation table to have the same number of columns as the import file

Can anyone advise
thanks

Simon

Re: Using OPENROWSET

jacob.sebastian6-Nov-09 9:38

jacob.sebastian

6-Nov-09 9:38

I would suggest you take a look at the example given at http://syntaxhelp.com/SQLServer/OPENROWSET/CSV[^]

Help On Read Image column from MS Acess Database

maysam gamini5-Nov-09 0:55

maysam gamini

5-Nov-09 0:55

HI Guys,
I have problem reading images from ms access database!
there is a clinic software who was written by a bankrupted company,
and now it doesn’t work by the some reason, and there is no support on this software,
All things that I should do are recover the patient’s data that was written by doctor handwritings in his notebook tablet-pc.

I think the doctor's handwriting must be saving as images in the patient’s records. The problem is I couldn’t read it from database because the data are like this:

AIfIAR0E2A/uBgIZAkgRBUgRRP8BBEgQRTUKSBFXDQAAAFD/AQQDATgAGCIgMgkA9P8CAekiOkUzCQD4nwIBWiA6RTgIAP4DAAAAgH8ADw4S+SONP88jjT8RAACAPx4+4gKD/glK/glJAAAAAAAAAAAYyAAAAAAAAAAAAAAAAAiQIkAAAAAAVMgAAAAESAEJAAAAAAxkAAAAAAIfEAAKLw+E+44+492VloilKFtOjSCG4PDhA6KAjpKCkI

I think images must be converted from binary to another type.
If anyone could help on this he/her save my life!

i attached the database file here/:

http://rapidshare.com/files/302706380/test.accdb.html

there is a tables in the database, and all thing that i need is the "VISIT Table" 's information on the column by the name "NOTES", i think it must be image.

HELP ME,THis IS UrGENT Problem!

Sql Server 2008 Installing Issue-Not able to see the "Feature Selection " window and further windows after installing till supporting Files

srikrishnakrishna murthy4-Nov-09 23:27

srikrishnakrishna murthy

4-Nov-09 23:27

Hi,

I am trying to install sql server 2008 from the last three days..but there was no progress.Earlier,I have SqlServer 08,but that was crashed,So I am trying to install sql server 08 from the begining.

So,
1)I have uninstalled the existed Sql server 08
2)Removed all the features from control panel->Programs and features and no sql services existed in the Services
3)Completely removed the folder"Microsoft SQL Server" from C:\Program Files.
4)Removed sql related registry keys from HKEY_LOCAL_MACHINE\Software\Mocrosoft\

When I am Installing ,I am able to install till support Files only.Supporting files are successfully getting installed .After support files,It is not showing any further windows (like Feature Selection Window etc),and again it is showing the starting window .So,I couldnot move forward.Please help in this to go ahead.

Thanks,
Krishna

How to assign a query result to a stored procedure

Nekkantidivya4-Nov-09 23:14

Nekkantidivya

4-Nov-09 23:14

Hi,
I my stored procedure I have an insert query. For this I have to get one value from a sub query. But it is not accepting that query. So I tried to declare a separate variable and assign the subquery value to this and insert it in the table.

Create Procedure [dbo].[AddOrRemoveFavourites]
(
@UserId bigint,
@VideoName varchar(50),
)
as

Declare @VideoId int
Set @VideoId = Select VideoId from Videos where Title='Monkeydog'

Insert into Favourites(UserId,VideoId) values(@UserId,@VideoId)

but in this I am getting error like "Incorrect syntax near the keyword 'Select'."

If anyone have any idea to solve this please reply me.

Thanks in advance.

Re: How to assign a query result to a stored procedure

Eddy Vluggen5-Nov-09 0:08

Eddy Vluggen

5-Nov-09 0:08

Try this one;

SELECT @VideoId = VideoId 
  FROM Videos 
 WHERE Title = 'Monkeydog'

I are Troll Suspicious | :suss:

Re: How to assign a query result to a stored procedure

Syed Mehroz Alam5-Nov-09 0:45

Syed Mehroz Alam

5-Nov-09 0:45

Hi,

You need to enclose your select in parenthesis and include a top(1) clause, like this:

Set @VideoId = ( Select top(1) VideoId from Videos where Title='Monkeydog' )

Regards,

Syed Mehroz Alam
My Blog | My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.