MSGROUPS.NET | Post Question | Search | About | Groups | Contact | Register | Login



microsoft public excel misc (5666)

microsoft public outlook general (4816)

microsoft public excel programming (4185)

microsoft public word docmanagement (3505)

microsoft public excel worksheet functions (3180)

microsoft public access (3139)

microsoft public windowsxp general (2033)

microsoft public windows vista mail (1618)

microsoft public office misc (1571)

microsoft public windows vista general (1506)

microsoft public windows live mail desktop (1353)

microsoft public access queries (1318)

microsoft public powerpoint (1268)

microsoft public access formscoding (1206)

microsoft public mac office entourage (1193)

microsoft public dotnet languages csharp (1174)

microsoft public access forms (1153)

microsoft public internetexplorer general (1123)

microsoft public access modulesdaovba (1065)

microsoft public windows server sbs (1059)

microsoft public greatplains (1045)

microsoft public mac office word (1018)

microsoft public outlook calendaring (961)

microsoft public excel (928)

microsoft public sqlserver programming (897)

microsoft public project server (874)

microsoft public windowsupdate (812)

microsoft public windowsxp help and support (798)

microsoft public windowsmedia player (781)

microsoft public project (772)

microsoft public mac office excel (759)

microsoft public access reports (758)

microsoft public exchange admin (747)

microsoft public dotnet framework aspnet (735)

microsoft public sqlserver server (700)

microsoft public outlook (697)

microsoft public outlook contacts (678)

microsoft public dataprotectionmanager (659)

microsoft public word vba general (635)

microsoft public windows server general (628)

microsoft public windows server active directory (586)

microsoft public outlook installation (580)

microsoft public word newusers (559)

microsoft public publisher (517)

microsoft public excel newusers (507)

microsoft public excel charting (499)

microsoft public word pagelayout (494)

microsoft public dotnet languages vb (464)

microsoft public vb general discussion (457)

microsoft public development device drivers (444)

microsoft public office setup (430)

microsoft public word application errors (428)

microsoft public sharepoint general (425)

microsoft public windowsce platbuilder (408)

microsoft public access gettingstarted (407)

microsoft public windows powershell (399)

microsoft public frontpage client (391)

microsoft public onenote (391)

microsoft public sqlserver reportingsvcs (382)

microsoft public outlookexpress general (369)

microsoft public access tablesdbdesign (352)

microsoft public pos (352)

microsoft public vc mfc (346)

microsoft public outlook program vba (341)

microsoft public windows live messenger (324)

microsoft public money (309)

microsoft public windowsxp hardware (299)

microsoft public windows 64bit general (295)

microsoft public scripting vbscript (289)

microsoft public windowsxp basics (236)

microsoft public windows mediacenter (230)

microsoft public dotnet general (225)

microsoft public mac office (198)

microsoft public nntp test (195)

microsoft public windows inetexplorer ie6 outlookexpress (191)

microsoft public windows vista music pictures video (153)

microsoft public win98 gen discussion (153)

microsoft public sharepoint setup and administration (139)

microsoft public windows vista hardware devices (130)

microsoft public dotnet framework (104)

microsoft public windows vista performance maintenance (97)

microsoft public visio (89)

microsoft public access 3rdpartyusrgrp (73)

microsoft public dotnet xml (59)

microsoft public access conversion (56)

microsoft public pt windowsxp (36)

microsoft public windows live photogallery (32)

microsoft public access developers toolkitode (31)

microsoft public dotnet faqs (15)

microsoft public dotnet framework aspnet datagridcontrol (10)


microsoft.public.sqlserver.programming Post New
Items(896) /9 Next >> Last >|
Subject Posted Replies From
Strange decimal rounding

Hi all I'm having a strange problem of rounding calculations with decimal variables in SQL Server (2008). It seems to me that it do not return the correct value when it performs a division to 1000000 (one million). I have this test script, that anyone can try in Management Studio: declare @input table (Consumi decimal(28,10), PCI decimal(28,10)) Insert into @input (Consumi,PCI) VALUES(3.767, 42621.0000000000) select * From @input declare @test table (Consumi decimal(28,10), PCI decimal(28,10), Contenuto decimal(38,10)) Insert into @test select Consumi, PCI, Consumi*PCI/1

3/11/2010 8:44:01 AM 0 =?Utf-8?B?THVpZ2k=?= <Lu...@discussions.microsoft.com>



GUIDs can be ordered, but can't get MIN or MAX?

SQL 2005 and 2008: When I learned that MIN and MAX would not work on UniqueIdentifier data types, it was intuitively obvious to me that you wouldn't be able to ORDER BY UniqueIdentifiers either. (Either they can be compared for "larger" and "smaller", or they can't.) I was wrong, as we all know. It was probably six months after SQL 2005 came out before I realized that you can ORDER BY a GUID; of course, it's one canonical way to select one or more random records from a table: Select Top <n> ColumnName From Table Order By Newid() Occasionally, in a RAISERROR message, I w

3/11/2010 3:38:39 AM 1 DWalker07 <n...@none.com>
Ranking question

Hello - Each individual has many records that are ranked as follows: Jim 1 Jim 2 Jim 3 George 1 George 2 Bob 1 Bob 2 Bob 3 Bob 4 I need a list returned with each individual's highest ranking e.g. Jim 3 George 2 Bob 4 How do I get to this result? -- Sheldon

3/11/2010 3:01:01 AM 1 =?Utf-8?B?U2hlbGRvbg==?= <Shel...@discussions.microsoft.com>
How to avoid table locks when deleting\ inserting huge records

We have a scenario where a complex calculation happens within a stored procedure which produces around 1 Lac records at the end of calculation. These 1 Lac records needs to be stored to a table. Everytime, this is calculated for the same parameter, it deletes previous result and inserts again. Since Delete\ Insert involves huge number of records, Lock Escalation happens and 'Table Lock' is applied. As the concurrent users increase, the response time from the Stored Proc increases drastically. Please suggest how else this can be done to bring down the response time. Thanks,

3/11/2010 2:35:02 AM 0 =?Utf-8?B?QXVndXN0aW4gUHJhc2FubmE=?= <AugustinPrasa...@discussions.microsoft.com>
Run as a transaction?

Hi Periodically I upload a table "tblLanguageValues" from my laptop to our hosted SQL 2005 server in Florida. It has 65,000 rows so takes about 30 minutes. During that time our website goes down because each page uses that table to fetch text items. Once the upload is complete all is well with it. I create a script locally using the "Database Publishing Wizard" to script that one table only and save it to a file "1.SQL". Then I open the remote database via SQL Server Management Studio Express and run the script. Can I do the whole thing as a transaction so the website rem

3/11/2010 1:54:40 AM 1 "Mark B" <none...@none.com>
Full Outer Join performance

I am looking at a procedure that does a FULL OUTER JOIN on 2 views. SELECT ... FROM vw1 WS1 FULL OUTER JOIN vw2 as WS2 ON WS1.WorkSpaceID = WS2.WorkspaceID I am running this without the Where clause and it runs about 4 seconds either way. Both views have 4 or 5 joins in them. But each view takes less than a second to run by themselves. If I change the FULL OUTER JOIN to JOIN, it runs in less than a second. So obviously the FULL OUTER JOIN is the problem. But what can you do to speed it up or can you? I had thought maybe it was because there are no indexes on WS1.Wor

3/11/2010 1:07:21 AM 4 "tshad" <...@dslextreme.com>
optimizer not executing my SP

