sql-server

SQL Insert Procedure

Why isn't my insert procedure returning the ID of the newly inserted row? AND, when calling this procedure, why do I have to supply a value for @EventId? That column is a PK with IDENTITY. IF OBJECT_ID ( 'vjsql.EventsINSERT', 'P') IS NOT NULL DROP PROCEDURE EventsINSERT GO CREATE PROCEDURE EventsINSERT @EventId int OUTP...

Sql server - how to insert single row into temporary table?

Hello, I have two temporary table, when i do cycle through one table and i get some values from it, in this cycle I need insert new row into another temporary table. Is this possible. Here is my sql code and error information: Alter PROCEDURE ProfitReportQ_Search_WithSub (@DateFrom datetime, @DateTo datetime, @DateActive bit, @User...

SQL Server add job agent T-SQL script issue

Hello everyone, I am using SQL Server 2008 Enterprise. I have created a very simple test SQL Server Agent job which just print out a string. And then I add a schedule to let the job run once a day. Then I select from SQL Server Management Studio => Jobs => Select the test job => Script Job as => Drop and Create To, here is the generate...

get iis server address when inserting data in sql sever 2005

Hello, When a user on my site registers and his data is inserted into a table on sql server 2005 I need to know from which iis server the user arrived - not the users ip address. (there are 2 different server addresses under a server in iis from which the data could have been received). I then need to get the address and insert it into...

import data from one table to another table

Hello everyone, I am using SQL Server 2008 Enterprise. I need to import all data from Server/Instance "Server Foo", Database "Foo" and table "Foo", into destination Server/Instance "Server Goo", Database "Goo" and table "Goo". Table Foo and Table Goo are of the same schema. If the same row exists for table Goo, I want to keep the origin...

SQL Server 2005 native stored procedures

Hello, Can i write a C or native C++ stored procedures for SQL Server? Thanks. ...

SQL Server diff tool

Working on a team where people are prone to amending dev SQL Server tables and forgetting about it, or preparing a change for deployment and having to wait for that deployment. This leaves our dev and live tables inconsistent, causing problems when SPROCs are pushed live. Is there a tool whereby I can enter a SPROC name and have it che...

Package for presenting OLAP data

Hi, I'm managing a very large data warehouse (>1 TB) based on MS SQL 2005. I would like to create a solution for the users to present data from the cubes on-line (web interface). Right now they are using Office Web Components, but it's not so good. I don't have any controls over the reports that they create. I googled, and there are so...

OLEDB comparison problem nvarchar against ntext (SQLServer 2005)

I have table Tbl1( SomeName nvarchar(64) ) Over OLEDB I'm trying to select SELECT 1 FROM Tbl1 WHERE SomeName = ? binding 3 character unicode as parameter causes: DB_E_ERRORSINCOMMAND(0x80040E14L) "The data types nvarchar and ntext are incompatible in the equal to operator" I have already tried following input bindings: 1) ... cu...

Unable to connect to SQL2005 using VBScript

I have the following VBScript, which is supposed to connect to a SQL Server 2005 database. But, my connection is failing. Why? Set dbConnection = CreateObject("ADODB.Connection") dbConnString = "Provider=SQLOLEDB.1;Data Source=srv\test1;" & _ "Initial Catalog=tset_DB;user id ='abc';password='abc'" 'Open the connection d...

Is it safe to convert varchar and char into nvarchar and nchar in SQL Server?

We currently have a number of columns in the database which are of type varchar. The application that uses them are in C# and it uses Linq2Sql for the communication (or what to call it). We would like to support unicode characters, which means we would have to convert the varchar columns into nvarchar. Is this a safe operation? Is it j...

When would you ever set the increment value on a database identity field?

Given the table: CREATE TABLE Table1 ( UniqueID int IDENTITY(1,1) ...etc ) Now why would you ever set the increment to something other than 1? I can understand setting the initial seed value differently. For example if, say, you're creating one database table per month of data (e.g. Table1_082009, Table1_092009) and want to s...

How to modify multiple nodes using SQL XQuery in MS SQL 2005

In an table I have the following. ParameterID (int) ParameterValue (XML) ------------ -------------- 1 <USER><User ID="1" Name="Billy"/><USER> <USER><User ID="2" Name="Billy"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER> 2 <USER><User ID="1" Name="John"/><USER> ...

What does the ISPALUSER function call in the Msmerge_*_VIEW views do?

I'm trying to understand how SQL Server 2005 replication works, and I'm looking at the views titled Msmerge_[Publication]_[Table]_VIEW. These views seems to define the merge filters, and are pretty straight forward, except for one line of sql in the WHERE clause: AND ({fn ISPALUSER('1A381615-B57D-4915-BA4B-E16BF7A9AC58')} = 1) What do...

Convert value into a URL friendly format - Unicode decomposition ähhh..

I need to convert a value "Convert value into a a URL friendly format - Unicode decomposition ähhh" into "convert-value-into-a-url-friendly-format-unicode-decomposition-ahhh". Is this possible in SQL-Server? All Unicode - Characters should be handled. I use SQL-Server 2005, 2008 as an option. EDIT Bogdan had a solution that worked for...

Fastest way for this query (What is the best strategy) given a date range

I have a table A that has a startDate and an end dateDate as 2 datetime columns besides some more other columns. I have another table B that has one datetime column call it dates column. This is in SQL Server 2005. Here the question: How to best set up the indexes etc to get the following: select .... from A , B where A.startDate >= B...

SQL Server : Join

Hi All, First two column is from one set of query statements and the remaining from other set. I want to display the values in a single row. Can anybody help me A B C D NULL NULL 0 22 0 699 NULL NULL SELECT statement: SELECT P.A, P.B, T2.C, T2.D FROM Table1 AS P JOIN ( SELECT MAX(ID) ID, COU...

ActiveRecord primary key is a varchar, error when saving

I have an active record class class Service_Catalogue < ActiveRecord::Base set_table_name "service_catalogue" set_primary_key "myKey" end myKey is an nvarchar (sql server). When I try and save it service_catalogue= Service_Catalogue.new() service_catalogue.myKey = "somevalue" service_catalogue.save I get the fol...

Unknown cause of linked server error

We have an old Windows 2000 server running SQL Server 2000 Standard Edition, SP3. This has a linked server setup to connect to an Informix DB via an ODBC connection. For most of the year, running OPENQUERY against the linked server works fine without any problem. However, every few months this linked server fails, and does not work again...

How can I automatically prepend comment blocks to SQL Server stored procedures?

In my organization, the standard is to comment all stored procedures with a comment block that looks like this: /*-- ============================================= -- Created by: Chris McCall -- Created date: 08.05.2009 -- Purpose: Inserts new setting value, code and description -- Modifications: -- <Date> <Programmer> <Change> -- ======...