|
Michael,
Thank you for your reply,
I find your soultion of using the multiple joins interesting...
however I have a question on it, the possiblity exist that there will be an unnumerable amount of phone_num results available for each user. (while the truth is we "attempt" to limit it to 4) How would you propose doing the multiple joins for x number of results?
My inital thought would be to use a while loop to loop thru all the results. but doing that still leaves the question of how to deal with the multiple results of the intial query...
for instance when you do a left join on these tables, because there are say 4 instances of a phone_num in the phone table per user, then my list of users (say 100) will return with 400 results. even if i do the multilple joins on the same table i'll still have 400 results from the inital query, since the join doesn't consume the results in any way.
--unless there is a way to include the TOP statement.... hmm, i'll look further into that....
If I figure that out then I'll post my resolution. -- 'course if you already know the resolution I'll happly accept assistance 
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Here is another solution, but with this no employee can have two of the same phone type:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cTypeID VARCHAR(10)
SET @sql = ''
DECLARE curTypes CURSOR FOR SELECT DISTINCT CAST(teltype AS VARCHAR(10)) FROM PhoneNumbers
OPEN curTypes
FETCH NEXT FROM curTypes INTO @cTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT phone_num FROM PhoneNumbers p WHERE p.empid = e.empid AND p.teltype = ' + @cTypeID + ') as Type' + @cTypeID
FETCH NEXT FROM curTypes INTO @cTypeID
END
close curTypes
DEALLOCATE curTypes
SET @sql = 'SELECT e.name,e.empid' + @sql + ' FROM employees e'
exec(@sql)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi,
Does anybody knows the MS Access equivalent for IS_Member(Role) in SQl.
The Is_Member() specifies wether the db user belongs to the group Role or not. if anyBody knows how to do this with MS Access please help.Any other suggestion is welcome
Thanks all
|
|
|
|
|
I've switched a project from using an SQL Express database to an Access database, and I'm finding a number of issues with the differences in SQL Syntax. Does anyone know of a good website that describes functions, syntax, etc. for the type of SQL that Access uses? What I am currently stuck on is how to convert an integer into a string in a select statement. This was the way I did it for SQL Express:
SELECT STR(myField, 10, 0) FROM myTable
But now with Access I'm getting an error about the number of parameter's I'm using, so Access must do things differently.
I would appreciate any help.
|
|
|
|
|
Kschuler wrote: how to convert an integer into a string in a select statement
SELECT CSTR(myField) AS myField FROM myTable
Don't know a good site for MSAcess SQL syntax.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi All,
In C#, to get the time you use DateTime.Now, and if you want just the date you can use DateTime.Today which returns you the time at Midnight this morning.
I need to set a DateTime variable in the same manner in Sql Server with 'Today'. I could do this by somehow lopping of the fractional part, by converting it to a float, flooring it, then converting it back to a DateTime or something like that, but this seems somewhat hideous to me.
Am I missing a simple solution to this? Help as always appreciated. 
Regards,
Rob Philpott.
|
|
|
|
|
Here is one way:
DECLARE @dtVar DATETIME
SELECT @dtVar = CONVERT(VARCHAR, GETDATE(),101)
PRINT @dtVar
you could CAST(CONVERT(varchar, GETDATE(),101) AS DATETIME) but i don't know if it is necessary...i think it is automatic
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Thanks Eric, that looks pretty good.
I've never quite got the hang of the date conversion functions with the arbitary number (101) denoting something. But does the tricky perfectly!
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: I've never quite got the hang of the date conversion functions with the arbitary number (101) denoting something.
It isn't quite arbitrary. 101 is a parameter for USA standard. It might be better to use 112, which is the ISO standard. But, everything I've developed so far is for the US only, so I don't know much about international standards.
Colin Angus Mackay and I had a little exchange about similar date stuff a few posts down: http://www.codeproject.com/script/comments/forums.asp?forumid=1725&select=1499243#xx1499243xx[^]
----
For more info, look up CAST and CONVERT in SQL Server Books Online.
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
Without century (yy) With century (yyyy)
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I finally have an understanding of how to create simple reports from my dataset using the ReportViewer. I now wonder how I can make custom (SQL-queries) on my dataset that will fill for e.g. a table.
For example I have a picture that I like to insert into my report. That picture do I store as a string in my database. (In my program I do conversions functions for: Image -> byte[] -> string -> byte[] -> Image).
_____________________________
...and justice for all
APe
|
|
|
|
|
Hi,
We are creating an ASP Database Application that connects to Sql Server 2000.
The application is for associating a zip code with a warehouse. For instance a zip code for the los angeles area would go to the los angeles warehouse and a zip code for the new york area would go to the new
york warehouse. We would like to enter zip code ranges into the following Wzip_Log table. Then we would like to programmatically populate the Wzip_Detail table and the Wzip table.
The Wzip_Log table holds zip code ranges, where as the Wzip_Detail and the Wzip table hold individual zip codes.
For instance, the Wzip_Log table might have the following record (completely fictitious)
Wzip_Log_ID: 1
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 1
Operator: jdoe
Time_Stamp: 00:00:00 6/1/2006
Then we would want the Wzip_Detail table to be populated as follows:
Wzip_Detail_ID: 1
Wzip_Log_ID: 1
PostalCode: 11111
CountryCode: USA
WarehouseNum: 1
Wzip_Detail_ID: 2
Wzip_Log_ID: 1
PostalCode: 11112
CountryCode: USA
WarehouseNum: 1
Wzip_Detail_ID: 3
Wzip_Log_ID: 1
PostalCode: 11113
CountryCode: USA
WarehouseNum: 1
The Wzip table would be populated exactly the same as the Wzip_Detail table at this point.
However, say a user later updated the Wzip_Log table as follows:
Wzip_Log_ID: 2
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 2
Operator: jdoe
Time_Stamp: 01:10:00 6/4/2006
The Wzip_Detail table would contain an INSERT of this data (giving it a total of 6 records)
whereas the Wzip table would contain an UPDATE of this data (giving it a total of 3 records)
For your reference, here are the table layouts:
Table Name: Wzip_Log
Wzip_Log_ID int 4 (primary key, Identity)
PostalCode_Start varchar 10
PostalCard_End varchar 10
CountryCode varchar 3
WarehouseNum int 4
Operator varchar 50
Time_Stamp datetime 8
Table Name: Wzip_Detail
Wzip_Detail_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4
Table Name: Wzip
Wzip_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
Wzip_Detail_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4
I think there is probably some way to do this programmatically in Sql Query Analyzer, but are not sure how, so I thought I would ask you here who have helped me tremendously in the past.
I am eager to learn more about writing such functions and am enjoying this project.
Thanks in advance for any help you can give,
Anne
|
|
|
|
|
Problem!!! i am creating database connection using VC++ and SQL server with authentication 'sa' and password "***". I am using ADO connetion (with DSN).
I have two accounts on my computer system Administrator and Operator.
1- Administrator have full access on computer system.
2- Operator have limited access in computer system (can't perform installation)
Well I can successfully connect with database from administrator account.
Buttt when i try to run my application from operator account then then database connection failed.
Would some body help me... Its urgent.
|
|
|
|
|
My colleague is not able to access the sql server that resides in my local system from the asp.net application in her pc even though my sql server is running.
She is able to access my sql server using her query analyser.
The code in web.config
add key="ConnectionString" value="Data Source=Myservername;User Id=mysqlservername;
Password=mysqlserverpassword;Initial Catalog=mydatabasename;Connection Lifetime=30"
I have used the same code in my web.config and i am able to access my sql server.
Do tell if i have to give some rights for her to access my sql server through asp.net application
-- modified at 6:59 Tuesday 23rd May, 2006
|
|
|
|
|
Check sql server authentication mode in your Enterprise manager. Try changing it to work in both windows and sql server mode.
|
|
|
|
|
Thanks for the reply.
The authentication mode is SQL Server and Windows.
|
|
|
|
|
Hi,
I need to do update operation for a set of ids, for this i need to use comma separator how can do it
I am giving like this
update Emp_Tbl set empstatus=1 where empid in (@empids)
is it enough or any other methods
|
|
|
|
|
This will work 
================
DECLARE @empids varchar(100)
SET @empids= '66696, 66695, 66694, 66693, 66692'
exec('update Emp_Tbl set empstatus=1 where empid in (' + @empid + ')')
Look where you want to go not where you don't want to crash.
Bikers Bible
-- modified at 6:46 Tuesday 23rd May, 2006
|
|
|
|
|
1)OLEDB Connection Pooling is handled by OLEDB.Net Provider and SQLClient Connection pooling is handled by____
2)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
3)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
4)Which object in ADO.Net is similar to the Recordset object of ADO?
plz let me konw the ans
witn regards
d.ravi kiran reddy
|
|
|
|
|
hi,
This is Dayakar
You need to add following namespaces for the specified data providers:
Data Provider Namespace
MS SQL Server System.Data.SqlClient
Oracle Database System.Data.OracleClient
OLE DB Databases System.Data.OleDBClient
ODBC Data Sources System.Data.ODBCClient
|
|
|
|
|
Hi,
I want to make one column as a index in one table. i.e how to make one column as a primary key at run time
|
|
|
|
|
ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_KeyName PRIMARY KEY CLUSTERED
(
FieldName
) ON [PRIMARY]
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Is there a way to view the results of an action query without committing the changes to the database in SQL Server 2000? Any suggestions or tips will be greatly appreciated.
-Garrett
|
|
|
|
|
I'm not really sure how wise this is but this:
SELECT * FROM [Order Details]
BEGIN TRANSACTION
DELETE FROM [Order Details]
SELECT * FROM [Order Details]
ROLLBACK TRANSACTION
SELECT * FROM [Order Details]
This shows the state of the Order Details table, then it shows the result of the delete operation, but after the transaction is rolled back the state of the database reverts.
Is this the kind of thing you are looking for?
[ADDITION]
I should point out that this does actually make changes to the database and it could impact performance of other things running in the database.
[/ADDITION]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 12:51 Monday 22nd May, 2006
|
|
|
|
|
Pretty much. Thanks for the help.
|
|
|
|
|
I have a sql problem.
suppose we have two tables ex. A and B which have A1,A2,A3 column and B has B1,B2,B3 Column A1 and B1 are primary key and B2 is foreign key of A(A1) Now B3 has some values in it which i wanted to map with column A3.
means i wanted to update column A3 as same as B3 where we have A1=B2 relationship. can we write this in a single sql statement. what i am doing is writing a cursor on table A which one by one retrieve value from table B(B3) and update table A(A3). But problem is that this process is very slow and lock the table at the time it perform operation so all other request will have to wait until this process terminate. otherwise some times sqlserver detects it as a deadlock condition and unconditionally terminate process. So my aim is improve performace of all such processes.
Error :
Transaction (Process ID 773) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I wanted some thing like as easy as
update A set A.a3= B.b3 where A.a1 =B.b2
which does not work in this case.
Table has bulk amount of data i. e. around 10-15 lacks records. I need some alternative solution for many of such process in which i have used same logic am working on. also the database is of prouction server so I cannot kept it busy for long time.
Pallav Deshmukh
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
