hi all ,
i am working on a migration project of database from access 2003 to sql server 2000 but facing a problem in converting queries from access to sql server , is there any way or tool through which i can convert the queries to sql server format
Thanks in advance
Diwakar
Hi,I urgently need help with this. I am trying to implement custom paging with Sql 2000. However I have ran into a few problems.I really hope that someone would be able to help me out. I need to get this done by tomorrow!! (I know I screwed up big time, but I didn;t relize that built in paging fetches all the records, and the displays it page by page!!) 1) Currently the paging seems to be working. However the primary key always resets itself. For example, lets say I have 4 records ID 1,2,3,4When I delete ID 3, instead of displaying ID 1,2,4 it shows ID 1,2,3. Is there any way to solve this problem?2) The database that I am currently creating will have about 40,000 records, is it advisible for me to use your control (I hope so)?Thank you and do have a nice day.http://www.codeproject.com/aspnet/ASPNETPagerControl.asp CREATE PROCEDURE [dbo].[GetPagedProducts11]
(
@PageSize int,
@CurrentPage int,
@ItemCount int output
)
AS
Declare @UpperBand int, @LowerBand int
-- Get The Count Of The Rows That They Meet the Criteria
SET @ItemCount = (SELECT COUNT(*) FROM aduan)
-- Calculate the @LowerCount and @UpperCount
SET @LowerBand = (@CurrentPage - 1) * @PageSize
SET @UpperBand = (@CurrentPage * @PageSize) + 1
-- create a temporaty table
CREATE TABLE #AllRows(
ad_id int PRIMARY KEY IDENTITY(1, 1),
ad_nama varchar(40),ad_tarikh datetime , ad_status varchar(30) ,ad_title varchar(100)
)
-- INSERT ALL THE Rows that meets the Criteria
INSERT INTO #AllRows
SELECT ad_nama,ad_tarikh, ad_status,ad_title
FROM aduan
-- AND finally select and return desired -Paged- Rows
SELECT ad_id, ad_nama, ad_tarikh, ad_status,ad_title
FROM #AllRows
WHERE ad_id > @LowerBand AND ad_id < @UpperBand
RETURN
CREATE TABLE [omx].[UserAuthorization]( [MemberAuthorizationRecordIdentifier] [varchar](50) NOT NULL, CONSTRAINT [PK_UserAuthorization] PRIMARY KEY CLUSTERED( [MemberAuthorizationRecordIdentifier] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
View 4 Replies View Relatedhi Friends...
I have got a ordermaster table in which the orderid field is autoincremental.Now I also have an Ordercode field which is a combination of [current (orderid)-LICON)]
eg 1-LICON ..
I achieve this using the following query ...
insert into ordermaster(ordercode,orderamt,orderdate) select cast(max(orderid)+1 as varchar)+'-LICON' as ordercode,1000,'3/6/2004' from ordermaster
But I think the above query could lead to an inconsistent state ....If say following occurs :
1) USER1 reads max orderid as 1
2) User2 also reads max orderid as 1
3)USER 1 inserts record with ordercode as 2-LICON.
4)USER 2 will also insert record for ordercode as 2-LICON since max orderid read by USER2 is also the same as USER1.
How do I avoid this....I know I there are LOcktypes. But aren't locktypes used when we are updating same records....
Please help me out .I really need help in ths context
Hi all,
i think i have serious problem. i have almost 20,000 rows with the following conflict on a table (generated by merge replication over a period of around 3 weeks) :
'The row was updated at 'SubscriberServer3.DatabaseA' but could not be updated 'at 'PublicationServer1.DatabaseA'. Metadata mismatch'
i have 20 remote subscribers & 1 publisher. The above message originates from practically everywhere.
Besides dropping(not practical at the moment) & recreating replication to get rid of them, what else can i do to resolve this problem?? Do i have to resolve this problem row by row?? i'm at my wit's end. Please help!!
Thanx in advance.
Hi!I have a large project that is due to complete this week. In order tocomplete it I need SQL Server 2000 installed on a remote server. Mydisk is corrupt and to order another media disk would damage mydeadline. I have the licence and serial key, but now need good installfiles. I am even ready to buy another retail box, if I can find asupplier that would give me a download site for the media, while I waitfor the shipment!Please PLEASE help!Regards,Barry
View 6 Replies View RelatedHello there guyz!ahm, i just want to ask, how can i upload a MS word text file to a SQL Dbase? I know i must use the Input File, the one in the Toolbox menu for HTML controls. but i need to know 1. How can i link the SQL dbase to the Input file Button? 2. How to put the MS word file onto the SQL dbase? 3. what should i use to declare the MS Word file on the SQL Dbase? is it variable? char? etc... THank you so much! Please i Really need this one to put up my thesis system. and if it isn't much of a task, please can anyone send me the SOURCE CODES for my questions?
View 9 Replies View RelatedPlease provide me with this queries solution
"select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"
It is a query in access, I want to use the same in SQL SERVER 2000 using VB 6.0
to create a code.
Hi everyone,
I have SQL SERVER 2005 on my desktop and i need to run queries from remote machines on my SQL SERVER.
I thought it would be a good i idea to create a web page on my machine to where remote users can contact and activate SQL QUERIES.
My question is very simple and i wonder i a simple answer is available:
How do i run SQL queries whereby ASP code ?
How do i make a connection between a web page (.asp code) and a SQL sever ?
If the answer is complicated as i'm afraid it is, could any one provide me a link for a tutorial dealing with the above issues ?
Thanks a lot !
Hello, I have an urgent need for a DBA who knows SQL server 6.5 and 7 very well. Also if you have any ASP experience this would be music to my ears. Please give me a call to find out more 02 9221 6601 Australia.
View 2 Replies View RelatedI have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
These two queries when executed seperately give results in under 10 secs A union between these two does not give results even after 20 minutes ... Any idea why this is happening SELECT T001W.NAME1, t25a5.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a5.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT= MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3) AND MARA.PRDHA <> '' AND MKPF.VGART IN ('WR','WF') AND MKPF.MJAHR=YEAR(@BUDAT1) AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP SELECT T001W.NAME1, t25a2.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a2 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a2.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT=MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3) AND MARA.PRDHA <> '' AND MKPF.MJAHR=YEAR(@BUDAT1) AND MKPF.VGART IN ('WR','WF') AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a2.bezek,T001W.NAME1,YPLNT.VKGRP
View 14 Replies View RelatedHello Friends,
1) I have a table and column names are
Executiveid
companyname
positiontitle
start date
enddate
I want to see all executives who worked for more than 1000 days in one job. I am not sure but may be i need to use datediff function.
i have 3 tables and i have to use joins
table 1 executive
executiveid
firstname
lastname
companyid
table2 company
companyid
companyname
sales
currencycode
table3 currency
currencycode
description
how am i supposed to join 3 tables. i know how to give joins on 2 tables.
Your help will be greatly appreciated. Thanks
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
i have created my sql mdf file
now i was tring to create sql script for the same how do i do that?
script to create databse and tables
in object explorer i right clicked on my databse file name
selecdted tasks and then generate script
after that i clicked on next button
now how do i proceed further?
Hi, My site (in VB.NET) has developed a intermediate timeout problem.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Can someone tell me what could cause this intermediate problem. Is it something wrong with the query? It is definitely the problem from the query as I have 2 queries using same design and both of them develop similar problem.
Pleaee help as our site will have major advertising next month so need to fix it by then.
My server is running Win2003 on SQL 2000.
I have shown the cut-down version of the original query:
***************************************************************CREATE PROCEDURE dbo.sp_getmyitems(@CurrentPage tinyint,@PageSize int,@TotalRecords int OUTPUT)AsSet NoCount ON
Declare @FirstRec intDeclare @LastRec intSet @FirstRec = (@CurrentPage - 1) * @PageSizeSet @LastRec = (@CurrentPage * @PageSize + 1)
Create Table #TempTable(LocalItemID int IDENTITY PRIMARY KEY,ItemID int,Title varchar(250),)
Insert Into #TempTable(ItemID,Title)SELECT ItemID, TitleFROM tbl_Items
Select ItemID,TitleFrom #TempTableWhere LocalItemID > @FirstRec AND LocalItemID < @LastRec
Select @TotalRecords = Count(*) FROM #TempTableGO
**************************************************************Thank you
4 days ago we had a Server crash luckily we have the daily full database backups...Since the server which was crashed is unusable I installed SQL 7.0 on another machine...I tried to restore databases from our backup tapes...Here is the T-SQL I am running:
restore database pentatel from tape='.ape0' with file=2,move 'PentaTel_dat' TO 'D:mssql7datapentatel.mdf', move 'pentatel_log' to 'D:mssql7datapentatel.ldf'
The error message I am getting is:
Processed 8992 pages for database 'pentatel', file 'PentaTel_dat' on file 2.
Processed 1 pages for database 'pentatel', file 'PentaTel_log' on file 2.
Server: Msg 9004, Level 21, State 1, Line 1
The log for database 'pentatel' is corrupt.
Connection Broken
After these when I look at the SQL Enterprise manager I see that the database is in loading status...I left it in that status for hours but there was no reaction...So I tried to detach and attach a single file.Here are the TSQLs and Error messages...
exec sp_attach_single_file_db pentatel, 'd:mssql7datapentatel.mdf'
Server: Msg 945, Level 14, State 2, Line 1
Database 'pentatel' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'pentatel'. CREATE DATABASE is aborted.
We have 7 backups and I tried all of them...Same error messages...
When I try to change the status from loading to emergency or normal the database is always inaccessible
I are running these statements
exec sp_configure 'allow',1
reconfigure with override
update sysdatabases set status=32768 where db_id=6
and I stop and start the Mssql7 service but still inaccessible
Any idea???
I despretaly need help...Any recomendation is greatly apreciated...
Sefa Sevtekin
Pls consider the following table:---
Table Name: ChemoAdmin
Fields:
Field Name: Field Type: Typical Value:
patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg
I require the following output using one query:---
DATE 09/17/2004 09/21/2004
Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg
Pls help.
Pls consider the following table:---
Table Name: ChemoAdmin
Fields:
Field Name: Field Type: Typical Value:
patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg
I require the following output using one query:---
DATE 09/17/2004 09/21/2004
Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg
Pls help.
hi guys
i have a query which will take lot of load on the sql server and it need to optimized.
can any one suggest in optimizing the below query.
SELECT TOP 1 p.store_purchase_id
FROM valid_purchase p WITH (NOLOCK)
INNER JOIN purchase_item pitm WITH (NOLOCK)
ON p.purchase_id = pitm.purchase_id
INNER JOIN customer_phone c WITH (NOLOCK)
ON p.customer_brand_id = c.customer_brand_id
INNER JOIN phone ph WITH (NOLOCK)
ON c.phone_id = ph.phone_id
WHERE
p.last_name = ''
AND pitm.item_datetime > CURRENT_TIMESTAMP
AND pitm.item_datetime <= DATEADD(d, 12, CURRENT_TIMESTAMP)
AND REPLACE(ph.number, '-', '' ) = ''
Thanks
Ramakanth
On one of our servers the sqlseragent was running fine. But now it is not running. We se the option to start the server agent each time the OS starts. Even when I tried to start the agent manually from service manager it is not starting. Would any one help on this urgently.
Also how can we identify under what account any job runs. The reason is when we deleted some NT user accounts one of the job failed as this job runs under that userid. But in the properties it shows that sa is the owner of that job. What is best way to create a job so that these jobs run under one userid so that it won't fail when we delete users.
Thanks
My DTS package for importing data from oversea's database fail becasue it use many time ( more 2 hours ) . It will be complete When the run duration was used less than 2 hours and fail when the run duration was used more than 2 hours . If we eleminate to improve network , there is any way to do ?
Thanks you
Hi, I'm building a SSAS Integration project, it consist of extracting data from a Microsoft Access 2003 database and consolidate data into a SQL Server 2005 database for datawarehousing.
I need to make several operations for integration, but i'm struck in the following:
- I added an Ole DB Origin that connects to my access database
- I made an sql query with multiple joins that extract all data i need for first instance.
- in data i extracted, i got one column named: FECHA (and many other colums), it contains data of the date of the actual sale; but in previous steps i fill a table named DimTiempo from my sql server database that contanins all the dates (with no repeating) of the sales and added an unique primary key for them; so, now i need to get that primary key for each column FECHA i gotted in my sql query in OLEDB ORIGIN, for example:
I have: 20/20/2004 (column FECHA on my OLEDB ORIGIN gotted with an sql query with multiple joins)
I need: 001 (extracted from DimTiempo, its the primary key of the registry 001 - 20/20/2004)
I need that code or primary key to insert into my new table on my sql server database because its related directly with my DimTiempo table filled previously.
For more directions, i don't have that primary key column on the original access data base; i reestructure completely the database on sql server to make the datawarehouse work fine.
Please, i hope you help me, tell me what do i need to do to get all the data to fill my sql server 2005 database.
Thanks.
Hi All,First of all its very very urgent. Secondly I am not an expert. Now here is my problem.I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.CODE:USE [AdventureWorks]DECLARE @cmd varchar(1000);DECLARE @FileName varchar(100);DECLARE @FilePath varchar(100);SET @FileName = 'C: est'+@FilePath+'html';SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' + 'SELECT ' + @FilePath + ', FileType, * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'EXEC (@cmd);Select * from myTableJust for practice I am using Adventureworks db. I am using SQL Express 2005.My html files are named something like this:AC0234.htmlDB9803.htmlCG4571.htmlI cannot change my file names.Thanks in advance.Regards;
View 1 Replies View RelatedWe have a BIG problem that has been occurring for quite sometime. We have a RAGGED-RIGHT FFS (FlatFileSource) component which receives a FF and then transforms to XML. We define 10 FIELDS with the last field as {CRLF} per ragged right style. Simple right? The source file is sent to us from multiple separate groups, some of which don€™t use the last two fields and have early line termination, so they basically are not there. The problem is that when we define X amount of fields in SSIS, it expects X amount of fields to be there. So when we receive the source file that doesn€™t contain last two fields, SSIS tries to read in the next line, and in most cases successfully places the fields from the next line into the missing fields. Now what we end up with is a source file with 10 rows, but only half successfully transformed, with every other row fitted into the last two XML fields. SSIS completely ignores the {CRLF} until those specific numbers of spaces/fields have been met €“ even if it has to go onto the next line to get them. This seems horribly wrong to me. I would think that at the very least there should be some type of validation that prevents it from grabbing the next line. I thought that is what fixed-length SSIS style is for, and ragged right for allowing to parse multiple rows. We could enforce all clients to produce X char spaces (and we do), but there can be cases in any aspect where a file could get accidentally sent over with a shortened row. I have heard from certain individuals that SSIS adapter wasn€™t designed with this in mind. Is this true, I am to believe that this would still get parsed without throwing any errors and potentially damage the ERP system it is going into? If this is the case then I feel extremely disheartened about SSIS €“ especially with all of the great advancements it has made.
We desperately need a workaround or at the very least some way to validate that a new row {CRLF} isn€™t getting picked up in the first row of the flat file connection. Please can someone help ASAP!?
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
View 3 Replies View RelatedHi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
View 10 Replies View RelatedHi,
For many to many relationship, what is the minimum number of tables required.
Hi plz read below is my scenarioI have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.Thanks in advance.bye bye.happy coding!!!
View 1 Replies View RelatedExperts,
I've few questions regarding SQL Server. Appreciated, If any one out there help me with it.
I would like to do T-SQL Programing and would like to know some good book. I'll mostly be doing Stored procedures,Triggers and views programming. Any advice?
On my sql server I've 10 logins. But, I cannot view these logins from the Enterprise GUI. But, When i run a query against the syslogin table, those all 10 logins shows up. Why is that?
any help would be greatly appreciated.
Access 97.................
I'm trying to construct the following SQL statement but am missing something somewhere.
I get a syntax error with the 1st FROM highlighted.
If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.
I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.
SELECT
Type,
Sum(Volume) AS Volume,
TotalVolume
FROM
>>>>>>>>>>this following bit works............
(SELECT TOP 5
VotingUniverse_TopFirmsVolumeByType.Type,
VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
WHERE
(((VotingUniverse_TopFirmsVolumeByType.Volume)
In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
ORDER BY Volume DESC)))
ORDER BY
VotingUniverse_TopFirmsVolumeByType.Volume;)
>>>>>>>>>>>end of bit that works
INNER JOIN
VotingUniverse_TotalVolumeByType
ON
Type = VotingUniverse_TotalVolumeByType.Type
GROUP BY
Type,
TotalVolume;
hope this makes sense to someone anyway
thanks
====
Paul
I have a xxx.exe running on SQL 2000 server as a sql job every hour. For some reason this job sometime just hung in the middle and never finish. But
if I stop it and rerun it, it will be down within 10 seconds. I am wondering
if we can set up something so that the job aborts automatically if it ran more than 10 minutes.
Hi everyone.
We are running Sql 6.5 sp5 on a Pentium II 350 Mhz with 512 Mb. RAM, 1 ScSi 4 Gb hd, one 8Gb ScSi hd and 2 IDE hd (13,6 Gb and 8Gb) computer running windows NT 4.0 sp 6 without any problems.
Now have bought a new computer. The new one is a Pentium III 733 Mhz 512 Mb Ram with 2 ScSi ultra wide 2 18Gb each and one 40 Gb Ide Hd. We have installed windows NT 4.0 sp 6 and Sql 6.5 sp 5. We have restored our database to make some tests to it. The database is 7 Gb big.
The results of our test are that the old computer is between 20% and 30% faster than the new one when doing sql instructions and using the database even knowing that the hd´s of the new computers are 4 times faster than the old ones.
Would you please give us any help or clue that would make the new computer go faster?
We know that upgrading to sql 7 will work but we need first to make this computer work better with the 6.5.
Thank you.