Dynamic SQL Help Needed.
May 20, 2004
I have the following stored procedure :
CREATE PROCEDURE usp_CraneRental_UpdateRate
@iEntryID int,
@cField sysname,
@cValue varchar(100),
@dtModified datetime OUTPUT
AS
SELECT @dtModified = GetDate()
DECLARE @cSql VARCHAR(500)
SELECT @cSql = 'UPDATE tbCraneRentalRates SET ' + @cField + ' = ' + RTRIM(@cValue) +
', Modified = ' + '"' + CAST(@dtModified AS VARCHAR) + '"' +
' WHERE EntryID = ' + CAST(@iEntryID AS VARCHAR)
EXEC(@cSql)
IF(@@ERROR <> 0 OR @@ROWCOUNT <= 0)
RAISERROR('Failed to update transportation rate!',16,1)
GO
that generates the following SQL String :
UPDATE tbCraneRentalRates SET MoveIn = 0, Modified = "May 20 2004 9:59 AM" WHERE EntryID = 1
The error I am getting is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'May 20 2004 9:59AM'.
?????
This doesn't make sence, the SQL statement looks perfectly fine ?
Any help?
Mike B
View 3 Replies
ADVERTISEMENT
May 29, 2008
When I try to run this query: DECLARE @sql varchar(200)DECLARE @CurrentTable varchar(200)SET @CurrentTable = 'AdditionalReqHelp'SET @sql = 'Alter Table' + quotename(@CurrentTable) + 'ADD BackgroundCost float NOT NULL CONSTRAINT BackgroundCost_Default DEFAULT 0'EXEC sp_executesql @sql I am getting this error...Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.Cant find what mistake I am doing.. any help is appriciated.Thanx.
View 7 Replies
View Related
Jun 3, 2008
TABLE IN USE:CREATE TABLE [dbo].[Book]( [FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Price] [decimal](18, 0) NOT NULL, [LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]------------------------------------------------------------------------------------------------ This is the procedure I have made. It compiles successfully. ALTER PROCEDURE InsertDataIntoBook@empdata ntext,@columns varchar(4000),@xpath as varchar(4000)ASDeclare @SQL varchar(max)DECLARE @ParmDefinition nvarchar(500)-- SET @SQL = 'DECLARE @XML as nvarchar(max)' + CHAR(13) + CHAR(10)SET @SQL = 'DECLARE @DocHandle as int' + CHAR(13) + CHAR(10)-- SET @SQL = @SQL + 'SET @XML = @xmldoc' + CHAR(13) + CHAR(10)SET @SQL = @SQL + 'EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmldoc' + + CHAR(13) + CHAR(10)SET @SQL = @SQL + 'INSERT INTO Book ('DECLARE @hDoc int DECLARE @pos int, @nextpos int, @valuelen int SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @Columns, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@Columns) + 1 END - @pos - 1 SET @SQL = @SQL + convert(varchar, substring(@Columns, @pos + 1, @valuelen)) SELECT @pos = @nextpos if @nextpos > 0 SET @SQL = @SQL + ',' ENDSET @SQL = @SQL + ')'SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'SELECT * FROM OPENXML(@DocHandle,''/book'',2)'SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'WITH('SELECT @pos = 0, @nextpos = 1 WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @XPATH, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@XPATH) + 1 END - @pos - 1 SET @SQL = @SQL + '[' + convert(varchar, substring(@XPATH, @pos + 1, @valuelen)) + ']' SET @SQL = @SQL + ' Varchar(1000)' + '''' + convert(varchar, substring(@XPATH, @pos + 1, @valuelen)) + '''' SELECT @pos = @nextpos if @nextpos > 0 SET @SQL = @SQL + ',' SET @SQL = @SQL + CHAR(13) + CHAR(10) ENDSET @SQL = @SQL + ')'Print @SQLEXECUTE sp_executesql @SQL, N'@xmldoc ntext',@xmldoc = @empdata; ------------------------------------------------------------------------------------------------------------------------ @SQL is string that contains the dynamic sql. Using @xmldoc as an input parameter to dynamic sql. ------------------------------------------------------------------------------------------------------------------------Executing like this: EXEC @return_value = [dbo].[InsertDataIntoBook] @empdata = '<book genre="autobiography" publicationdate="1981-03-22" ISBN="1-861003-11-0"> <title>The seven Kingdoms</title> <author> <first-name>Benjamin</first-name> <last-name>Franklin</last-name> </author> <price>8.99</price> </book>', @columns = N'FirstName, Price, LastName', @xpath = N'//book/author/first-name,//book/price,//book/author/last-name' ---------------------------------------------------------------------------------------------------------------------Giving this error :DECLARE @DocHandle as intEXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmldocINSERT INTO Book (FirstName, Price, LastName)SELECT * FROM OPENXML(@DocHandle,'/book',2)WITH([//book/author/first-name] Varchar(1000)'//book/author/first-name',[//book/price] Varchar(1000)'//book/price',[//book/author/last-name] Varchar(1000)'//book/author/last-name')Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.The dynamic sql string is build correctly but somehow it is not getting executed.Thanks for reading through this long post.
View 4 Replies
View Related
Sep 7, 2007
I have some dynamic SQL code that is executed using the following:
SET @SQLSTRING = @SELECT+@FROM+@WHEREPARM+@ORDER
EXEC sp_executesql @SQLSTRING
and this works fine if my @WHEREPARM is
SET @WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate'
However, it fails if my @WHEREPARM is
SET @WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate
and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''
I am getting the following error: Incorrect syntax near '460'
I believe I need to change the number of apostrophes around the 460 value but can't quite figure this out. Can someone provide me a correct example?
Thanks!
View 4 Replies
View Related
Apr 23, 2014
I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:
select *
from Table
where D10 > midDaypct * 2 and DOW6 > 0
union
select *
from Table
where DOW6 >midDOWpct * 2 and D10 > 0
We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow.
View 5 Replies
View Related
Sep 30, 2015
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Oct 24, 2004
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.
View 2 Replies
View Related
Apr 15, 2008
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
View 14 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Mar 9, 2007
I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.
Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.
I have come to understand that a dynamic anything is a moving target!
Please advise.
View 3 Replies
View Related
Sep 24, 2006
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 Related
Oct 30, 2007
Hi 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 Related
Dec 1, 2004
Hi,
For many to many relationship, what is the minimum number of tables required.
View 1 Replies
View Related
Oct 17, 2005
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 Related
Dec 20, 2000
Experts,
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.
View 1 Replies
View Related
Mar 25, 2004
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
View 5 Replies
View Related
Aug 15, 2002
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.
View 1 Replies
View Related
Feb 22, 2001
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.
View 3 Replies
View Related
Jun 18, 2004
Hello,
When I try to run my asp file it gives me this error. :mad:
Technical Information (for support personnel)
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
/conOpen_inc.asp, line 10
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Page:
GET /Default.asp
Anyone can tell me whats happening ? thank you
View 5 Replies
View Related
Oct 12, 2004
I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”.
Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
Thanks
View 8 Replies
View Related
Jul 25, 2007
Dear All,
I have a client requirement. We are integrating two applications. So the problem is that, whenever some updates (meaning to say "create" , "delete" , "update") happens in the SQL DB Tables, i needed to track that data and send it to a different system.
What I have thought abt is that if SQL could generate a file with all the values deleted/updated/created in the DB, then I could take those values and do the needful. Please help me..
Thanks,
Sanjo
View 2 Replies
View Related
Feb 28, 2004
Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:-
This is my input table
TableA
ID A B C
------------------------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn
My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
-----------------------------------------------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl
Please help me on how to create my output.
Thanks in advance,
Sid.
View 11 Replies
View Related
Mar 26, 2004
Hi,
I have a table structure like this
TableName: Common
Columns
PartnerId: int
NativeId: int
FirstName: nvarchar(50)
LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId,
2) if duplicates exists in the above condition i should select top 1 (first record)
How can i do it??
TIA,
sudheer
View 5 Replies
View Related
Apr 12, 2004
Hi,
I have been SQL developer for past 2 yrs, want to get into Administration(DBA) can any one suggest me some good articles on net and good books for this.
TIA,
sudheer.
View 14 Replies
View Related
Feb 26, 2008
Hi experts,
I am upgrading my database from SQL Server 2000, SP4 to SQL server 2005, SP2.
1) Is it required to recreated my user defined functions? I mean drop them and recreate them again?
2) Do I need to recreate the views and Stored procedure?
3) Do we have a checklist from Micorosoft for upgradation?
Thanks in advance
Regards
Sachin
Don't sit back because of failure. It will come back to check if you still available. -- Binu
View 2 Replies
View Related
Jul 23, 2005
Hey all, I need some help to build some data bases and maybe some morestuff. Please reply only if you can do it volunteerly or very cheap please;but there is incentives in time..TIA
View 4 Replies
View Related
Jul 20, 2005
I have a tableCreate Table Payments {paymentid int,customerid int,amount int,date datetime}What I want is the sum of the amounts of the last payments of all customers.Now the last payment of a customer is not necessarily the one with thehighest paymentid for that customer BUT it is the one with the highestpaymentid on the MOST RECENT date. We dont keep the time part just the dateso if there are more than 1 payments of a customer on a date ( and there aremany such cases ) only then the paymentid decides which is the last payment.Further the last payment may be the last as of today but I may want to findthe sum of all the last payments upto say March 1, 2003or any date. My own solution is too slow even it is correct.SELECT SUM( AMOUNT )FROM PAYMENTS AS P1WHERE PAYMENTID =( SELECT MAX( PAYMENTID ) FROM PAYMENTS AS P2 WHERE P1.CUSTOMERID =P2.CUSTOMERID AND DATE =( SELECT MAX(DATE) FROM PAYMENS AS P3 WHERE P3.CUSTOMERID = P2.CUSTOMERIDAND DATE < #9/8/03# ))What would be the most efficient solution to this.Both in SQL Server and in Access 2000thx in advance
View 3 Replies
View Related
Feb 11, 2008
I am using the following T-SQL to pull records from a given table that have a start time between 6:59:59PM and 7:00:00AM. However, it's not working. The SQL I'm using is listed here. What am I doing wrong?
select RecNum, convert(varchar(8), StartTime, 8) as Start_Time from TableA
where convert(varchar(8), StartTime, 8) between '18:59:59' and '07:00:00'
By the way, there are a multitude of records that I'm looking for in the table.
View 4 Replies
View Related
