SQL Server / 2 select in the same Stored procedure
Here's what I'm trying do to in a single SQL Server procedure: @ID1 int select ID2 from TableA where ID1 = @ID1 (for each selected @ID2) select * from TableB where ID2 = @ID2 Any ideas? ...
sql-server
Can I edit an office document directly from SQL Server?
Hi, I would like to build a Shared Documents Library in ASP .Net and SQL Server but I have a problem, the customer wants to open the documents (stored in SQL Server) in Office UI and when save it, the document should be saved directly in database like SharePoint Server. I've been researched on web since last week and I didn't found any...
VS2010 DB Designer
Trying to use Database Designer in VS2010 for the first time. I started adding Tables/ Columns to the Schema View and was able to successfully deploy the database to SQL server 2008 R2 on a server running windows 2008R2. Then I discovered DB designer which seemed like a better & quicker way to create my database. In server View I clic...
trigger in sql server
can we create more than on insert trigger on a single table? if yes then how they execute and what exactly idea about for,After trigger and when they construct? if no then why? ...
SQL Server joins, groups, and etc. to do simple number crunching
Here's what I'm working with (SQL Server): departments: deptID, deptName students: studID, studName, deptID assignment: studID, courseID, status Students are assigned to a department and the student record holds the department's ID number. The "assignment" is a link between a student and a course (course not shown) that holds a statu...
Group by Week of year (Week number) in Linq to SQL
In regular SQL i could do something like SELECT * From T GROUP BY DATEPART(wk, T.Date) How can i do that in Linq to SQL ? The following don't work From F In DB.T Group R By DatePart(DateInterval.WeekOfYear, F.Date) Also don't work: From F In DB.T Group R By (F.Date.DayOfYear / 7) ...
Empty space at the end of SQL Server query results
I just installed Microsoft SQL Server 2008 R2 to test with LINQ to SQL. I have a table with one column of type nchar(20) and two rows: '123' and 'Test'. If I query all the rows from that table and wrap each value with "'" I get the following: '123 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ ' ( _ is a space) 'Test _ _ _ _ _ _ _ _ _ _ _ _ _ _ _...
How can I store data in a vertical table without losing precision?
I would like to have a vertical table (I know, I know... it's pretty much unavoidable) that is able to store an identifying field, the value, and the original type. Obviously the value field needs to be something generic or I won't be able to store various different types of data in it (varchar, text, int, decimal, bit, etc). What sugge...
DELETE SQL with correlated subquery for table with 42 million rows?
I have a table cats with 42,795,120 rows. Apparently this is a lot of rows. So when I do: /* owner_cats is a many-to-many join table */ DELETE FROM cats WHERE cats.id_cat IN ( SELECT owner_cats.id_cat FROM owner_cats WHERE owner_cats.id_owner = 1) the query times out :( (edit: I need to increase my CommandTimeout value, default is ...
FluentNHibernate mapping settings - file storage in SQL Server database
Hello, I've read various things but I didn't see something specific, so I'm reposting. Sorry if I missed one and duplicate posted. I am storing files in a database; previously, with ADO.NET Entity Framework, I would use image type and it streams it as byte[] array. Is that the approach to do it in NHibernate with FluentNHibernate map...
SQL Identity with leading padded zeros
I have marked a column as Identity in my table create table Identitytest( number int identity(1,001) not null, value varchar(500) ) I need the identity column to be incremented as 001,002,003, etc. The database shows that it is inserting as 1,2,3, etc. How can this be done? ...
Need help for general sql stored procedure for pagination
Hi, I've created following stored procedure in my SQL server 2005 database for general pagination: USE [training] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_Pagination1] -- Add the parameters for the stored procedure here @SqlColumns VARCHAR(MAX), @SqlFriendlyColumns VARCHAR(MAX), ...
Find and replace all links in database.
I want to find and replace in all db fields which contains a link in my database?how is that possible? I have more tables but i don't know which tables :),i need to search programmatically. I need to a method,example,GetContainsaLink(dbname) it should returns me tables and which field contains a link, I have already a dictionary map o...
Rolling back the whole procedure (all statements)
How can I write the procedure in a way so that I can ROLLBACK all the INSERT, UPDATE and DELETE statements whenever ANY statement in it had an error. Please note that my procedure might and might not have statements listed in sequence. In other words, I have an INSERT statements, then some IF logic, then a select statement, then another...
What is the default username and password of my SQL Server Express?
It installed automatically with Visual Studio 2010 Ultimate. I didn't create any users for the server or ANYTHING. I'm using: string connectionString = @"Server=.\SQLEXPRESS;Database=SportsStore;Trusted_Connection=yes;"; I get error that authentication failed. So the server is being found, but my credentials are wrong. What would the...
How do I pass a UDF scalar function into a stored procedure?
Trying to put a function call that returns an int into a call for a stored procedure. Getting "Incorrect syntax near '.'." usp_Document_ReserveForGrader '98832750-142F-4623-91F7-6E43C6F1A963', 12,dbo.Workflow_FirstProgress() ...
SQL to query the maximum number of available items for a specific period (reservation system)
For a reservation system there is an inventory table and each item has a quantity (e.g. there are 20 chairs). Now the user can make a reservation for a specific period (e.g. 5 chairs for two hours "2010-11-23 15:00" - "2010-11-23 17:00"; another reservation could be for several days "2010-11-24 11:00" - "2010-11-26 14:00"). What's the b...
Calling one stored procedure from another in C#
I have error 'Error converting data type nvarchar to decimal' when call stored procedure call another stored procedure from C# as cmd = new SqlCommand("tax_Base_emp", con); cmd.CommandType = CommandType.StoredProcedure; parm1 = new SqlParameter("@emp_code", SqlDbType.BigInt); parm1.Value = emp_code; parm1.Direction = ParameterDirectio...
NHibernate group by named parameters doesn't work with repeated parameters
My query in HQL is basically: select functionA(a, :paramA), functionB(b, :paramB), functionC(c, :paramC), sum(d) from tableA groupby by functionA(a, :paramA), functionB(b, :paramB), functionC(c, :paramC) However this gets turned into SQL of select functionA(a, @param0), functionB(b, @param1), functionC(c, @param2), sum(d) from table...
how to display database records even when cell is null
hi, i'm here again to ask help regarding my problem with the view that i created. On my tblEvents there 8 records but i created the view it only displays 3 records. I'am suspecting that the view doesn't read a null value from my tblEvents. How do i fix this? SELECT dbo.tblUsers.UserID, dbo.tblUsers.UserFirstName + ' UserL...