Database Discussion Boards

prathapcode6-Nov-09 2:37

6-Nov-09 2:37

Hi,

I developed a small website using express edition and MS SQL server 2005(sql server management studio express). Now iam planning to host the application.To publish website with express edition is possible?
What is difference between Proffesional and standard editions of visual studio?
To publish a website standard edition is enough?
And now coming to SQL server whether licensed key is required?

Thanks in advance

Regards,
Prathap

Re: Sql server license key

Abhishek Sur8-Nov-09 9:05

Abhishek Sur

8-Nov-09 9:05

prathapcode wrote:
And now coming to SQL server whether licensed key is required?

AFAIK sql server express can be freely distributed. Just you need to create a setup application and execute its installer from your program.
Smile | :)

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

Mysql

geeeeeeeetha5-Nov-09 23:59

geeeeeeeetha

5-Nov-09 23:59

hi,

I want to search particular string in entire database and i need to retun the full row in Mysql...

Regards
Jayabharathi Ramasubbu

Re: Mysql

Niladri_Biswas6-Nov-09 1:14

Niladri_Biswas

6-Nov-09 1:14

Hi Jayabharati,
I am sorry but I am a SQL SERVER guy. Henceforth I am not able to write the query for you.
But you can find your answer form here(Exactly the same question you are looking for.. I believe)

http://stackoverflow.com/questions/1384245/way-to-search-entire-database-for-a-string-in-mysql[^]

Hope this helps.
Smile | :)

Niladri Biswas

How to update multiple rows in a table.

chakran5-Nov-09 23:08

chakran

5-Nov-09 23:08

Hi,
I have requirement to update the weekno and StartdayofWeek.

By using the below query I can get weekno and StartdayofWeek but I need to insert/update these values in respective columns in the original table. Can any one help me.

Select BugDate,
          DATEADD(wk, DATEDIFF(wk, 6,[BugDate]), 6) as startdayofweek,
          Datepart(wk,[BugDate]) + ((Datepart(year,[BugDate]) - 2008) * 52) as WeekNumber
from [BugsDB_DefectVolume]

Table:

ID     BugDate              Weekno   BugNo StartDayofWeek
111     2009-04-12      NULL     3     NULL
111     2009-04-19      NULL     1     NULL
111     2009-04-26      NULL     1     NULL
111     2009-05-03      NULL     1     NULL
111     2009-05-10      NULL     9     NULL
111     2009-05-17      NULL     9     NULL
111     2009-05-24      NULL     4     NULL
111     2009-05-31      NULL     2     NULL
111     2009-06-07      NULL     4     NULL

Thanks.

Re: How to update multiple rows in a table.

geeeeeeeetha6-Nov-09 0:17

geeeeeeeetha

6-Nov-09 0:17

hi,
if both table having unique column,then u can use below query

update MyTable
set NtextColumn = ot.NtextColumn
from dbo.MyTable
join anotherDB.dbo.OtherTable ot
on MyTable.KeyColumn = ot.KeyColumn

Re: How to update multiple rows in a table.

chakran6-Nov-09 0:24

chakran

6-Nov-09 0:24

sorry there is no other table. We have only one table called "BugsDB_DefectVolume". In that single table we need to update weekno and Startdayofweek using Bugdate column...

Re: How to update multiple rows in a table.

geeeeeeeetha6-Nov-09 0:35

geeeeeeeetha

6-Nov-09 0:35

hi,

create one temporarytable...

insert values to temporary table...
and use previous post query...

if i'm wrong plz excuse

Re: How to update multiple rows in a table. [modified]

Niladri_Biswas6-Nov-09 1:05

Niladri_Biswas

6-Nov-09 1:05

Hi,

Update the target table based on bug date

Here is the example

Just creating a dummy source table

declare @tblSource table(bugdate datetime)
insert into @tblSource 
select '2009-04-12' union all 
select '2009-04-19' union all
select '2009-04-26' union all
select '2009-05-03' union all
select '2009-05-10' union all
select '2009-05-17'	union all
select '2009-05-24'	union all
select '2009-05-31'	union all
select ' 2009-06-07'

Your target table schema

