SQLServerCentral / SQL Server 2005 / SQL Server 2005 General Discussion

BCP Utility to output contents into CSV

bikram.g.it — Tue, 21 May 2013 05:28:07 GMT

I have a stored procedure that gets a SQL query, file path (to output) and file name as parameter which I'm using to build a bcp command and finally executing the same e.gSET @sql = 'bcp "'+ @query+ '" queryout "+ @path_nm+ @file_nm+ '" -c'+ ' -t,'+ ' -T' EXECUTE MASTER..xp_cmdshell @sql, NO_OUTPUTUpon executing the stored proc it doesn't produce the file specified in the parameter.I have verified the parameters and they are correct. I did a print to see what's in @sql and say I get the following -'bcp "SELECT [col1],[col2],[col3] FROM [##results]" queryout "E:\test\test.csv" -c -t, -T'On executing the command standalone (i.e. not from a stored proc) from SSMS produces the output file correctly e.g.EXEC MASTER..xp_cmdshell 'bcp "SELECT [col1],[col2],[col3] FROM [##results]" queryout "E:\test\test.csv" -c -t, -T', NO_OUTPUTConfused!Are there any limitations on running BCP utility from a stored proc - am I missing something very obvious?Please help!Thanks, Bikram

Deploying ssis package

shrsan — Mon, 20 May 2013 12:19:45 GMT

Hi All,Am trying to deploy a package. which is created in VS2010 into sql server 2012. but am getting below error while execution of the job.Date Log Job History (Abdc)Step ID 1Server Job Name ABCDStep Name ABCDDuration 00:00:01Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: XYZ. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: Error: Code: 0xC00291CB Source: Send Mail Task Send Mail Task Description: Either the file "C:\Users\***\Desktop\&&&\ynmd.txt" does not exist or you do not have permissions to access the file. End Error Error: Code: 0xC0024107 Source: Send Mail Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: Finished: Elapsed: 0.375 seconds. The package execution failed. The step failed.when i execute it without sql job it executes but it is not working in sql job?Thanks in Advance.

SQL Package downgrade from VS 2010 to 2008r2

shrsan — Mon, 20 May 2013 12:22:15 GMT

Hi,I have created packages in VS2010 in development but my production env is 2008r2. how do i downgrad my packages?Thanks in Advance

Retrive parent child relation

vishnurajeshsat — Tue, 21 May 2013 01:52:17 GMT

Hi all below is my table and I want query for parent child relation. CREATE TABLE [dbo].[PRDST]( [PARENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CHILD] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CHILDATY] [decimal](13, 4) NULL, [POSNO] [smallint] NOT NULL CONSTRAINT [DF__PRDST__MPTPST__3D2915A8] DEFAULT (0), CONSTRAINT [PKPARCHR] PRIMARY KEY CLUSTERED ( [PARENT] ASC, [POSNO] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055874' , '1001544' , '2' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055874' , '5030036' , '1' , '2')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055874' , '4000069' , '2' , '3')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012327' , '1' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012328' , '1' , '2')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012329' , '1' , '3')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012330' , '6' , '4')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '1012331' , '2' , '5')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1001544' , '3000191' , '4' , '6')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012327' , '2020003' , '5' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012328' , '2020003' , '9' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012329' , '2020003' , '4' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012330' , '2020001' , '6' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012331' , '2020003' , '8' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055872' , '1012345' , '1' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1055872' , '5030036' , '5' , '2')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012345' , '1012325' , '1' , '1')INSERT INTO PRDST (PARENT , CHILD , CHILDQTY , POSNO) VALUES ('1012325' , '2020005' , '6' , '1')In the above table I had updated 2 products(1055874 , 1055872 ).Each parent has child(one or more than one and ) then child become parent(not all the child becomes parent) like that it go upto some level then it stops. I know only the 1st parent of all products(105 series) So once if I query for 1055874 then it has to retrive upto the last parent(1012331) as like below and the posno is in asc. Can some one help me.PRODUCT1PARENT CHILD CHILDQTY POSNO1055874 1001544 2 11055874 5030036 1 21055874 4000069 2 31001544 1012327 1 11001544 1012328 1 21001544 1012329 1 31001544 1012330 6 41001544 1012331 2 51001544 3000191 4 61012327 2020003 5 11012328 2020003 9 11012329 2020003 4 11012330 2020001 6 11012331 2020003 8 1PRODUCT2PARENT CHILD CHILDQTY POSNO1055872 1012345 1 11055872 5030036 5 21012345 1012325 1 11012325 2020005 6 1