I am getting "invalid object name '#Workspace_List'" in display estimated execution plan which works fine when I actually run it. One explanation was that the temporary table doesn't yet exist. But I have other stored procedures that have temporary tables that work fine. The procedure is doing a SELECT/INTO the temporary table. Why would I get the error and is there a way fix it so I can look at my plan to see what needs to be optimized? Thanks, Tom

3/11/2010 12:07:16 AM 2 "tshad" <...@dslextreme.com>
DBTYP.NET Studio 2010 - Database Comparison Suite Released

DBTYP.NET Studio 2010 - Database Comparison Suite Released Vienna, Austria - March 8, 2010 Today, BYPsoft announced the availability of DBTYP.NET Studio 2010, the newest version of the cross-database comparison tool that compares SQL Server, MySQL and Oracle databases (schema and data). DBTYP.NET Studio 2010 is available for download from http://www.bypsoft.com. With its rich support for schema and data cross-database comparison, DBTYP.NET Studio takes the mystery out of databases, making their comparison practical and easy for programmers and database administrators everywh

3/10/2010 7:18:38 PM 0 <Tommy>
Infinite Recursive CTE

I have a situation where you can have an infinite recusion. There is an issue where editing a page could cause infinite recursion. This would happen if a distribution folder were put into itself. It would also happen if you put folder "A" into folder "B" where folder "B" is in any of the folders contained in any of the folders found in folder "A". A little convoluted sentence but that is the problem. A is in B and B is in C If you put A into C then C will eventually point back to itself and you the recursion will continue on YOu have to have a way to say that you have alr

3/10/2010 6:12:21 PM 6 "tshad" <...@dslextreme.com>
SP parameter optional

I have a stored procedure that I want to make the last parameter optional and default to NULL if not sent. Below is the procedure declaration and I want to have the @Week1Ending date as optional and null. How do I do this? Thanks. ALTER PROCEDURE [dbo].[mc_insTimesheetsTemp] @WorkerLinkID int, @ClientLinkID int, @PriorPeriodEnding date, @PeriodEnding date, @Week1Ending date -- David

3/10/2010 5:09:01 PM 1 =?Utf-8?B?RGF2aWRD?= <dlch...@lifetimeinc.com>
SQL connections

Hi All, I have a requirement to be monitoring the SQL connnection to one server, and before to do a process to be loggin into a table each log in and each log out, I would like to ask you if you know if exists something in SQL that could be saving all the conections for a range of time. I don't want to use the SQL profiler because it depends if the DBA turn it or not. the solution should be something that can be installed and/or reader directly from the server. Thanks Carlos A. Lopez

3/10/2010 4:17:01 PM 1 =?Utf-8?B?Q2FybG9zIEEu?= <Carl...@discussions.microsoft.com>
CTE parameters

When do you need to use a column list with a CTE. This is apparently optional. I just used one where I didn't use the list but I am not sure why when I would have to. Thanks, Tom

3/10/2010 1:43:50 AM 2 "tshad" <...@dslextreme.com>
convert string to datetime

Hi all, I used this to append to a some backups. DECLARE @DATE VARCHAR(20) = CONVERT(VARCHAR(10),CURRENT_TIMESTAMP,112) + '-' + RIGHT('00' + CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS VARCHAR(10)),2) + RIGHT('00' + CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP)AS VARCHAR(10)),2) + RIGHT('00' + CAST(DATEPART(SECOND,CURRENT_TIMESTAMP)AS VARCHAR(10)),2) How can I convert this back to datetime? After pasing out of the filename and removing the dash this is what is left: 20100303075951 thanks gv

3/9/2010 9:56:24 PM 6 "gv" <viator.ge...@gmail.com>
How to update the Year in a date field?

Hi All, ----------------------------SQL SERVER 2008 Database----- The AP clerks forgot to change the year from 2009 to 2010, so they did lots of data entry with invoice dates instead of 2010, they added 2009 and the date field looks like this "2009-01-16 00:00:00.000", is there a way to replace 2009 with 2010. Please help me and thanks in advance, Ramesh

3/9/2010 9:19:10 PM 5 DVR <dvramesh...@gmail.com>
Optimizing query

I have a query where the execution plan is showing a table scan with 32% cost. I have tried to put indexes on pretty much all the fields and still get the same result. The predicate from the Query Analysers execution plan for this table is: [dbo].[REVIEWER].[EnvironmentID]=[@EnvironmentID] AND ([@NameChoice] IS NULL OR CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')<>'' THEN ([dbo].[REVIEWER].[LastName]+', ')+[dbo].[REVIEWER].[FirstName] ELSE CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')='' THEN [dbo].[REVIEWER].[LastName] ELSE NULL END END like [@NameChoice]+'%')

3/9/2010 7:06:36 PM 4 "tshad" <...@dslextreme.com>
Supporting Oracle as well as SQL Server

Hi all, I have just been asked if we can support Oracle as well as SQL Server. And what the implications are. A bit about our setup. Basically we deal in data - there is an app, but it is secondary to the data. Lots of data migrations etc. We have a complicated database schema with loads of constraints and loads of sprocs that are not written in neutral SQL. Also we use CONTEXT_INFO for auditing the username. Firstly - are there automated tools for porting to Oracle from SQL Server and rewriting all the nonstandard TSQL? And are they any good? Secondly, I'm thinking this is a b

3/9/2010 6:36:49 PM 4 JimLad <jamesdbi...@yahoo.co.uk>
order by case value

If I have the following: SELECT UserID as RID ,EnvironmentID as EnvID ,@authorType as RType ,'A' as Type ,'Author' as FullType ,Case When (isnull(FirstName, '') <> '' AND isnull(LastName, '') <> '') then LastName + ', ' + FirstName When (isnull(FirstName, '') <> '' AND isnull(LastName, '') = '') then FirstName When isnull(FirstName, '') = '' then LastName End as RName ,Email as EmailAddress FROM User WHERE EnvironmentID = @EnvironmentID Now I want to only have rows that have values that start with a letter. Do I have to do this with a derived table

3/9/2010 6:04:31 PM 2 "tshad" <...@dslextreme.com>
Full outer join on 3 tables. How to?

Hi, I have 3 tables: 1) table a: id int colA int 2) table b: id int colB int 3) table c: id int colC int If I'm given an id (e.g. @id = 2), how do I query the three tables for rows that match @id? I need a full outer join. For example, if table c is the only table containing a row matching @id the return values (colA, colB, colC) sould be: NULL, NULL, n Thanks.

3/9/2010 2:38:15 PM 3 "Robert" <...@robert.com>
Option(RECOMPILE)

I have a UDF say Which is caluculating salary of an employee by using Cusror. Now i m using that UDF in a View in which i m passing it Id for a value in view. That's working fine if no of records are say around 1000 but if No are Increasing then View is working very slow Because foreach row in view that function is executing and Output is produced I have Searched for solution I Find an way to use Option(Recompile) but don't know how to use with UDF. Any Other Suggestion to Improve performance of view will bw welcomed Thanx

3/9/2010 9:30:31 AM 3 sam <sumesh.jangra2...@gmail.com>
Query Decimal Date field for Yesterday's Records

I have a table in which the records contain a Date (DDate) that is stored as a Decimal value (IE. 30710). I need to SELECT from that table only the records WHERE DDate is equal to yesterday (Current Date - 1) and I am having a heck of a time figuring out how to do this. Any help would be appreciated! Thanks!