create table tblTarget (id int,bugdate date,weekno int,bugno int,startdayofweek datetime)
insert into tblTarget 
select 111,'2009-04-12',null,3,null union all 
select 111,'2009-04-19',null,1,null union all
select 111,'2009-04-26',null,1,null union all
select 111,'2009-05-03',null,1,null union all
select 111,'2009-05-10',null,9,null union all
select 111,'2009-05-17',null,9,null	union all
select 111,'2009-05-24',null,4,null	union all
select 111,'2009-05-31',null,2,null	union all
select 111,' 2009-06-07',null,4,null
select * from tblTarget

Output (Before updation)

id	bugdate	          weekno	bugno	startdayofweek
111	2009-04-12	          NULL	         3	     NULL
111	2009-04-19	          NULL	         1	     NULL
111	2009-04-26	          NULL	         1	     NULL
111	2009-05-03	          NULL	         1	     NULL
111	2009-05-10	          NULL	         9	     NULL
111	2009-05-17	          NULL	         9	     NULL
111	2009-05-24	          NULL	         4	     NULL
111	2009-05-31	          NULL	         2	     NULL
111	2009-06-07	          NULL	         4	     NULL

Next execute the query

;with cte as
(
	select	bugdate,
			DATEADD(wk, DATEDIFF(wk, 6,bugdate), 6) as startdayofweek,          
			Datepart(wk,bugdate) + ((Datepart(year,bugdate) - 2008) * 52) as WeekNumber     
	from @tblSource
)
update tblTarget
set tblTarget.weekno = c.WeekNumber,tblTarget.startdayofweek = c.startdayofweek
from cte c
where c.bugdate = tblTarget.bugdate

Output(After Updation)

id	bugdate	       weekno	  bugno	       startdayofweek
111	2009-04-12	        68	   3	    2009-04-12 00:00:00.000
111	2009-04-19	        69	   1	    2009-04-19 00:00:00.000
111	2009-04-26	        70	   1	    2009-04-26 00:00:00.000
111	2009-05-03	        71	   1	    2009-05-03 00:00:00.000
111	2009-05-10	        72	   9	    2009-05-10 00:00:00.000
111	2009-05-17	        73	   9	    2009-05-17 00:00:00.000
111	2009-05-24	        74	   4	    2009-05-24 00:00:00.000
111	2009-05-31	        75	   2	    2009-05-31 00:00:00.000
111	2009-06-07	        76	   4	    2009-06-07 00:00:00.000

Hope this helps.
Smile | :)

Niladri Biswas

modified on Friday, November 6, 2009 7:26 AM

Unable to get the data from 2 tables which are in 2 differenet servers

vnr5-Nov-09 18:58

vnr

5-Nov-09 18:58

Hi all,

I am trying to get the data from 2 tables which are in 2 different servers , to get that i have used
"sp_addlinkedserver" .Using this I am able to connect to the server also .I have used the following commands

for example my server ip is 11.12.23.121

EXEC sp_addlinkedserver '11.12.23.121'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '11.12.23.121'

after this I am not bale to get the results of the linked server using following query

select * from '11.12.23.121'.testdb.dbo.userreg

Please tell me how to get the data from the linked server.

Thanks in advance

Re: Unable to get the data from 2 tables which are in 2 differenet servers

Mycroft Holmes7-Nov-09 13:17

Mycroft Holmes

7-Nov-09 13:17

Try the following

select * from [11.12.23.121].testdb.dbo.userreg

Never underestimate the power of human stupidity
RAH

Re: Unable to get the data from 2 tables which are in 2 differenet servers

vnr7-Nov-09 19:15

vnr

7-Nov-09 19:15

HI,

Thank for your reply , i have tried to get the data using the query but getting the following error

"OLE DB provider "SQLNCLI" for linked server "[server]" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "[server]" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53]. "

can you please tell me the solution for this.

Thank you

Re: Unable to get the data from 2 tables which are in 2 differenet servers

Mycroft Holmes7-Nov-09 22:46

Mycroft Holmes

7-Nov-09 22:46

When setting up the linked server you have not applied the correct credentials. When, under duress, we need to do this we use a SQL standard account between servers and set it up in SSMS when creating the linked server.

Never underestimate the power of human stupidity
RAH

horizontal union operation

loyal ginger5-Nov-09 10:03

loyal ginger

5-Nov-09 10:03