Date reflect in profiler show different for 2 PC

rmata_76 — Tue, 21 May 2013 21:45:01 GMT

Dear All,Pardon me, if im not sure how to title my topic correct, i hope i can explain it clearly here.Here it goes, we are using SQL 2005 and we have 2 client PC connecting to it. Both PC are using windows XP OS.I've installed the same program for both PC, but when they try to retrieve the data from the server using a particular report, 1 PC can display the information, while the other one dont display anything.I've performed an SQL profiler to see what went wrong. What i gather was that 1 PC, was sending the query to the server with the date format 'May 17, 2013' while the other PC will send to the server as '2013/05/17' (btw server regional setting is DD/MM/YYYY).I thot both date will get me a result, but when i try to run the individual query, the query with 'May 17, 2013' works fine, but the other query with '2013/05/17' will give me a 'out-of-range' error, but if i swap 05 and 17 it will return me the same value as the 1st query.Now i was wondering how come the 2 PC, doesn't reflect the same date format, i've connected using the same login.Appreciate if any can help me on this issue, as i've cracking my head on finding one. Any help or advise will greatly appreciated.CheersRandy :-)

Database data files

IgorMi — Mon, 20 May 2013 09:23:07 GMT

Hi All.I have a database with a single file which size is 288GB. I'm dropping the indexes and the data is only 90GB. I added three more files and re-created the dropped indexes. Data is equally distributed on the three files and reaches about 66GB on each. In future all four files will have same size.There is also possibility to remove the data, and then insert it again, but that is the harder option, and much time consuming. So in that case I can have all four files with equal size -288/4 = 72GB. My situation is 90GB,66GB,66GB,66GB. I know it is important for the tempdb data files' initial sizes to be equal and with same autho-grow configuration.Does it matter with the user-database data files? I think no. Anyone against, with suggestion, or to confirm ..you're welcome.Thanks in advance,IgorMi

enabling READ_COMMITTED_SNAPSHOT

Chris Townson — Mon, 13 Mar 2006 07:08:00 GMT

Hi,

I'm currently installing service pack 1 to webCT 6 Campus Edition (Virtual Learning Environment) which requires READ_COMMITTED_SNAPSHOT to be turned on.

I have connected to the database at administrator level (owner of db) and executed the following query:

ALTER DATABASE webctdatabase SET READ_COMMITTED_SNAPSHOT ONGo

The query runs but never finishes. I have been checking for changes using:

select name,is_read_committed_snapshot_on from sys.database

but the result is still 0 (off).

Interestingly I ran the following query and it completed successfully, instantly.

ALTER DATABASE webctdatabase SET ALLOW_SNAPSHOT_ISOLATION ON

Where am I going wrong?

Chris.

WAITFOR DELAY causes a lock

facemann — Fri, 17 May 2013 10:57:13 GMT

Not an issue, but I found this curious. If I run the code:[code="sql"]USE AdventureWorksGOWAITFOR DELAY '00:00:15'[/code]Then in a 2nd window run [code="sql"]EXEC sp_WhoIsActive @get_locks = 1,@output_column_list = '[sql_text][sql_command][locks][%]'[/code]It shows a lock?!?The lock info is as follows[code="xml"]<Database name="AdventureWorks"> <Locks> <Lock request_mode="S" request_status="GRANT" request_count="1" /> </Locks></Database>[/code]I also tried updating the AdventureWorks.Production.Product table during the lock and it updated immediately with no issues. I don't understand:1. Why is a lock is issued on a WAITFOR command?2. How can I update even while there is a lock in place?Just curious.Thanks. :-)

Help regarding Adding new SQL 2005 instance

farushaik — Fri, 17 May 2013 03:20:49 GMT

Hi Wondering if someone could help me.I have a server which already has 2 instances (2005SP3 and 2008). There is requirement for another 2005 instance. I started the setup with setup.exe SKUUPGRADE=1 from cmd. I'm stuck at Feature selection page. It won't let me go further to select named instance or anything. It just has options to select client components/Documentations which were already installed.No database components. How do I proceed to install new named instance? Please help