3/9/2010 8:37:42 AM 6 "jtibbs" <u58...@uwe>
SQL2000: Permissions Stored Procedures

I have some stored procedures, which I've granted execute permissions to a user for. When the user hits the stored procedures from an outside program, it errors out stating that the permissions are required for the underlying tables. Both the tables & the stored procedure are owned by dbo. I only want to grant permissions on the procedures, not the individual tables. Thanks in advance for any help. It would be greatly appreciated. bh

3/9/2010 3:37:58 AM 1 "bh" <somb...@somewhere.com>
Sequence

I have 2 columns in table Orders. OrderId which is int InventoryId which is int. I would like to write a query to update a column called sequence so it assigns an automatic number based on OrderId and InventoryId. For same Order Id and different InventoryId the sequence increases. When we get a new OrderId it restarts from 1. OrderId InventoryId Sequence 100 505 1 100 508 2 100 907 3 100 908 4 101 400 1 101 1008

3/8/2010 9:16:26 PM 3 Mary Phelps <icanhel...@gmail.com>
Convert Positive Value to Negative Value

How can I get the "Amount" to be a negative number when the "Code" = "G6"? Maybe I could multiply by -1 and use the CASE statement, but I don't know how. Table Name = EncounterData Amount Code $15.00 G2 $13.00 G2 $18.00 G6 $16.00 G2 $25.00 G6 $30.00 G6 -- Tyro from Missouri

3/8/2010 9:11:01 PM 5 =?Utf-8?B?VHlybw==?= <T...@discussions.microsoft.com>
Delete All Jobs Except 2?

Using something that would work in 2005 and 2008, I am trying to figure out how to delete all jobs except for two. Some runaway scripts have added 40+ crazy-named jobs, and I need to get rid of them all, but keep just two. Can anyone point me to the right queries to capture all jobs into a table var or something better? Thanks.

3/8/2010 8:46:02 PM 2 SnapDive <SnapD...@community.nospam>
Subquery for column names

I have a database table with a large amount of analytical data in it - all floats but for a timestamp column. Instead of returning the entire set, I want to be able to return specific columns based on a "system" number that is specified as a parameter (in this case it is written in as "S02" for testing purposes) and compare the provided system number with the column names within the table - which is what the subquery currently does correctly. Aside from creating a stored procedure to handle the result and reformat it into a string to then use as a parameter of a second query, is th

3/8/2010 7:37:17 PM 4 jtertin <jter...@gmail.com>
AND optimization in WHERE clause

I’m having an issue with SQL Server 2008 not optimizing AND clauses in my where statement. Let’s say I have a single table: TABLE T ( A INTEGER, B INTEGER, C INTEGER ) Now, I can search this tables for values on either A, B or C, so I generate a query like: SELECT * FROM T WHERE (@UseA = 1 AND @UseB = 0 AND @UseC = 0 AND A >= @MinValue AND A < @MaxValue) OR (@UseA = 0 AND @UseB = 1 AND @UseC = 0 AND B >= @MinValue AND B < @MaxValue) OR (@UseA = 0 AND @UseB = 0 AND @UseC = 1 AND C >= @MinValue AND C < @MaxValue)

3/8/2010 6:09:02 PM 12 =?Utf-8?B?VGVycnkgU3RleWFlcnQ=?= <TerrySteya...@discussions.microsoft.com>
Query

I would like to get sum of the price for all items from table ProductVariant CREATE TABLE #Kits ( ShoppingCartRecId int, InventoryVariantId int, SumTotal money ) INSERT #Kits (ShoppingCartRecId,InventoryVariantId) select ShoppingCartRecId,InventoryVariantId from Orders_KitCart update x set x.SumTotal=y.max([Price]) from #Kits as x inner join ProductVariant as y on x.InventoryVariantId=y.skusuffix I get an error Cannot find either column "y" or the user-defined function or aggregate "y.sum", or the name is ambiguous.

3/8/2010 5:36:41 PM 3 Mary Phelps <icanhel...@gmail.com>
getting an execution plan in C#

Are there any methods that can expose a stored procedure's execution plan using C# / SQL Server 2008? Paul

3/8/2010 3:41:42 PM 3 "PJ6" <no...@nowhere.net>
SELECT SUBSTRING

I want to select a substring from a text this way: blablaNAME:myNameADDRESS:myAddressblabla What I want to get is all the text between "NAME:" and "ADDRESS:" I'm trying: SELECT SUBSTRING(myField, PATINDEX ('%NAME:%', testo) + 5, xxxxxx) Of course I can't know how long the substring is, so I don't know what to put over the xxxxx Anybody helps me? Thanks. Luciano

3/8/2010 3:09:49 PM 2 "Luciano \(DOC\)" <luciano...@luciano.doc>
SSMS Solutions

is there a way to set the solution to remain collapsed when it opens. I have some solutions that have many projects. I've tried saving everything with everything collapsed. When I re-open it, everything is expanded. Manually collapsing everything each time seems crazy. Is there a way to combat this? thanks in advance, ...bob

3/8/2010 1:40:29 PM 4 "Bob McClellan" <bobmcc...@gmail.com>
Xml shredding performance

Hi, we are having some performance issues regarding xml shredding. At this point we are extracting data from xmls from nearly 60 different companies - and therefore 60 different xml structures. The total amount of xml is about 350MB and we are trying to extract the data as fast as possible. Our current system extracts, transforms and loads the data in about five minutes. We would however like to do this in about one minute to be pleased. We use the "nodes/cross apply"-technique to shred the xmls into our internal format. This is how we shred the data. ----------------

3/8/2010 9:37:12 AM 12 Johnny Persson <...@a.a>
Running diffrence

I have Table employee Having Column Name EName,Esal and Empno as Primary key ENAME SAL RUNNING_DIFF ---------- ---------- ------------ MILLER 1300 1300 CLARK 2450 -1150 KING 5000 -6150 I would like to caluculate it like this

3/8/2010 6:52:42 AM 2 sam <sumesh.jangra2...@gmail.com>
Table Insert Strategy in SQL Server 2005

Nice people in this forum showed me excellent trick how to perform delete in small chunks. I was wondering if there is a trick to perfrom the same but for inserts. I have a single query that inserts rows from select statement. The select statement produces tens of millions of rows. Thanks in advance

3/8/2010 3:00:04 AM 4 "RG" <nob...@nowhere.com>
Table Delete Strategy on SQL Server 2005

I have a table that has over 300 million rows. Each row is quite hefty in size. I need to do selective delete of quiete a few rows, in millions. Could somebody suggest an approach to best handle such a scenario. The approach can include settings or adding db objects that would speed up the handling of this. Thanks in advance

3/7/2010 3:26:49 PM 12 "RG" <nob...@nowhere.com>
Transactions in SQL Server 2005

A stored procedure loops through a cursor. For each entry it invokes another stored procedure. That other stored procedure updates table other than was defined in the cursor. After so many updates, the other stored procedure commits work. Would the commit in other stored procedure affect the cursor? Thanks in advance

3/7/2010 6:39:51 AM 4 "RG" <nob...@nowhere.com>
PayPeriod table

I am working on a payroll system. One of the task is to create a payperiod table to capture the previous payroll currently store in the Excel file from year 2006 to present. The payperiod is bi-weekly and I want to populate this table with the pervios payperiod and up to year 2050. The columns in this table will have the following fields: ID Calendar Year - which is determined by the PayPeriod End Date PayPeriod Start Date PayPeriod End Date PayPeriod - PayPeriod Start Date - PayPeriod End Date How to write a sql query to populate this table? PayPeriod always start on Sund