I have two irrelavent Microsoft Access tables. They have the same number of records but no other relations. Is there a way to create a new table using SQL with fields from the two source tables put together?

For example:

Table1:

Name          Address
---------------------
John       302 ABC Street
Smith      412 DEF Street</code>

Table2:

Phone         email
----------------------
123-4567    m1@hotmail.com
234-5678    s1@gmail.com

Can I use a simple SQL command to create:

Table3:

Name          Address         Phone        email
--------------------------------------------------
John        302 ABC Street    123-4567     m1@hotmail.com
Smith       412 DEF Street    234-5678     s1@gmail.com

I was thinking of using a join operation but the two source tables does not have any relations, and I can't join the table by record numbers. The only thing that's guaranteed is that the two source tables have the same amount of records.

Is there any way to achieve this just by using SQL commands without resort to writing a custom program?

Thanks!

Re: horizontal union operation

Garth J Lancaster5-Nov-09 10:45

Garth J Lancaster

5-Nov-09 10:45

I'm wondering what you'd do if you had to 'resort to writing a custom program?' - since as you say, they are (I'll use the terms) disjoint/un-related instead of your 'irrelavent'

since you dont have anything in common between the tables, and as you say, you cant join by record numbers the only way I can see of doing this (which assumes you can see record numbers) is to manually create a 'link' table with two columns, record-number-table-1, record-number-table-2, and manually enter the pairs of corresponding record numbers into it using 'eyeball matching' and 'insert' statements - of course, if you have a truckload of data this is going to take a while. You then select record from each table based on the rows in the 'link' table

There must be more to the situation than you're showing us here - To 'write a custom program' implies you know of some relationship between the tables/data, but just because they 'have the same amount of records' is a bad assumption - what about duplicates for example ?

'g'

Re: horizontal union operation

Luc Pattyn5-Nov-09 12:59

Luc Pattyn

5-Nov-09 12:59

Hi,

if everything else fails, you could add a field to both tables ("ID") and fill it with sequential numbers.
Then join both tables on the new field.

Smile | :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Re: horizontal union operation

Niladri_Biswas5-Nov-09 17:07

Niladri_Biswas

5-Nov-09 17:07

Try this

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

declare @tbl2 table(phone varchar(20), email varchar(50))
insert into @tbl2
select '123-4567','m1@hotmail.com' union all select '234-5678','s1@gmail.com'

;with cte1 as (select t1.*, ROW_NUMBER()over (order by name) as rn from @tbl1 t1)
,cte2 as (select t2.*, ROW_NUMBER()over (order by phone) as rn from @tbl2 t2)
select c1.name,c1.address,c2.phone, c2.email
from cte1 c1
inner join cte2 c2
on c1.rn  = c2.rn

The output is

name	address	phone	email
John	302 ABC Street	123-4567	m1@hotmail.com
Smith	412 DEF Street	234-5678	s1@gmail.com

But ur database design is wrong.. normalize that please.
I use the Row_number() function to accomplish the task....
Smile | :)

Niladri Biswas

Re: horizontal union operation

Shameel9-Nov-09 3:01

Shameel

9-Nov-09 3:01

You cannot do this in Access Frown | :(

clarification on my original question

dxlee6-Nov-09 5:43

dxlee

6-Nov-09 5:43

First of all, thanks for all who replied.

I was given those Access tables and was asked to do that "horizontal join". I did not design the database, so I have no control over the design of the tables. For example, there is no primary key in those tables. Had I designed them, I would probably put a rowid field there as the primary key.

The person who asked me to do this needed the result table "so that he can load the table in GeoMedia to map the points." I don't know the details about GeoMedia. I just do whatever he asks me to do. He is my boss. The real requirement is more than what I presented here. He actually asked me to horizontally join more than two tables. Actually the number of tables are unknown at design time. I am actually writing a program to do this. However, I want a query to simplify my program. I was hoping that I can avoid the result table creation part because I don't want to deal with Access data types. If a query is possible, I will just use "select ... into ..." to create the result table, instead of using "create table ..." because to construct the create command I have to find out the fields' info (name and type) for the result table.

Thanks again for all your replies.

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:

Last Visit: 31-Dec-99 18:00 Last Update: 1-May-24 14:56

Refresh

ᐊ Prev 1...728 729 730 731732733 734 735 736 737 Next ᐅ

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