What Do You Call This Relationship ?

homebrew01 — Wed, 20 Feb 2013 11:49:40 GMT

I have a situation with items that are related, but not really in a heirarchy ... more like parent to parent. As shown below, all the ODD numbers are related to each other equally so that '1' is related to '11' via the other entries. ie, 5 is related to 1, and 11 is related to 5, therefore 11 is related to 1. And all the EVEN numbers are related to each other equally, so that '12' is related to '2' because 12 is related to 10, 10 is related to 8, and 8 is related to 2.I need to consolidate related rows into 1 summary row (selecting oldest & newest dates), and looking for examples on the InterWebz, but not sure what keywords I should be looking for to describe this kind of relationship.[code="sql"]Item_A Item_B5 16 47 38 29 710 610 811 511 912 10[/code]

Removing commas and quotes from numeric fields in csv file using SSIS

khushboo.dudani — Tue, 10 Jan 2012 01:45:39 GMT

I am creating SSIS package which reads data from a csv file and stores in SQL Server database. There are a few numeric fields in the csv files. They sometimes contain value like [b]"1,008.54"[/b]How do I remove the quotes and comma from the value?I have successfully separated the rows with this kind of data by using [b]Conditional Split Transformation[/b]. (SUBSTRING([Column 9],1,1) == "\"")After this, I tried using [b]Derived Column Transformation[/b] to REPLACE comma and quotes with empty string. But it is not working.Please advise.Thanks!

PROBLEMS WHEN UPDATING LINKED SERVER WITH MSSQL DATABASE

koen_huys — Wed, 15 May 2013 00:59:05 GMT

I have a linked server (mysql) and I want to update some colums with the information on MSSQL informationnow I get the following errorOLE DB provider "MSDASQL" for linked server "mysqlapp" returned message "Data provider or other service returned an E_FAIL status.".Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "mysqlapp".where mysqlapp is the name of my linked serverupdate tabel3set tabel3.price = dt.ac_prixVenfrom openquery (mysqlapp, 'select * from product_special') as tabel3inner join (select Ar_ref, Ac_prixVen, AC_categorie,product_id, pricefrom openquery (mysqlapp, 'select * from product') as tabel2 inner jointractov15.dbo.f_artclient as tabel1on tabel2.model = tabel1.ar_ref where tabel1.ac_categorie = '1') as dt ontabel3.product_id = dt.product_idwhere tabel3.customer_group_id = '1'and tabel3.price<> dt.ac_prixVen

OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active."

Beratung — Fri, 26 Oct 2007 17:26:43 GMT

I have linked a local SQL 2005 Standard Server 32-bit Unclustered to a remote SQL Server 2005 Standard 64-bit Clustered Server. I am able to run a Stored Procedure that is on the remoted clustered from the Local Unclustered server and have the results grid populate with the select statement's results that is within the stored procedure.When I try to run the same stored procedure on the Local and insert it into a Local table I get the following error:OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active.".Msg 7391, Level 16, State 2, Line 40The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "" " was unable to begin a distributed transaction.I have seen a lot of post in various forums and on MSDN and have addressed all issues with com services settings and the like, but to no avail. Anyone else have any other suggestions?Thanks,DP

Report executing but not finding a stored procedure during Verify Database

ronmoses — Wed, 15 May 2013 09:52:10 GMT

Please disregard this question. (Mods, delete?) I misinterpreted my results, it's definitely on the Crystal side. My apologies.

Combining two query result sets.

William Gary Wright — Mon, 13 May 2013 11:22:59 GMT

Well I tried this in the reporting services forum got 1 reply and it seems to have fallen into the crack. I have not presented my request here the same as I did in the forum. Anyway, I am trying to write a report. The primary query looks like this. Select * from streets where calltime >= 'Sometime' and street = 'Some street' (streetname) and streetnumber >= somenumber (streetfrom) and streetnumber <= somenumber (streetto) The values for the where clause will come form this query and I need to loop these values through the first query. select streetname,streetfrom,streetto from streetranges where streetcategory = 'CFMH'

real time backup in sql2000

vishnuprasadkv — Tue, 14 May 2013 08:35:56 GMT

haiii, i m using msserver2003(sql2000) database server in a domain network.also i configured client system with the same configuration of server as a standby server.if my server may down,i can easily change to standby server,how can i create a realtime backup to standby server?i m awaiting kindful replies............ thanking, vishnu

PROFILER - Trace Templates that include Groups

YSLGuru — Wed, 16 Sep 2009 13:02:11 GMT

Does anyone know if it is possible to save to template a Trace file that includes 1 or more GROUP items? When I setup a Trace Temploate and within the ORGANIZE COLUMNS feature I move a column up until it is in the GROUP area I can use the trace but it will not let me save that as a Template. The SAVE menu items are all dimmed out. If I edit a template to include that change, moving a column up into a GROUP, it will let me save the Template but that change is not actually saved and this can be verified because then next trace created from this template does not include any columns in the GROUP area.Thanks

Updating field

cyberdaemon — Thu, 09 May 2013 21:45:17 GMT

Good day,I am having trouble with my update query.I want to update txtReferrence but the update value will be the return or result of the another sp which run over linked server.here is my code..ALTER PROCEDURE [dbo].[sp_UPDATEREFNUMBER] -- Add the parameters for the stored procedure here @bintDriver BIGINT --,@dtDeliveryDate Datetime ,@decTotalAmount DECIMAL(18,2) ,@decAmountPaid DECIMAL(18,2) ,@bintShipment BIGINT ,@intStatus INT ,@dtpaymentDate DATETIME ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure hereDECLARE @root AS NVARCHAR(20) ,@dpNumber AS BIGINT ,@amounttobepaid AS DECIMAL(18,2) ,@paymentdate AS DATETIME-- SET @dpNumber = (SELECT bintDriver = CONVERT(NVARCHAR,bintDriver) FROM tblDeliveryListAmount WHERE bintDriver = @bintDriver AND bintShipment = @bintShipment) SET @amounttobepaid = (SELECT decAmountPaid = CONVERT(NVARCHAR,decAmountPaid) FROM tblDeliveryListAmount WHERE bintDriver = @bintDriver AND bintShipment = @bintShipment) SET @paymentdate = (SELECT CONVERT(VARCHAR,dtDeliveryDate,101) FROM tblDeliveryListAmount WHERE bintDriver = @bintDriver AND bintShipment = @bintShipment)--EXEC @root = [dev02-kc2-bd].ar_dev.dbo.spQuery_GetSequenceNo @dpNumber, @amounttobepaid, @paymentdateUPDATE tblDeliveryListAmount--SET --decAmountPaid = @decAmountPaid txtReferrence = @root --,intStatus = '2' --,dtpaymentDate=getdate() WHERE bintDriver = @bintDriver AND bintShipment = @bintShipmentAll i want is to update the txtReferrence with the result of the EXEC @root = [dev02-kc2-bd].ar_dev.dbo.spQuery_GetSequenceNo @dpNumber, @amounttobepaid, @paymentdatethat's why I assign the txtReferrence to var @root just to update the value. but the value always return 0.how can i update it..>?thanks

HOW TO JOIN 2 DIFFERENT MYSQL TABLES WITH A SQL TABLE

koen_huys — Mon, 13 May 2013 13:19:20 GMT

I have the following query, I have 3 tablesin the first tabel1 (tester.f_artclient) there are 3 colums : name, sellingprice, categorie (sql table)in the second tabel (tabel2) (mysql) I have the following colums :model, productin the third tabel (Tabel3) (mysql) I have the following colums: product, price and custumer_groupfinally what i want is that the sellingprice from tabel1 comes into price from table 3I get an error in 'from' on line 5update tabel3set dt.price = tabel1.ac_prixVenfrom openquery (mysqlapp, 'select * from product_special') as tabel3inner join (from openquery(mysqlapp,'select * from product') as tabel2 inner jointester.f_artclient as tabel1on tabel2.model = tabel1.name collatedatabase_default where c.ac_categorie = '1'and tabel2.price <>tabel1.sellingprice) as dt ontabel3.product = dt.productcollate database_defaultwhere tabel3.customer_group = '1'and tabel3.price<> tabel1.ac_prixVencan somebody help we what I am doing wrong

how can I get the DTS Wizard?

Blair Dee-474691 — Mon, 13 May 2013 12:17:52 GMT

Hi, My work laptop just died and I am working on a loaner until they can get mine fixed. I just installed SQL Server Management Studio Express and It looks like I dont have the DTS Wizard. Where can i get the DTS Wizard?