3/6/2010 6:37:36 AM 5 "Paul" <paul_...@shaw.ca>
index needed for join and where clause

I have an index related question and let me set up the tables and indexes as follows. Table Test Field A int primary cluster key Field B int Field C varchar(50) Field D int Field Z int Table Test2 E int Primary clustered index F int Index ix_test_a on Test(B, D) index ix_test_b on Test(D, C,Z, B) Index ix_test2 on Test2(F) Will the above index, ix_test_a on table Test be used for the following three statements? Do I need to create another index ix_text_c on Test(D, B)? or ix_text_b will cover this order specific situation already. Let assume all the indexes

3/5/2010 10:27:32 PM 2 "OceanDeep via SQLMonster.com" <u46...@uwe>
sql server formats

The following two questions pertain to format questions I have about sql server 2008: 1. When I export some of the sql server 2008 tables to either word or PDF, and the table goes to two or more pages, there is a complete empty page break. Basically I will see the table, followed by a blank page, the table is continued to another page, there is another empty page, the table is continued to another page, there is another empty page. Thus can you tell me how to make certain there are not all the empty pages I just listed above? 2. I am thinking of creating 1 table

3/5/2010 7:26:01 PM 1 =?Utf-8?B?amF6enlkYW5jZQ==?= <jazzyda...@discussions.microsoft.com>
sql server displays

I would like to know what I can use in sql server report 2008 to display several sentences or paragraphs prior to where I have a table generated. Each table in each report will display the data. To display the paragraph(s) and/or sentence(s) before the table containing data what toolbox item would I use? Should I use a matrix, rectangle, textbox, and or another table?

3/5/2010 6:21:01 PM 1 =?Utf-8?B?amF6enlkYW5jZQ==?= <jazzyda...@discussions.microsoft.com>
Retrieve Records if a Value is Null/Empty in SQl Server 2005

select Field1, Field2, Field3, Field4, Field5, Field6 from Mytable I need to retrieve records if any of the listed filed value is empty/null.

3/5/2010 4:52:01 PM 4 =?Utf-8?B?Umljaw==?= <R...@discussions.microsoft.com>
Index include primiary key

I have the following Table structure Table Test Field A int primary cluster key Field B int Field C varchar(50) Field D int Say I do a select statement as follows: Select B, C from Test where D = 1000 To improve this query, I create an index for field D. My question is, is there any benefit to put Field A in the 'Include columns'? Would it be redundent and unnecessary as the root key of the index already has the RID to point to the actual data location? Or this will make the search even quicker since the actual primary key is provided within the index. od -- Mess

3/5/2010 4:02:46 PM 21 "OceanDeep via SQLMonster.com" <u46...@uwe>
Export table with Cursor

Hi there, I'm using SQL Server 2008 on Windows Vista x64 In Northwind database I wrote DECLARE @List varchar(50) DECLARE c1 CURSOR READ_ONLY FOR select Table_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' OPEN c1 FETCH NEXT FROM c1 INTO @List WHILE @@FETCH_STATUS = 0 BEGIN Print @List --DECLARE @sql VARCHAR(1000) -- Select @sql = 'bcp "northwind..' + @List + ' ' +' queryout "C:\mobiledoc\"'+@List+ '.txt'' -c -t,-T -S.\' + @@Servername -- Exec master..xp_cmdshell @sql FETCH NEXT FROM c1 INTO @List END CLOSE c1 DEALLOCATE c1 When

3/5/2010 10:53:03 AM 4 "odeddror" <odedd...@cox.net>
Q;re Descriptive stats

Hi Suppose I have the following data in a column (if is sample data, but the DB I am working with has some specific values to indicate other data properties - I fully accept that there may well be better ways to indicate this). Elapsed Days 1 2 3 4 5 6 7 8 NULL -99999 -88888 What SQL can I write so that I can eliminate the -99999 and -88888 for the purposes of min/max/avg/sum yet include them for the purposes of count So my stats would be count=10 min=1 max=8 sum=36 avg=3.6 Ideally I need to be able to do this in a single SQL statement. I have had a

3/5/2010 9:40:36 AM 2 Simon Woods <simonjwo...@hotmail.com>
Opposite to INNER JOIN

There is a simple task - to return the records from table1 where the key table1.key1 doesn't exist in the table2. The first idea was to write it as: SELECT * FROM table1 WHERE NOT table1.key1 IN (SELECT table2.key2 FROM table2) Simple, but it's way too inefficient for sure. Is there some trick that I'm missing, something like an opposite to INNER JOIN? I doubt it exists, but who knows... There were two other ideas - to use a status column and change the status when the record was sent to the table2, using this column in the query, but for some reason my boss doesn'tlike th

3/5/2010 5:47:41 AM 6 "Just D." <...@spam.please>
Null in select list

I have never seen this format before: INSERT INTO #temp SELECT name, null [Title] FROM persons Is that the same as: INSERT INTO #temp SELECT name, CASE WHEN Title IS NULL THEN NULL ELSE Title END FROM persons Thanks, Tom

3/5/2010 2:03:00 AM 7 "tshad" <...@dslextreme.com>
CTE with multiple anchors

I have a recursive function that is pretty complicated but there are many anchors. All the examples I see have only one anchor such as this from: http://msdn.microsoft.com/en-us/library/ms186243.aspx ********************************** USE AdventureWorks; GO WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) AS ( -- Anchor member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level FROM HumanResources.Employee AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.EmployeeI

3/5/2010 1:39:46 AM 8 "tshad" <...@dslextreme.com>
Need help with a recursive query

I have the following query that takes a base member of it & traces its parentage that excludes the top level of the table (PLOB). select z.id, parent = (select top 1 y.id from mbrProduct y where y.id = z.parenth1), grandparent = (select top 1 a.id from mbrProduct a where a.id = (select top 1 y.parenth1 from mbrProduct y where y.id = z.parenth1)), grgrandparent = (select top 1 b.id from mbrProduct b where b.id = (select top 1 a.parenth1 from mbrProduct a where a.id = (select top 1 y.parenth1 from mbrProduct y where y.id = z.parenth1))and b.parenth1 not in ('PLOB')) , grgrgrand

3/4/2010 10:33:39 PM 9 Blasting Cap <goo...@christian.net>
Tivoli Storage Manager 5.4

Officially, TSM Server 5.4 does not support Windows Server 2008 and SQL Server 2008 ... Does anyone know if unofficially it works or not ?

3/4/2010 9:42:32 PM 0 "John Grandy" <john.arthur.grandy-at-gmail.com>
Random assignment

