Tuesday, June 25, 2013

[SQL Server] Query improvement

[SQL Server] Query improvement

Query improvement

Posted: 25 Jun 2013 03:02 PM PDT

Dear AllI have folloing queryselect sum(yy) sumyy, 'summary line' xxfrom tableawhere somedate_column < @startdate_parameterunionselect yy, xxfrom tableawhere somedate_column between @startdate_parameter and @enddate_parameterIs there a better way to achive this or this is the okRegardsKrishana

Access form front end to database

Posted: 25 Jun 2013 01:36 PM PDT

Hi,I have created a database with a table called "cable"Fields areID - identitycableID - nchar(8) not nullother fields are present but irrelevant for what I need to ask.The cableID field consists of an alphnumeric code.I have created a trigger on update of cable table which works the way I want it.My problem is that I want to use an Access 2010 form as the front end to enter information to the cable table to cause the trigger to run but as I have made cableID not null it must be filled in and it cannot be duplicated.In my form the identity field autoincrements which is great and what I want to appear in the cableID field is the next cableID based on the highest cableID present in the database.The code I have written below as an SQL query does what I want and creates the value (@newcableID) but I do not know how to have this appear automatically in the cableID field in the Access form so that when the users finishes filling the other fields and inserts the record it gets put into the database and therefore runs the trigger. This query would have to run each time a new record needs to be created so that it increments. I cannot use the ID field as there is some data already in the table that needs to stay and it is not in line with the ID field. There may also be instances where data needs to be entered manually which is why I have not used the identity field. It is there for other future uses.declare @newcableID nchar(8)declare @cableIDnum intdeclare @maxcableID nchar(8)set @maxcableID = (select max(cableid) from tblCable)set @cableIDnum = (convert(int, substring(@maxcableID,3,8)))set @cableIDnum = @cableIDnum + 1set @newcableID = (select 'CA' + right('000000' + cast((@cableIDnum) as varchar),6))I hope somebody understands what I am trying to do and I don't know if this is the correct site to ask this question but if anyone can help it would be great.

IDENTITY_INSERT, and Using Alphanumeric Autoincrement Primary Key

Posted: 31 May 2013 11:38 PM PDT

Hi Folks,Please help me on below few queries My table is Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)1) now im trying SET IDENTITY_INSERT USERS.ID OFFBut error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.Why I am getting that error? and How to eradicate it?&How to Set our Primary KEY to Alphanumeric AutoIncrementing?

SSIS: Not able to transfer a FLAT FILE Data to Database. Please help

Posted: 24 Jun 2013 08:19 PM PDT

Hi teamMy flat file content is emp_no emp_fname emp_lname dept_no,25348 Matthew Smith d3 ,10102 Ann Jones d3 ,18316 John Barrimore d1 ,29346 James James d2 ,9031 Elsa Bertoni d2 ,2581 Elke Hansel d2 ,28559 Sybill Moser d1ROW DELIMITER is COMMA & COLUMN DELIMITER IS COLUMN.& my Table in SQL SERVER IS EMP_DATA(EMP_ID int,F_NAME varchar(20),L_NAME varchar(20),DEPT_ID varchar(10))But when i carry on the PACKAGE Execution, the below error are stopping me[Data Conversion 0 - 0 [47]] Error: The "output column "Column 3" (59)" failed because truncation occurred, and the truncation row disposition on "output column "Column 3" (59)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.[Data Conversion 0 - 0 [47]] Error: Data conversion failed while converting column "DEPT_ID" (22) to column "Column 3" (59). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 0 - 0" (47) failed with error code 0xC020902A while processing input "Data Conversion Input" (48). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.The mapping is done Correct.The Length of column variables match in CONTROL FLOW ALSO DATA FLOW. Could anyone please help me understanding the above errors with possible scenarios?

How to move an excel file if it contains a specific sheet name like [sheet1$]

Posted: 25 Jun 2013 02:05 AM PDT

I was trying to move Excel files to a New folder if it contains a sheet name like sheet1 can any one of you help me in this ?

'No process is on the other end of the pipe'

Posted: 25 Jun 2013 01:17 AM PDT

When I try to log in to one instance of SQL Server as anything [i]other than sa[/i]*, I get the following error message (hopefully attached). Short version is[b]a connection was successfully established with the server, but then an error occurred during the login process. No process is on the other end of the pipe.[/b]I've Googled it but haven't found much.SQL Server 2008 Standard Edition.Mixed authentication mode is ON.Max user connections is set to 0 (unlimited)The password is correct.Shared Memory is enabled Named Pipes is enabledTCP/IP is enabledVIA is disabledAny ideas please?* edit: Windows authentication works fine. It's just SQL Server authentication that seems to cause this.

cant find download link for SQL server 2005 (64 bit)

Posted: 18 Jul 2011 04:14 AM PDT

Hi allI have been using SQL server 2005 Standard (32-bit).I am planning to upgrade to 64 bit. But unfortunately I cant find a download link for [b]SQl server 2005 Standard (64 bit)[/b]. If any one knows the microsoft link, it would be a great help. Thanks a million.AD

external keyword

Posted: 24 Jun 2013 04:26 PM PDT

what is external keyword when and where we use it?please give example?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog