sql-server

DataSet returns error "Must declare the scalar variable ..."

I'm trying to add a query to my dataset and getting an error "Must declare the scalar variable @searchstr". I find this a little odd because I've used the @variable to pass parameters before no problem, but for whatever reason it fails here. select DISTINCT g.groupname, CASE WHEN s.siteguid = @searchstr THEN 1 ELSE 0 END AS doesitexis...

Instead of trigger to update primary key

Hi! Long time reader, first time poster ;-) I'm implementing a system based on an old system. The new system uses SQL Server 2008 and my problem comes when trying to insert new items in the main table. This will happen in two ways: It may be imported from the existing system (1) or may be created in the new system (2). In case (1) the...

Error during loading of SSIS package

Hi, I'm trying to execute a SSIS package on a SQL Server 2008R2. The script retrieve data on a remote server and copy them to its local database. This job is scheduled every hour, the SQL Agent use a proxy to authenticate itself to the remote machine. Authentication seems to be ok but I get an error during loading of the SSIS package. ...

MS-SQL-08: Is it possible to create a view that depends on a function before the function has been created ?

Hey folks, Using MS-SQL-08: Is it possible to somehow temporarily disable dependency checking, so that I can create a view that uses a function that does not exist (has not yet been created) at the time of creating the view. [then create the required function, and finally re-enable dependency checking] So, lets say I have view V, whi...

Out of memory error as inserting a 600MB files into sql server express as filestream data

(please read the update section below, I leave the original question too for clarity) I am inserting many files into a SQL Server db configured for filestream. I am inserting in a loop the files from a folder to a database table. Everything goes fine until I try to insert a 600 MB file. As it inserts it there is a +600MB memory usage...

Retrieve ID of deleted Database row in C#

I have a FormView through which I set up the default new, update and delete commands for communicating with an SQL Server database. When the CREATE command is executed the data is added to the database and some images are uploaded to a server and linked to the database. Problem When I press the Visual Studio's default FormView DELETE ...

Excel dump takes long time.

So what I have is an asp page that allows my user to hit a button and it will generate a dump of data from our sql server containing about 100k rows and 23 columns to an excel file. This takes about 10+ minuets. Should it be taking that long? ...

Creating XSDs in Bulk

I have about 45 XML data files and I need to create XSDs for them. I have Visual Studio 2008 installed. What I am doing right now is opening up each XML file and creating the XSD from the menu option and then saving the XSD. Is there a command line option for me to create all XSDs? If not, is there a bulk XSD generation tool that can ...

How to force LINQ to SQL to pass .NET DateTime as datetime2?

I have a simple query in LINQ to SQL: var id = 23479824; var date = Changes.Where(ch => ch.Id == id).First().Date; var diff = Changes.Where(ch => ch.Id == id).Select(ch => SqlMethods.DateDiffNanosecond(date, ch.Date)).First(); The diff variable should be zero, however is not. The generated SQL is following: DECLARE @p0 DateTime = '20...

are there other non-printable characters in SSIS

From within SSIS, {t}, {CR}, {LF}, and {CR}{LF} are listed in the dropdown of row and column delimiters. I was wondering if there are other nomenclatures using the braces notation for non-printable characters? I've tried the following without success: {bell} - bell {b} - backspace {^^} - column delimiter control character ...

inserting date into sql script

i am doing sql server backups like this in script: BACKUP DATABASE databasename TO DISK = `path' since this backup is going to be automated, how do i insert today's date into 'path' ? ...

SQL Inserting multilingual data - loses diacritic marks etc

Inserting multilingual data into a SQL 2008 database (nvarchar field) I notice that it seems to lose some special character marks. e.g. INSERT INTO [dbName].[dbo].[tbl_Question_i18n] ([QuestionId] ,[LanguageId] ,[QuestionText]) VALUES (@lastinsertedquestionid ...

Creating T-SQL Summary Rows

I have data in a table that looks like the following sample data: varchar(20) | DateTime | varchar(20) |varchar(255) _Serial_Number__|_Date_Time______________|_System_ID___|_Test_Result________________ C035993 0703 05 |2005-08-18 13:43:33.717 |VTI-Chamber1 | (BLUE) TEST ABORTED, LEAKFP SPUN DOWN C035993 0702 05 |2005-0...

how do i check under which username a script is run?

i have this script: BACKUP DATABASE timeclockplus TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\backup.bak' how do i know which username is executing it? the reason i ask is because i want to grant extra privelages to the current username so that instead of writing that backup folder i can write to a different ...

sql script returns UPDATE SYSDEVICES

I'm running this statement: declare @path varchar(255), @mydb varchar(50) SELECT @mydb = 'timeclockplus' select @path = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\' + @mydb + '-' + convert(varchar(8),getdate(),112) + '.bak' BACKUP DATABASE @mydb TO @path and getting this error: Msg 3206, Level 16, State 1, Line 4 No ...

Is there a way to bulk insert into two tables with FK from one to the other?

I'll give a pseudocode example of my current method and if anyone knows of a method that doesn't work one row at a time, I'd be quite appreciative. I'm using MS SQLServer 2008 define cursor for the data to be inserted (about 3 million records) loop ( insert record into table 1 use scope_identity() to get key insert record into tab...

[SQL] duplicate row -- select all columns except one

Hi all, I know this question could have passed a few times here but I haven't really found a workaround whatsoever. Also could my question be a little different. Situation is: I have a table with 130 columns (+100.000 rows), and the number of columns will even increase in the future. For this reason I would like to define the selection...

Cube Design Question ( Star Vs Constellation Schema)

Here are the two designs I have : (I'm using SQL Server 2005) Constellation Schema : http://img84.imageshack.us/img84/7206/constellation.jpg Star Schema : http://img692.imageshack.us/img692/971/starme.jpg I need to build a report which shows ProjectID,ProjectName,Hours,isImpacted,isRequesting for a given line of business(LO...

Sql Server - Constraint - Allow to set column A only if column B is null and vice-versa

Is there a way to add a constraint in SQL Server 2008 that will verify that if a user try to enter a value in a column A, it can only if column B is null and vice-versa Example if A is NULL, B can have a value if B iS NULL, A can have a value A and B can't have value at the same time ...

sql server 2008 r2 database downgrade

Is there a way to downgrade a database, that has been created on SQL Server 2008 R2, to work on an older version of SQL Server 2008 (RTM/SP1). ...