I have three tables CREATE TABLE [dbo].[STAFF]( [StaffKEY] [int] NOT NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[Clients]( [ClientKEY] [int] not NULL, [ClientName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[STAFF_Collections]( [CLIENTKEY] [int] NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Staffkey] [int] NULL ) ON [PRIMARY] Sample data Staff: 303, 'Paul Newman' 405, 'Jane Fonda' 605,'Lola Falana' 789

3/4/2010 7:19:00 PM 3 amj1020 <angelicre...@hotmail.com>
Result set does not contain the current month

Hi All, The SQL below does not contain the YearMonth 2010/03 like I expected. Any ideas? SELECT @minWeekEnding = '3/9/2008' SELECT @maxWeekEnding = '3/7/2010' ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1), N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y), N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y), N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y), Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N4) SELECT CONVERT(CHAR(6), DATEADD(MONTH, (n-1), @minWeekEnding), 112) AS YearMonth , (ROW_NUMBER() OVER(ORDER BY n DESC))-1 AS MonthsAgo FROM Nums WHERE DATEADD(MONTH, (n-1), @minW

3/4/2010 3:41:01 PM 4 =?Utf-8?B?cm9kY2hhcg==?= <rodc...@discussions.microsoft.com>
trying to return data with only the latest date

when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counterid wher

3/4/2010 3:24:49 PM 2 Alen Teplitsky <alent1...@gmail.com>
SQL SELECT Help! (supersets)

Hi. I have an Orders database (orders, products, orderlines tables). What I want to write is, a select statement that, when there is an incoming order (containing let's say 4 products), it shows all other orders that contain at least those products the incoming order contains, or more. Which should be the proper table indexing choice, for the above scenario? TIA Iordanis

3/4/2010 3:23:01 PM 5 =?Utf-8?B?U2F2dm91bGlkaXMgSW9yZGFuaXM=?= <SavvoulidisIorda...@discussions.microsoft.com>
Memory Table vs. CTE

I had posted a week or so ago about a performance issue with Null-able variables being passed into stored procedures and the performance gains by setting those parameters to local variables declared in the stored procedure. I followed that advice and all was well. How ever, the stored procedure started to time out the again, running the stored proc on my local SQL server would take 3:45 to return 5 rows. Something was wrong. The stored procedure was using 4 CTE tables, one of those tables being referenced in 5 different locations through-out the stored proc. The only change I made t

3/4/2010 2:51:36 PM 7 Brocja01 <brocj...@gmail.com>
Change management - views that select data from tables in differen

Thanks in advance. Some co-workers use a third party analysis tool built upon a complex, proprietary 2005 db. To assist their efforts to validate their inputs to this tool, I long ago created a some views in another db on the same server. The views join some 18 or more tables from the proprietary db. All the joined table names are fully qualified with the prefix 'dbname.dbo.' Also created were functions and sprocs that also reference the proprietary db. These views, sprocs, & functions are used by both an Excel application and by a DotNet app, i.e., they're referenced fro

3/4/2010 2:10:05 PM 4 =?Utf-8?B?RHVrZSBDYXJleQ==?= <DukeCa...@discussions.microsoft.com>
non-equal DRI: a possible new sql feature

(contrived example coming) if i sell christmas trees and hire temp workers each year and i also reward the top sellers how can i enforce sales data within the timeframe the employee works for me? The same guy could work for me each year. I don't want to be concerned having to figure out sales from an Emp that were outside his hiredates. the DB should do that for me. CREATE TABLE Employees (Fname VARCHAR(20), LName VARCHAR(20)); CREATE TABLE EmployeeHireDates( Fname VARCHAR(20), LName VARCHAR(20), HireDate DATETIME, TerminationDate DATETIME); (insert obvious PK and FK in the abbr

3/4/2010 1:13:06 PM 2 Dan Holmes <dan.hol...@routematch.com>
Multiple Active Result Setc (MARS)

Hi, Using SQL Server 2005, .NET 3.5 A simple task; 1. Obtain a result set based on criteria 2. Foreach row in the result set 3. Check the row against another database 4. If 'flagged' Delete the row 5. Next row In .NET, the enumeration of the result set could use an SQLDataReader, the deletion of the row could use SQLCommand.ExecuteNonQuery(). The problem is, when you perform the row deletion, you are potentially affecting the DataReader enumeration. If you try to run this it fails saying "There is already an open DataReader associated with this Command which must

3/4/2010 12:21:07 PM 1 "Gerry Hickman" <gerry666...@newsgroup.nospam>
T-SQL to retrieve language pairs with a fallback to a default language when missing

I'm having difficulty trying to write a stored procedure that retrieves records from a table that stores culture codes. Here is the code used to create the table and store some records for test purposes... CREATE TABLE [dbo].[CultureCodes] ( [LanguageID] [nvarchar](15) NOT NULL, [CultureCode] [nvarchar](15) NOT NULL, [Language] [nvarchar](35) NOT NULL ) Insert Into CultureCodes (LanguageID, CultureCode, Language) Values ('en-us', 'en-us', 'English - USA') Insert Into CultureCodes (LanguageID, CultureCode, Language) Values ('en-ca', 'en-us', 'English - Canada') Insert In

3/4/2010 9:52:37 AM 10 Polaris431 <johannbl...@gmail.com>
CTE question

I just started using CTEs and I thought that it was really just a restatement of a derived table: with MyCTE(x) as ( select x='hello' ) select x from MyCTE Could be rewritten as a derived table: select x from ( select x = 'hello' ) a But then looking at this one: with MyCTE(x) as ( select top 10 x = id from sysobjects ) select x, maxx = (select max(x) from MyCTE), pct = 100.0 * x / (select sum(x) from MyCTE) from MyCTE I can't restate like so: select x, maxx = (select max(x) from MyCTE), pct = 100.0 * x / (select sum(x) from MyCTE)

3/4/2010 6:02:22 AM 4 "tshad" <...@dslextreme.com>
CLR UDF, how to tell selectivity to the optimizer?

We use some CLR UDFs to process BLOBs. In many cases, the optimizer seems to choose a bad plan because it guesses that our UDF will limit the results much more than it does. For example, when we have a WHERE condition such as OurUDF( Data ) = 1, the optimizer apparently guesses that the number of rows will drop to one third (33%). In reality, the number of rows remains almost the same because the UDF will return 1 for 95% to 100% of rows. Is it possible to tell the optimizer that this UDF has poor selectivity and that it is thus better to evaluate almost any other restricting co

3/4/2010 5:44:01 AM 1 =?Utf-8?B?QW50dGkgTml2YWxh?= <AnttiNiv...@discussions.microsoft.com>
SQLjobvis 3.0 (free) - Visualize your SQL agent job histories...

SQLsoft is pleased to announce the official release of version 3 of SQLjobvis - a totally free tool for SQL Server 2005 and above. SQLjobvis displays your SQL agent job run history in a timeline, making it easy to identify long-running, failed or clashing jobs. Check it out today at http://www.sqlsoft.co.uk/sqljobvis.php. Regards, Will Alber (SQLsoft). __________ Information from ESET NOD32 Antivirus, version of virus signature database 4913 (20100303) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com

3/4/2010 1:35:01 AM 0 "Will Alber" <j...@crazy-pug.co.uk>
TSQL Transpose

Hi All! I'm trying to create a query that does a real transpose similar to Excel function - it is slightly different from Pivot/Unpivot functionality of SQL Server. Basically, I have a date dimension table and I need to list corresponding dates, separated by coma for each month. So, for January 2010, my results would be something like this: Month Dates 2010-01 01/01/2010, 01/02/2010, 01/03/2010...01/31/2010 What would be a way to achieve this? Thanks, Pasha

3/4/2010 1:03:01 AM 1 =?Utf-8?B?UGFzaGE=?= <Pa...@discussions.microsoft.com>
Please help with SELECT sequel statement.

Given LoanNum as a input parameter and need to return all the same addresses. I know this table is a mess but I have been asked to do not to clean up the data. Any helps would greatly appreciated. Please see the desired result below. -- TRUNCATE TABLE T INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('100882', '1146 EAST 32ND STREET', 'LOS ANGELES', 'CA', '90011') INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('103385', '1146 EAST 32ND STREET', 'LOS ANGELES', 'CA', '90011') INSERT INTO

3/3/2010 11:22:01 PM 3 =?Utf-8?B?TE4=?= <...@discussions.microsoft.com>
AdventureWorks: can I query table w/o using table_schema name - in

I tried to query the Contacts (Person.Contacts) table in the AdventureWorks DB, but it wouldn't let me unless I included Person. in front of the table name: select * from Person.Contact If I query the same table from a VS(2008) project using LinQ To Sql, this appears to forgo the table_schema name. Is there a way to forgo the table_schema name in QA? Thanks, Rich

3/3/2010 10:21:01 PM 4 =?Utf-8?B?UmljaA==?= <R...@discussions.microsoft.com>
Not allow multiples in same month

I have a stored procedure where I want to not allow an ActivityCode = 64 more than once in a month for any one person (PeopleLinkID). Below is my IF condition that is not working correctly. Also, I think it is sometimes returning an error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Any help is appreciated. IF @ActivityCode = 64 AND NOT EXISTS(SELECT dbo.ActivityHistory.PeopleLinkID FROM dbo.ClientCaseNotes INNER JOIN dbo.ActivityHistory ON dbo.ClientCaseNotes

3/3/2010 8:59:02 PM 8 =?Utf-8?B?RGF2aWRD?= <dlch...@lifetimeinc.com>
Update Query

I have a table called accthist which has the following coloums Acct, subacct, BegBal, YearEndBal, FiscYR I want to update the BegBal for FiscYR 2010 for all distinct (acct subacct) items with the YearEndBal for these distinct (acct subacct) items from FiscYR 2009. So basically set the BegBal for 2010 with the YearEndBal for year 2009. Could someone guide me on the sql for this. It will be highly appreciated. Thanks Sam

3/3/2010 7:54:18 PM 1 "Sammy" <s_com...@hotmail.com>
Help with duplicate fields

Hi All, Supose I have a Products table with code and description fields: cod description --- ----------- 001 hamburger 002 cheesburger 003 hotdog In this particular case, duplicate codes are not a problem since the code is not a primary key,and similar items stocked at various facilities will have the same code, with another field being used to determine the specific stock item's location. So this looks more like the actual table: code loc description ---- --- ----------- 001 A hamburger 002 A cheesburger 003 A hotdog 001 B hamburger 002 B cheesburger

3/3/2010 5:35:35 PM 7 Bruno <brun...@hotmail.com>
eigenheimzulage antragsformular , kredit zum hauskauf , kredit hausbau , hypothekenkredit konditionen , hypothek formel , ratgeber immobilienfinanzierung , baufinanzierung aktuell ,

eigenheimzulage antragsformular , kredit zum hauskauf , kredit hausbau , hypothekenkredit konditionen , hypothek formel , ratgeber immobilienfinanzierung , baufinanzierung aktuell , + + +++ GUENSTIGE KREDITE ONLINE +++ KREDITE IM INTERNET OHNE SCHUFA IMMOBILIEN +++ + http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL http://WWW.IMMOBILIEN-KREDIT-ONLINE.NL ht

3/3/2010 5:29:36 PM 0 cheng goethe <xdf...@googlemail.com>
delete rows from a large table with text column

hi guys, I need to remove old data on regular basis from a big table (over 50 million) with a large text column. That column has average around 3000 charaters. The deletion is painful. It takes a minute or more to delete just 5000 rows. Since there should be no down time for production, all I can do now is to delete in small batches. And the table is not partitioned so I can't use partition switch. Do you know if there's any better way to do the deletion? Should I convert this text field to varchar(max)? Thanks.

3/3/2010 4:57:18 PM 3 mirthcyy <mirth...@gmail.com>
Set Transaction Syntax?

I am trying to get some 'case/set' logic down to change the transaction level in TSQL and it's not working, can anyone comment on what I would have to change? Declare @tx Int Set @tx = ( select transaction_isolation_level from sys.dm_exec_sessions where session_id = @@Spid ) print @tx -- later the transaction level is set to something else for some -- other tsql to run -- now i need to set it back to what it was earlier select case @origTxLevel When 1 Then Set Transaction Isolation Level Read Uncommitted When 2 Then Set Transaction Isolation Level Read Committed When 3 The

3/3/2010 4:40:49 PM 2 SnapDive <SnapD...@community.nospam>
german string to float?

Hello, I am wondering if anyone can help me. I have a small stored procedure which takes a float as paramemter. Today one of my german colleague found that she can't put 0,5 in, and it gives error message --- Error converting data type nvarchar to float. She tried a couple of days before and she did can type 0,5 and save. I am wondering what could trigger this problem? The front side is very supid, no checking at all. it just pass anything it receives to the stored procedure. Many Thanks

3/3/2010 3:58:21 PM 1 DAXU <jerryxu7...@googlemail.com>
Returning ID's with broken series

Table 'TEST' has information on 4 staff members. For Staff_ids 1 and 4 there is no missing (brake) year while for staff_id 2 and 3 there is brake in years. Can someone help me with a query to return only the staff_ids for which one or more years are missing in between. CREATE TABLE [dbo].[TEST]( [Staff_id] [int] NOT NULL, [Year] [int] NOT NULL ) ON [PRIMARY] GO insert into TEST select 1,2005 insert into TEST select 1,2006 insert into TEST select 1,2007 insert into TEST select 1,2008 insert into TEST select 2,2005 insert into TEST select 2,2007 insert into TEST

3/3/2010 7:15:37 AM 15 "M.K" <mianksa...@yahoo.com>
Am I supposed to see the OUTPUT parameter's value in SQL Mgmt Stud

Hi, I have a stored procedure with an OUTPUT parameter. When I execute the procedure in SQL Server Mgmt Studio, am I supposed to see the OUTPUT parameter's value? I'm troubleshooting an issue and at this point I'm not sure if it's the application code or the stored procedure. When I execute the stored procedure, it does what it's supposed to do i.e. insert a new record, but I do not see the value of the output param -- unlike when I return values through a SELECT statement. Is this normal or am I supposed to see the value returned by the output param? -- Thanks, Sam

3/3/2010 5:48:01 AM 5 =?Utf-8?B?U2Ft?= <...@discussions.microsoft.com>
likining view from other DB?

Hi, It have 2 databases on my SQL Server, a Project DB and a Village Stastictic DB. Most of the users use Project DB. I want users to assess the statistic from village DB, but instead of creating new front ent and seting up new ODBC connection for them, can I just link the view from Village Statistic DB to the Project DB. If so how to do that. SF

3/3/2010 12:53:54 AM 2 "SF" <samna...@pactcambodia.org>
Execution Plan and Clustered Index Scan

I am running a query that tells me it is doing a clustered index search for a value not in the Clustered index. I have the following: SELECT rfd.FileID,rfd.FileDetailId,GiftAmount FROM FileDetail rfd JOIN File rf on rf.FileId = rfd.FileId WHERE ClientID = 876 File has a clustered index on FileID and FileDetail has a clustered Index on FileDetailID. I also have an index on the ClientID, since it is in the where clause. When I look at the execution plan: It does an Index Seek (non-clustered) 33 % on the index that points to the ClientID as expected. But if I take delet

3/2/2010 10:57:56 PM 8 "tshad" <...@dslextreme.com>
Show actual days

I have two tables : CREATE TABLE [dbo].[FD__PROGRAM_CLIENT]( [ClientKey] [int] NOT NULL, [PgmKey] [int] NULL, [Date_Admit_Program] [datetime] NULL, [Date_Discharged_Program] [datetime] NULL, CONSTRAINT [PK_SYS_FD__PROGRAM_CLIENT] PRIMARY KEY CLUSTERED CREATE TABLE [dbo].[DateLookup]( [DateKey] [int] NOT NULL, [DateFull] [datetime] NULL, [CharacterDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FullYear] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [QuarterNumber] [tinyint] NULL, [WeekNumber] [tinyint] NULL, [WeekDayName] [varchar](10)

3/2/2010 10:27:31 PM 6 amj1020 <angelicre...@hotmail.com>
Need help with SELECT statement (return MAX date).

I need to return the MAX date either of these 3 columns below. Thank you so you in advance. Below is the rules and desire results. IF OBJECT_ID('T1', 'u') IS NOT NULL DROP TABLE T1 GO CREATE TABLE [dbo].[T1] ( [LoanNum] [varchar](10) NOT NULL, [OfferPrice] [money] NULL, [MgrApprDate] [datetime] NOT NULL, [DirApprDate] [datetime] NOT NULL, [ExecApprDate] [datetime] NULL, [ExitStrategyMainCategoryID] [int] NULL, [ExitStrategySubCategory1ID] [int] NULL ) GO INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMain

3/2/2010 10:21:01 PM 3 =?Utf-8?B?TE4=?= <...@discussions.microsoft.com>
Drop time conditionally

I have an SQL SELECT (see below) that I want to have the column named ActivityDate display as either a date and time or just a date. For example, if the time is anything other than 12:00 AM then I want to show the date and time, otherwise, just the date. Thanks. SELECT [ActivityID], [PeopleLinkID], [ActivityCode], [ActivityDate], [EnteredBy], [ActivityNotes FROM [vw_ActivityHistoryCaseNotes] WHERE ([ActivityID] = @ActivityID) -- David

3/2/2010 10:05:01 PM 3 =?Utf-8?B?RGF2aWRD?= <dlch...@lifetimeinc.com>
Identity Column in SQL Server View

Access 2007 front end with SQL Server 2005 Express back end. Using SQL Server Profiler, I have observed the following: When an Access datasheet form is bound to a table (or view containing one table), with an Identity (Access Autonumber) column, and a new record is inserted via the bound form, Access uses sp_executesql to insert the new values into the table (or view). If I look at design view of the table (or view) in Access, the identity column appears as an autonumber. Since Access recognizes the Identity (Autonumber) column, it uses SELECT @@Identity to retrieve the new i

3/2/2010 8:36:43 PM 3 "AG" <NOSPAMagi...@newsgroup.nospam>
DBCC Showcontig

What does it mean when this report shows blank under index name? Thanks in advance

3/2/2010 8:07:02 PM 3 =?Utf-8?B?Ukc=?= <...@discussions.microsoft.com>
Create Multiple Triggers from a Cmd File

I want to create 100 triggers, one on each of 100 tables. The body of the trigger is the same for each table. Is there a clever way I can automate this using a .bat file or a Powershell file or the like. Ideally I'd have the trigger in a file and have the script loop through the table names in sysobjects and use each to create the trigger. Thanks very much for any help.

3/2/2010 5:34:15 PM 1 nzrdb6 <nzr...@googlemail.com>
insert BLOB from web source

Hi, I'm trying to fetch and insert an image into a column, but I keep getting an error: Cannot bulk load because the file "http:\ \www.google.co.uk/images/nav_logo7.png" could not be opened. Operating system error code 123(error not found). Here is my query: declare @BLOBTest table (BLOBName VarChar(50), BLOBData VarBinary(Max)) INSERT INTO BLOBTest (BLOBName, BLOBData) SELECT 'First test file', BulkColumn FROM OPENROWSET( Bulk 'http:\\www.google.co.uk/images/nav_logo7.png', SINGLE_BLOB) AS BLOB select * from @BLOBTest Any ideas?

3/2/2010 4:06:46 PM 4 Dan Bridgland <danbridgl...@gmail.com>
Editing Multiple SPs

Is there any way I can making the same tweak to 100 SPs without having to script them all out into a big file and edit that file in MS Word? Is there a more clever way to do it? Anything in SQL 2008? Any 3rd party tools?

3/2/2010 10:24:17 AM 4 nzrdb6 <nzr...@googlemail.com>
NTUserName not working in Profiler as a filter.

We are running traces and tried to use NTUserName as a filter. If I have "Like" = "joe" and joe is not an NTUserName - it still displays the traces. The Column shows nothing in the NTUserName when stored procedures come across from my web page. I expected that since this is tracking a web page and not logged on as a user, but does it show the traces where the column is blank? It also works fine from a Query from SSMS where I am logged on a Windows user. thanks, tom

3/2/2010 6:12:49 AM 2 "tshad" <...@dslextreme.com>
Track deadlocks and locks that take too long

My client has an aspx page that normally takes about 4 seconds to load. We are pretty sure the time is because of a set of recursive Stored Procedures and functions. The Stored Procedure is setting up a Cursor that handles the processing to create a tree like you see in windows explorer in the right side pane. This works pretty well most of the time and was written by a 3rd party. Periodically, a couple of my clients customers have run into a Page Load time of about 3-4 minutes. The problem is it is sporadic. It may be only 30 - 40 seconds at times. We are pretty sure

3/2/2010 4:38:54 AM 12 "tshad" <...@dslextreme.com>
HELP-SQL Server 2005 Rollup Query for Distinct Values

Folks, I realize that group by rollup for distinct values in SQL Server 2005 is not supported. Can you please help me in writing the following situation. I have a table which keeps track of an Employee working in multiple offices located in different states. I need to get the count of Employees working in each office by state and/or office code. Please keep in mind that I have simplified the question using this example. My case actually has close to 7 dimensions. Table=WorkPlace State OfficeCode EmployeeId ========================== NJ O1

3/2/2010 12:47:29 AM 2 "Krish" <n...@no-reply.com>
Retrieving records between dates

Here is my table: CREATE TABLE [dbo].[FD__PROGRAM_CLIENT]( [ClientKey] [int] NULL, [PgmKey] [int] NULL, [Date_Admit_Program] [datetime] NULL, [Date_Discharged_Program] [datetime] NULL, CONSTRAINT [PK_SYS_FD__PROGRAM_CLIENT] PRIMARY KEY CLUSTERED SAMPLE DATA: 609 6 2008-05-27 00:00:00.000 2008-06-02 00:00:00.000 459 4 2008-07-30 00:00:00.000 2008-08-12 00:00:00.000 605 6 2008-06-17 00:00:00.000 2008-06-30 00:00:00.000 607 6 2008-04-23 00:00:00.000 NULL 1671 4 2008-07-15 00:00:00.000 2008-07-16 00:00:00.000 1757 6 2008-05-17 00:00:00.000 NULL

3/1/2010 10:58:24 PM 3 amj1020 <angelicre...@hotmail.com>
Need help with OUTPUT parameters

Hi, I am trying to execute two child stored procedures from a parent one. The first stored procedure inserts a new company into Companies table and returns its CompanyID, the second stored procedure inserts an address into Addresses table and returns its AddressID and the third one assigns the address to the company. Everything works fine but when I run the parent stored procedure, I only want to return the CompanyID back to my application. However, the code below returns both CompanyID and AddressID. I'm using OUTPUT clause so that I can grab CompanyID and AddressID retur

3/1/2010 9:21:02 PM 7 =?Utf-8?B?U2Ft?= <...@discussions.microsoft.com>
parse uri

Hi, I have uri query string in a database field like this: com=123&user=234&order=01 com=123&user=234&order=02 how to parse the string and get each value: com=123 user=234 order=01 com=123 user=234 order=02 Thanks...

3/1/2010 8:56:14 PM 3 <some...@js.com>
UPDATETEXT Dropping Character

I am using UPDATETEXT to update and empty ntext field. I con't figure out why the last character, a period, keeps getting dropped? This is SQL 2005. DECLARE @name nvarchar(60) DECLARE @ptr binary(16) DECLARE @buf nvarchar(1000) UPDATE receiptFormat SET [xmlData] = '', jsData = '', defaultBeforeFl = 0, defaultDuringFl = 0, defaultAfterFl = 0, allCentersFl = 1, warningFl = 0, errorFl = 0, workflowID = NULL, receiptFormatTypeID = 1 WHERE [name] = 'DeliveryAdjustmentReceipt' SELECT @ptr = TEXTPTR(xmlData) FROM receiptFor

3/1/2010 8:05:01 PM 3 =?Utf-8?B?TWFyayBQcmljZQ==?= <MarkPr...@discussions.microsoft.com>
Varchar to Numeric

I've got a script that works great on one server where the database is less than a terabyte. However, the script below I cannot get to work on a server where the database is about 1.5 terabytes. I have tried all different values of p and s for the numeric data type and just cannot get it quit erroring out. I can't figure out why this won't work. Below is the error msg I get. Any help would be much appreciated. "Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric." --drop table #temcm_helpfile create table #temcm_helpfile ( [Name] va

3/1/2010 7:50:01 PM 1 =?Utf-8?B?Q0xN?= <...@discussions.microsoft.com>
sql server deadlock (keylock)

hi guys, I have two procedures: one for selecting data from a table and do some calculations and the other for update or insert into the same table. There are two indexes on that table: one clustered (primary key): Pk_Table and one nonclustered: Ix_Table. I've captured a few deadlocks on those two procedure executions. It seems one proc gets x lock on the Pk_table key first and the other proc gets the x lock on Ix_Table and then both proc tries to get s lock on the other key. Then the deadlock happens. The below is part of deadlock xml: <resource-list> <key

3/1/2010 7:00:35 PM 2 mirthcyy <mirth...@gmail.com>
How do I get scalar value of a child stored procedure?

Hi, I'm trying to execute multiple stored procedures from a parent storedproc. One of the child stored procedures returns a value which I need for the next child stored procedure I need to execute in the parent stored procedure. I used the following syntax but looks like it's not the correct one. Could someone help me with the correct syntax? -- Execute child sp and get UserID DECLARE @UserID uniqueidentifier SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith' I appreciate your help with this. -- Thanks, Sam

3/1/2010 6:56:01 PM 5 =?Utf-8?B?U2Ft?= <...@discussions.microsoft.com>
How to get a list of a tables column names

[I originally sent this last week, but as it hasn't shown up yet, I'm sending it again. Apologies if it shows up twice.] Using SQL 2008. In SSMS Query Analyzer, given a table that I'm interested in, I would like to output the names of all that tables column names. I'd like it to be a list displayed in the Results pane, with one column name per row, left-justified if possible. I want to be able to copy and paste those names. (I know that if I set "Result to Text" and execute "select top 0 * from tablename" it will display all the column names such that I can copy and paste

3/1/2010 6:46:20 PM 2 Rav <Pa...@cais.com>
how to return previous week from calender table

Hello, I have a calender table that contains the field week_number numeric(6,0). I would like to know if there is a way to retrieve the previous week for every week that is defined in the calender table without using the CROSS APPLY command. Thank you for your help. -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1

3/1/2010 6:35:02 PM 3 "Cismail via SQLMonster.com" <u14...@uwe>
what hosting deal you recommend me?

Hi asp.net 3.5 My site take about 3 seconds to load, as a result my site has a drop in google ranking from 40 to 500 (very competive niche). Google has started taking site peformance as a factor for ranking. Today my site is hosted on a shared cluster at the host, because it's shared the host will not enable compression or browser caching etc The database is MSSQL 2005, so the eventuel new host must support .NET and MSSQL 2005 I talked to a support guy at webhuset.no (the host) and they said the next step would be to host the site on a virtual server. the entry price f

3/1/2010 3:20:21 PM 1 "Jeff" <it_consulta...@hotmail.com.NOSPAM>
'MYDB" already exists?

I am running the following using a SQLClient connection. When it runs I get an error that the db already exists. I can verify that the files in fact do not exist. But, when I connect to the SQLEXPRESS via Management Studio I see a MYDB database. There is no "+" beside the database and I et an error when I click it. Code: cmd.CommandText = "CREATE DATABASE [MYDB] ON PRIMARY ( NAME = N'MYDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MYDB.mdf' , SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'MYDB_log', FILENAME = N'c:\

3/1/2010 1:43:01 PM 2 =?Utf-8?B?V2lsbCB0aGUgVGhyaWxs?= <WilltheThr...@discussions.microsoft.com>
Copying Information

> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3350283589_2423585 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Hello everyone, I'm having trouble copying data from a table ( st ) in one db ( tecnicil_industria1 ) to the same table in a different db ( tecnicil_ind2010r ). Below is my query and the result error I'm getting. Can any help on how to go about doing this: Use tecnicil_ind2010r Go insert into st select * from tecnicil_industria1..st

3/1/2010 11:19:48 AM 1 Altair Brito <a.br...@tecnicil.com>
Logshipping from SQL2000

Hello, I have ServerA which is SQL2000 Enterprise Edition(latest sp) and ServerB which is SQL2008 Developer edition(latest sp). Can I set up Log Shipping from ServerA to ServerB? tia, AbbA

3/1/2010 6:31:36 AM 2 "Abba" <sql_h...@aaa.com>
How to script all Stored procedures as Alter

I am trying to script all my stored procedures to a query window. And I am using the Scripts Wizard which by default does a Create for all my procedures. I tried changing the "Script Create" option to false but it didn't work. It ran as if it did but I assume since I said false to Script Create it had nothing to do and a new Query window never opened up. If I say true to Script Create it works fine. If you do an individual script it works fine (without the wizard). Is there a way to do it? Thanks, Tom

3/1/2010 5:08:00 AM 3 "tshad" <...@dslextreme.com>
Mass change to Store Procedures

I have a case where I am referencing I different database (same server) on my test machine in many Stored Procedures, but they all need to be changed on a different database. This is done because different people use the same database on the test machine. So in stored procedures I access the database like so: Select * from MyDataTest.dbo.Employee but when I move the data to the production server I need all the references to be something like: Select * from MyData.dbo.Employee Is there an easy way to do this other then script all the stored procedures, change all ref

3/1/2010 4:09:17 AM 4 "tshad" <...@dslextreme.com>

Pages: 2 3 4 5 6 7 8 9











Newest Articles

Test Application for Direct Show Filter
11 min. 20 sec. ago

Posts still do not appear
22 min. 10 sec. ago

Outlook auto letter
23 min. 0 sec. ago

SQL 2005 attemted to read or write protected memory.
23 min. 10 sec. ago

Strange decimal rounding
25 min. 10 sec. ago