SQL Monitoring Tools

dva2007 — Sat, 11 May 2013 04:15:15 GMT

hi friends,any feedback/comments on this kind of tools available in the market?http://www.idera.com/ProductsSolutions/SQLServer.aspxHas any one used this?Any help will be appreciated. Thanks

Create BAT File and execute the same in Trigger

mail2payan — Thu, 09 May 2013 05:49:53 GMT

Dear All,I am situation, where we have a table named as [b]Project[/b], columns for the table as follows:[code]------------------------------------------ID | ClientCode | ProjectName------------------------------------------ 1 | AAA | Dubai Airport Phase I 2 | AAA | Dubai Airport Phase II 3 | ARC | Salala 4 | MIZ | UMBC Building------------------------------------------[/code]Now my task was, whenever a project name and other details being created, then a Folder will be created in a server itself in the path E:\ProjectFolder\ in following way:E:\ProjectFolder\AAA\AAA1E:\ProjectFolder\AAA\AAA2E:\ProjectFolder\ARC\ARC3E:\ProjectFolder\MIZ\MIZ4You can see here Folder and sub-folder is being created with that following project - client code & IDI used following trigger to do the same:[code]CREATE TRIGGER [dbo].[CreateFolderName]ON [dbo].[Project]after INSERTASSET NOCOUNT ONBEGIN declare @chkdirectory as nvarchar(4000), @folderName varchar(100), @mainfolderName varchar(100) declare @folder_exists as int SET @mainfolderName = (SELECT ClientCode AS Project FROM INSERTED) SET @folderName = (SELECT (ClientCode + cast(ID as varchar(10))) AS Project FROM INSERTED) set @chkdirectory = 'E:\ProjectFolder\' + @mainfolderName + '\' + @folderName declare @file_results table (file_exists int, file_is_a_directory int, parent_directory_exists int ) insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec master.dbo.xp_fileexist @chkdirectory select @folder_exists = file_is_a_directory from @file_results --script to create directory if @folder_exists = 0 begin print 'Directory is not exists, creating new one' EXECUTE master.dbo.xp_create_subdir @chkdirectory print @chkdirectory + ' created on ' + @@servername end else print 'Directory already exists' ENDSET NOCOUNT OFFGO[/code]This worked like a charm, now my next task is using same trigger, I have to create a BAT file inside that SubFolder - T-SQL for creation of BAT File as follows:[code]DECLARE @FileName varchar(50), @bcpCommand varchar(2000)SET @FileName = REPLACE('E:\ProjectFolder\[b][red](select ClientCode from INSERTED)[/red][/b]\[b][red](select ClientCode + cast(ID as varchar(10)) from INSERTED)[/red][/b]\xcopy_'+ (SELECT cast(ID as varchar(10)) FROM INSERTED) +'.bat','/','-')SET @bcpCommand = 'bcp "[red]SELECT 'xcopy "E:\ProjectFolder\' + clientCode + '" "\\10.0.0.35\Project\Folder" /T /E /I' FROM INSERTED[/red]" queryout "'SET @bcpCommand = @bcpCommand + @FileName + '" -U SQLServerUsername -P SQLServerPassword -c'EXEC master..xp_cmdshell @bcpCommand[/code]Here I am not understanding how to insert the above T-SQL in the Trigger as well as the above T-SQL is not right, what's wrong in this?Last query that will be included in the trigger is to execute the newly created bat file.Hope I am able to make you understand my query. I am sorry, I am bad in english, so maybe I was not able to make you understand my query in proper way. Please if you are unable to understand my query, please ask.I beg you all to solve this query. Please help.

Column summary

kgeeel240 — Fri, 10 May 2013 04:14:38 GMT

Hii have a table with 4 columnsYear Month Prod-Group Product Value2010 April Fruit Oranges 22010 April Sweets Cad bury 32010 August Toys Doll 5.52011 August Fruit apple 12011 December Fruit pear 22012 February Toys car 102012 July Sweets choc 3.5Is it possible for me to get a summary of this for example i want to know from the Prod-Group column which one appears the mostE.G. i would like my results to be like: Fruit

Pass a string into SP and parse on space

j2cagle — Thu, 09 May 2013 15:48:30 GMT

