sql-server-2005

I want to import data from my sql proc to csv file

hi, I am using sql server 2005, I want create sql procedure will save data into csv file. Below is the qurey I am trying but it is not creating any file in my system. use PBMS_DocumationWorkflow go create proc s_bcpMasterSysobjects as select '"' + name + '"' + ',' + '"' + convert(varchar(8), crdate, 112) + '"' ...

SQL - Is there a better way of passing a list of keys, for use in a where clause, into a Stored Procedure?

Here's the scenario; I have a list of CustomerIds (1, 2, 3) that have relating OrderIds. I have one Stored Procedure Delete_OrdersByCustomerIds, which deletes all the orders that are related to the CustomerIds specified. Currently, the way I do this is to concatenate the CustomerIds into a string, i.e. "1,2,3". I then pass this string t...

Reporting Services 2005 configuration

I need help with configuring Reporting Services. I have the ReportServer and Report Manager running successfully under a virtual website and under their own virtual directories (Reports and ReportServer) on port 80. I then tried to set up another virtual website running on port 1001 with the same virtual directory names I am able to ...

Why are SQL server inserts so slow?

I'm trying to insert rows of in memory data into a table on sql express 2005. It is running what seems to me very slowly - about 5 seconds per 1000 rows inserted. I am just using a basic "INSERT INTO" command. The slowness does not depend on the table data - it is still slow with a table with one int column and no index. It is nothing to...

Column 'dbo.mdSpaceSales.wkid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I am getting the above error for the query below : SELECT a.stid, a.pfid, pf.pfname, a.mdid, mm.mdDescription, a.categoryid, a.SummedSpace, a.AvgSpace, mm.size_sqft as MML_size, a.TotalRev, a.TotalProfit, a.wkCount, a.Lastwkinst FROM (SELECT wkid, stid, mdid, pfid, categoryid, sum([space]) as SummedSpac...

Getting a list of columns to show next to the column they are related to

I've got a table setup like so: [ReferredID], [Name] 1, Irwin 2, Marc 1, Johnny 5, Jackie 2, Perry 1, Reuben I'm trying to get a query that will produce this: [ReferredID], [List] [1], [Irwin, Johnny, Reuben] [2], [Marc, Perry] [5], [Jackie] Can you help me find the right query to produce these results or something near? ...

The quest for 0x0B

I get this error when reading some data from an SQL column then converting it to XML: "System.InvalidOperationException: There is an error in XML document (182, 16). ---> System.Xml.XmlException: ' ', hexadecimal value 0x0B, is an invalid character." Fair enough, maybe the data is malformed. Except, how can I find the culprit row? S...

dynamically create parameter at runtime for ssrs

I am using Visual Studie 2005 and SQL Server 2005. I trying to run some ssrs reports that I created on the server. Can I create paramters at run-time in my code even though I did not create the parameters in the report. Here is my sample code: ReportNum = Test.Left(cboReportList.Text, 9); reportViewer1.ServerReport.Re...

Can foreign keys hurt query performance

As I understood from this post, there are some scenarios where foreign keys can improve query performance. I've heard the opposite claim though, that because of referential integrity checks, foreign keys can actually hurt query performance. Under which conditions (if at all) is this true? 1) The term query seems to be misleading. I ...

isnull(email,'') = '' is NOT interpreted by it self as (email is null or email = '')?

Hi, as a fact of performance which one is better? Is there a difference between the actual 3 three versions of sql-server (2000 / 2005 / 2008)? ...

How to pass NULL or empty strings to stored procedure input parameter with ADO and VB?

I have a stored procedure in Sql Server 2005 with a varchar input parameter defined as: @Value varchar(24) = NULL in my VB6 application I try to set the parameter with an ADO funtion: Set prmParamVal = cmdChkParam.CreateParameter(, adVarChar, adParamInput, Len(paramValue), paramValue) If the value I try to pass is an empty (zero le...

Performance of COUNT SQL function

Hi All, I have two choices when writing a SQL statement with COUNT function. SELECT COUNT(*) FROM SELECT COUNT(some_column_name) FROM In terms of performance what is the beast SQL statement? can I obtain some performance gain by using option: 1 ? Thanks, Upul ...

Best way to deploy VB.NET Code / create an assembly on a SQLServer

I've created some functions / procedures with VB.NET and want to deploy them to a SQLServer. Within Visual Studio you can click right and select "deploy". An assembly will be created on the server and a lot of functions and procedures. It's really easy. Now I want to script the whole deployment process. What is Visual Studio doing when...

SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It is sent by one long string command. If one...

SQL Server 2005 Index Fragmentation

Several keys in a SQL Server 2005 database I inherited have very high fragmentation percentages. When using the following SQL: select OBJECT_NAME(object_id), avg_fragmentation_in_percent, record_count, * from sys.dm_db_index_physical_stats (DB_ID(N'FragmentedDB'), NULL, NULL, NULL, 'Detailed') s I see that several tables with fra...

DLL needed after assembly creation?

Can I delete the DLL-File C:\PATH\TO\DLL\FILE.DLL after creating an assembly with CREATE ASSEMBLY MyAssemblyName FROM 'C:\PATH\TO\DLL\FILE.DLL' ? Everything seems to work fine after deleting the file. But SELECT * FROM sys.assembly_files shows the path of the file in the name column. Can this cause problems? (or is the content of...

Select from multiple tables matching multiple criteria

Hello, I am working with 3 tables, trying to pull a list that match certain criteria. I need a list of companies from the companies table, that have an active opportunity (iactive is 1 in opportunities table) AND have a record in the notes table of type order or order2. Companies Table +------+------------------+ | cid | ccyname ...

Query to show result in the charts.

Hi, I have to implement charts in my application. Suppose i have a table structure DECLARE @SONGS TABLE ( [ID] INT IDENTITY, [SONGNAME] VARCHAR(20), [CREATEDDATE] DATETIME ) INSERT INTO @SONGS SELECT 'SONG1','20091102' UNION ALL SELECT 'SONG2','20091103' UNION ALL SELECT 'SONG3','20091107' UNION ALL SELECT 'SONG4'...

How risky is development against SQL Server 2008 with production on SQL Server 2005

My dev environment has SQL Server 2008 installed. I have an asp.net 1.1 site in production using SQL Server 2005. I'd rather not install SQL Server 2005 on my development environment. How risky would this be? What kinds of things might cause problems? ...

SQL Server DataType Testing

Hi Guys, I have column called code in source table which is of type varchar(40) and they changed it from varchar(40) to varchar(65). We created a ETL Package and which is creating 3 tables. In all 3 table's the column need's to be changed from varchar(40) to varchar(65). Now I need to test this change. I know By looking into the table ...