I want to run parameterized SQL with .NET. But when I use AddWithValue() the generated command does not work. SqlCommand cmd = new SqlCommand("SELECT * FROM table WHERE table.name = @A") cmd.Parameters.AddWithValue("@A", "A string"); Generates this sql command: exec sp_executesql N'SELECT * FROM table WHERE table.name = @A',N'@A nva...
I'm attempting to rename a stored procedure in SQL Server 2008 with sp_rename system sproc. The third parameter is giving me difficult though and I keep receiving the following error: Msg 15249, Level 11, State 1, Procedure sp_rename, Line 75 Error: Explicit @objtype 'P' is unrecognized. As the message indicates I'm passing in a P fo...
We have a SQL server with many databases in it. We have customers with multiple versions of a similar app and multiple apps for a single customer. Almost all databases are tied to specific websites. How do you stay organized with your database names? Surely there is no single answer, but do you have a database naming strategy that is w...
This is the contents of my XML field in SQL: - <TestingConfig xmlns="http://tempuri.org/TestingConfig.xsd"> `- <Config>` `<ConfigId>75</ConfigId>` `<PlaceId>2</PlaceId>` `<Identifier>05</Identifier>` `<Name>TEST1</Name>` `</Config>` `- <Config>` `<ConfigId>76</ConfigId>` `<PlaceId>2</...
Lets say I have two tables tblA ( tableAID INT IDENTITY(1,1), foo VARCHAR(100)) tblB ( tableBID INTIDENTITY(1,1), tableAID INT, bar varchar(100)) tblB.tableAID is a FK to tblA. I want to insert a bunch of records (pulled from some other table in the system) into this pair of tables. I need to know what the ID from in...
I'm reading up on indexes for optimizing database performance and would like to gather best practices for various database situations in terms of applying indexes. Take a totally non-indexed database with non-trivial amount of tables and rows, what rules do you use in determining what index is applied to what column of what type to achi...
I have a table, tblDocs that has a few columns: DocName varchar(50) DocLocation int Active int DocID int All entries in the table have a DocName and DocLocation. Active and DocID are blank. What I need to do is for each row in tblDocs I need to check the value of DocLocation and based on that value I update tblDocs, setting the Acti...
Hello, I already had the SQL Server 2008 Express engine installed (just the bit that comes with VS 2008 Express). I then installed SQL Server 2005 Express followed by SQL Server 2005 Management Studio Express, all of which seemed to install OK without any errors. However, when I run SQL Server 2005 Management Studio and enter the Serve...
Hello Everyone, Let's say I have two tables one called Customers and one called CustomerAddresses. Then lets say I have a Customer object which contains a property that is of the type List<Address>. This property holds a list of all the different shipping addresses a customer may have used, so it could contain 0 - n addresses in it (j...
The Situation (Ignore this it is boring): I have reports that I created using reporting services. Some of these reports take the parameter, "Month". They enter in an integer for the month they want. Example: December = 12. In order to view the report, I am simply using the Report Viewer in visual studio. I need the month field to be a dr...
How to save query result using T-SQL in a csv file in SSMS 2008? Needs to be done programmatically. ...
I have a website that's sort of a Craiglist type app. In my Database, should I create a table called UserRoles, and assign the UserRoleID as a Foreign Key to every user created? 1 for Admin(Complete Priveledges), 2 for Moderator, 3 for Normal User, etc. Also, inside of my ASP.Net application, say I have a UserControl. Inside of that us...
This program will copy all records inside the table 1 into table 2 and also write into a text file. After it finishes copied all the records, the records will be delete make the table1 empty before new record is added. i like to enhance my code for example : like inserting code to verify if records empty or not, if got problem in copyi...
Is there a command I can run inside my SQL script so that it stops outputing information about each operation that gets run? ie I don't want to see this: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (Apparently this is in Experts-Exchange, but it's not accessible) ...
What is the best currency format to use in my database in conjunction with my ASP.NET web app. I am not sure if float or decimal is better. Please note, I will not need culture specific settings. ...
If I have this stored proc definition minus the body ALTER PROCEDURE sp_AlloctionReport(@where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS And I call like this. sp_AllocationReport "ProductCode = 'FA' AND AllocationDate = '20090112' AND VenueInfo.VenueID In(SELECT vf.VenueID FROM VenueFilters vf INNER J...
I would like to implement the conversation per business transaction pattern in a WPF application as described here. Unfortunately this application has a large existing database using SQL Server generated identity keys in all tables. I know that adding a new object to an NHibernate session will cause it to be inserted if it has an ident...
When I try to get the sum of a column from a table I get the error 'Arithmetic overflow error converting expression to data type int' because the resulting number is to big for an INT. So I tried to CAST to a BIGINT using SELECT CAST(SUM(columnname) AS BIGINT) FROM tablename but I get the same error. Any ideas what i'm doing wrong? ...
Hi, I have several tables within my database that contains nothing but "metadata". For example we have different grouptypes, contentItemTypes, languages, ect. the problem is, if you use automatic numbering then it is possible that you create gaps. The id's are used within our code so, the number is very important. Now I wonder if it i...
Is there a simple way of extracting the database model of existing DBs? I played around in the management studio and in Visio, even though I found very interesting features of data integration I failed to perform a simple export of the structure of the DB. Could anyone pls give me a hint how to perform such an export, basically I only n...