I'm wanting to send a string into a stored procedure, but only use the first characters up to the first space.For example, if I pass in:"ABC DEF GHI"I'm only interested in "ABC".How would I go about handling that in the SP? Currently, I'm setting up the parameter to be VARCHAR(MAX). However, when I pass in a string like above, I get an error:Incorrect syntax near 'DEF'Any ideas?

A way to auto disconnect all connections to a database in SQLsrv2005? And self intro

TecNQ — Tue, 07 May 2013 19:50:41 GMT

Hi.I am new to the forums.Thank you for having me.I have provided some bio info in my profile for those interested in personal stuff.My question:Is it possible to schedule a nightly process to disconnect all live connections to a database? I am asking this because sometime we need to detach databases for maintenance requirements, and re-attach them again.I am new to SQL server to be honest, and I personally prefer to use TOAD for SQL Server (a front-end from Quest software).Thanks in advance and look forward to learning a lot here.Cheers from Australia! :-)

OVER and PARTITION BY. Please explain

TecNQ — Wed, 08 May 2013 16:32:41 GMT

Hi.We have a function that is meant to return currently enrolled programs for students doing certain courses.In the functions we have this ...row_number() OVER (PARTITION BY r.StudentID ORDER BY ISNULL(r.StudProgEnrolStatusID, 3), r.EnrolmentDate, r.ProgramEnrolmentID DESC)'r' is the alias to our table which holds what students are enrolled in certain programs.The StudProgEnrolStatusID = 3 means that they are a specific type of student studying a specific type of programs / courses if you will.What is happening here please? What if OVER(PARTITION... meant to do?I am trying to re-write this so that it has the same result without the convoluted functions above, so that other SQL developers here can understand the code better.Thanks

SQL Server 2005 hangs in scanning config.

mdthomas48 — Tue, 07 May 2013 09:54:54 GMT

I had this installed before on this pc and removed it. Now trying to reinstall it but it gets hung in scanning the computers config. OS is XP Pro SP3. Currently have the full version of VS loaded and need SQL Server 2005 for that application. When loading the full version of VS, it hung up at installing SQL Server 2005 (which was last in the list of installs). Then I tried to installed SQL Server 2005 from the MS site, but then noticed it was hanging at the computer scan config point. I'm assuming that is were it was hanging up in the VS install too - but it didn't give me an error to read.QUESTION: Could the Windows SP3 be the problem, or something else?Thanks in advance!Mike

sql service broker

mukki — Sun, 05 May 2013 09:13:15 GMT

HiNeed help on getting code and info onservice broker (for sql server 2005 only)for auditing DML commands.Respond if any.Thanks

Synonyms on a table

Jay B Mehta — Wed, 12 Apr 2006 07:12:00 GMT

Hi All,

I have a synonym on a base table. The base table is changed as per some logic. Somehow I am unable to trucate the table using the synonym.If synonym is defined on the base table, and if I say "trucate table dbo.<synonym>", it gives error as.

Msg 1088, Level 16, State 7, Line 1

Cannot find the object "<synonym name>" because it does not exist or you do not have permissions.

I do have permission on the synonym, as I am the database owner.Any updates from you guys is welcome.

Update Join Query Help!!!!

prb88 — Mon, 29 Apr 2013 03:47:36 GMT

hi GuysI need help with a simple update join query that i need to do. below are my example of the two tables:[u]TABLE A[/u]DIVISION | SUB_UNIT | REASONSREFERRAL NULL NULL 4 NULL NULL 2 NULL NULL 3TABLE B (Lookup Table)ID | Description | ORDER2 workload issues 93 work conditions 104 work relationships 11I need to replace the numbers in the reasons for referral column in table a with the text in the descripton column in table b with a update join query. i know it sounds simple but i am relatively new to sql coding. any help with this. many thanks

Database mail with @query issue!

waitstate — Fri, 25 Jan 2008 14:36:39 GMT

I'm trying to send db mail using @query parameter and I get the error below:Msg 22050, Level 16, State 1, Line 0Error formatting query, probably invalid parametersMsg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478Query execution failed: Msg 15404, Level 16, State 19, Server ######, Line 1Could not obtain information about Windows NT group/user #####, error code 0x5.I exclude the @query parameter and the mail will be sent. The dbmail profile I'm using is public and my login is sysadmin.It has something to do with security though.. I set up a job and ran it is an administrator which is sysadmin as well and it worked with @query! Should I be mapped to msdb in a particular way?The query I'm testing is simple: 'select 1' :PTIA

What is this saying ?

steve4134 — Mon, 29 Apr 2013 12:41:53 GMT

I believe this is saying Last day of previous month and first day of current month ? Can someone please confirm ?Steve DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) and DATEADD(month, -1,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))

