If I join Table1 to Table2 with a WHERE condition, is
it the same if I would join Table2 to Table1 considering
that the size of the tables are different.
Let's assume Table2 is much bigger than Table1.
I've never used MERGE, HASH JOINs etc, do any of
these help in this scenario?
Thank you
I work on the Clinical Surgery Department Data Application.
The patient gets through the Admission, Surgery, PostSurgery and Discharge stages during his staying in the department. The Doctors just insert their medical data in to the different tables. I have three main classes: User, Patient and Portfolio ( patients portfolio ) and all the business bases on these classes.
The problem is with the Classes per Medical Data Tables. The Medical tables should exist cause it includes the medical data about the patient however what is not clear: Whether I should create Business and Entity classes per each medical data table ( and this is something like 50 tables ) or I should insert all the functions in the Portfolio class and then there will be a mess of functions in one class? What is the accepted way to perform it ?
I have two tables that I am trying to join and both have similar columns with which I am trying to use. One is Date and the other is LOGdate. Date's format is "01/01/2000" BUT LOGdate's is "01/01/2000 12:41:00 PM/AM". Can I join these two tables?
Any help would be greatly appreciated.
Thank you
I have two tables that I am trying to join and both have similar columns with which I am trying to use. One is Date and the other is LOGdate. Date's format is "01/01/2000" BUT LOGdate's is "01/01/2000 12:41:00 PM/AM". Can I join these two tables? If yes how do I get rid of the excess time on LOGdate.
Any help would be greatly appreciated.
Thank you
Hi all,
Does anyone know if it is possible to register SQL Server 7.0 in SQL Server 6.5 and vice versa?
I can't find the SQLOLE70.SQL file in SQL Server 7.0 installation disk.
Thanks in advance
Hi guys, how are you? I was wondering how I do to convert int to nvarchar and vice versa?
Thank you very much.
Hey Guys, are you aware of anyone moving from MYSQL to MSSQL or vice versa - what were your experiences - Mark Smith
View 3 Replies View RelatedMy asp.net website is using sqlexpress2005. I am planning to use sql2000 with this existing website. My questions:
1. How can I transfer the database definitions and actual data from 2005 to 2000? is this possible?
2. What if I have exisiting database in sql2000, can I convert it to be used in sql2005?
3. should I manually recreate the database when converting from one version of sqlserver to another? and how abt the actual data?
PLS HELP. big thanks!
I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?
View 6 Replies View RelatedHi,
We have setup SQL Server 2000 32-bit (Publisher/Subscribers) replication with 5 different locations. We are planning to purchase SQL Server 2005 Enterprise Edition 64-bit and/or 32-bit. I need suggestion, if we have both version of 2005 Enterprise, can we setup replication with SQL Server 2005 64-bit with SQL Server 2005 32-bit? is that supporting?
Also can we run replication with SQL Server 2005 64-bit (Publisher) with SQL Server 2000 32-bit (Subscriber).
- Publisher Subscribers
--------------------------------------------------------------------------------------------------------
1- SQL Server 2005 64-bit SQL Server 2005 32-bit
2- SQL Server 2005 64-bit SQL Server 2000 32-bit
Shamshad Ali
shamshad_ali74@hotmail.com
shamshad_ali74@yahoo.com
Can i call Tigger in store procedure or vice versa in sql server 2000.
and How it implement ?
I would like to change rows into columns and columns to rows for the query output table
If the query output is shown the table below (including the column names in the first row).
A B C D E
a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 b3 c3 d3 e3
a4 b4 c4 d4 e4
a5 b5 c5 d5 e5
The table needs to be converted to (the rows become columns and columns become rows).
A a1 a2 a3 a4 a5
B b1 b2 b3 b4 b5
C c1 c2 c3 c4 c5
D d1 d2 d3 d4 d5
E e1 e2 e3 e4 e5
Thanks
KK
We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.
So if A is the principal and B is the mirror, A to B fails but B to A works.
If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.
Any suggestions?
Can i call tigger in store procdure and vice versa??
if yes how it ?????????
In C#, I can convert UTC time to a specific timezone using:
TimeZoneInfo localTimeZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
DateTime UTCTime = TimeZoneInfo.ConvertTimeToUtc(localTime, localTimeZone);
Is there any equivalent function in T-SQL (SQL Server 2014 / SQL Azure)?
Specifically, I want to be able to convert a time into UTC before inserting it into a table. Right now I am doing:
INSERT INTO Incident (IncidentDescription, dtOccurred)
VALUES (@IncidentDescription, DATEADD(hour, 8, @dtOccurred))
I don't want to hardcode "8" and I want it to adjust for daylight savings time (DST).Â
Is there any concise solution that does not require me to define SQL functions or have lookup tables? I also don't want to use the difference between GETDATE() and SYSUTCDATETIME(), because the timezone of my server is not my local timezone.
1. Is it possible and what would be the gotchas for taking a backup of a database in a 32 bit instance of sql server 2000 and loading it into a 64 bit instance of 2000? How about 2005 to 2005?
2. Is it possible to reverse that and go from a 64 bit to a 32 bit?
3. How about going from a 32 bit sql server 2000 into a 64 bit sql server 2005?
We're on the verge of upgrading a rather large database from sqlserver 2000 (32 bit) into 2005 (32 bit) but they anticipate eventually upgrading the hardware to 64 bit at least for production. The test node would remain at 32 bit.
Thanks for any help.
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?
View 1 Replies View RelatedI would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).
What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:
tblEmployee
EmployeeID (PK)
LastName
FirstName
etc......
tblSubject
SubjectID (PK)
Description
tblOwner
SubjectID (FK)
EmployeeID (FK)
tblExpert1
SubjectID (FK)
EmployeeID (FK)
tblExpert2
SubjectID (FK)
EmployeeID (FK)
tblExpert3
SubjectID (FK)
EmployeeID (FK)
Does this make sense? Would I run into any issues when trying to display this on a form in VB?
Thanks in advance for any help!!!!!
Cal
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
Hello, could someone pls help me with this table design.
I have a project table and a code table. The code table has things like priorities (High, Medium, Low).
Now, I want all projects to be able to use these 'global' codes as well as define their own. So, they could define their own priority code 'Critical', that only their project can see.
Any help would be great.
Dear Participant,
I face following problem by last few day, please help me for the same
My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.
I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.
Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.
I don’t understand what is the problem.
Thanks
R.Mall
Hello.I am administering a SQL Server (Enterprise Edition on Windows 2003)from some month and can't understand what is going on in the latestweek (when the db grow a lot).The DB is around 250G, and has one table with 1 billion rows. It isperforming in a decent way, but can't understand why a particolar tablehas strong performance problem.I have a stored procedure that read table from table A and insert them,after processing in table B, and then move them in other table (similarto a Star Schema) for reporting.Table B is, for how the SP is written, not more than 3000 lines. TableB is very simple, has 3 rows, and no index.What is very strange is that performance of table B is really slow. IfI do a select count (*) from table_b it takes between 30s & 2minutes toreturn it has 0 lines. When the stored procedure insert 1000 lines, ittakes 20/30 seconds and it takes 20/30 seconds to delete them.To me it doesn't look like a lock problem, because it is slow also whenthe only procedure that access that table are stopped. I did an updatestatistics with fullscan on this table with no improvement.The DB is on a Storage Area Network that should perform decently. TheLUN I use is configured to use a piece of 32 disk that are used also byother application. I don't have performance data of the SAN. Themachine is an HP DL580 with 4 CPU (hiperthreading disabled), 8G of RAM,AWE and PAE and 5G reserved for SQL Server.I don't know what to do to solve this situation. Could it be a"corruption problem" that slow this table so much? is it possible thefact the db grow a lot in the last week created problem also to thissmall and simple table?Do you have any idea or hint on how to manage this situation, orpointer to documentation that can help in analizing this situation?For the ones that arrived till here, thank you for your time andpatience reading my bad english...Best Regards,MamoPSI can't rewrite the stored procedure, because it is part of a closedsource product.
View 16 Replies View RelatedIm having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.
The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:
12:30 PM
It will be stored as:
15/11/2003 12:30:00 PM
I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.
How can i correct this?
In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0
Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
Dear Participants,
We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-
Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-
Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go
Code validation from here only.
Right validation from here only.
Report retrival validation from here only.
It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.
It was working prior fine but rightnow get problem for while-
Today Dated 08-March this table not accessible fro three times in eight hours-
1st time for 10 minute.
2nd time 10 minutes
3rd time 52 minutes.
Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.
We had have do following by yesterday-
Drop table xxxxmast.
Create table xxxxmast.
Insert required data.
This is realy trouble for our application.
Any help realy great for us.
Thanks
R.Mall
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end
Hi,
Please could you tell me how big sql tables are when people refer to them as small, medium and large? Preferably in terms of disk space or rows (each row in my table will contain a standard length job advert and 20 additional columns with an average of 8 characters)
Thanks for your help! :-)
Stu
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[Â Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx