i always got the message "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation." when i run the script that came from MSSQL 2005 server. btw i used MSSQL 2008. here is the script USE [database1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec rsp_S...
Hi guys, Trying to connect to a SQL Server 2005 instance remotely but getting the following exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow r...
Hi, I'm reading an article from this website, but when i run the code that article provided, i get the error: Msg 102, Level 15, State 1, Line 16 Incorrect syntax near '.'. I double checked the syntax and can't find the cause of this error. The code is as follows, formatted by SQL Prompt. Anybody can help me with it? Great thanks. ...
my stored procedure is- CREATE PROCEDURE [dbo].[usp_SetMenu]( @locationId BIGINT, @menuId BIGINT = NULL, @name VARCHAR(100) = NULL, @taxable BIT = NULL, @type VARCHAR(100) = NULL, @dateFrom DATETIME = NULL, @dateTo DATETIME = NULL, @timeFrom VARCHAR(10)...
What are the topics for optimizing the performance for query in XML Data Type in SQL Server 2005? ...
I have a table that contain about 60 columns and if i write a select query in query analyser to get a row it show it and i must scroll to show data .. How can i get one row in which it shows as 60 row, each row contain 2 columns one for column name and other for value For example Select * from table where id = 1 in default it shown as ...
I'm working on a system to store appointments and recurring appointments. My schema looks like this Appointment ----------- ID Start End Title RecurringType RecurringEnd RecurringTypes --------------- Id Name I've keeped the Recurring Types simple and only support Week Days, Weekly, 4 Weekly, 52 Weekly If RecurringType is null the...
Hi, I am using following script to shrink the database: ****set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[ShrinkDatabaseProc] AS BEGIN SET NOCOUNT ON DECLARE @FileName NVARCHAR(260), @GroupId SMALLINT, @strSQL NVARCHAR(4000), @DbId INT, @DatabaseName NVARCHAR(260) SELECT @DbId = dbid FROM master..sysproces...
Hi, I want to have list of all SPs from my database that fulfill some conditions, say all those SPs that have StudentId in them as i want to update those SPs where StudentId would be a key column. How can i get the list of all such SPs? Thanks for your inputs. ...
Hi, I am attempting to convert an access cross tab query to SQL, and am hoping someone here can help me in the process. The access query is: TRANSFORM First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS FirstOfBUGDET_LINE_BURDENED_COST SELECT tbl_Proj_Budget.PROJ_ID, First(tbl_Proj_Budget.BUSDET_LINE_BURDENED_COST) AS [Total Of BUGDE...
I'm trying to determine how to best design a storage facility for fast searching of text. There will be a different file format for each customer These files are XML, and the field names and attributes are not standard, and do not follow a schema The customer has an option to choose certain fields to be searchable There could be 100,00...
Hi, In Sql Server 2000 and 2005 I am running a select statement in a while loop. JFYI, this select statement is connecting to many linked servers and getting some values. IF there is any error, i want it should still execute next statement in the loop (similar to continue statement in c#) Example:- while @rowcount < 10 begin set @sq...
I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table I set the default schema for my user using management studio and also ran the ALTER USER myUser WITH DEFAULT_SCHEMA [myschema] and I still get the invalid object 'table' when writing a query...
I am starting a new project using C# and ASP.NET 3.5 with SQL Server 2005, and I am trying to decide which ORM is the best to use between LinqToSQL, LinqToEntities, or NHibernate. Ideally I would like to use the preferred Microsoft best practice; but I need to use a solution that will provide performance comparable to using stored proce...
Looking for a script to scan all tables in all SQL Server databases and list the columns which are large objects (TEXT, NTEXT, ,IMAGE VARCHAR(MAX), NVARCHAR(MAX), FILESTREAM, XML, VARBINARY). while I probably can code this myself, I want a ready made script. ...
Thanks everyone for the feedback Hi All, I'm am trying to create a stored procedure that does two inserts into two different tables. DECLARE @New_Group1_Id DECLARE @New_Group2_Id INSERT INTO Group1 (Group1_Desc) VALUES (N'Indianapolis') SELECT @New_Group1_Id = Scope_Identity() INSERT INTO Group2 ...
This is not a homework question, just something at work that's bugging me. I'm trying to write a query against these tables below. The two tables, history and code are joined to the historyassignment table by the historyid and codeid columns. Table: HistoryAssignment | AssignmentID | HistoryID | CodeID | |--------------|-----------...
I am running a dynamic sql command with sp_msforeachdb for each database. However the command bombs for a certain database. How is '?' used to display the database name when the error happens? I tried using it in a Catch statement but my syntax is wrong. ...
I have data that looks like this Investor Contact IBM James IBM Dean IBM Sean Microsoft Bill Microsoft Steve I need the data to look like this Investor Contact IBM James,Dean,Sean Microsoft Bill,Steve OR if the above is impossible Investor Contact1 Cont...
I am calling the following statement from withing a Stored Proc in SQL Server 2005. Backup Database @dbname to disk = @dest I need to be able to store the result of the 'Backup Database' in a variable so if something goes wrong I can kick off an alert to notify someone of the failure. How can I get that value? ...