SQL to XML

Bill.Smith — Mon, 29 Apr 2013 09:02:11 GMT

Any suggestion on how to create XML like this from SQL Server?I was able to create the XML without the Metadata lines using, "FOR XML RAW ('Request'), ELEMENTS". <Request> <RequestType>NEW</RequestType> <CompanyName>Main St. Bistro</CompanyName> <CompanyID>1234567890</MerchantID> <Country>US</Country> <PostalCode>60602</PostalCode> <Metadata name="PROGRAM" global="true">A</Metadata> <Metadata name="LEVEL" global="true">2</Metadata> <Metadata name=”PROMOTION”>HAPPYMEAL</Metadata></Request>

Adding Rows to an existing table sql 2005

kgeeel240 — Fri, 26 Apr 2013 02:08:00 GMT

hii have a table from navision PLSo that is being updated every hour now i have created a table on my server PLSy now i want PLSo to update PLSy after 30 min it has got that info .. i don't mind doing it by my self if i cannot schedule it..Please help with the statement.. PLSo has a field that is never duplicated so i used it to say PLSo give everything(new rows that are captured on you) that PLSy do not haveselect * into [PLSy] from [srv5].[DB].[dbo].[PLSo]--Navisionwhere [timestamp] not in (select [timestamp] from [PLSy]) I tried using this statement it does not work since the table is there so for the past week i have been deleting and re creating this tableplease help

Memory setting change in existing cluster

inevercheckthis2002 — Mon, 29 Apr 2013 07:03:55 GMT

In my new job, I've inherited a SQL Server 2005 two-node cluster configured as active-passive.Each server is Windows Server 2003 R2 32-bit with 18 GB RAM.I thought that you need to make any changes to a cluster using Cluster Administrator. I'm not seeing anything in Cluster Administrator that refers to memory.In SSMS, I see that the memory settings are at their default. 'Use AWE' is not checked and the Max Memory is at 2147483647 MB. 2 petabytes??Do I use SSMS to change this? I suspect that I should change to check AWE and set Max Memory to 15300 MB, leaving 3 GB for the OS.Any suggestions or direction you can provide is appreciated. Thanks!

Extract data into specific csv columns?

Tony414 — Tue, 23 Apr 2013 09:34:27 GMT

Hello,First off I am new to this forum and don't know much about sql. I guess that's why I am here :-) I hope I am in the right forum.I'm hoping someone can lead me in the right direction here. I have a sql query that was written for me some time ago. I need to have the data placed in specific columns and rows within the csv file. Is it possible for me to do this? Here is what I have below... Lets say I need to place "ASSRREAL.Street" in column D row 3 of the csv file. Any help or ideas would be great. Even if there's a 3rd party tool that would allow me to do this? Thanks SET NOCOUNT ON; SELECT '"' + RTRIM(ASSRREAL.Unique_id) + '"' AS Uniqueid, '"' + RTRIM(ASSRREAL.Taxpayer) + '"' AS Name, '"' + '"' As Name2, '"' + RTRIM(ASSRREAL.In_Care_Of) + '"' AS Careof, [b]'"' + RTRIM(ASSRREAL.Street) + '"' AS Address1, [/b]'"' + RTRIM(ASSRREAL.Street_Mailing_Addr2) + '"' AS Address2, '"' + RTRIM(ASSRREAL.City) + '"' AS City, '"' + RTRIM(ASSRREAL.State) + '"' AS State, '"' + RTRIM(ASSRREAL.Zip1 + ASSRREAL.Zip2) + '"' AS Zipcode, ASSRREAL.Acreage AS Acres, '"' + RTRIM(ASSRREAL.Map_Block_Lot + Xtr_Mbl) + '"' AS MBL, '"' + CONVERT(varchar(5), ASSRREAL.Volume) + '"' AS Volume, '"' + CONVERT(varchar(5), ASSRREAL.Page) + '"' AS Page, '"' + RTRIM(LTRIM(ASSRREAL.Prop_Loc_St_No)) + '"' AS Street_Number, '"' + RTRIM(ASSRREAL.Prop_Loc_St_Name) + '"' AS Street_Name, '"' + RTRIM(ASSRREAL.Prop_Loc_Unit) + '"' AS Street_Unit FROM ASSRREAL WHERE ASSRREAL.Delete_Flag = 'N' AND ASSRREAL.GSequence = 0 AND ASSRREAL.Record_Year = 2013 ORDER BY ASSRREAL.Prop_Loc_St_Name, ASSRREAL.Prop_Loc_St_No, ASSRREAL.Prop_Loc_Unit

