InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 17 May 2008 08:48:10 GMT20http://www.sqlservercentral.com/Forums/Topic501128-145-1.aspxI have a table XYZ with a datetime field.In this field the date and time is stored: for example:datetime-----------------------2008-05-15 12:59:36.0002008-05-15 12:55:09.0002008-05-15 00:00:00.0002008-05-15 00:00:00.000I want to get the count of this datetime field with a where condition: for exampleselect count(datetime) from XYZ where datetime='2008-05-15'The result is 2, but the actual result should be 4.this is because they differ with time also, but for me when i give only date condition i should get count as 4 irrespective of time.Thu, 15 May 2008 03:40:11 GMTmuthukrishnan.ehttp://www.sqlservercentral.com/Forums/Topic502501-145-1.aspxHi,With the TRY .. CATCH block, you can determine the procedure name with ERROR_PROCEDURE(). Unfortunately, this function only returns data when there's an error. How can I determine the procedurename without having an error?I want to use this for batchlogging like:[code]insert into processlogvalues [b]ERROR_PROCEDURE()[/b], CURRENT_TIMESTAMP[/code](Yes I know, this example is'nt working) :crazy:thanks!Sat, 17 May 2008 07:16:24 GMTWilfred van Dijkhttp://www.sqlservercentral.com/Forums/Topic500283-145-1.aspxHello allwhen i am executing following query i am getting following error. my query as follows[code]select ques.catid,ques.question,ques.qid,ques.subcatid,sub.subcategory from ada_question ques,ata_subcat sub whereques.subcatid=sub.subcatid and ques.catid=1 and ques.status=1 and ques.qid in (cast(@qid as int))order by sub.subcatid,ques.qid asc[/code]where @qid contain='39,40,1,4,35,41,52,54,55,56'I need to convert string value int because my dolumn data type is int.Any Help?Wed, 14 May 2008 04:18:01 GMTrahulhttp://www.sqlservercentral.com/Forums/Topic501942-145-1.aspxCan i use a temp table in function ?Fri, 16 May 2008 05:49:38 GMTZahid Farooqhttp://www.sqlservercentral.com/Forums/Topic502145-145-1.aspxHi,Does it matter in using OR or IN in a WHERE clause.SELECT a.Col1, b.Col2 FROM dbo.Table1 a INNER JOIN dbo.Table2 b ON a.Col1 = b.Col1 WHERE b.Col3 IN ('Q', 'P')VsSELECT a.Col1, b.Col2 FROM dbo.Table1 a INNER JOIN dbo.Table2 b ON a.Col1 = b.Col1 WHERE b.Col3 = 'Q' OR b.Col3 = 'P'Thanks,-RFri, 16 May 2008 08:57:15 GMTramhttp://www.sqlservercentral.com/Forums/Topic501611-145-1.aspxI have a query with many joins (2 inner the rest outer). table A may be related to B or C or neither. my query currently has multiple ors on outer tables (like below). the outer join data looks correct. But I doubt it is correct since B and C are outer joins. I am stuck on moving the or logic from where to outer join and. I tried moving "where" logic to left outer join with "or and" which runs forever. I tried moving "where" logic to left outer join with "and / and" and the outer b table data is missing. select *from a...left outer join bon a.aid = b.bid left outer join con a.cid = c.cidWhere a.flg=1 AND b.b_status_cd > 1 And a.a_date >= @startDate And a.a_date < @Enddate OR ( b.b_date >= @startDate And b.b_date < @Enddate) OR ( c.c_date = @startDate And c.date < @Enddate)Could this data be correct? any ideas on query redesign?Thu, 15 May 2008 13:57:57 GMTpnethhttp://www.sqlservercentral.com/Forums/Topic501871-145-1.aspxHi Folks!I've created a lead-table with an instead-of-trigger for insert,delete,update. The trigger distributes the data to different sub-tables.If I do a bulk insert from file via management studio query, everything works fine: The records inserted into the lead-table are distributed to the subtables.If I do the same from my application which has an OleDb connection with fastload property set on and fastload option FIRE_TRIGGERS set, the instead-of-trigger is ignored and all records are written into the lead-table.Has anyone encountered a similar problem and knows a proper solution for it?Many pre-thx in advance!cu,AndreasFri, 16 May 2008 04:09:34 GMTandreas.dillinghttp://www.sqlservercentral.com/Forums/Topic501850-145-1.aspxHi,I have a dataset result as given below.Year Sales Discount Taxes----- ----- -------- ------2001 100 25 152002 200 20 152003 150 15 122004 160 25 16I want the SSRS table to display output as 2001 2002 2003 2004 ---- ---- ---- ----Sales 100 200 150 160Discount 25 20 15 12Taxes 15 15 12 16Please let me know if some has tried or some has idea on thisThanks in Advance Fri, 16 May 2008 03:18:22 GMTdgvsbabuhttp://www.sqlservercentral.com/Forums/Topic501868-145-1.aspxHi,I'm new with stored procedures and I don't know if what I want to do is possible.I have in my application a dynamic query that can be spread over 3 tables (linked with joins).But first the database structure witch cannot be changed (external systems): Table Bernt (193 columns): FLTIME datetime, indexed Meting1 float Meting (in Dutch) = Measurement Meting2 float ... Meting192 float TeOud boolean (is set to true by a insert trigger if FLTIME is older than 7 days) Table Draeger (165 columns): FLTIME datetime, indexed Meting1 float Meting2 float ... Meting64 float TeOud boolean Table MDA (129 columns): FLTIME datetime, indexed Meting float Meting2 float ... Meting128 float TeOud boolean Table Detector: Groupnumber int State nvarchar(10) TAG nvarchar(50) [used for linking to the three other tables] Loglevel float ... other fields witch we don't needIn the first three tables there is a new record added each second. So the amount of data is huge.The SQL Server is set up to make a backup and I restore it programmatically to a other database so we don't have any problem with insert locks.Now I have to run some queries, depending on the state of a measurement (Active/inactive) and the groupnumber (defined in table Detector).I do this in my VB.NET2005 program with ADO.NET. But this is to slow and doesn't work all the time (Connection time out). So maybe it's possible to put in into a stored procedure to speed up the process.The query is variable as I already told you:[code]Query = SELECT B.FLTIME, B.Meting103 AS B_Meting103, B.Meting104 AS B_Meting104, B.Meting105 AS B_Meting105, B.Meting106 AS B_Meting106, D.Meting2 AS D_Meting2 FROM Bernt B, Draeger D WHERE ( B.Meting103 > 0.7 OR B.Meting104 > 10.8 OR B.Meting105 > 0.5 OR B.Meting106 > 0.8 OR D.Meting2 > 3.8) AND B.TEOUD = 1 AND B.FLTIME = D.FLTIME ORDER BY B.FLTIME ASCQuery = SELECT B.FLTIME, B.Meting42 AS B_Meting42, B.Meting43 AS B_Meting43 FROM Bernt B WHERE ( B.Meting42 > 6.0 OR B.Meting43 > 0.8) AND B.TEOUD = 1 ORDER BY B.FLTIME ASCLargest possible query = SELECT B.FLTIME, B.Meting1 AS B_Meting1, B.Meting2 AS B_Meting2, ..., B.Meting192 AS B_Meting192, D.Meting1 AS D_Meting1, ..., D.Meting64 AS D_Meting64, M.Meting1 AS M_Meting1, ..., M.Meting128 AS M_Meting128 FROM Bernt B, Draeger D, MDA M WHERE (B.Meting1 > 0.5 OR B.Meting1 > 0.8 OR ... [for each measurement]) AND B.TEOUD = 1 AND B.FLTIME = D.FLTIME AND B.FLTIME = M.FLTIME ORDER BY B.FLTIME ASC[/code]I tried with this query but that didn't work:[code]ALTER PROCEDURE [dbo].[usp_GetBrentDraegerMdaData] @Columnnames ntext, @Tablenames nvarchar(100), @WherePart ntextASBEGIN SET NOCOUNT ON; SELECT TOP 10 @Columnnames FROM @Tablenames WHERE Bernt.FLTIME < DATEDIFF(d,3, getdate()) and @WherePartEND[/code] And also this:[code]ALTER PROCEDURE [dbo].[usp_GetBrentDraegerMdaData] -- Add the parameters for the stored procedure here @Tabellen nvarchar(100)ASBEGIN SET NOCOUNT ON; SET @Commandstring = 'SELECT top 10 Meting1 FROM ' + @Tabellen EXEC @CommandStringEND[/code]If I replace every parameter to the text it should be then I works fine. But the text must be a parameter.The 'TOP 10' is just for limiting the result for testing, otherwise the resultcount exceeds 25*10^6 records.Does anyone have a solution for this problem or any other thing that I could try for getting the data?Fri, 16 May 2008 03:58:46 GMTjo.woutershttp://www.sqlservercentral.com/Forums/Topic501256-145-1.aspxHi All,Yesterday i was asked a question regarding join and i told them that but after that the other question was can we use joins in update, i said no(with my knowledge). I want to know that i am correct or not.Please update me.Thanks in advance,Syed Sanaullah KhadriDBAThu, 15 May 2008 06:53:50 GMTSanaullahhttp://www.sqlservercentral.com/Forums/Topic501663-145-1.aspxI have this canned code in VB.NET that I use to write out an XML file from a dataset (mostly for debugging purposes) Private Sub GenerateXMLFile(ByVal ds As DataSet) Dim dd As XmlDataDocument = _ New XmlDataDocument(dsVendorItem) Dim txtXMLTemp As XmlTextWriter = _ New XmlTextWriter(TempFilePath & "\" & "MyFile.XML", Nothing) dd.WriteTo(txtXMLTemp) txtXMLTemp.Close() txtAsk.Text = "XML file created at: " & TempFilePath End Subthe file ends up looking like this: and so forthdsh is the name of a Schemahow do I use this file to update a SQL table?Bulk Insert?Insert with XML?Thu, 15 May 2008 15:11:50 GMTSeggermanhttp://www.sqlservercentral.com/Forums/Topic500759-145-1.aspxCREATE TABLE #XYZ(NTUser varchar(20),FullNTName varchar(50),FirstName varchar(20),MiddleName varchar(20),LastName varchar(40),Rolename varchar(30))DECLARE @NAME AS varchar(1000)DECLARE @ADDRESS AS varchar(1000)Declare CUR_C CursorForSelect RolenameFrom DCJ_SecurityRoleOpen CUR_CFetch Next From CUR_Cinto @NAMEWhile @@fetch_status =0BeginIF @NAME not in('All','PUBLIC')BeginSET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com'INSERT INTO #UserDetailEXEC ('SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME,initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY(ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM''''LDAP://DC=XYZ,DC=com''''WHEREMemberOf=''''' + @ADDRESS +''''' '')')ENDFetch Next From CUR_Cinto @NAMEENDCLOSE CUR_CDEALLOCATE CUR_CWhen I executes this code I am getting some syntax error likeMsg 102, Level 15, State 1, Line 8Incorrect syntax near 'APJPRD',where APJPRD is one of the role,Can anyone suggest what dynamic sql part has error??Wed, 14 May 2008 11:19:05 GMTnirav284http://www.sqlservercentral.com/Forums/Topic500905-145-1.aspxHi, One of my store proc takes 15 min to run on dev, but when I moved to production server (better and faster machine), it takes 11 hours to finish. Can anyone think of anything (server setting/configuration....) might lead to this? I do have cursor in the store proc, but since it only takes 15 min to run I didn't bother to change that. ThanksiceWed, 14 May 2008 14:40:58 GMTiceheartjadehttp://www.sqlservercentral.com/Forums/Topic501120-145-1.aspxDear all Gurus / MastersI do have another problem as followsI am trying to loop in the record set of one table, my code is as followsThe following code is on a click event of a push button on a form-- code starts hereDim Conn As ADODB.ConnectionSet Conn = New ADODB.ConnectionConn.Provider = "Microsoft.jet.OLEDB 4.0"Conn.Open "C:\Study\STUDY_PAYROLL.mdb"Dim MyTableRs As ADODB.RecordsetSet MyTableRs = ADODB.RecordsetMyTableRs.Open "COM_EMPMST", Conn, adOpenDynamic, adLockPessimisticMyTableRs.MoveFirstDo Until MyTableRs.EOF mychk = DCount("[EMP_CODE]", [ATT_IMPMST], "[att_emp_code] = [EMP_CODE] and [att_date] = #02/05/2008#") If mychk = 0 Then MsgBox ("Employee Not Found in Attendance") Else MsgBox ("Employee found in attendance") End IfLoopMyTableRs.CloseSet MyTableRs.ActiveConnection = NothingConn.CloseSet Conn = Nothing-- code endsNow, when I run this code I am getting error "Method or Data Member Not Found" Please help me, I don't know why I am getting this errorb rgdssunil joshiThu, 15 May 2008 03:15:18 GMTsunnyjoshibombayhttp://www.sqlservercentral.com/Forums/Topic499269-145-1.aspxIn terms of normalization vs performance...I have data in a table (call it Company.Address) and I have another table that *optionally* overrides the data (call it CompanyBranch.Address) - ie: If the CompanyBranch.Address IS NULL then use the Company.Address as the default.Should I put the address data in a separate table and join using a FK or just leave it in the existing two tables.According to the rules of normalization I should split out the Address data, but it seems easier to keep it like it is.Any ideas on the best approach? NB: The real life scenario involves 23 columns of data.TIA (my 1st post - Hi everyone!)Mon, 12 May 2008 21:10:43 GMTCypherOzhttp://www.sqlservercentral.com/Forums/Topic501054-145-1.aspxhi,I'm having a temp table like belowcode1 description amt---------------------------------S123 onetwothree 15S123 onetwothree 15In the above table i want the amt field of the duplicate row to show/have 0 as belowcode description amt---------------------------------S123 onetwothree 15S123 onetwothree 0Is it possible ? Can any one plz help me on this. :(Thu, 15 May 2008 00:51:50 GMTShttp://www.sqlservercentral.com/Forums/Topic500849-145-1.aspxI have 1 simple problem, and little bit confused why it happend!There is situation:I create the table like this ....CREATE TABLE KONTAKT( IDK INT IDENTITY(1,1) NOT NULL, FNAME VARCHAR(20), SNAME VARCHAR (20), SEX CHAR(1), CONSTRAINT pk_IDKONTAKT PRIMARY KEY (IDK), CONSTRAINT CHK_GJINIA CHECK (SEX = 'M' OR SEX = 'F'));and I try to insert values like:INSERT INTO KONTAKTVALUES ('aaa', 'aaaaa', 'M');GOSELECT * FROM KONTAKT;GOok in this situtation you have the IDK = 1if you try to insert values like:INSERT INTO KONTAKTVALUES ('aaa', 'aaaaa', 'G');GOThe check constraint will fire and the values will not inserted but the IDK as Identity Column with grow by 1 and if you try to insert values regularly such as:INSERT INTO KONTAKTVALUES ('bbb, 'bbbbb', 'F');GOSELECT * FROM KONTAKT;GOYou will see that the IDK = 3 =================================Any explain where is gone the IDK = 2?:w00t:Wed, 14 May 2008 13:11:36 GMTDugihttp://www.sqlservercentral.com/Forums/Topic500732-145-1.aspxIf i wanted to create a search that would work within a report, how in the world would i do that?Example:*************Report name Search: Enter key word :search button:Search finds first instance of key word*************I realize contol f exists, but not our users are more visual.Wed, 14 May 2008 11:01:19 GMTjeannie.cribleyhttp://www.sqlservercentral.com/Forums/Topic500743-145-1.aspxI've got a matrix table with 3 columns frozen and the title of the table is visable when scrolls. I build it, and it runs. I preview and it's beautiful. everything works.I deploy and the formatting only works on the last page.What the heck could i have done to make this happenPS. I deployed using IE not any other browser.Wed, 14 May 2008 11:06:22 GMTjeannie.cribleyhttp://www.sqlservercentral.com/Forums/Topic499229-145-1.aspxHi,Is there any system stored procedure or something that helps me to retrieve Hardware info in order to exclusively identify a SQL Server?I'm designing a centralized license validation schema and I've covered everything ( I guess ! ;)) but a Server Hard Disk Cloning. This way anybody could duplicate an instance of my application easily.Any advice?Thanks in advance!Jean PaulMon, 12 May 2008 17:58:27 GMTJean Paul Mir V.http://www.sqlservercentral.com/Forums/Topic499458-145-1.aspxcan someone assist me in joining the two queries together into 1so that I can get one result set---------------------------------------------------------SELECT count(ee_occup_levels) AS OcpLvl, ee_occup_levels AS [Occupation Levels], headcountFROM headcountdecWHERE period = 'March 2008' AND headcount ='NewHires' AND staffno IS NOT NULL AND ee_occup_levels is not null GROUP BY ee_occup_levels, race, gender, headcountORDER BY gender, Race, ee_occup_levels, headcountselect count(headcount + headcount) AS Transfaresfrom headcountdecwhere Period = 'March 2008' AND headcount IN ('TransferOut', 'TransferIn')ThanxThatoTue, 13 May 2008 05:21:15 GMTthatokhttp://www.sqlservercentral.com/Forums/Topic500000-145-1.aspxI am trying to combining multiple rows into one for display purpose only.Would appreciate any help. Thanx :)-------------------------------------------------------------Create table #test(PlaceCode int, Contract nvarchar(20), SetNo nvarchar(20), Failure1 nvarchar(50), Failure2 nvarchar(50), Failure3 nvarchar(50))Insert into #testselect 13234,'450a','20a','Specs Available',null,'low voltage'union allselect 13234,'450a','21a','Specs Available',null,nullunion allselect 12003,'100c','15a','Specs Available','Exceeds',null--select * from #test--Desired output:PlaceCode Contract SetNo 13234 450a 20A:Specs Available,low voltage 21a :Specs Available12003 100c 15a: Specs Available,ExceedsTue, 13 May 2008 14:59:09 GMTsapna_damodarahttp://www.sqlservercentral.com/Forums/Topic500160-145-1.aspxI have a table that stores all of our Server Patching information. We track the Server Name, Patch_Day (Mon, Tues, etc..) Patch_Start_Time, Patch_End_Time, etc..We use a dtsx job to go out and schedule all of our patching jobs through altiris, but we have a little bit of manual intervention in this process, since I don't know how to Correspond the Day listed in Patch_Day with a Date. I'd like to add a column for Patch_Date or even better use the date that shows in the Patch_Start_Time field...So for instance if Patch_Day = Friday then Patch_Date = (2nd Tuesday of month + 3)Is there a way to do this, and have the database roll over each month so that it's always using the current month's (2nd Tuesday + 3)?:hehe:Tue, 13 May 2008 21:56:45 GMTSQL-challengedhttp://www.sqlservercentral.com/Forums/Topic499691-145-1.aspxI have an transactional application that was built long before SQL server, the only way I know to retrieve data from this app is ODBC - System DNS. Currently, I can retrieve using SSIS and msQuery. I would like to skip SSIS and have everything inside Stored procedure. Can I retrieve data using ODBC-System DSN inside of Stored procedure?Tue, 13 May 2008 08:52:32 GMTedstalkehttp://www.sqlservercentral.com/Forums/Topic498283-145-1.aspxMy db structure is as follows:All invoices have a user account associated through an idAll tickets have an invoice associated to the ticketsI am wanting to find all users, whose first created invoice, contains ticket(s) to a specific event.Can someone tell me how to write this sort of query?Thanks guys!Fri, 09 May 2008 17:40:56 GMTjreedhttp://www.sqlservercentral.com/Forums/Topic499037-145-1.aspxHello, I am migrating data from our legacy database to SQL 2005 and I need to convert plain varchar data to rtf data. Has anyone ever accomplished this in puer t-sql. Thanks for your assistance.Mon, 12 May 2008 12:40:10 GMTrmartinhttp://www.sqlservercentral.com/Forums/Topic498782-145-1.aspxHello,I am triyng to update all the columns in all the tables if i find a certain value but I can't do it on my own. This is the best i can achieve as it is the first time with cursors. But it has an error: Must declare the table variable "@table_name".Is this query possible?There is another way?SET NOCOUNT ONDECLARE @table_name nvarchar(500), @column_name nvarchar(500) --big enough :) but it doesn't care the db is not on production serverDECLARE vend_cursor CURSOR FOR select TABLE_NAME, COLUMN_NAME from Information_schema.COLUMNS where TABLE_NAME in (SELECT TABLE_NAME FROM Information_schema.TABLES) OPEN vend_cursor FETCH NEXT FROM vend_cursor INTO @table_name, @column_name WHILE @@FETCH_STATUS <> -1 BEGIN UPDATE @table_name set @column_name = replace(@column_name,'','') ENDCLOSE vendor_cursorDEALLOCATE vendor_cursorTanks all in advanceMon, 12 May 2008 07:49:42 GMTSimone Foschihttp://www.sqlservercentral.com/Forums/Topic497791-145-1.aspxI have been working upon sql server 2005 and trying to insert binary data intodatabase but without successful and I just wonder if anybody could give mesome advice, thank you.1, I created a table with varbinary(max) field for storing binary data[code]CREATE TABLE [dbo].[img_item]( [id] [int] IDENTITY(1,1) NOT NULL, [img_data] [varbinary](max) NOT NULL, CONSTRAINT [PK_img_item] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFF[/code]2, I got a bulk of data and sprintf trying to insert into database[code]sprintf ((char*)binary_query_str, "INSERT INTO [dbo].[img_item] (id, img_data) VALUES ('%d', '%s')", newId, binary_escape_str);BYTE *binary_escape_str;[/code]The data I am trying to insert is:[quote] \0\b\b\t\t\b \r  $.\' \",#(7),01444\'9=82<.342踈0?郳0JFIF\0

\0\0
\0
\0\00*Intel(R) JPEG Library, version 1,5,4,36\0踈0CC
\t\t\t \r\r2!!22222222222222222222222222222222222222222222222222?0\0






\0\0\0\0\0\0\0\0
\b\t\0
\0\0
}
\0!1AQa\"q2亼b#B绷R佯$3br俓t\%&\'()*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxy\z儎厗噲墛挀敃枟槞殺￥ウЖ┆渤吹斗腹郝媚牌侨墒矣哉肿刭卺忏溴骁栝犟蝮趱鲼?\0








\0\0\0\0\0\0
\b\t\0
\0
w\0
!1AQaq\"2乗bB憽绷\t#3R?br裓n$4醆%?&\'()*56789:CDEFGHIJSTUVWXYZcdefghijstuvwxy\z們剠唶垑姃摂晼棙櫄ⅲぅΗī炒刀犯购旅呐魄壬室釉罩棕仝忏溴骁栝牝篝貊鼬繺0\b\0d\0d
\"\0

赲0 
\0\0?\0?x3路^瘐徘喆iL秠$捖&gc扞+扞颷燄倄?M\0?\0?x蘒鐔快k\0篭n\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0鵖沔摝柁:狈音鸎L嵹;XV\%-媸2B€3€9?鍀?磜鼣;?G\0??伏\t\0$竺\_鯸n?\0袺]p?眖4鹈勐\%a仁Q庝X損柔NH钖cG*磏2?0霢iM騖%\'元q鏽+“为9`*紖O呦也NI?歮s猴?ufD?缇*誤兊?…熔\z苉JSr芪?F薻\z啤i狵凳?aLa彬凖k殯?癧Ms6?qCpm潔?恥\0卥鴱\\恥鑬耱瓁志s岘OG坒\0衆rtR+?#;?磨樐]剻d1榥a??巍璡b\0憥!\\伏价WIS抗峏蒶┹N? 韝蝴O醁:y艝t6?觚??*閄Xwds驰屮屟]蟡u?伶鶓?嗥\tq摿?矿?]陹?涗秌?l姤U蕇瀪??萅Ж祟诚Z椓鈂rj?钮1N鸔?\0旧黬*徙nI弧┗?\0扑榈/h譿2嗓\0?惴壹谓鉢0频忴?鲴=y誶瓓>埔釂\_匎{剠釓N磄Ib?僜b\\
?剥]\t9螝坁h?+*?@n\z
蹼?詾€茳0?V陧［n~V騑?灑臣7)昻娙?敋0b(鸜F\t蒹2悾 ??SǒE??鉣y珇︽趎=?\0QHq焅z??娬眗著=?U?邦煃iI贔U曘s楍\b垡裎?輁_笸rrx7A笧鈟lwKp扭c+騅n$s?樸#‘穃_\_鵀Nq纤?鱁TU=v淲L[M劁g沰0E巅賛mt窻蜠tW暐\%芒?呦a炚.嫟鴇Zy狠ゥ溑稤弎\"噺\r唖怤}?汁3阇#箃益絺\b氄|?傎?尪8??A沧
}\_蘡c\t來?根?-憣 ?T\'\z彏N签墹?-A]Z锂恐タ\'?=宗-^驺腆鞮wy€嗐v9 \zt??:腛umh?厄I Id\0^O錦\啗釢/Z?骚亟緭f豬|苚窗=塡'邈蔽=\t鬕懚謈鑷鵙鴶涆╀GS羱2葁C驱\0J\'?疐s環$g兀樽蟐sX瓓>唣/鼡蟎r??\0E-S2Og琝%A寤J泵*3終/Rq笘儒€趡鮘\餦'鼡蟎r??\0E-f牮汷夆筴e戔歽$怽b?腟hbF<.7q摑i菤耦塷暕?3灤?)阋C{T謲嫊<笈G帵湽WV嘌?b褏??2?5煫X蒈i峔r澚唙U+\"筣紓y佴g揦慶簴?丫7T洙闔\'├陠眎v哱rZ鼏噇?;暩羘?譩k[渏椹&?xoR?2$笽-?<槤奔曺癋3亴謂结絉株?g槯偬穡?艕yo‰廽朦\%\i?on镤濧?栢酣莭\_[浴x/,RI<駆司9v`?熄?\0氕)‥Y猹苫谨锈-婈贘i?煣}奤y眿瓹g怚鐑撝簕?濫c縞`鱓6塷o╆(?觨s€XdG-幑\0c姚氁嬉?\_呷\b9\0I/?\\駛~O\_cNs媞N如鰊=O#\0!??\0ｇ?疍s|??甄u??\0?鹱齻?鬜?U厒FQ?妑虡\\卜伾;FP\0?鹱齻?鬜諒?絼w0\tH逼Py泿凸~?弻懭Cp鴷墯鱄鈶\%xZu br2欑庛垡瀄zUM畁舾$欻\'旑磉|d a粚
蟸氮?~?齥扨|蚗韰H颚賁方9Q茚T撿+E-\"N鳗駧斦浜擘<灼$:?}║!H,0\z翮帣fウ\\Z]GxCn^#T9蕦幗?綷"跪髶盭ch麳х\\?E璿,j\0
剎?汶盈|\z盐's]*塍,?v?皩:?縍?:螙驸鈌杋I\0?躙z齖t李;饈j绳ヽ???1霃诗KtmXf@c?侫S?詂?&4?iA筡b徧#+?岤帲鵥燊硋!\_.F?,樔U\0摓=?\z琝%骕C!]蠃P\0\0镓騸圍MЩGh蹎椰?l?熕穃z?搮?戞?\0????魘蹒U锜\0????魘蹒u躣}苦O鵟'?\0癠奪輺(鎵5?eq怗?呧O鵟'?\0癠奪?
\00牓\0(\0图?紹v氷蠯#c$籿靔缠CAA呌恦巅黭n奬0萛_枞r禶:HC醈醢S邨o癍??Q??0}櫩茣|)"lK?限秇>Zm〕裰沷n沑"M&=珤q廫z騔??﨡圐偅\0巡讖??煆?汛k.逴褮+x磴i!敱TP牅Hp=\\妄铪铪铪' [/quote]Or say, anybody knows something about how to excute statements that contain binary data, especially bulk binary data, into mssql ? Um....Fri, 09 May 2008 06:16:02 GMTRoger Wuhttp://www.sqlservercentral.com/Forums/Topic498646-145-1.aspxPlease help with this.Im trying to search a table that contains addresses for a specific suburbs and postal codes. The address table has 30,000,000 addresses in it. After applying basic criteria (ie:Eliminating invalids etc) and in an effort to increase speed I have created a physical temp table, reducing the records and leaving me with approx 12,000,000. I have also created a clustered index on the temp table, each addressline has been indexed as well.Using a table variable i insert each suburb and code in. I then use a coursor to search the temp address table using the table variable. This ran for more than 14 hours and still wasnt done.I need to get this running faster.See example below.--Load the physical temp table with addresses INSERT INTO Temp_Address (Addressline1, Addressline2, Addressline3) SELECT Addr1, Addr2, Addr3 FROM Database.dbo.AddressSource WHERE (Basic criteria used). -- Load table variable DECLARE @Tbl TABLE ( ID int identity (1,1) PRIMARY KEY, Area varchar (100), Code varchar (50)) INSERT INTO @Tbl (Area, Code) VALUES ('Area1', 'Code1') INSERT INTO @Tbl (Area, Code) VALUES ('Area2', 'Code2') INSERT INTO @Tbl (Area, Code) VALUES ('Area3', 'Code3')--Fetch addresses that have the area or code in it. DECLARE @Area varchar (100) DECLARE @Code varchar (50) DECLARE C CURSOR FOR SELECT Area, Code FROM @Tbl OPEN C FETCH NEXT FROM C INTO @Area, @Code WHILE @@Fetch_Status = 0 BEGIN INSERT INTO AD_Final (Address1, Address2, Address3) SELECT Addressline1, Addressline2, Addressline3 FROM Temp_Address WHERE (CHARINDEX(@Area,Addr1)>=1 OR CHARINDEX(@Area,Addr2)>=1 OR CHARINDEX(@Area,Addr3)>=1) FETCH NEXT FROM C INTO @Area, @Code END CLOSE C DEALLOCATE CThanx.Mon, 12 May 2008 04:35:00 GMTMark Kinnearhttp://www.sqlservercentral.com/Forums/Topic496843-145-1.aspxI have a question. Our DBA is pushing for a new structure for our databases. First I have to say that we have a lot of different databases and many tables with a lot of data that we have to join together for reports on a daily basis. With 3 different developers writing procedures for these databases you can imagine the jumbled mess we have. The idea our DBA has is to have one database that holds nothing but stored procedures that connect to the another database or (if necessary) databases for the data. A naming convention would be enabled so that by looking at the name of the stored procedure you would know what it does and potentially where it gets the data.I, being simply a database and web programmer think this sounds like a bad idea, but I have no real evidence to say that it is or isn't. I am more of the opinion that we should have the databases split out by client and write procedures, tables, views, etc that are specific to the client; only branching out if we need to. This way certainly gives you alot more databases to manage, but it also seems structurally more sound and more along the way I believe MS SQL was intended to be structured.I am NOT looking for opinions to back up my own opinion... I just want to be clear on that... I'm simply looking for information on one way or the other so I can help make the most intelligent decision. Our DBA is really a brilliant and innovative guy; and ultimately knows more about databases then I do but the way he does certain things just seems to be an organizational nightmare. Like having 1 stored procedure that does the insert, updates and deletes and just passing parameters to it to tell the stored procedure which action to perform. I've always learned that you should break each action (within reason) out into a seperate stored procedure. Again, I have no evidence saying one way is better then the other so I'm just trying to gleam some information from other experts like yourselves! Any help, opinions, etc. would be appreciated.Wed, 07 May 2008 22:10:42 GMTStouthearthttp://www.sqlservercentral.com/Forums/Topic498450-145-1.aspxDear sir,how to have a #cursor return from executing anther stored procedure.ThanksSun, 11 May 2008 05:48:44 GMTissam mansourhttp://www.sqlservercentral.com/Forums/Topic498387-145-1.aspxHI all...I am new to SQL.Please provide me some sql materials to learn quickly.Thanks in advance.Sat, 10 May 2008 11:08:41 GMTv-makathhttp://www.sqlservercentral.com/Forums/Topic498351-145-1.aspxHow to get, programatically, a list of the sql database views?Sat, 10 May 2008 06:36:40 GMTsamir_issahttp://www.sqlservercentral.com/Forums/Topic496705-145-1.aspxI know this may sound strange.... I'm not sure I believe it....but I tested it several timesCurrently I have a query where the natural order when run has Record A first. When I add a 'TOP 1' to the query, record B is first. I can't explain that, but for my purposes here it didn't matter.The other problem is that the TOP 1 changes the run time on the query from 2 seconds to well over 45 seconds.It doesn't sound like a lot but when run 250,000 times it makes a HUGE difference.Any ideas what else I can use other than TOP?FYI: I tried setting the return values to single variables and reversing the sort order so that the variables brought back the LAST VALUES, giving me a 'TOP 1' but it only shaved off maybe 10 seconds....WHY IS 'TOP' so slow!!!!!! (MSSQL 2K)Wed, 07 May 2008 14:47:59 GMTadrian_townsendhttp://www.sqlservercentral.com/Forums/Topic482035-145-1.aspxHello,I have 3 questions about SQL 2005 Reporting Services, can you help me ?(1)I can't hide the HTML Viewer toolbar. I have tried both the &rc:Toolbar=false and &rs:Toolbar=false, they could display the report but the Toolbar still existed.(2)I want the Report hide the Toolbar but still displays user entered input parameter values, is it possible ?(3)Can I only hide the Toolbar through URL access ? Eventually, I will use a ReportViewer control and use ASP to display a Report, but I found I couldn't enter any URL parameter in Visual Studio Report Designer. How can I, for example, on my ASP, display a SQL 2005 Report with input parameter values but no Toolbar ?Tue, 08 Apr 2008 22:29:02 GMTonlohttp://www.sqlservercentral.com/Forums/Topic497503-145-1.aspxI am trying to run following command but getting error likeProcedure or function 'Procedure Name' expects parameter '@status', which was not supplied.declare@SQLCmd Varchar(1000)set @SQLCmd='Exec xp_cmdshell '+char(39)+'Sqlcmd -S scdc330db -d IDX_OPS -Q "exec SCP_AppStatus_Notify " -v @status="Start" -v @app="Analysis Data Distribution" -v @task="Test"'+char(39)Exec(@SQLCmd)Any help will be appreciatedThu, 08 May 2008 16:42:17 GMTbalbirsinghsodhihttp://www.sqlservercentral.com/Forums/Topic496672-145-1.aspxHi All, I have a situation where I need to create a cursor based on condition. Here is my code:IF @bCustom = 0 SET @SQLCmd = 'SELECT * FROM TableA ELSE SET @SQLCmd = 'SELECT * FROM TableB DECLARE RandomUser_CURSOR CURSOR LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS FOR @SQLCmd FOR UPDATE OPEN RandomUser_CURSOR FETCH NEXT FROM RandomUser_CURSOR INTO @Col1, @Col2, @Col3 WHILE @@FETCH_STATUS = 0 BEGINIt gives me "Incorrect syntax near '@SQLCmd'"Any help will be greatly appreciated.Thanks.Wed, 07 May 2008 14:16:22 GMTramadesai108http://www.sqlservercentral.com/Forums/Topic493954-145-1.aspxHelloI have a text box on my web form where the user can enter multiple comma delimited search words. The value from this text box goes to my search stored procedure in the form of a search string.I am able to parse this comma delimited search string and get the words into a temp table.If there are 2 words in the temp table then this is the sql that I wantselect * from Itemswhere (description like word1 or tagnumber like word1 or user like word1)and (description like word2 or tagnumber like word2 or user like word2) description,tagnumber, user or the fields of the Items table.There could be any number of words in the search string.I tried doing thisselect items.* from itemsjoin #temptable ON (description like word or tagnumber like word or user like word)but this gives me a result equivalent to select * from Itemswhere (description like word1 or tagnumber like word1 or user like word1)OR (description like word2 or tagnumber like word2 or user like word2)and what I want is a result equivalent to select * from Itemswhere (description like word1 or tagnumber like word1 or user like word1)and (description like word2 or tagnumber like word2 or user like word2)Any ideas of how to get this done with any number of search words being matched against number of column/s.Any help regarding this is appreciated.Thank you.Thu, 01 May 2008 16:15:27 GMTkavitha challahttp://www.sqlservercentral.com/Forums/Topic493802-145-1.aspx/*CREATE procedure UL_transaction_calculationsas *//* Documentation *//* *//* Declare temporary variables */DECLARE @currentdate datetime/* Declare transaction table variables */DECLARE @datediff_week floatDECLARE @Row_Date datetime/* Set variables */SELECT @currentdate = getdate()/* Declare cursor */DECLARE UL_transaction_cursor CURSOR FORSELECT Row_DateFROM UL_Phone_Stats/* Open ul_transaction_cursor */OPEN ul_transaction_cursor /* First read for ul transaction */FETCH NEXT FROM ul_transaction_cursor INTO@Row_DateWHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS = 0) /* Record exists */ BEGIN select @datediff_week = datediff (week,@Row_Date,@currentdate) print @datediff_week END/* Write transaction count records INSERT INTO UL_TRANSACTION_CALCS (date_recorded, date_work_performed, approval_time, approval_count, close_time, close_count, inforce_time, inforce_count, lta_time, lta_count, mail_time, mail_count, newapp_time, newapp_count, part2_time, part2_count, rushes_time, rushes_count, associate_name, team_id, login_id, champ_production_time, champ_production_count, [Month], Follow_Up_time, Follow_Up_count) values (@currentdate, @last_activity_time, @approvals_CALC, @approvals, @close_CALC, @close, @inforce_CALC, @inforce, @LTA_CALC, @lta, @mail_CALC, @mail, @newapp_CALC, @newapp, @PartII_CALC, @PartII, @rushes_CALC, @rushes, @name_conv, @team_conv, @phone_logid_name, @champ_production_time, @champ_production_count, @date_month_format, @follow_up_CALC, @follow_up) */ /* Read next request record */FETCH NEXT FROM ul_transaction_cursor INTO@Row_DateEND /* End the WHILE loop */CLOSE ul_transaction_cursorDEALLOCATE ul_transaction_cursorGOThu, 01 May 2008 11:31:05 GMTstuart.dosshttp://www.sqlservercentral.com/Forums/Topic497307-145-1.aspxIs there a documented or undocumented system stored procedure that parses SQL code passed to it?Something similiar to what happens when you depress the "checkmark" toolbar button?Thu, 08 May 2008 11:47:15 GMTJason Selburg