JANAKIRAM writes "Hi,
I am using SQL Server 2005 & Windows XP O/s with SP6.
My SQL Server could not recognize the all new SQL Keywords & Methods incorporated into 2005.
It's givng Incorrect Syntax error.
Why so. What am I missing to cinfigure before using them.
Please let me know.
Thanks & Regards
Janakiram"
I am using the top keyword as follows in the same SP. The select and the update statements are one below the other:
Select TOP(1) col1,col2 from table1 where col3='val1'
Update TOP(1) table1 set col2= 'val2' where col3='val3'
There is a primary key on the column which is not included in either the select or the update statement.
Can anyone confirm that the both the statments will return the same row?
Full Text search must keep a list of key words or items somewhere.
How can I get access to this list to present to a user.
For example: show me all indexed words starting with letter a* and then ac*.
I'm trying to find this information without success.
The application (SQL2000 SP4) is running and appears to be functioning OK. The issue is that the SB Server doesn't recognize it, so when I try to add services to SBS, I fails the check. When I try to publish it to the AD, I get error 22039. MSCRM 3.0 is also installed on the same box and is working fine.
View 1 Replies View RelatedI have a GridView that is populated by a stored procedure via linq. I changed the stored procedure to return an additional column, but I can't get the additional column to show up in the GridView. The GridView has autogeneratecolumns=true, so if I were using a SqlDataSource the new column would show up automagically. But now it is not showing up and I cannot reference it. I can't figure out how to make linq recognize that my stored procedure has changed. I have tried deleting the sproc definition from the dbml file, making sure the partial class is removed the .vb file, refreshing the stored procedures list in database explorer, and adding the sproc definition back again (a hassle), but still it insists on defining only the original columns and not the new column. How do get linq to get back in sync with the database?
View 1 Replies View RelatedHi,
I'm using SQL SERVER 7.0.
I'm driving myself crazy on this one. I have 2 tables that look like this:
T1T2
C1C2C3C1C2C3
JOE1OTTAWAJOE1TORONTO
MARC1OTTAWAMARC4OTTAWA
GAVINNULLHALIFAXGAVIN3HALIFAX
DARRINNULLHALIFAXDARRIN3HALIFAX
DENISENULLPITTSBURGHDENISENULLPITTSBURGH
LOUISENULLRUSSELLLOUISE2RUSSELL
ANDREA3STITTSVILLEANDREANULLSTITTSVILLE
MARIO66PITTSBURGHGEORGE6KINCARDINE
LARRY6KINCARDINE
MARIO66PITTSBURGH
What I need to do is get all of the records from T2 that don't match EXACTLY to a record in T1. So I figured a LEFT OUTER JOIN should work:
SELECTT2.*
FROMT2 LEFT OUTER JOIN T1
ONT2.C1 = T1.C1
ANDT2.C2 = T1.C2
ANDT2.C3 = T1.C3
WHERET1.C1 IS NULL
But this statement returns the DENISE record when I do this (which has an EXACT match).
So, my thoughts took me to the NULL values in T1.C2 and T2.C2 for this record and I thought that, perhaps, the NULL values aren't being recognized as being equal (as they are UNKNOWN).
So I started digging around and found SET ANSI_NULLS OFF. I tried it but with no luck. Can you offer any insight on this one? What can I do to have NULL values recognized as being equal?
This is the result set that I would like to have returned in this example:
JOE1TORONTO
MARC4OTTAWA
GAVIN3HALIFAX
DARRIN3HALIFAX
LOUISE2RUSSELL
ANDREANULLSTITTSVILLE
GEORGE6KINCARDINE
LARRY6KINCARDINE
I've included a script to build and populate the tables below.
Any help on this will be greatly appreciated.
Thanks in advance,
Darrin
------------------------------------------------
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T1'
)
DROP TABLE T1
GO
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T2'
)
DROP TABLE T2
GO
CREATE TABLE [dbo].[T1] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T2] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO
INSERT T1 VALUES('JOE', '1', 'OTTAWA')
INSERT T1 VALUES('MARC', '1', 'OTTAWA')
INSERT T1 VALUES('GAVIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DARRIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T1 VALUES('LOUISE', NULL, 'RUSSELL')
INSERT T1 VALUES('ANDREA', '3', 'STITTSVILLE')
INSERT T1 VALUES('MARIO', '66', 'PITTSBURGH')
GO
INSERT T2 VALUES('JOE', '1', 'TORONTO')
INSERT T2 VALUES('MARC', '4', 'OTTAWA')
INSERT T2 VALUES('GAVIN', '3', 'HALIFAX')
INSERT T2 VALUES('DARRIN', '3', 'HALIFAX')
INSERT T2 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T2 VALUES('LOUISE', '2', 'RUSSELL')
INSERT T2 VALUES('ANDREA', NULL, 'STITTSVILLE')
INSERT T2 VALUES('GEORGE', NULL, 'KINCARDINE')
INSERT T2 VALUES('LARRY', NULL, 'KINCARDINE')
INSERT T2 VALUES('MARIO', '66', 'PITTSBURGH')
GO
Our network people changed our machine name one character without our permission, they say their naming standards can't handle an "underscore" we put in our name...now the SQL Server won't run because it doesn't recognize the name of the machine.
Is there a way of bypassing or telling SQL server that we changed the name without having to reload all of our information?
I have MS SQL 2000 Standard edition licensed in "per device" mode on adual xeon. However the properties->Processor tab on EM only list 1 cpu(CPU 0) and has "use all available processors" selected and the use [#]is greyed out.Anybody else experience this? Is it really using both processors? Or doI have some limited product key? I called MS tech support but they werelike, "um that'll be $295 to open a support instance" <*click*>.Thanks!--~zillaPosted via http://dbforums.com
View 2 Replies View RelatedHi all,
I'm having a strange behavior here, or maybe I'm doing something wrong, I'm not sure.
Anyway, I have a csv file, the Flat File Connection Manager is configured like this:
Row delimiter: {CR}{LF}
Column delimiter: {;}
For some rows in my file the last two columns are empty and the there is no semicolon for these empty rows but these rows are still ended by a CRLF but SSIS does not consider the CRLF as the end of the row, it consider the first 2 columns of the next row as the last 2 columns of the current row.
Sample:
CSV file:
Col 1;Col 2;Col 3;Col 4;Col 5
AAAA;BBB;CCC;;
AAA1;BBB1;CCC1;;
AAA2;BBB2;CCC2
AAA3;BBB3;CCC3
Imported rows in SSIS:
Col 1 Col 2 Col 3 Col 4 Col 5
AAAA BBB CCC
AAA1 BBB1 CCC1
AAA2 BBB2 CCC2 AAA3 BBB3;CCC3
Any idea ?
Sébastien
I'm using DTS to import a text file (fixed field). In my sample data, I have 21 rows of data.
DTS only brings in 15 or so, because it fails to recognize the CRLF on some of the rows, and just treats it and the subsequent row as part of the previous row.
The text file is being produced via FTP from an IBM Iseries. It imports just fine into Notepad and Excel.
Anyone have any ideas why DTS would have trouble with this ?
Thanks
Greg
I have a maintenance job which has many job steps - checkdb, backup, rebuild index, etc...
I was asked to setup a job which will copy all backup files to a central location.
Now I wrote the script and its working good (part of a new job step)
But, I am now thinking to add some more logic in the job step:-
that is, if the previous job (server maintenance) is successfully completed with the backup job (if completed successfully), then only the copy to location job will start, if the other job step is failed, my copy bkp job will not run.
I'm a report developer who has created some reportsd against sybase data on another server. I am developing on a 64 bit Server 2003 box using SQL 2005 Reporting services. I hace created 32 bit ODBC data dsns using the supplied utitlity (odbcad32.exe). As I am creating the reports I can pull data using the data sources without a problem, however when I deploy my datasource and report to Report manager and run I get the following:
An error has occurred during report processing.
Cannot create a connection to data source 'Chicago_Reporting'.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This has been driving me crazy for 2 days now. Any help would be very much appreciated.
Thanks
I have two issues:
I have a simple parent that uses an Execute Package Task to call a simple child package. The child package has a Data Flow that I disabled. When running the child package by itself, the data flow task is bypassed. When running the package via the parent the data flow task executes.
Second issue is when you disable the package configurations in the child, the parent doesn't recognize that it's diables and tries to load the configurations.
It's almost like there are some settings in a child that get ignored by the parent??
Anyone else experience this?
thx
I have a Windows Internal Database (installed by SharePoint WSS 3.0) and SQL Server Management Studio Express installed on W2K3 Std server. The server has local hard disk drive C, E, F & G. However SQL Server Management Studio Express only recognize C and E drive, for example in the set default database location or in attach database file dialog window only C & E drive are available. Anyone knows why and how to fix the problem?
Thanks
I have an Excel file(.xls) that I need to import to SQL Server database. I am trying to use the Import wizard. However, when I select the file and click next I get the warning "External table is not in the expected format.(Microsoft JET Database Engine)" . I opened the .xls file using a text editor. It looks like some html document.
<html xmlns="urnchemas-microsoft-comfficeffice"
xmlns:x="urnchemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>.................
I also used OPENQUERY to see if it works.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 xml;HDR=Yes;Database=C:myexcelfile.xls', 'SELECT * FROM [Table1$]');
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:datamyexcelfile.xls', 'SELECT * FROM [Table1$]')
I get the following errors.
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "External table is not in the expected format.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
When I open the file and save it to overwrite the existing .xls file it works fine.
Is there a way to import such files using SSIS without having to open and save it?
Thanks in advance!
Tkaram
I have created a table name "tblPopulation" with fields 'Country', 'State' etc....
When i query it with table name as tblpopulation with fields 'country', 'state' then i get error as invalid object name.
Hi, i'm new to SSIS and trying to import some csv files (comma delimited) into SQL Server. A NULL value for a CHAR column is correctly regonized as NULl in SQL Server, but a NULL value for of a mapping to a VARCHAR column in SQL Server is not recognized correctly and i get the value "'NULL'" in SQL Server (including the single comma.
Sample:
CSV file contains columns A and B. A and B contains the Text NULL.
Column A is mapped to a CHAR field, and column B is mapped to a VARCHAR field in SQL Server.
After the import, SQL has the following value: A = NULL as NULL, B 'NULL' as text.
did anyone else had this problem?
thanks so much for any help.
Hi,
I just started working with Visual Web Devloper 2005 Express Developer and sql server 2005 Express Edition. My web is hosted with 1and1.com. They have informed me that I can't upload the database generated by the developer software and from what I understand, I will import those files into the mssqlserver database on my site. From my limited understanding, this is done from the backup file that is created from my local sql database. I didn't see a way to do that within VWD 2005 so I downloaded SQL Server Management Studio Express. When I opened it I chose the SQLServer (sqlExpress) located on my computer. The connection seems to work fine. However when I go to open a file and open the database that is in my web I get an error that states that there is no editor and to be sure the aplication for file type .mdf has been installed.
I'm not sure where to go from here or if I'm even on the correct path.
Thanks,
Leesha
I have just upgraded from Report server 2005 to 2008 r2. I have a program that calls the report server to run some reports but I have getting the error Server did not recognize the value of http header SOAPAction:
[URL] .....
I can log in to the report server and run any report that I want without any issues.
I am using Excel 2013 64bit and use an english Excel version, but with a comma as a decimal seperator and semi-colon as a list seperator.
In Excel everything works fine, but PowerPivot does not properly recognize that I use a semi-colon for formulas.
PowerPivots lets me write formulas with the semi-colon and not the comma, so that is fine.
However, two issues appear:
the yellow smart formula help box that appears when you start typing a formula, thinks I have to use commas, so when I use semi-colons instead, it does not jump to the next parameter.This problem also causes parameters where I have to enter a table or field to not suggest me table and fields when I start typing.Sometimes the formula validation even throws me an exception, that my formula syntax is incorret, ebcause Id id not use a comma. However, commas also do not work. I have to do some weird playing around until it finally accepts my formula.
I hope this buggy behaviour gets fixed, but is there a way I can work around this without changing my formula/list seperator? I also do not want to use a German Excel version, because I am used to the english formulanames.
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
I am trying to pass several ids to use in a where clause. 1 For Each row as GridViewRow In gv_child.Rows
2 If row.RowType = DataControlRowType.DataRow Then
3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox)
4 If chk IsNot Nothing AndAlso chk.Checked Then
5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text)
6 ids &= "," & id
7 End If
8 End If
9 If ids.Length > 1 Then ids = ids.Substring(1)
10 'Submit to sql with ids as param...
11 Next
I am getting an exception. Error converting '38,39' to a column of datatype int. What am I doing wrong?
I need to retrieve records where the date is in between the current date and 4 days previous.
I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...
Can someone help out?
It's blue in SQL Studio but I can't find it in SQL Books on-line. Iwanted to use it as a field name but don't like using [].Is it reserved?
View 6 Replies View RelatedHi guys,
Just a newbie question here.
Do we really need to use the GO keyword?
Thanks,
MeTitus
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.
At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
View 7 Replies View RelatedHi everyone,
I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that:
private SqlConnection connection;public SqlConnection Connection{get{ if (connection == null) connection = new SqlConnection(); if (connection.State != ConnectionState.Open) { connection.ConnectionString = ConnectionString; connection.Open(); } return connection;}}
The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL();
userDAL.DoSomethingAndAccessConnectionProperty();
// Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
So, if I do the following:using (UserDAL userDAL = new UserDAL())
{
userDAL.DoSomethingAndAccessConnectionProperty();
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
}
I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Can this be done? What would the query look like?
I am looking for a list like:
bull
barrel
rodeo
western
cowboy
Any suggestions?
Thanks,
Rob
If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?
ie,
SELECT DISTINCT fieldA, fieldB
FROM tableA
but not
SELECT fieldA, DISTINCT fieldB
FROM tableA
or
SELECT DISTINCT fieldA, DISTINCT fieldB
FROM tableA
thanks again, this is a great forum
ddave
hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?
.
.
.
.
here is my query :
SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j
Good luck for all the folks
Hi all,
I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key).
The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book.
Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work:
SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID
But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.