Unable to change to SA as database owner

marygrace_laurente23 — Tue, 23 Apr 2013 00:28:05 GMT

Hi guys,I've encountered problem upon changing database owner from specific user to SA.Lock request time out period execeeded. (Microsoft SQL Server, Error: 1222)

how to install the sp4 service pack in the sqlserver 2005 clusterenvironment

naga.rohitkumar — Fri, 26 Apr 2013 00:31:23 GMT

Hi to all need some details how to install the sp4 service pack in the sqlserver 2005 cluster environment?1. first i want to install in the secondary server [DR] what are the steps to follow while installing in secondary server [DR] which is in clustering in which database instance in running logshipping with databases read/only [please share a doc if available for DR]2. after successful installation in secondary server [DR] we want to install the SP4 in primary server [DC] for this what steps should i follow [please share a doc if available for DC] 3.is it necessary to bring secondary online before installing in primary ???

Assistance w/query that pulls table name, col name, data type

karen.richardson — Thu, 25 Apr 2013 10:41:58 GMT

Hello Everyone! I'm working on a query to pull table name, schema, column name, data type, and field length; just can't get the data type right. Here is what I have so far:[b]SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_name, t.name AS type_name, c.max_length AS length FROM sys.tables AS tINNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID ORDER BY schema_name, table_name;[b][/b][/b]t.name AS type_name returns table name, I need to have it return the data type. It works perfectly for my needs, ok, except that part. :-PThank YOU in advance for your assistance :-DWren

Partitioned views and query compile time.

blernsball — Wed, 10 Apr 2013 10:47:31 GMT

So I've inherited a SQL Server 2005 DB with a whole bunch of tables in the following format:rank_xxxx, where xxxx is a date identifier.ex:rank_3425rank_3426etc.Then they have stored procedures that use dynamic SQL to generate queries that will access the correct "partition". It's an awful mess.Because I can't change the tables, I thought that a quick way to gain some performance improvements would be to create a partitioned view of these tables. In that way we could simplify the queries and speed things up.Starting issues: 1. There isn't a useful partitioning key available in these tables...2. There are a lot of these rank_xxxx tables. And there is that 256 table limit...What I did was create some subviews in this format:[code="other"]CREATE VIEW [dbo].[vw_rank_all_part_1] AS SELECT 1 as period, * FROM dbo.rank_3425UNION ALL SELECT 2 as period, * FROM dbo.rank_3426....[/code]So period is set up to be the partition key.And then I create a master view like this:[code="other"]CREATE VIEW [dbo].[vw_rank_all] AS SELECT * FROM vw_rank_all_part_1 UNION ALL SELECT * FROM vw_rank_all_part_2...[/code]This seems to work. Sort of.If I query the vw_rank_all table and provide period as a static value like so:[code="other"]SELECT count(*)FROMdbo.vw_rank_all whereperiod = 400[/code]And then check my plan, I can see that partition elimination is working. It finds the base table and only looks at that one. So far so good.The problem occurs when I drop the view into the stored procedure:[code="other"]CREATE PROCEDURE [dbo].[a_test_rankpartitoning] @period intASBEGINSELECT top 100*FROMdbo.vw_rank_all whereperiod = @periodEND[/code]The plan looks ok, and partition elimination is happening. But.The compile time on first run is massive compared to the execution time. In the above SP it's not a huge amount, but when I use the view in a more complicated SP it drives the compile time through the roof (resulting in a total time of 50s on first run and then 2s on subsequent runs).Any idea why this is happening? Is it just a side affect of using partitioned views? Is it because of my partitioning key? Any way to avoid this?Thanks in advance for any comments.