After performing a database restore, I want to run a dynamic script to fix ophaned users. My script below loops through all users that are displayed after executing sp_change_users_login 'report' and applys an "alter user [username] with login = [username]" statement to fix SID conflicts. I'm getting an "incorrect syntax error on line 15...
Possible Duplicate: What datatype should be used for storing phone numbers in SQL Server 2005? There are various datatypes from which i can choose for example varchar or bigint or decimal or money but which one could be the best in all respect. ...
Create table testxml (xmldata xml) declare @var nvarchar(max) set @var = N'الوفاق الوطني المخ' insert into testxml select @var After inserting , i am getting data from table like select * from testxml --------------------- ???????? Can you provide me the solution? ...
I'm looking up access logs for specific courses. I need to show all the courses even if they don't exist in the logs table. Hence the outer join.... but after trying (presumably) all of the variations of LEFT OUTER, RIGHT OUTER, INNER and placement of the tables within the SQL code, I couldn't get my result. Here's what I am running: ...
I am trying to figure out how SQL Server DBAs are doing their backups and verify in 2005. I use the Idera's free stored procs (which is no longer available to download btw) to backup and verify and have gotten around 65% compression. If there any other free alternative? Thanks in advance, -Subhash ...
As part of our corporate security practices it has been suggested that we turn on successful and failed login auditing on our SQL Server. Unfortunately the successful logins flood the event log with what appears to be polling from the SSMS client. Does anyone know if this is true and is there a way to change the polling interval or sto...
I'm working on a system that takes relational data from a sql server DB and uses SSIS to produce an XML extract using sql server 2005's 'FOR XML PATH' command and a schema. The problem lies with replacing the XML reserved characters. 'FOR XML PATH' is only replacing <, >, and &, not ' and ", so I need a way of replacing these myself. I...
This deletes the document from the Document table and outputs information about the deleted document into the FinishedDocument table. DELETE FROM Document OUTPUT Deleted.DocumentId , Deleted.DocumentDescription INTO FinishedDocument WHERE DocumentId = @DocumentId I need to delete the document not just from the Document table, but ...
Hi i have a huge database, around 1 TB in size, most of the space is consumed by a table which stores images, the tables has right now almost 800k rows. server response time has increased, i would like to know which techniques should i use or you recomend, partitioning? o how to reorganize the table every row is accessed by the image ...
I have SQL Server 2005 stored procedure. Someone one is calling my stored procedure within a transaction. In my stored proc I'm logging some information (insert into a table). When the higher level transaction rolls back it removes my insert. Is there anyway I can commit my insert and prevent the higher level rollback from removing my i...
Hi I have this SP USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[UsersInsert](@UpdatedProdData XML) AS INSERT INTO dbo.UserTable(UserId,UserName,LicenseId,Password,PasswordSalt,Email,IsApproved,IsLockedOut,CreateDate, LastLoginDate,LastLockOutDate,FailedPasswordAttempts,RoleId) ...
I have see a lot of competing theories about hierarchical queries in fluent-nHibernate or even basic nHibernate and how they're a difficult beast. Does anyone have any knowledge of good resources on the subject. I find myself needing to do queries similar to: (using a file system analog) select folderObjects from folders where folder....
I'm doing some benchmarking on large data sources and binding/exporting data for reporting. I started with using a data set, filling it with 100000 rows and then attempting to open a crystal report with the retrieved data. I noticed that the data set filled just fine (took about 779 milliseconds) however, when attempting to export the ...
I've read in a lot of web sites that the following code will not work, where @table_name is a table variable: INSERT INTO @table_name EXEC usp_name usp_param1, usp_param2 But the above exact code works fine for me inside a stored procedure in SQL Server 2005 (version 9.0.4035). Even MSDN (URL: http://msdn.microsoft.com/en-us/library/a...
I have this stored procdure CREATE PROC dob.p_foobar ( @foo bit = NULL, @Bar bit = NULL ) AS IF @Foo == 1 BEGIN SELECT 'Hello, World' END When I parse, I get the error "Incorrect syntax near '='". Please tell me what I'm doing wrong. I know it's something stupid, but I just can't figure it out. Thank you ...
SQL 2005 using T-SQL, I want to rotate rows into columns. Sample script: Use TempDB Go CREATE TABLE [dbo].[CPPrinter_InkLevels]( [CPPrinter_InkLevels_ID] [int] IDENTITY(1,1) NOT NULL, [CPMeasurementGUID] [uniqueidentifier] NOT NULL, [InkName] [varchar](30) NOT NULL, [InkLevel] [decimal](6, 2) NOT NULL, CONSTRAINT [PK_C...
Hi I am wondering how can do a mass insert and bulk copy at the same time? I have 2 tables that should be affect by the bulk copy as they both depend on each other. So I want it that if while inserting table 1 a record dies it gets rolled back and table 2 never gets updated. Also if table 1 inserts good and table 2 an update fails tab...
Hi I am using ms sql server 2005 and I want to do a mass update. I am thinking that I might be able to do it with sending an xml document to a stored procedure. So I seen many examples on how to do it for insert CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData XML) AS INSERT INTO dbo.UserTable(CreateDate)...
I am trying you insert some string which is not in English (other language). when i fetch back they are not correct. They comes like "?????". But at the same time when I enter the string through the SQL Server UI (SSMS) to enter the string, it works OK. What could be the solution please? ...
Greetings, I have a problem as follows: I have an SQL variable declared: DECLARE @myVariable nvarchar(max) a third party library set a value for this variable. To simplify, lets say that the value is as follows: SET @myVariable = 'Select ROWGUID from MySampleTable' Now, I want to execute the following query: SELECT ROWGUID FROM my...