|
Because I am trying to insert into the Table variable using dynamic sql, its not doing, can you please help me how can I insert into the Table variable within dynamic sql without using the User Defined Type? Thanks in advance buddy.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: @Results Results READONLY
There's a slight clue in that line to suggest that you can't write to the table valued parameter. 
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
If you need to write to the table, use a temp table instead. It looks like you at least started trying to do that - one of the commented-out lines refers to #results instead of @results.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
hi Rick, yes that's true I did achieve by using the Temp Table but I am always puzzled if I have any way to insert using table variable rather than temp table, because I don't like to drop, hence like to use Table variable which will be lost after the scope.
Any other way I can insert into table variable within dynamic sql?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
If you pass a table valued parameter around, you can't modify the contents. You can only modify it in the procedure where it's declared.
A temp table is definitely the way to go, and there's no danger in dropping it. Each active connection gets its own set of temp tables, so there's no chance of the code executing on one connection affecting a temp table from another connection.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi Everyone, so i have a problem with my MySQL database, whenever i try to import the database i get an error that says
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
I was running a site and using VestaCP i wanted to reinstall my server, so i simply exported the database from PHPmyAdmin and then reinstalled the whole server.
Then again i installed VestaCP and now when i am trying to import the Database i get the following error
ERROR 1064 (42000) at line 31734: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''C:24:"WPSEO_Sitemap_Cache_Data":152414:{a:2:{s:6:"status";s:2:"ok";s:3:"xml";s:' at line 2
It is a wordpress database, i really have no clue as to whats happening. If you guys can help me out i'd appreciate it.
I read some articles on Google, and i think its related to the Mysql or PHp versions. But i couldn't find answers or threads specifically for this issue. So please help me out ASAP because my website is down from days. Thanks
|
|
|
|
|
Can you not post the full SQL? But I suggest you look at the second of the first pair of apostrophe's, which on the face of it should either not be there or be escaped.
|
|
|
|
|
You are taking about these
''C:24 apostrophes? i just have to remove this from the sql file and upload it?
I cant post the full sql file because its 1 GB and very very large.
|
|
|
|
|
Quote: just have to remove this from the sql file
Not necessarily, it depends on what comes before it - if those are just two apostrophe's on their own in the middle of a longer statement they're fine, and the problem lies elsewhere. BUT you shouldn't really be posting strings in an SQL statement anyway - it's a security risk; look into paramatised queries instead And they are far less prone to syntax errors
|
|
|
|
|
Member 13755227 wrote: I cant post the full sql file because its 1 GB and very very large.
1 GB? 
I thought the limit is about 1 Mb...
|
|
|
|
|
What limit are you talking about? A 1 meg database would be pretty small these days.
|
|
|
|
|
jschell wrote: What limit are you talking about? A 1 meg database would be pretty small these days.
The SQL file. I meant a limit for SQL query, not a limit of the database itself.
If I incorrectly understood the OP then sorry!
|
|
|
|
|
Member 13755227 wrote: at line 2
So right at the top of the file.
The error message certainly seems to suggests that the imported file is NOT sql.
If you export a database using process X then you must import it using process X also. Because the export and import can use a format specific to that process and not a general one.
|
|
|
|
|
Hello All,
I have a SSIS package wherein the first step is excution of C# program to convert a csv file to excel file. The second step is the loading of this excel file into a table.
This programs with no errors when execute from IDE.
Now, i am setting the process as SQL server agent Job, and i give the path to the package for execution. The job fails with message that "Access is denied".
I moved the package to C:\ drive. Now the program fails on first step with null reference.
why the difference in results when executed from SSIS package environment versus the SQL server agent job?
I did resolve this issue by removing the C# program execution and set it up as scheduled task, but would like to understand the behaviour.
Many thanks
|
|
|
|
|
Sorry I can't help with the problem, but I am intriqued as to why you would convert a CSV to "excel" just to load it to a table... the step is totally unnecessary unless you need the xlsx file for something else as well.
|
|
|
|
|
There is one field in the csv file, a freeform field. The users have ability to type anything in this field, along with special characters. Since there was no common delimiter, had to go with converting the csv file to excel file route to load this file using SSIS.
|
|
|
|
|
So it would be surrounded by quotes in the csv.. I stand by my comment
|
|
|
|
|
You should specify UNC paths when you're loading files in your packages. That way, it will work no matter where you put the file (as long as you're using the correct UNC path of course).
BTW, you can write a script task in a package that will import that data (as opposed to running an external application).
I wrote an article not to long ago that includes code to import excel and CSV files - SQLXAgent - Jobs for SQL Express - Part 3 of 6[^]
I think you might find it useful. I'm currently extracting that code into it's own assembly for easier inclusion into projects like yours. It even goes to some length to determine the data type represented in each column.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Hi,
I have taken a backup of Database A from Server A1 then I restored this Database with name B on Server B1, now the application is working fine with Database A on Server A1 but not working with Database B on Server B1, I have checked the users, I have the users and I have given access to the users when it didn't work I have dropped the User which is connecting from Web application and recreated that user and granted the same permissions it had previously on this Database but still I am not able to use the application.
Here is the error that I am getting:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Any help would be greatly helpful thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
It's nothing to do with the database - your code can't connect to SQL Server.
- Check the name and instance name in your connection string.
- Check that SQL Server on server B1 is configured to allow remote connections.
- If you're using the Express version, check that the SQL Browser service is running on server B1.
- Using the SQL Server Configuration Manager on server B1, check under "SQL Server Network Configuration" that there is at least one enabled protocol;
- Using the SQL Server Configuration Manager on the client, check under "SQL Native Client Configuration" (both 64- and 32-bit) that at least one of the server protocols from B1 is enabled;
- Check any firewalls between the client and the server;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yeah that's true I messed up connection string, thanks my friend.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello All,
We have job set up on a newly built server, windows 2012, with sql server 2012.
We have a job set up to run everyday. The first day the job ran look 7 hours. Slowly, over one months time it has creeped up to 8 hours now.
We are puzzled as to why this steady increase in time. WE have confirmed with our network folks no other job/process is running on the viritual server at the same time.
Looked at the event viewer, nothing to report. Is there any other place i could view the logs to get a better insight?
thanks!
|
|
|
|
|
VK19 wrote: Looked at the event viewer, nothing to report. Is there any other place i could view the logs to get a better insight?
Perhaps, in SQL Server Logs?
|
|
|
|
|
Would the logs files be in the following location:
\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log
|
|
|
|
|
It seems to be correct.
My Logs are in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log
Alternatively you can see the log in SSMS under Object Explorer -> Management -> SQL Server Logs
|
|
|
|
|
What is the nature of this job? Does it create records ? Or read the data? How big is this database ? How are many disks are installed on the server? How are they being used by the database engine? Are the Operating System and database files on different disks?
On the database server from a command prompt type "perfmon" to bring up the Performance Monitoring Utility. You want to add a counter for Logical disk / Avg Disk Queue Length (All disks) Look at the graph while the job is running, if you see high activity on one disk and low activity on another disk, you may want to consider moving some DB files around.
Here are a couple of things to consider:
1) Count the number of rows in the tables involved. Check these values daily, weekly, monthly.
2) Identify the tables that are growing.
3) Are there indexes on these tables? Check the fragmentation of the indexes. Possible create new indexes or revise existing ones.
NOTE: Be careful when adding indexes, it may help your one job, but have a negative impact on the rest of the system.
With the limited information given, I'm giving you some basic areas to investigate. I'll be glad to help you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
